View Issue Details

IDProjectCategoryView StatusLast Update
0004090SymmetricDSBugpublic2022-10-06 19:46
Reporterwuzhijun092987 Assigned Toemiller  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.4 
Target Version3.14.1Fixed in Version3.14.1 
Summary0004090: Firebird ddl/schema problem when creating tables and deferring constraints
DescriptionThe following configuration does not work:
# When this node sends an initial load of data to another node, first send table create scripts.
initial.load.create.first=false
Steps To ReproduceCreate the below table on Firebird either using the initial load with deferred constraints or using DB Import to load the XML twice.

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="BOM">
        <column name="BOM_ID" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="firebird" type="INTEGER" size="10"/>
        </column>
        <column name="BOM_NAME" required="true" type="VARCHAR" size="32" default="">
            <platform-column name="firebird" type="VARCHAR" size="32" default=""/>
        </column>
    </table>
</database>
Tagsddl/schema, dialect: firebird

Relationships

has duplicate 0004359 closedemiller The handling of ''(empty string) default value of varchar columns of dbexport and dbimport is not correct for mssql server 
related to 0005517 closedpmarzullo Empty string default values not syncing to target 

Activities

wuzhijun092987

2019-09-17 02:58

reporter  

hanes

2019-09-18 12:46

developer   ~0001599

Can you provide a little more information? For example, when you did your initial load, did you find that it sent down the table create batches whether you set the parameter to false or true? How did you go about initiating the initial load?

wuzhijun092987

2019-09-19 02:58

reporter   ~0001601

My previous description may have problems. The server sometimes pushes the table structure to the client. Due to database design reasons(due to the database's table, there are both foreign keys and storage procedures in use), the client can not delete the database table., When the client inserts the same table into the database again, the client returns the error message.

I thought the configuration(initial.load.create.first) was used to set up whether or not to push the table structure. Do you support the configuration related to updating the table structure between the service and the client? At present, I do not need to update the table structure. Just update the data.
symmetric.log (536,723 bytes)
symmetric-2.log (168,469 bytes)

elong

2022-08-05 16:44

developer   ~0002145

When using initial.load.create.first=true parameter or sym_table_reload.create_table=1 configuration setting, it will create the tables as part of the initial load. The initial load is queued as a series of batches that contain create statements and data statements. Once it is queued up, it has to complete or be cancelled, and changing the settings do not effect queued up loads. There are create statements at the beginning of the load that leave out the foreign key constraints and indexes. There are create/alter statements at the end of the load that add back the foreign key constraints and indexes. It does that for performance and to avoid foreign key errors from changes being made while the load runs. To just create tables with foreign keys and indexes at the beginning instead, use initial.load.defer.create.constraints=false.

In the logs attached to this issue, it is a Firebird database sending an initial load to another Firebird database. It has an error on a batch to create a table. The table already exists, but the table definition does not match the source. It thinks that alters are not possible, so it is migrating the table to a temporary table so it can drop and re-create the table with the correct definition. The only difference in the table definition that I see is default value is supposed to blank, but it thinks it is currently defined to default as null. Here is sample of SQL and XML:

CREATE TABLE "BOM"(
    "BOM_ID" INTEGER NOT NULL,
    "BOM_NAME" VARCHAR(32) DEFAULT '' NOT NULL,
)

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="BOM">
        <column name="BOM_ID" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="firebird" type="INTEGER" size="10"/>
        </column>
        <column name="BOM_NAME" required="true" type="VARCHAR" size="32" default="">
            <platform-column name="firebird" type="VARCHAR" size="32" default=""/>
        </column>
    </table>
</database>

So, this looks like a DDL/schema bug on Firebird. The workaround might be to either use initial.load.defer.create.constraints=false OR create.table.without.defaults=true.

Related Changesets

SymmetricDS: 3.14 2f80b6c9

2022-08-18 17:15:54

evan-miller-jumpmind

Details Diff
0004090: Fixed empty string default values not syncing to target Affected Issues
0004090
mod - symmetric-db/src/main/java/org/jumpmind/db/io/DatabaseXmlUtil.java Diff File

Issue History

Date Modified Username Field Change
2019-09-17 02:58 wuzhijun092987 New Issue
2019-09-17 02:58 wuzhijun092987 File Added: sunserver-000.properties
2019-09-17 02:58 wuzhijun092987 Tag Attached: configuration
2019-09-18 12:46 hanes Assigned To => hanes
2019-09-18 12:46 hanes Status new => feedback
2019-09-18 12:46 hanes Note Added: 0001599
2019-09-18 12:46 hanes Assigned To hanes =>
2019-09-18 12:47 hanes Summary Relevant configuration information does not work => Relevant configuration information does not work (initial.load.create.first=false)
2019-09-19 02:58 wuzhijun092987 File Added: symmetric.log
2019-09-19 02:58 wuzhijun092987 File Added: symmetric-2.log
2019-09-19 02:58 wuzhijun092987 Note Added: 0001601
2019-09-19 02:58 wuzhijun092987 Status feedback => new
2022-08-05 16:44 elong Note Added: 0002145
2022-08-05 16:44 elong Tag Detached: configuration
2022-08-05 16:44 elong Tag Attached: ddl/schema
2022-08-05 16:45 elong Tag Attached: dialect: firebird
2022-08-05 16:45 elong Summary Relevant configuration information does not work (initial.load.create.first=false) => Firebird ddl/schema problem when creating tables and deferring constraints
2022-08-05 16:47 elong Steps to Reproduce Updated View Revisions
2022-08-18 17:35 emiller Assigned To => emiller
2022-08-18 17:35 emiller Status new => resolved
2022-08-18 17:35 emiller Resolution open => fixed
2022-08-18 17:35 emiller Fixed in Version => 3.14.1
2022-08-18 18:00 Changeset attached => SymmetricDS 3.14 2f80b6c9
2022-08-30 13:04 admin Status resolved => closed
2022-09-08 19:34 emiller Relationship added has duplicate 0004359
2022-10-06 19:46 pmarzullo Relationship added related to 0005517