View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0001660 | SymmetricDS | Bug | public | 2014-04-02 19:08 | 2014-06-17 00:27 |
Reporter | andre.cardoso | Assigned To | josh-a-hicks | ||
Priority | urgent | ||||
Status | closed | Resolution | fixed | ||
Product Version | 3.5.19 | ||||
Target Version | 3.6.0 | Fixed in Version | 3.6.0 | ||
Summary | 0001660: Table creation generates wrong syntax when source is SQLServer | ||||
Description | When using SymmetricDS to replicate an SQLServer database using initial.load.create.first=true, the table creation fails with a wrong syntax error. This is the table's original DDL generated by Hibernate Tools: CREATE TABLE BADGE_MODEL ( ID NUMERIC(19,0) IDENTITY NOT NULL, CREATED_DATE DATETIME NULL, CREATED_USER VARCHAR(255) NULL, UPDATED_DATE DATETIME NULL, UPDATED_USER VARCHAR(255) NULL, LAYOUT VARCHAR(3000) NULL, NAME VARCHAR(40) NOT NULL, PRIMARY KEY (ID) ); This is the log on a SQLServer to SQLServer initial load: 2014-04-02 14:43:40,857 INFO [branch-001] [DatabaseWriter] [qtp17555298-29] About to create table using the following definition: <?xml version="1.0"?> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"> <database name="dbo"> <table name="BADGE_MODEL"> <column name="ID" primaryKey="true" required="true" type="NUMERIC" size="19" autoIncrement="true"> <platform-column name="mssql" type="numeric() identity" size="19"/> </column> <column name="CREATED_DATE" type="TIMESTAMP" size="23,3"> <platform-column name="mssql" type="datetime" size="23" decimalDigits="3"/> </column> <column name="CREATED_USER" type="VARCHAR" size="255"> <platform-column name="mssql" type="varchar" size="255"/> </column> <column name="UPDATED_DATE" type="TIMESTAMP" size="23,3"> <platform-column name="mssql" type="datetime" size="23" decimalDigits="3"/> </column> <column name="UPDATED_USER" type="VARCHAR" size="255"> <platform-column name="mssql" type="varchar" size="255"/> </column> <column name="LAYOUT" type="VARCHAR" size="3000"> <platform-column name="mssql" type="varchar" size="3000"/> </column> <column name="NAME" required="true" type="VARCHAR" size="40"> <platform-column name="mssql" type="varchar" size="40"/> </column> </table> </database> 2014-04-02 14:43:40,863 INFO [branch-001] [MsSqlDatabasePlatform] [qtp17555298-29] Running alter sql: SET quoted_identifier on; SET quoted_identifier on; CREATE TABLE "BADGE_MODEL" ( "ID" numeric() identity(19) NOT NULL IDENTITY (1,1) , "CREATED_DATE" datetime, "CREATED_USER" varchar(255), "UPDATED_DATE" datetime, "UPDATED_USER" varchar(255), "LAYOUT" varchar(3000), "NAME" varchar(40) NOT NULL, PRIMARY KEY ("ID") ); It seems to be messing up the type configuration by concatenating the type in the platform-column tag with the size instead of putting the size inside the parentheses, and adding "IDENTITY (1,1)" at the end. On SQLServer to MySQL, the following is used to declare the primary key, which also generates a syntax error: `ID` DECIMAL(19,0) NOT NULL AUTO_INCREMENT, DECIMAL(19,0) should be BIGING in that case. We've tried having the tables already present in the database, but SymmetricDS seems to be ignoring the initial order and causing FK errors. With the table creation option, it is picking the first table in the correct order, despite the syntax error. | ||||
Tags | No tags attached. | ||||
|
We'll get the ddl fixed. Sorry about that. The <platform-column/> feature is fairly new. Regardless of whether the create tables property is on symmetricds should sent the initial load in the same order. If the initial load order on sym_trigger_router is set to 0 it will attempt to order the tables for load based on the fk dependencies. Maybe the fk references are circular or self referencing and symmetricds can figure them out? You can manually specify the order. |
|
It behaves mostly the same regardless of the order. For example, take these two triggerRouters: INSERT INTO SYM_TRIGGER_ROUTER (trigger_id, router_id, initial_load_order, create_time, last_update_time) VALUES ('profile_access', 'master-2-branch', 110, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); INSERT INTO SYM_TRIGGER_ROUTER (trigger_id, router_id, initial_load_order, create_time, last_update_time) VALUES ('profile_access', 'branch-2-master', 110, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); INSERT INTO SYM_TRIGGER_ROUTER (trigger_id, router_id, initial_load_order, create_time, last_update_time) VALUES ('profile_access_site', 'master-2-branch', 290, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); INSERT INTO SYM_TRIGGER_ROUTER (trigger_id, router_id, initial_load_order, create_time, last_update_time) VALUES ('profile_access_site', 'branch-2-master', 290, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); Despite that configuration, I'm getting FK errors in profile_access_site because the parent table is empty when it tries to load the transformation, yet I don't see any entry in the log that indicates an error or skip on profile_access. Does the order change when transformations are in effect? |
SymmetricDS: master de8e436f 2014-05-01 16:11:42 James Details Diff |
0001660: Added the ability for a ddlBuilder to filter a sql column type. MSSQL Server drivers return an identity as part of the column type so this is removed as part of column type (otherwise it appears twice in ddl). |
Affected Issues 0001660 |
|
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java | Diff File | ||
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mssql/MsSql2000DdlBuilder.java | Diff File |
Date Modified | Username | Field | Change |
---|---|---|---|
2014-04-02 19:08 | andre.cardoso | New Issue | |
2014-04-02 19:26 | chenson | Note Added: 0000505 | |
2014-04-02 19:26 | chenson | Assigned To | => chenson |
2014-04-02 19:26 | chenson | Status | new => assigned |
2014-04-02 19:26 | chenson | Target Version | => 3.6.0 |
2014-04-03 12:32 | andre.cardoso | Note Added: 0000506 | |
2014-04-10 15:44 | chenson | Assigned To | chenson => josh-a-hicks |
2014-05-01 20:12 | josh-a-hicks | Assigned To | josh-a-hicks => chenson |
2014-05-01 20:15 | josh-a-hicks | Status | assigned => resolved |
2014-05-01 20:15 | josh-a-hicks | Fixed in Version | => 3.6.0 |
2014-05-01 20:15 | josh-a-hicks | Resolution | open => fixed |
2014-05-01 20:15 | josh-a-hicks | Assigned To | chenson => josh-a-hicks |
2014-05-01 21:00 | josh-a-hicks | Changeset attached | => SymmetricDS trunk r8228 |
2014-06-17 00:27 | chenson | Status | resolved => closed |
2015-07-31 01:49 | Changeset attached | => SymmetricDS master de8e436f |