Kafka Connect MySQL "server time zone value 'EDT' is unrecognized"

Question

I'm new to Kafka but had Debezium Connect/Mysql up and running in Docker just fine. All of a sudden all docker containers were gone and upon restart and attempt to reconnect to MySQL the JDBC connection fails with this response to the Connect rest API:

Using ZOOKEEPER_CONNECT=0.0.0.0:2181
Using KAFKA_LISTENERS=PLAINTEXT://172.19.0.5:9092 and KAFKA_ADVERTISED_LISTENERS=PLAINTEXT://172.19.0.5:9092
HTTP/1.1 400 Bad Request
Date: Fri, 30 Apr 2021 20:39:56 GMT
Content-Type: application/json
Content-Length: 
Server: Jetty(9.4.33.v20201020)
                    
{"error_code":400,"message":"Connector configuration is invalid  
            and contains the following 1 error(s): \nUnable to connect: The server time  
            zone value 'EDT' is unrecognized or represents more than one time zone. You  
            must configure either the server or JDBC driver (via the 'serverTimezone'   
    configuration property) to use a more specifc time zone value if you want  
            to utilize time zone support.\nYou can also find the above list of errors  
            at the endpoint `/connector-plugins/{connectorType}/config/validate`"}

in response to running this:

docker run -it --rm --net mynet_default debezium/kafka \
curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" \
mynet_connect_1:8083/connectors/ \
-d '{ 
"name": "my-connector-001",
"config": { 
"connector.class": "io.debezium.connector.mysql.MySqlConnector", 
"tasks.max": "1", 
"database.hostname": "my.domain.com", 
"database.port": "3306",
"database.user": "myuser",
"database.password": "mypassword", 
"database.server.id": "6400", 
"database.server.name": "dbserver001",
"database.include.list": "mydb", 
"database.history.kafka.bootstrap.servers": "kafka:9092", 
"database.history.kafka.topic": "dbhistory.metrics.connector004",
"table.include.list":"mydb.users,mydb.applications"

} }'

This worked fine for a couple hours. Then as I was watching updates mostly straight from the Debezium tutorial, all the kafka containers were gone and then ever since (with the exact same config) will no longer connect, citing the timezone thing. I can connect with the same credentials in the mysql client (via the docker network) and the MySQL permissions and grants did not change. There's a Gitter mention last July that this error is itself an erroneous indication of some other connection failure. And there are multiple reports of it being a bug in JDBC. Is there any other possibility beside someone having changed something on our database?

This is the Connect log:

connect_1    | 2021-04-30 20:28:47,254 INFO   ||  [Worker clientId=connect-1, groupId=1] Session key updated   [org.apache.kafka.connect.runtime.distributed.DistributedHerder]
connect_1    | 2021-04-30 20:39:56,996 ERROR  ||  Failed testing connection for jdbc:mysql://my.domain.com:3306/?useInformationSchema=true&nullCatalogMeansCurrent=false&useSSL=false&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=CONVERT_TO_NULL&connectTimeout=30000 with user 'myuser'   [io.debezium.connector.mysql.MySqlConnector]
connect_1    | java.sql.SQLException: The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
connect_1    |  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
connect_1    |  at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
connect_1    |  at io.debezium.jdbc.JdbcConnection.lambda$patternBasedFactory$1(JdbcConnection.java:231)
connect_1    |  at io.debezium.jdbc.JdbcConnection.connection(JdbcConnection.java:872)
connect_1    |  at io.debezium.connector.mysql.MySqlConnection.connection(MySqlConnection.java:79)
connect_1    |  at io.debezium.jdbc.JdbcConnection.connection(JdbcConnection.java:867)
connect_1    |  at io.debezium.jdbc.JdbcConnection.connect(JdbcConnection.java:413)
connect_1    |  at io.debezium.connector.mysql.MySqlConnector.validateConnection(MySqlConnector.java:98)
connect_1    |  at io.debezium.connector.common.RelationalBaseSourceConnector.validate(RelationalBaseSourceConnector.java:52)
connect_1    |  at org.apache.kafka.connect.runtime.AbstractHerder.validateConnectorConfig(AbstractHerder.java:375)
connect_1    |  at org.apache.kafka.connect.runtime.AbstractHerder.lambda$validateConnectorConfig$1(AbstractHerder.java:326)
connect_1    |  at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
connect_1    |  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
connect_1    |  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
connect_1    |  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
connect_1    |  at java.base/java.lang.Thread.run(Thread.java:834)
connect_1    | Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
connect_1    |  at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
connect_1    |  at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
connect_1    |  at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
connect_1    |  at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
connect_1    |  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
connect_1    |  at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
connect_1    |  at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132)
connect_1    |  at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2120)
connect_1    |  at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2143)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1310)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:967)
connect_1    |  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
connect_1    |  ... 17 more
connect_1    | 2021-04-30 20:39:56,998 INFO   ||  AbstractConfig values: 
connect_1    |    [org.apache.kafka.common.config.AbstractConfig]

Is there a parameter I could be passing in via the Connect rest API to specify the timezone in the JDBC string (shown near the top of this log)? I'm using the Debezium (1.5) Docker images per this tutorial.

Answer 1

in my case, using kafka connect, i had to modify the connector config - mysql connection string (connection.url property) like this:

jdbc:mysql://<server ip or dns>:<port>?serverTimezone=GMT%2b8:00&<more parameters>

Note: %2b is the + character.

Answer 2

I think EDT is not in the mysql.time_zone_name table

SELECT * FROM mysql.time_zone_name;

Adding the configuration "database.serverTimezone" solves this issue on my end, e.g.

"config": {
    ...
    "database.serverTimezone": "America/Los_Angeles",
    ...
}