如何在Spring Boot配置中设置正确的MySQL JDBC时区 [英] How to set correct MySQL JDBC timezone in Spring Boot configuration

问题描述

数据库:

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for osx10.10 (x86_64) using  EditLine wrapper

Spring Boot:2.1.1.Rosease

Spring Boot: 2.1.1.RELEASE

错误:

2019-01-01 15:56:25.849 ERROR 39957 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.
> :bootRun
java.sql.SQLException: The server time zone value 'AEDT' 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.

我的属性文件的相关部分:

Relevant parts of my properties file:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&serverTimezone=UTC
spring.datasource.username=#####
spring.datasource.password=########
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

我对此感到奇怪的是，该错误指示该时区正在使用它的AEDT，但是我在spring.datasource.url中指定了UTC. Hikari初始化时是否还会读取其他内容?

What I find odd about this is that the error indicates that the timezone being used it AEDT, and yet I specified UTC in the spring.datasource.url. Does Hikari read something else when it initializes?

看起来非常像Hikari忽略数据库URL中的服务器时区设置，而是使用我自己机器的时区，恰好是"AEDT"(澳大利亚墨尔本)-这是不想要的行为.我希望Hikari忽略我自己机器的时区.有谁知道如何做到这一点?

It does look very much like Hikari ignores the server timezone setting in the database url in favour of using my own machine's timezone which happens to be 'AEDT' (Melbourne, Australia) - This is unwanted behaviour. I would like Hikari to ignore my own machine's timezone. Does anyone know how to make it do that?

推荐答案

感谢您的回答，但我找到了解决方法.

Thanks for your answers, but I have found the solution.

正如我怀疑的那样，Hikari会忽略您在数据源url中输入的任何内容(对不起，伙计们，您在其中插入的内容无关紧要)，从本质上讲，它会从MySQL本身读取时区设置，即无论您得到什么结果查看发出命令的时间

As I suspected, Hikari ignores whatever you put in the datasource url (so sorry guys, it doesn't matter what you chuck in there), essentially, it reads the timezone setting from MySQL itself, i.e., whatever the result you see when issuing the command

SELECT @@GLOBAL.time_zone;

.在我的情况下，结果为"SYSTEM"，这与设置的本地计算机无关.这是AEDT，MySQL驱动程序不支持，因此是我的例外.

in MySQL. In my case, the result was "SYSTEM", which is whatever my local machine it set at. This was AEDT, which is not supported by the MySQL driver and hence my exception.

在AWS中运行相同的查询会产生值"UTC"，该值受支持(实际上是我想要的).

Running this same query in AWS yielded the value "UTC", which is supported (and, actually what I wanted).

因此，我不得不在本地MySQL服务器中设置时区.

Therefore, I had to set the timezone in my local MySQL server.

首先，我必须将主机(Mac OS X)上的可用时区加载到MySQL中.我必须找出zoneinfo文件在哪里(在我的情况下是/usr/share/zoneinfo)，然后找出mysql_tzinfo_to_sql实用程序在哪里(MySQL安装的bin目录)，并使用它来加载本地计算机支持的时区.在Mac OS X中，我最终运行了命令:

Firstly, I had to load the available timezones from my host (Mac OS X) into MySQL. I had to find out where the zoneinfo file was (/usr/share/zoneinfo in my case) then find out out where the `mysql_tzinfo_to_sql' utility was (bin directory of the MySQL installation) and use it to load my local machine's supported timezones. In Mac OS X, I ended up running the command:

/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

然后在MySQL中，我可以运行命令

Then in MySQL I could run the command

SET GLOBAL time_zone = UTC;

此是有效的时区，并且已与基于云的实例同步.

this is a valid timezone, and is synchronized with the cloud based instances.

对于许多使用Spring Boot的MySQL的人来说，这是一个真正的陷阱.当人们处于受支持的时区时，它可以工作，但是如果您的开发机器应切换到不受支持的时区，它将神秘地中断，我很惊讶没有在任何地方对此进行记录. MySQL Connector/J的源代码使它显而易见，但否则您将一无所知.

I think this is a real trap for a lot of people using MySQL with Spring Boot. It will work while people are in supported timezones, but if your development machine should switch to an unsupported timezone, it will quite mysteriously break, I'm surprised that it isn't documented anywhere. The source code of the MySQL Connector/J makes it obvious, but you wouldn't know it otherwise.

也许是因为MySQL才5年前，而且我是一个古老的化石，而且，好吧，就从我的草坪上走下来吧！

Maybe its because MySQL is just so 5 years ago, and I'm an old fossil and, and, well, just get off my lawn!

这篇关于如何在Spring Boot配置中设置正确的MySQL JDBC时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！