MySQL JDBC Driver 5 1 33 - Time Zone Issue

Question

Some background   I have a Java 1 6 webapp running on Tomcat 7  The database is MySQL 5 5  Previously  I was using Mysql JDBC driver 5 1 23 to connect to the DB  Everything worked  I recently upgraded to Mysql JDBC driver 5 1 33  After the upgrade  Tomcat would throw this error when starting the app   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    Why is this happening

User · Answer

The above program will generate that time zone error.

After your database name you have to add this: ?useTimezone=true&serverTimezone=UTC. Once you have done your code will work fine.

Best of luck :)

User · Answer

Apparently  to get version 5 1 33 of MySQL JDBC driver to work with UTC time zone  one has to specify the serverTimezone explicitly in the connection string   jdbc mysql   localhost db useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC

User · Answer

It worked for me just by adding serverTimeZone UTC on application properties  spring datasource url jdbc mysql   localhost db serverTimezone UTC

User · Answer

I had the same problem when I try to work with spring boot project on windows    Datasource url should be   spring datasource url jdbc mysql   localhost database useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC

User · Answer

I am late  But If you are struggling through the following error and using datasource javax sql DataSource     The server time zone value  CEST  is unrecognized or represents more than one time zone    Set following line to get rid of the error   MysqlDataSource dataSource   new MysqlDataSource    dataSource setServerTimezone  UTC

User · Answer

Connection con   DriverManager getConnection  jdbc mysql   localhost 3306 resultout  useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC   root         This is actually the solution to this problem  but don t just copy and paste it in your program  If you just read the line you will find  resultout   that s the name of my database  and you have to write your s   There are three string components  first one is url  second is username  and third one is password  In above paragraph we cleared  url  The second and third String components as said your username and password you have to change accordingly    Thanks

User · Answer

If you are using Maven  you can just set another MySQL connector version  I had the same error  so i changed from 6 0 2 to 5 1 39  in pom xml    lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt version gt 5 1 39 lt  version gt   lt  dependency gt    As reported in another answers  this issue has been fixed in versions 6 0 3 or above  so you can use the updated version    lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt version gt 6 0 3 lt  version gt   lt  dependency gt    Maven will automatically re-build your project after you save the pom xml file

User · Answer

Run below query to mysql DB to resolve the error  MariaDB  xxx gt  SET   global time zone     00 00   Query OK  0 rows affected  0 062 sec   MariaDB  xxx  gt  SET   session time zone     00 00   Query OK  0 rows affected  0 000 sec   MariaDB  xxx  gt  SELECT   global time zone    session time zone

User · Answer

I have the same problem and i solved it append only   serverTimezone UTC  to my string connection      sinossi my problem   java sql SQLException  The server time zone value  CEST  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   my dbDriver   com mysql jdbc Driver  my jar   mysql-connector-java-8 0 12 jar  my java   1 8  my tomcat   Apache Tomcat Version 8 5 32  my MySql server   MySql ver 8 0 12

User · Answer

I faced the same error and in my case  I change the Server Port Number to 3308 where previously it was 3306  This connect my project to the MySQL database   Here we have to change the connection code also  Class forName  quot com mysql cj jdbc Driver quot    cn  java sql Connection DriverManager getConnection  quot jdbc mysql   localhost 3308 test2 zeroDateTimeBehavior convertToNull quot   quot root quot   quot  quot     Changing the port number in the connection code is also necessary as localhost 3308 to resolved the error  Also  the admin properties in my case

User · Answer

This is a bug in mysql-connector-java from version 5 1 33 to 5 1 37  I ve reported it here  http   bugs mysql com bug php id 79343  Edited  This has been corrected from mysql-connector-java 5 1 39  It was a typo in TimeUtil class in loadTimeZoneMappings method that raises a NPE locating  com mysql jdbc TimeZoneMapping properties file  If you look at the code  the file should be located within TimeUtil class loader  not TimeZone   TimeUtil class getResourceAsStream TIME ZONE MAPPINGS RESOURCE     The parameter useLegacyDatetimeCode allows to correct the difference between client and server timezones automatically when using dates  So it helps you precissely not having to specify timezones in each part  Althought using serverTimeZone parameter is a workaround  and meanwhile the patch is released  you can try better correcting the code by yourself as I did    If it s a standalone application  you can try simply to add a corrected com mysql jdbc TimeUtil class to your code and be careful with jar loading order  This can help  https   owenou com 2010 07 20 patching-with-class-shadowing-and-maven html If it s a web application  the easier solution is to create your own mysql-connector-java-5 1 37-patched jar  substituting the  class directly into the original jar

User · Answer

From mysql workbench run the following sql statements    SET   global time zone     00 00   SET   session time zone     00 00     with the following sql statements check if the values were set   SELECT   global time zone    session time zone

User · Answer

Just modify the connection string with the following code in the application properties file    spring datasource url jdbc mysql   localhost 3301 Db     useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode     false amp serverTimezone UTC

User · Answer

i Got error similar to yours but my The server time zone value is   Afr  centrale Ouest   so i did these steps     MyError  on IntelliJ IDEA Community Edition         InvalidConnectionAttributeException  The server time zone value  Afr  centrale Ouest  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 u       I faced this issue when I upgraded my mysql server to SQL Server 8 0  MYSQL80    The simplest solution to this problem is just write the below command in your MYSQL Workbench -    SET GLOBAL time zone     1 00    The value after the time-zone will be equal to GMT  - Difference in your timezone  The above example is for North Africa GMT 1 00    or for India GMT 5 30   It will solve the issue   Enter the Following code in your Mysql Workbench and execute quesry   source link for question problem       source link for answer     Solution ScreenShot

