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

你的名字 2023-03-13 05:54 155阅读 0赞

## 一、问题 ##

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.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70][]

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)

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70 1][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70 2][]

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

![20200511200659148.png][]

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

default-time_zone = '+8:00'

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70]: /images/20230312/829242dfdaa542938b0b8992b03438ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70 1]: /images/20230312/7265add0ec6f4c85bccbf4431a1e8812.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l5cDAzMDREZXZpbg_size_16_color_FFFFFF_t_70 2]: /images/20230312/b175f1ea85ec451e835efc7965167ea8.png
[20200511200659148.png]: /images/20230312/48edc254702646cf9e4603550cf1701e.png