View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004083 | SymmetricDS | Bug | public | 2019-08-29 01:58 | 2025-03-26 17:29 |
| Reporter | MrKich | Assigned To | |||
| Priority | normal | ||||
| Status | closed | Resolution | no change required | ||
| Product Version | 3.10.4 | ||||
| Summary | 0004083: Auto converting date to null for not mysql db brokes synchronization | ||||
| Description | We 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 Reproduce | 1. 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 | ||||
| Tags | dialect: mysql/mariadb | ||||
|
|
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? |
|
|
Yes, I think that parameter will solve our problems. With exception that for parameter could be not only date field, but datetime field. |
|
|
If you remove zeroDateTimeBehavior=convertToNull from the db.url, it will go back to the same behavior it had in 3.10.3. |
|
|
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? |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
The workaround is to create the transform described by MrKich. |
| 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 | |
| 2025-03-26 17:29 | emiller | Status | new => closed |
| 2025-03-26 17:29 | emiller | Resolution | open => no change required |
| 2025-03-26 17:29 | emiller | Note Added: 0002743 |