User · Answer

You can use the MySQL connector in the Maven dependency     lt dependency gt       lt groupId gt mysql lt  groupId gt       lt artifactId gt mysql-connector-java lt  artifactId gt       lt version gt 8 0 14 lt  version gt   lt  dependency gt    Then you need the set the right parameters in the application properties file    spring datasource url jdbc mysql   localhost 3306 UserReward useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC spring datasource username testuser spring datasource password testpassword   MySQL driver spring datasource driverClassName com mysql jdbc Driver spring jpa database-platform org hibernate dialect MySQL5Dialect

User · Answer

After reading several posts on this topic  testing different configurations and based on some insights from this mysql bug thread that s what I have understood    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

User · Answer

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 other words  simply setting serverTimezone UTC with a different time zone on the database server will shift any dates extracted from the database

User · Answer

This worked for me   on DBeaver 6 0   Go to Connection Settings   Driver Properties   Server Time Zone   Set UTC   Also  in spring boot config  had to set below property   jdbc mysql   localhost   serverTimezone UTC

User · Answer

I executed following on my database side   mysql gt  SET   global time zone     00 00    mysql gt  SET   session time zone     00 00    mysql gt  SELECT   global time zone    session time zone    I am using Server version  8 0 17 - MySQL Community Server - GPL  source  https   community oracle com thread 4144569 start 0 amp tstart 0

User · Answer

Everything that we need to fix the problem with serverTimezone   String url    jdbc mysql   localhost 3306 db serverTimezone     TimeZone getDefault   getID

User · Answer

I solved this issue without any single code change  just goto system time setting and set the time zone  In my case the default time zone was UTC which I changed to my local time zone  After I did restart all services  everything worked for me

User · Answer

Setting the time zone by location for a spring boot application inside the application properties file to spring datasource url jdbc mysql   localhost 3306 db serverTimezone Europe Berlin  resolved the problem for the CET   CEST time zone  The pom xml uses the maven artifact      lt  -- https   mvnrepository com artifact mysql mysql-connector-java -- gt       lt dependency gt           lt groupId gt mysql lt  groupId gt           lt artifactId gt mysql-connector-java lt  artifactId gt           lt version gt 8 0 22 lt  version gt       lt  dependency gt

User · Answer

Apparently  to get version 5 1 33 of MySQL JDBC driver to work with UTC time zone  one has to specify the serverTimezone explicitly in the connection string   spring datasource url   jdbc mysql   localhost 3306 quartz demo useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC

User · Answer

The connection string should be set like this   jdbc mysql   localhost db useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC   If you are defining the connection in an xml file  such as persistence xml  standalone-full xml  etc     instead of  amp  you should use  amp amp  or use a CDATA block

User · Answer

I added in mysql config file in section  mysqld   default time zone   03 00   And restart mysql server    sudo service mysql restart    Where  03 00 my UTC time zone   Path to config file on my os ubuntu 16 04    etc mysql mysql conf d mysqld cnf   WARNING  IF YOUR TIME ZONE HAVE SUMMER AND WINTER TIME  YOU MUST CHANGE UTC IN CONFIG IF CHANGE TIME  TWICE IN YEAR USUALLY  OR SET CRONTAB WITH SUDO   My url jdbc connection     jdbc mysql   localhost java

User · Answer

In my case  it was a test environment and I had to make an existing application to work without any configuration changes  and if possible without any MySQL config changes  I was able to fix the issue by following  vinnyjames suggestion and changing server timezone to UTC   ln -sf  usr share zoneinfo UTC  etc localtime service mysqld restart   Doing that was enough for me to solve the issue

User · Answer

I m using mysql-connector-java-8 0 13 and had the same problem   I created my database in the command line console and solved this problem by using  Dimitry Rud s solution on the command line   SET GLOBAL time zone    -6 00     I didn t need to restart anything  set the time and immediately run my code in eclipse  it connected with no problems   The bug is supposed to be fixed in an older version  but I think I got this error because after I created the database in the console  I didn t set this  I m not using workbench nor another app to manage this rather than the console

User · Answer

I solved putting below connection string in the URL  jdbc mysql   localhost 3306 db useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC

User · Answer

I also was having the exact same problem in LibreOffice Base   So I just specified a non  daylight savings time zone  in the connection string    I tried without the   amp serverTimezone MST  but that failed as well   I also tried   amp serverTimezone MDT  and that failed  so for some reason  it doesn t like daylight savings time

User · Answer

I ve solved this problem by configuring MySQL    SET GLOBAL time zone     3 00

User · Answer

Agree with  bluecollarcoder answer  but it s better to use TimeZone getDefault   getID    at the end of the connection string    jdbc mysql   localhost db useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone     TimeZone getDefault   getID        In this case Timezone parameter automatically updates depending on the local machine timezone

User · Answer

I have added the following line to my  etc mysql my cnf file   default time zone   00 00    Restarted the MySQL server   systemctl restart mysql   And it works like a charm

User · Answer

I also got the same running java JDBC in NetBeans  This is how it fixed  I use Xampp  In conf button of Apache I opened httpd conf file and on first line I typed     Set timezone to Europe Athens UTC 02 00  SetEnv TZ Europe Athens    In conf button of MySQL I opened my ini file and on last line I typed  Europe Athens   Stopped and started both Apache and MySQL  Problem fixed     Local mechine time zone is different  but no problem

[java] MySQL JDBC Driver 5.1.33 - Time Zone Issue

Examples related to java

Examples related to mysql

Examples related to tomcat

Examples related to jdbc

Examples related to timezone