View Issue Details

IDProjectCategoryView StatusLast Update
0001660SymmetricDSBugpublic2014-06-16 20:27
Reporterandre.cardosoAssigned Tojosh-a-hicks 
PriorityurgentSeverityblockReproducibilityalways
Status closedResolutionfixed 
Product Version3.5.19 
Target Version3.6.0Fixed in Version3.6.0 
Summary0001660: Table creation generates wrong syntax when source is SQLServer
DescriptionWhen 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.
TagsNo tags attached.

Activities

chenson

2014-04-02 15:26

administrator   ~0000505

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.

andre.cardoso

2014-04-03 08:32

reporter   ~0000506

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?

Related Changesets

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).
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

Issue History

Date Modified Username Field Change
2014-04-02 15:08 andre.cardoso New Issue
2014-04-02 15:26 chenson Note Added: 0000505
2014-04-02 15:26 chenson Assigned To => chenson
2014-04-02 15:26 chenson Status new => assigned
2014-04-02 15:26 chenson Target Version => 3.6.0
2014-04-03 08:32 andre.cardoso Note Added: 0000506
2014-04-10 11:44 chenson Assigned To chenson => josh-a-hicks
2014-05-01 16:12 josh-a-hicks Assigned To josh-a-hicks => chenson
2014-05-01 16:15 josh-a-hicks Status assigned => resolved
2014-05-01 16:15 josh-a-hicks Fixed in Version => 3.6.0
2014-05-01 16:15 josh-a-hicks Resolution open => fixed
2014-05-01 16:15 josh-a-hicks Assigned To chenson => josh-a-hicks
2014-05-01 17:00 josh-a-hicks Changeset attached => SymmetricDS trunk r8228
2014-06-16 20:27 chenson Status resolved => closed
2015-07-30 21:49 Changeset attached => SymmetricDS master de8e436f