View Issue Details

IDProjectCategoryView StatusLast Update
0003545SymmetricDSBugpublic2019-08-09 15:41
Reporterwoehrl01Assigned Topmarzullo 
Prioritynormal 
Status assignedResolutionopen 
Product Version3.9.6 
Target Version3.10.5Fixed in Version 
Summary0003545: Mapped default values lead to always detecting a model change
DescriptionIf you have a default value which gets mapped to a different value on a different dialect you always get a model change detection.
Tagsddl/schema

Activities

hanes

2018-10-10 14:12

developer   ~0001267

Can you perhaps provide an example that would allow us to reproduce this issue? For example, source database platform, target database platform, and table definitions.

woehrl01

2018-10-11 04:38

reporter   ~0001270

I commented about a reproduction here: https://github.com/JumpMind/symmetric-ds/pull/80

You can simply check this if you have Oracle Database with SYSTIMESTAMP or SYSDATE default on the source and a Sqlite Database on the client.

e.g:

create table employee_history
        ( employee_id number(6) not null,
          hire_date date default sysdate
        )

pmarzullo

2019-06-14 15:42

developer   ~0001529

I have attempted to reproduce this issue with the SymmetricDS base code.
I have not been able to reproduce it.

I used MSSql as the source, and SQLite as the target.

MSSql had a table built as:
create table employee_history
        ( employee_id integer not null,
          hire_date date default GETUTCDATE()
        )

I also attempted with the MSSql table built as:
create table employee_history
        ( employee_id integer not null,
          hire_date date default sysdatetime()
        )

Can you specify exactly a case that reproduces this issue?

woehrl01

2019-06-14 15:55

reporter   ~0001530

Hi pmarzullo,

of course. You can't reproduce it with Microsoft Sqlserver as my PR#14 is not merged yet. Please use Oracle as suggested in the comments above.

Best

pmarzullo

2019-06-17 07:52

developer   ~0001531

I attempted to reproduce the issue using the following:
Server:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SymmetricDS 3.10.* (latest code)

Client: SQLite JDBC 3.25.2
Same SymmetricDS instance

Server table:
create table employee_history
( employee_id number(6) not null,
     hire_date date default sysdate
);

Manage -> Nodes -> Send Schema
employee_history schema sent to client

Successfully creates table:
2019-06-17 07:41:33,258 INFO [client] [DefaultDatabaseWriter] [client-dataloader-2] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="EMPLOYEE_HISTORY">
        <column name="EMPLOYEE_ID" required="true" type="NUMERIC" size="6">
            <platform-column name="oracle" type="NUMBER" size="6"/>
        </column>
        <column name="HIRE_DATE" type="TIMESTAMP" size="7" default="sysdate">
            <platform-column name="oracle" type="DATE" size="7" default="sysdate"/>
        </column>
    </table>
</database>
2019-06-17 07:41:33,265 INFO [client] [SqliteDatabasePlatform] [client-dataloader-2] Running alter sql:
CREATE TABLE "employee_history"(
    "employee_id" NUMERIC NOT NULL,
    "hire_date" TIMESTAMP DEFAULT (CURRENT_DATE)
);

2019-06-17 07:41:33,307 INFO [client] [PullService] [client-job-10] Pull data received from server:server:server on queue default. 1 rows and 1 batches were processed. (employee_history)

Manage -> Nodes -> Send Schema
employee_history schema sent to client

2019-06-17 07:42:03,540 INFO [client] [DefaultDatabaseWriter] [client-dataloader-3] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="EMPLOYEE_HISTORY">
        <column name="EMPLOYEE_ID" required="true" type="NUMERIC" size="6">
            <platform-column name="oracle" type="NUMBER" size="6"/>
        </column>
        <column name="HIRE_DATE" type="TIMESTAMP" size="7" default="sysdate">
            <platform-column name="oracle" type="DATE" size="7" default="sysdate"/>
        </column>
    </table>
</database>
2019-06-17 07:42:03,541 INFO [client] [SqliteDatabasePlatform] [client-dataloader-3] Tables up to date. No alters found for employee_history

woehrl01

2019-06-17 08:04

reporter   ~0001532

The column type TIMESTAMP does not work in your example as it will throw an IllegalArgumentException in symmetric-ds/symmetric-db/src/main/java/org/jumpmind/db/model/Column.java:getParsedDefaultValue() which results in comparing NULL with NULL of both columns (which is a probably a bug, too), inside the ModelComparator.java.

Please use a DATETIME column instead.

Related Changesets

SymmetricDS: 3.10 aadf37e3

2019-05-13 12:40:51

Philip Marzullo

Details Diff
0003545: Overridden method.
0003545
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2018-05-04 08:54 woehrl01 New Issue
2018-10-10 14:12 hanes Status new => feedback
2018-10-10 14:12 hanes Note Added: 0001267
2018-10-11 04:38 woehrl01 Note Added: 0001270
2018-10-11 04:38 woehrl01 Status feedback => new
2018-10-17 09:22 hanes Status new => confirmed
2018-10-17 09:22 hanes Target Version => 3.10.0
2019-01-23 10:45 pmarzullo Assigned To => pmarzullo
2019-01-23 10:45 pmarzullo Status confirmed => assigned
2019-03-14 08:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 09:46 elong Target Version 3.10.1 => 3.10.2
2019-04-24 13:51 elong Tag Attached: ddl/schema
2019-05-08 08:51 admin Target Version 3.10.2 => 3.10.3
2019-05-13 13:00 Changeset attached => SymmetricDS 3.10 aadf37e3
2019-06-14 15:42 pmarzullo Status assigned => feedback
2019-06-14 15:42 pmarzullo Note Added: 0001529
2019-06-14 15:55 woehrl01 Note Added: 0001530
2019-06-14 15:55 woehrl01 Status feedback => assigned
2019-06-17 07:52 pmarzullo Status assigned => feedback
2019-06-17 07:52 pmarzullo Note Added: 0001531
2019-06-17 08:04 woehrl01 Note Added: 0001532
2019-06-17 08:04 woehrl01 Status feedback => assigned
2019-06-24 13:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 15:41 elong Target Version 3.10.4 => 3.10.5