View Issue Details

IDProjectCategoryView StatusLast Update
0004161SymmetricDSBugpublic2019-12-09 17:26 Assigned Toelong  
Status closedResolutionfixed 
Product Version3.10.5 
Target Version3.10.7Fixed in Version3.10.7 
Summary0004161: MySQL ignore zeroDateTimeBehavior=convertToNull if server is set to NO_ZERO_DATE
DescriptionThe settings of db.url with zeroDateTimeBehavior=convertToNull and server set to sql_mode of NO_ZERO_DATE are not compatible. SymmetricDS will try to compare dates with 0000-00-00 to convert them, but MySQL will return a syntax error, which causes queries and triggers to break.

In a fresh installation of Ubuntu 18.04.3 Desktop and Fedora31 Workstation in VirtualBox and with addition of MySQL on both VM's provisioning of Master Node is stuck at at Installing Node banner and it just keeps on hanging, Even after refresh. I can see in MySQL DB that SymmetricDS tables get installed but the installation does not finish, even after couple of hours.
Steps To ReproduceInstall Ubuntu 18.04.3 Desktop or Fedora31 Workstation
Install Java 11 headless
Install MySQL DB
Grant correct users
Create a DB.
Install SymmetricDS 3.11.0 as new installation in /opt/symmetricds with defaults
Go to Mozzila Firefox to localhost:31415
Install Master Node with MySQL Connection String
Choose Standard2Tier
All permissions granted
Press Install button
TagsNo tags attached.


related to 0004163 closedelong MySQL ignore zeroDateTimeBehavior=convertToNull if server is set to NO_ZERO_DATE 


2019-11-23 18:56


InstallingNodeHanging.PNG (66,241 bytes)   
InstallingNodeHanging.PNG (66,241 bytes)   


2019-11-23 18:59

developer   ~0001649

Can you attach the symmetric.log file from /opt/symmetricds/logs?

2019-11-23 20:57

reporter   ~0001651

OK. I have deleted the system and currently trying to run it in Ubuntu 19.10. As soon as I have the system running I will send the requested logs

2019-11-24 11:01

reporter   ~0001652

Hi. Here are all the logs for SymmetricDS. At this point the "Installing Node" step was running 30 minutes and did not finish. This behaviour was also noticed in 3.10.5 version but not in 3.10.4 version (14,740 bytes)


2019-11-24 13:52

developer   ~0001653

In the db.url, it has the parameter of zeroDateTimeBehavior=convertToNull. On MySQL server, it has a sql-mode with NO_ZERO_DATE and/or NO_ZERO_IN_DATE. Together, these settings are not compatible. Maybe SymmetricDS should defer to MySQL server and ignore the user's parameter to convert zero dates to null, since the server already forbids zero dates.

You should decide what you want to happen for dates with zeros, like 0000-00-00, since these dates are not compatible with databases other than MySQL. Some people want to sync 0000-00-00 between two MySQL databases. Some people want to convert them to null when syncing to non-MySQL database. Some people don't want them at all and tell MySQL to prevent their use.

Since you have your MySQL server setup to forbid zero date, you should edit the /opt/symmetric-ds/engines/ and remove zeroDateTimeBehavior=convertToNull from db.url.

Related Changesets

SymmetricDS: 3.10 a02c469a

2019-11-24 12:19:21


Details Diff
0004161: MySQL ignore zeroDateTimeBehavior=convertToNull if server is
Affected Issues
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/ Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/ Diff File

Issue History

Date Modified Username Field Change
2019-11-23 18:56 New Issue
2019-11-23 18:56 File Added: InstallingNodeHanging.PNG
2019-11-23 18:59 elong Note Added: 0001649
2019-11-23 20:57 Note Added: 0001651
2019-11-24 11:01 File Added:
2019-11-24 11:01 Note Added: 0001652
2019-11-24 13:52 elong Note Added: 0001653
2019-11-24 17:13 elong Assigned To => elong
2019-11-24 17:13 elong Status new => assigned
2019-11-24 17:13 elong Product Version 3.11.0 => 3.10.5
2019-11-24 17:13 elong Fixed in Version => 3.10.7
2019-11-24 17:13 elong Target Version => 3.10.7
2019-11-24 17:13 elong Summary Creating Master Node stuck at Installing Node banner => MySQL ignore zeroDateTimeBehavior=convertToNull if server is set to NO_ZERO_DATE
2019-11-24 17:13 elong Description Updated View Revisions
2019-11-24 17:13 elong Steps to Reproduce Updated View Revisions
2019-11-24 17:13 elong Project SymmetricDS Pro => SymmetricDS
2019-11-24 17:19 elong Status assigned => resolved
2019-11-24 17:19 elong Resolution open => fixed
2019-11-24 17:20 elong Issue cloned: 0004163
2019-11-24 17:20 elong Relationship added related to 0004163
2019-11-24 18:00 admin Changeset attached => SymmetricDS 3.10 a02c469a
2019-12-09 17:26 admin Status resolved => closed