JDBC 连接MySQL 报exceptions.InvalidConnectionAttributeException，: The server time zone value.... 解决办法

一、问题

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value ' й   ׼ʱ  ' 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.

java.sql.SQLException: The server time zone value ' й   ׼ʱ  ' 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.

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)

at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)

at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)

at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)

at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at com.atdljd.java.JDBCTest06.login(JDBCTest06.java:47)

at com.atdljd.java.JDBCTest06.main(JDBCTest06.java:22)

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value ' й   ׼ʱ  ' 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.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)

at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)

at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132)

at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2120)

at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2143)

at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1310)

at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:967)

at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)

... 7 more

二、原因

之所以出现这种问题，就是时区导致的。JDBC 与 MySQL 的这种误解，导致出现了数据库实际存储的时间戳，与业务系统取到的值差了 13 或者 14 个小时，这个时间与美中是(=13)否(=14)采用夏令时息息相关。

当 JDBC 与 MySQL 开始建立连接时，会调用 com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer() 获取服务器参数，其中我们看到调用 this.session.configureTimezone() 函数，它负责配置时区。我们的JDBC与MySQL连接时，由于时区不一致就出现了这种错误。有关时差问题可以自己查阅资料。

三、解决办法

修改配置文件，在配置文件的url地址后加上?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai

?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai

 

在代码中获取连接的地方也修改过来

重新登录，问题就解决了。

总结：经常写代码，获取连接都需要修改文件，加地址，比较麻烦，为了解决这种问题可以将时区设置默认在东八区。

default-time_zone = '+8:00'