View Issue Details

IDProjectCategoryView StatusLast Update
0003545SymmetricDSBugpublic2020-09-23 15:32
Reporterwoehrl01 Assigned Topmarzullo  
Prioritynormal 
Status assignedResolutionopen 
Product Version3.9.6 
Target Version3.12.5 
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 18: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 08: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 19: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 19: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 11: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 12: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. Affected Issues
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 12:54 woehrl01 New Issue
2018-10-10 18:12 hanes Status new => feedback
2018-10-10 18:12 hanes Note Added: 0001267
2018-10-11 08:38 woehrl01 Note Added: 0001270
2018-10-11 08:38 woehrl01 Status feedback => new
2018-10-17 13:22 hanes Status new => confirmed
2018-10-17 13:22 hanes Target Version => 3.10.0
2019-01-23 15:45 pmarzullo Assigned To => pmarzullo
2019-01-23 15:45 pmarzullo Status confirmed => assigned
2019-03-14 12:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 13:46 elong Target Version 3.10.1 => 3.10.2
2019-04-24 17:51 elong Tag Attached: ddl/schema
2019-05-08 12:51 admin Target Version 3.10.2 => 3.10.3
2019-05-13 17:00 Changeset attached => SymmetricDS 3.10 aadf37e3
2019-06-14 19:42 pmarzullo Status assigned => feedback
2019-06-14 19:42 pmarzullo Note Added: 0001529
2019-06-14 19:55 woehrl01 Note Added: 0001530
2019-06-14 19:55 woehrl01 Status feedback => assigned
2019-06-17 11:52 pmarzullo Status assigned => feedback
2019-06-17 11:52 pmarzullo Note Added: 0001531
2019-06-17 12:04 woehrl01 Note Added: 0001532
2019-06-17 12:04 woehrl01 Status feedback => assigned
2019-06-24 17:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 19:41 elong Target Version 3.10.4 => 3.10.5
2019-10-31 14:59 admin Target Version 3.10.5 => 3.10.6
2019-11-14 19:47 elong Target Version 3.10.6 => 3.10.7
2019-12-09 15:25 admin Target Version 3.10.7 => 3.10.8
2020-01-02 14:40 admin Target Version 3.10.8 => 3.10.9
2020-01-22 19:03 elong Target Version 3.10.9 => 3.10.10
2020-02-14 13:46 elong Target Version 3.10.10 => 3.10.11
2020-03-16 20:14 admin Target Version 3.10.11 => 3.10.12
2020-05-26 12:28 elong Target Version 3.10.12 => 3.11.10
2020-06-24 20:14 elong Target Version 3.11.10 => 3.12.2
2020-07-20 13:06 elong Target Version 3.12.2 => 3.12.3
2020-08-31 19:47 elong Target Version 3.12.3 => 3.12.4
2020-09-23 15:32 admin Target Version 3.12.4 => 3.12.5