View Issue Details

IDProjectCategoryView StatusLast Update
0004083SymmetricDSBugpublic2021-03-16 12:28
ReporterMrKich Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.10.4 
Summary0004083: Auto converting date to null for not mysql db brokes synchronization
DescriptionWe use symmetricds for replication between mariadb hosts and oracle.
There is a datetime field in mysql db that allows null and a date field in oracle which does not.

Maybe it's not a good architecture but it exists.

After upgrading from 3.10.3 to 3.10.4 replication from mariadb nodes to oracle was broken as symmetric tried to insert 'null' to a non-null date field.

We have plenty of similar fields and I believe that this behavior should be tunable.
Steps To Reproduce1. Setup replication between mariadb host and oracle host.
2. Create table on maridb host with datetime field.
3. Create table on oracle host with date non-null field.
4. Insert 'null' to datetime in mariadb table
5. Error will be raised as 'null' is not allowed for date field at oracle host
Additional Information"Improvement" issue - https://www.symmetricds.org/issues/view.php?id=4065
Tagsdialect: mysql/mariadb

Activities

elong

2019-08-29 17:22

developer   ~0001596

Maybe we add a parameter of mysql.convert.zero.date.to=null and you could change it to what you need, like mysql.convert.zero.date.to=0001-01-01. Would that meet your requirements?

MrKich

2019-08-29 17:44

reporter   ~0001597

Yes, I think that parameter will solve our problems.
With exception that for parameter could be not only date field, but datetime field.

elong

2019-10-31 00:01

developer   ~0001626

If you remove zeroDateTimeBehavior=convertToNull from the db.url, it will go back to the same behavior it had in 3.10.3.

MrKich

2021-02-12 17:15

reporter   ~0001889

It seems that zeroDateTimeBehavior=convertToNull (or removing it) is for syncing to mysql and we have problem inserting null to a non-null date field to oracle.

Actually, we reverted to 3.10.3, where we don't hit that issue.
Right now we are upgrading to 3.12.6, and we have hit that problem again.

I understand that null datetime in mysql should resolve to null in oracle date field (and not to "0002-11-30 00:00:00 BC" as we had in 3.10.3).
However, we can't change database schemas easily.

Is it possible that this old odd behaviour would be supported via some flags or configuration options, or should we mitigate this using transforms?

josh-a-hicks

2021-02-17 18:33

developer   ~0001890

Yes you would need transforms to put in a date that would work with your applications. What would you expect to be in there if it was automated the minimum date that Oracle allows, current date? Removing the zeroDateTimeBehavior=convertToNull will revert to the same behavior of sending a non null date to Oracle in 3.12.6 as well.

MrKich

2021-03-02 12:57

reporter   ~0001892

I would expect the minimum date that Oracle allows, as it was in previous versions of SymmetricDS.
We don't have "zeroDateTimeBehavior=convertToNull" in db.url in any engine configurations.

MrKich

2021-03-16 12:28

reporter   ~0001897

If anyone is interested in transform to keep old behavior aka ' "0000-00-00 00:00:00" from mysql to "0002-11-30 00:00:00 BC"' in oracle or postgresql, here it is:
type: bsh
expression: "if (currentValue.equals("0000-00-00 00:00:00")) { return -62170167600000L; } else { return currentValue; }"

Negative long number is used because returning string does not apply "BC" modifier.

Issue History

Date Modified Username Field Change
2019-08-29 01:58 MrKich New Issue
2019-08-29 17:22 elong Note Added: 0001596
2019-08-29 17:44 MrKich Note Added: 0001597
2019-10-31 00:01 elong Note Added: 0001626
2019-10-31 00:01 elong Tag Attached: dialect: mysql/mariadb
2021-02-12 17:15 MrKich Note Added: 0001889
2021-02-17 18:33 josh-a-hicks Note Added: 0001890
2021-03-02 12:57 MrKich Note Added: 0001892
2021-03-16 12:28 MrKich Note Added: 0001897