背景知识:

我有一个运行在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工作台中运行以下sql语句:

设置@@global。Time_zone = '+00:00'; 设置@@session。Time_zone = '+00:00';

使用以下SQL语句检查是否设置了值:

选择@@global。time_zone @@session.time_zone;

其他回答

我只需要在application.properties上添加serverTimeZone=UTC就可以了。 spring.datasource.url = jdbc: mysql: / / localhost / db吗?serverTimezone = UTC

在应用程序内按位置为spring引导应用程序设置时区。属性文件

spring.datasource.url=jdbc:mysql://localhost:3306/db?serverTimezone=Europe/Berlin

解决了CET / CEST时区的问题。xml使用maven工件

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.22</version>
    </dependency>

在我的例子中,它是一个测试环境,我必须让一个现有的应用程序在没有任何配置更改的情况下工作,如果可能的话,没有任何MySQL配置更改。 我能够通过遵循@vinnyjames的建议并将服务器时区更改为UTC来修复这个问题:

ln -sf /usr/share/zoneinfo/UTC /etc/localtime
service mysqld restart

这样做对我来说已经足够解决问题了。

在阅读了几篇关于这个主题的文章后,测试了不同的配置,并根据这个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)

我没有修改任何代码就解决了这个问题。只需进入系统时间设置和设置时区。在我的情况下,默认时区是UTC,我将其更改为我的本地时区。重启所有服务后,一切都恢复正常了。