java.sql.SQLException：服务器时区值“ xx time”无法识别

使用最新的mysql-connector-java:8.0.16与JDBC服务器建立JDBC连接，并遇到以下SQLException：

经过测试

• MySQL 5.7
• Java 8
• JDBC驱动程序，mysql-connector-java 8.0.16

try (Connection conn = DriverManager.getConnection(
			"jdbc:mysql://127.0.0.1:3306/test", "root", "password")) {

		//...

	} catch (Exception e) {
		e.printStackTrace();
	}

输出量

java.sql.SQLException: The server time zone value 'Malay Peninsula Standard Time' 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:835)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
	at com.mkyong.jdbc.JDBCExample.main(JDBCExample.java:23)
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Malay Peninsula Standard Time' 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 java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
	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:2243)
	at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2267)
	at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1319)
	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:966)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
	... 6 more

1.解决方案

1.1查找MySQL配置文件，例如my.ini或my.cnf 。 在文件末尾添加默认时区：

my.ini

# Indicates the InnoDB Cluster Port.
# innodbclusterport=3306

# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load

# MySQL server's plugin configuration.
# loose_mysqlx_port=33060

# Set default time zone
default-time-zone = '+08:00'

1.2重新启动MySQL服务。

PS阅读此MySQL选项文件 ，找出文件（ my.ini或my.cnf ）在哪里。

2.解决方案

或者，直接在JDBC连接字符串中传递serverTimezone属性。

try (Connection conn = DriverManager.getConnection(
			"jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "password")) {

		//...

	} catch (Exception e) {
		e.printStackTrace();
	}

参考文献

• 使用JDBC驱动程序连接到MySQL
• MySQL选项文件
• 如何设置MySQL的时区？

标记： java jdbc mysql

翻译自: https://mkyong.com/jdbc/java-sql-sqlexception-the-server-time-zone-value-xx-time-is-unrecognized/