0

I am trying to set up a IBM Data Replication for AS400 to MySQL. For the target MySQL, it is now the latest MySQL 8.0.20 Community Server. I have tested the JDBC connections well. However, the CDC instance is never started (by testing MySQL Jdbc connection from 8.0.9rc to 8.0.20). In Windows, the instance is added but not started. Some action is done but the instance could not be added in RHEL. However, the log error message is exactly the same.

  1. IBM InfoSphere CDC Access Server (11.4.0.2 - 11016)
  2. IBM InfoSphere Data Replication for MySQL (11.4.0.2 - 5548) Remark: both RHEL / Windows 2016 testing environments facing same results

For only 8.0.9rc to 8.0.11, it is likely trying to connection with errors returned.

  • Malformed database URL, failed to parse the main URL sections.

As the JRE / JDK of 1.8 provided IBM, I have set proper JAVA_HOME, PATH for the settings. Is there any clues? I have tried the connection strings with: (on both Windows / Linux Access Server + IIDR)

  • jdbc:mysql://127.0.0.1:3306/testdb
  • jdbc:mysql://127.0.0.1:3306/testdb?useSSL=false
  • jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf-8&useSSL=false
  • jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT

Actually, it provided the same result. I tried to type something wrong with jdbc:mysql://127.0.0.1:3306/testdb&useSSL=false

Then, it can return syntax error. Does anyone have any clues? Is it related to JDBC VS Java version compatibility issue?

Detailed Java Log Message: 36 2020-06-07 11:21:51.335 main{1} com.datamirror.ts.engine.ReplicationExecutive startup() Monitoring Statistics database error: A SQL exception has occurred. The SQL error code is '0'. The SQL state is: 08001. The error message is: |Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the main URL sections.†com.datamirror.ts.monitoragent.statistics.MonitorStatisticsPbException Monitoring Statistics database error: A SQL exception has occurred. The SQL error code is '0'. The SQL state is: 08001. The error message is: |Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the main URL sections.| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.openConnection(MonitorStatisticsDatabase.java:616)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.(MonitorStatisticsDatabase.java:129)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.monitorStatsCollectOn(MonitoringStatistics.java:162)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.(MonitoringStatistics.java:145)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.start(MonitoringStatistics.java:177)| at com.datamirror.ts.engine.ReplicationExecutive.init(ReplicationExecutive.java:430)| at com.datamirror.ts.engine.ReplicationExecutive.startup(ReplicationExecutive.java:244)| at com.datamirror.ts.commandlinetools.script.Startup.executeUtility(Startup.java:92)| at com.datamirror.ts.commandlinetools.shared.BaseUtility.entryPoint(BaseUtility.java:267)| at com.datamirror.ts.commandlinetools.script.Startup.main(Startup.java:105)|Caused by: java.sql.SQLNonTransientConnectionException Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the main URL sections.| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:108)| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)| at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:79)| at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:131)| at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:232)| at java.sql.DriverManager.getConnection(DriverManager.java:675)| at java.sql.DriverManager.getConnection(DriverManager.java:258)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.createConnection(MonitorStatisticsDatabase.java:765)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.openConnection(MonitorStatisticsDatabase.java:569)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.(MonitorStatisticsDatabase.java:129)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.monitorStatsCollectOn(MonitoringStatistics.java:162)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.(MonitoringStatistics.java:145)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.start(MonitoringStatistics.java:177)| at com.datamirror.ts.engine.ReplicationExecutive.init(ReplicationExecutive.java:430)| at com.datamirror.ts.engine.ReplicationExecutive.startup(ReplicationExecutive.java:244)| at com.datamirror.ts.commandlinetools.script.Startup.executeUtility(Startup.java:92)| at com.datamirror.ts.commandlinetools.shared.BaseUtility.entryPoint(BaseUtility.java:267)| at com.datamirror.ts.commandlinetools.script.Startup.main(Startup.java:105)|Caused by: com.mysql.cj.core.exceptions.UnableToConnectException Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the main URL sections.| at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)| at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:83)| at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)| at java.lang.reflect.Constructor.newInstance(Constructor.java:437)| at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:60)| at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:99)| at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:232)| at java.sql.DriverManager.getConnection(DriverManager.java:675)| at java.sql.DriverManager.getConnection(DriverManager.java:258)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.createConnection(MonitorStatisticsDatabase.java:765)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.openConnection(MonitorStatisticsDatabase.java:569)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.(MonitorStatisticsDatabase.java:129)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.monitorStatsCollectOn(MonitoringStatistics.java:162)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.(MonitoringStatistics.java:145)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.start(MonitoringStatistics.java:177)| at com.datamirror.ts.engine.ReplicationExecutive.init(ReplicationExecutive.java:430)| at com.datamirror.ts.engine.ReplicationExecutive.startup(ReplicationExecutive.java:244)| at com.datamirror.ts.commandlinetools.script.Startup.executeUtility(Startup.java:92)| at com.datamirror.ts.commandlinetools.shared.BaseUtility.entryPoint(BaseUtility.java:267)| at com.datamirror.ts.commandlinetools.script.Startup.main(Startup.java:105)|Caused by: com.mysql.cj.core.exceptions.WrongArgumentException Malformed database URL, failed to parse the main URL sections.| at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)| at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:83)| at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)| at java.lang.reflect.Constructor.newInstance(Constructor.java:437)| at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:60)| at com.mysql.cj.core.conf.url.ConnectionUrlParser.parseConnectionString(ConnectionUrlParser.java:140)| at com.mysql.cj.core.conf.url.ConnectionUrlParser.(ConnectionUrlParser.java:130)| at com.mysql.cj.core.conf.url.ConnectionUrlParser.parseConnectionString(ConnectionUrlParser.java:119)| at com.mysql.cj.core.conf.url.ConnectionUrl.getConnectionUrlInstance(ConnectionUrl.java:197)| at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:202)| at java.sql.DriverManager.getConnection(DriverManager.java:675)| at java.sql.DriverManager.getConnection(DriverManager.java:258)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.createConnection(MonitorStatisticsDatabase.java:765)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.openConnection(MonitorStatisticsDatabase.java:569)| at com.datamirror.ts.monitoragent.statistics.MonitorStatisticsDatabase.(MonitorStatisticsDatabase.java:129)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.monitorStatsCollectOn(MonitoringStatistics.java:162)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.(MonitoringStatistics.java:145)| at com.datamirror.ts.monitoragent.statistics.MonitoringStatistics.start(MonitoringStatistics.java:177)| at com.datamirror.ts.engine.ReplicationExecutive.init(ReplicationExecutive.java:430)| at com.datamirror.ts.engine.ReplicationExecutive.startup(ReplicationExecutive.java:244)| at com.datamirror.ts.commandlinetools.script.Startup.executeUtility(Startup.java:92)| at com.datamirror.ts.commandlinetools.shared.BaseUtility.entryPoint(BaseUtility.java:267)| at com.datamirror.ts.commandlinetools.script.Startup.main(Startup.java:105) 37 2020-06-07 11:21:51.335 main{1} com.datamirror.ts.util.TsExceptionHandler processUnhandledException() An uncaught exception has occurred: †java.lang.RuntimeException com.datamirror.ts.monitoragent.statistics.MonitorS

