Connection to MySQL can fail if the server time zone is not recognized by the JDBC driver

[aadant]

debezium-embedded_1  | 	at com.altinity.clickhouse.debezium.embedded.ClickHouseDebeziumEmbeddedApplication.start(ClickHouseDebeziumEmbeddedApplication.java:204)
debezium-embedded_1  | 	at com.altinity.clickhouse.debezium.embedded.ClickHouseDebeziumEmbeddedApplication.main(ClickHouseDebeziumEmbeddedApplication.java:174)
debezium-embedded_1  | Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'CDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'connectionTimeZone' configuration property) to use a more specific time zone value if you want to utilize time zone support.
debezium-embedded_1  | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
debezium-embedded_1  | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
debezium-embedded_1  | 	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
debezium-embedded_1  | 	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
debezium-embedded_1  | 	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
debezium-embedded_1  | 	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
debezium-embedded_1  | 	at com.mysql.cj.util.TimeUtil.getCanonicalTimeZone(TimeUtil.java:178)
debezium-embedded_1  | 	at com.mysql.cj.protocol.a.NativeServerSession.getSessionTimeZone(NativeServerSession.java:353)
debezium-embedded_1  | 	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:686)
debezium-embedded_1  | 	... 52 more
debezium-embedded_1  | 100805 2023-10-17 20:24:32.829 [pool-12-thread-1] INFO io.debezium.jdbc.JdbcConnection  - Connection gracefully closed

Workaround :

set the time_zone on the source server to a time zone that JDBC understands and restart the server

time_zone=America/Chicago

We should be able to specific the JDBC properties in the config.yml not to touch the source and not restart it

database.jdbc.properties: "useLegacyDatetimeCode=false&serverTimezone=America/Chicago"

[subkanthi]

https://stackoverflow.com/questions/67340500/kafka-connect-mysql-server-time-zone-value-edt-is-unrecognized

[ne1r0n]

Try to specify the following setting in config, at least that works in not embedded debezium

database.connectionTimeZone: "America/Chicago"

[aadant]

Cool, will try out. If it works, we should add it to the default config (even commented)

[subkanthi]

Thanks @ne1r0n , yes it seems to work.

Setting the environment variable for mysql in docker-compose.yml

- TZ = America/Chicago

Checked mysql

mysql> show variables like '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CDT    |
| time_zone        | SYSTEM |
+------------------+--------+

And setting this variable in config.yml

database.connectionTimeZone: "America/Chicago"