背景知识:

我有一个运行在Tomcat 7上的Java 1.6 web应用程序。数据库为MySQL 5.5。之前,我使用Mysql JDBC驱动程序5.1.23连接到DB。一切工作。我最近升级到Mysql JDBC驱动程序5.1.33。升级后,Tomcat在启动应用程序时会抛出这个错误。

WARNING: Unexpected exception resolving reference
java.sql.SQLException: The server timezone value 'UTC' is unrecognized or represents
  more than one timezone. You must configure either the server or JDBC driver (via
  the serverTimezone configuration property) to use a more specifc timezone value if
  you want to utilize timezone support.

为什么会这样?


当前回答

运行下面的查询到mysql DB来解决这个错误

MariaDB [xxx> SET @@global.time_zone = '+00:00';
Query OK, 0 rows affected (0.062 sec)

MariaDB [xxx]> SET @@session.time_zone = '+00:00';
Query OK, 0 rows affected (0.000 sec)

MariaDB [xxx]> SELECT @@global.time_zone, @@session.time_zone;

其他回答

在阅读了几篇关于这个主题的文章后,测试了不同的配置,并根据这个mysql bug线程的一些见解,我明白了:

the server time zone is important in particular to convert dates stored in the database to the time zone of the application server. there are other implications but this is the most noticeable one GMT x UTC time zone systems. GMT was conceived in the late 19th century and can be shifted between standard time and summer time. this property could lead to a situation where the database server shifts to summer time and the application doesn't notice it (perhaps there are other complications but I didn't research further). UTC does not vary over time (it is always within about 1 second of mean solar time at 0° longitude). serverTimeZone definition was introduced in mysql jdbc connectors versions 5.1 ahead. until version 8 it could be ignored with useLegacyDatetimeCode=true , which in conjunction with useJDBCCompliantTimezoneShift=true would make the application get the database time zone on every connection. In this mode GMT time zones such as 'British Summer Time' would be converted to the internal java/JDBC format. New time zones could be defined in a .properties file such as this one Starting with jdbc driver version 8, automatic time matching (useJDBCCompliantTimezoneShift) and legacy time format (useLegacyDatetimeCode) were removed (see mysql jdbc connector changelog). therefore setting these 2 parameters has no effect as they are completely ignored (new default is useLegacyDateTimeCode=false) In this manner setting serverTimezone became mandatory if any of the time zones (application/database servers) are not in the format 'UTC+xx' or 'GMT+xx' There is no impact of setting server time as UTC (for instance with jdbc:mysql://localhost:3306/myschema?serverTimezone=UTC, even if your application / database servers are not in this timezone. The important is for the application connection string + database to be synchronized with the same time zone. In different words, simply setting serverTimezone=UTC with a different time zone on the database server will shift any dates extracted from the database The MySQL default time zone can be set to UTC+0 with the my.ini or my.cnf files (windows / linux respectively) by adding the line default-time-zone='+00:00' (details in this StackOverflow post) Databases configured on AWS (amazon web services) are automatically assigned UTC+0 default time (see AWS help page here)

只需在应用程序中使用以下代码修改连接字符串。属性文件。


spring.datasource.url=jdbc:mysql://localhost:3301/Db?
   useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=
   false&serverTimezone=UTC

显然,要使MySQL JDBC驱动程序5.1.33版本与UTC时区兼容,必须在连接字符串中显式指定serverTimezone。

jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

我迟到了,但如果你正在努力克服以下错误并使用数据源(javax.sql.DataSource):

The server time zone value 'CEST' is unrecognized or represents more than one time zone.

设置以下行以消除错误:

MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setServerTimezone("UTC");

这是mysql-connector-java从5.1.33到5.1.37版本的一个错误。 我已经在这里报告了:http://bugs.mysql.com/bug.php?id=79343

编辑: 这已经从mysql-connector-java 5.1.39修正

这是在loadtimezonemapping方法中的TimeUtil类中出现的一个拼写错误,该方法会引发NPE定位/com/mysql/jdbc/TimeZoneMapping。属性文件。如果你看一下代码,文件应该位于TimeUtil类装入器中,而不是TimeZone:

TimeUtil.class.getResourceAsStream(TIME_ZONE_MAPPINGS_RESOURCE);

参数useLegacyDatetimeCode允许在使用日期时自动纠正客户端和服务器时区之间的差异。因此,它帮助您精确地不必在每个部分指定时区。虽然使用serverTimeZone参数是一种变通方法,并且在发布补丁的同时,您可以像我一样尝试自己更正代码。

如果它是一个独立的应用程序,您可以尝试简单地添加 纠正com/mysql/jdbc/TimeUtil类到你的代码,要小心 与罐子装载顺序。这可以帮助: https://owenou.com/2010/07/20/patching-with-class-shadowing-and-maven.html 如果它是一个web应用程序,更简单的解决方案是创建自己的应用程序 Mysql-connector-java-5.1.37-patched.jar,直接替换。class 放进原来的罐子里。