Willie S
  • 73
  • 6

2 Answers2

0

The Exception shown in mysql agent trace logs pointing the URL construction is not appropriate, can you please take a look at below example.

Example: jdbc:mysql://hostname:db_port/db_name?serverTimezone=UTC

The detailed steps how to configure are documented in Knowledge Center at https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_11.4.0/com.ibm.cdcdoc.mysql.doc/tasks/addanewinstance_windows.html

Please find the steps provided under community edition section, hope this will address the issue.

Thank you Sudarshan K IBM IDR CDC

sudarshan
  • 11
  • 2
  • Thanks for your reply. I have reinstalled the IIDR 11.4.02-5560 rather than 5548 to get it work. The ?serverTimezone=UTC can be ignored. However, the service is started but it is allowed to be source only. As IBM document listed, MySQL should be able to be target. Please suggest whether I have missed anything. – Willie S Jun 09 '20 at 08:35
  • Also, the MySQL Jdbc connector is only working with 8.0_18 (not 8.0_20). – Willie S Jun 09 '20 at 08:39
  • The target mySQL is just 1 node. I am thinking about the slave but it implies the master... So, what are the proper way to get it work with MySQL community. – Willie S Jun 09 '20 at 08:50
  • The target MySQL is just 1 node at the testing env. I am thinking about the SLAVE mentioned but it implies the master missing... So, what are the proper way to get it work with MySQL community. I am using the parameters: log-bin=path to binary logs log-bin-index=path to binary logs windex binlog_format=row enforce_gtid_consistency = ON gtid_mode = ON log_slave_updates = TRUE server_id = integer value of 1 or higher From: https://www.ibm.com/support/producthub/db2/docs/content/SSTRGZ_11.4.0/com.ibm.cdcdoc.mysql.doc/concepts/cfgreplication.html – Willie S Jun 09 '20 at 10:20
  • CDC for MySQL has support for MySQL as source, CDC for FlexRep has support for MySQL as target. CDC for FlexRep supports MySQL,PostgreSQL,EDB as targets through JDBC drivers which we documented the officially supported JDBC drivers at https://www.ibm.com/support/knowledgecenter/SSTRGZ_11.4.0/com.ibm.cdcdoc.cdcflexrep.doc/concepts/supportedflexrepjdbcdrivers.html. Note: IBM CDC FlexRep can also be configured to use third-party JDBC drivers. If problems arise when using third-party JDBC drivers, customers may contact IBM technical support to request assistance. Thank you Sudarshan K – sudarshan Jun 09 '20 at 15:01
0

After testing a number of JDBC driver, it is suggested to use MySQL Connector mysql-connector-java-8.0.18.jar currently. CDC is still working with JAVA 8. (IBM JAVA 9)

mysql-connector-java-8.0.20.jar is likely to work with OpenJDK 11 better.

For the connection string, it is possible be just: jdbc:mysql://hostname:db_port/db_name

The CDC itself will take some default parameter already like the timezone by the server setting.

Willie S
  • 73
  • 6