View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004090 | SymmetricDS | Bug | public | 2019-09-17 02:58 | 2022-10-06 19:46 |
| Reporter | wuzhijun092987 | Assigned To | emiller | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.10.4 | ||||
| Target Version | 3.14.1 | Fixed in Version | 3.14.1 | ||
| Summary | 0004090: Firebird ddl/schema problem when creating tables and deferring constraints | ||||
| Description | The 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 Reproduce | Create 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> | ||||
| Tags | ddl/schema, dialect: firebird | ||||
|
|
|
|
|
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? |
|
|
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. |
|
|
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. |
| 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 |