SymmetricDS 3.7.38 with MariaDB DDL failed: ALTER TABLE

[Arun Sreedharan]

Hello there,

We've been using SymmetricDS 3.7.38 to sync data from mysql and sqlanywhere. Now we've just replaced mysql(v 5.6.48) with mariadb (v 10.5.5).
The mariadb driver(v 1.1.7) that is shipped with SymmetricDS 3.7.38 failed to start the SymmetriDS application itself (shipped version not compliant with jdbc 4.2 for jdk 8 and above) so we replaced the mariadb driver to ver 2.6.2.

Now we are able to start the application but we see that every time SymmetricDS is restarted it does a DDL applied: ALTER TABLE along with drop and add of foreing keys for the base tables.

Randomly, two drop statements are executed one after the another causing a failure after which the registration of other node fails and its delayed for long.

This issue is explicitly seen after we changed the DB to MariaDB. Please suggest a workaround for this issue.

db.driver=org.mariadb.jdbc.Driver
db.url=jdbc:mariadb://localhost:3306/mydb?tinyInt1isBit=false

symmetric.log snippet:

2020-10-12 17:53:33,820 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_conflict
DROP FOREIGN KEY sym_fk_cf_2_grp_lnk
2020-10-12 17:53:33,833 WARN [startup] [AbstractLifeCycle] [main] FAILED org.eclipse.jetty.server.Server@40cb698e: java.net.BindException: Address already in use StackTraceKey [BindException:280032371]
2020-10-12 17:53:33,836 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_file_trigger_router
DROP FOREIGN KEY sym_fk_ftr_2_ftrg
2020-10-12 17:53:33,839 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_file_trigger_router
DROP FOREIGN KEY sym_fk_ftr_2_rtr
2020-10-12 17:53:33,841 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_grouplet_link
DROP FOREIGN KEY sym_fk_gpltlnk_2_gplt
2020-10-12 17:53:33,843 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_node_group_link
DROP FOREIGN KEY sym_fk_lnk_2_grp_src
2020-10-12 17:53:33,846 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_node_group_link
DROP FOREIGN KEY sym_fk_lnk_2_grp_tgt
2020-10-12 17:53:33,848 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_node_security
DROP FOREIGN KEY sym_fk_sec_2_node
2020-10-12 17:53:33,850 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL applied: ALTER TABLE sym_router
DROP FOREIGN KEY sym_fk_rt_2_grp_lnk
2020-10-12 17:53:33,901 INFO [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] There are SymmetricDS tables that needed altered
2020-10-12 17:53:33,915 WARN [ims-000] [MariaDBSymmetricDialect] [symmetric-engine-startup-0] DDL failed: ALTER TABLE sym_conflict
DROP FOREIGN KEY sym_fk_cf_2_grp_lnk
2020-10-12 17:53:33,916 WARN [ims-000] [JdbcSqlTemplate] [symmetric-engine-startup-0] (conn=112) Can't DROP FOREIGN KEY sym_fk_cf_2_grp_lnk; check that it exists. Failed to execute: ALTER TABLE sym_conflict
DROP FOREIGN KEY sym_fk_cf_2_grp_lnk
2020-10-12 17:53:33,918 ERROR [ims-000] [AbstractSymmetricEngine] [symmetric-engine-startup-0] An error occurred while starting SymmetricDS StackTraceKey.init [SqlException:1217605771]
org.jumpmind.db.sql.SqlException: (conn=112) Can't DROP FOREIGN KEY sym_fk_cf_2_grp_lnk; check that it exists
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:283)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:274)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:487)
at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:395)
at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:462)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:155)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:405)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:396)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:535)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:527)
at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:238)
at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:464)
Caused by: java.sql.SQLSyntaxErrorException: (conn=112) Can't DROP FOREIGN KEY sym_fk_cf_2_grp_lnk; check that it exists
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)
at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:501)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:410)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:395)

thanks,
arun

[Philip Marzullo]

The version of SymmetricDS that you are using is 3.7.38, which is end of life Jun 30, 2017.
The release was first released Jan 7, 2015, which means that the driver for MariaDB in that version was probably from 2014.

You need to upgrade.