The server time zone value 'AEST' is unrecognized or represents more than one time zone
JavaMysqlHibernateMavenJava Problem Overview
I'm trying to setup a simple hibernate application, when I run it I get a stack trace full of errors.
I have the following maven dependencies in my pom.xml file:
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.0.Final</version>
</dependency>
<!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.2</version>
</dependency>
And the mysql version I'm running locally is:
5.7.9, for osx10.11 (x86_64)
I'm running, what seems, a very simple method, and still getting errors:
// create session factory
SessionFactory factory = new Configuration()
.configure("hibernate.cfg.xml")
.addAnnotatedClass(Model.class)
.buildSessionFactory();
// create session
Session session = factory.getCurrentSession();
Model newModel = new Model("....", "...", "...");
// start a transaction
session.beginTransaction();
// save the student object
System.out.println("Saving the model...");
session.save(newModel);
After the above is executed, I get a long stack trace...The System.out.println doesn't print either...So the connection just doesn't seem to be connecting.
INFO: HHH000115: Hibernate connection pool size: 1 (min=1)
Thu Jun 09 17:36:28 EST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in thread "main" org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:244)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:208)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:189)
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:217)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:189)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.build(MetadataBuildingProcess.java:83)
at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:418)
at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:87)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:691)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:723)
at com.parkingapi.tests.Test.main(Test.java:17)
Caused by: org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:105)
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:123)
at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:41)
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:58)
at org.hibernate.engine.jdbc.connections.internal.PooledConnections.addConnections(PooledConnections.java:106)
at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:40)
at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:19)
at org.hibernate.engine.jdbc.connections.internal.PooledConnections$Builder.build(PooledConnections.java:138)
at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.buildPool(DriverManagerConnectionProviderImpl.java:110)
at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:74)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:217)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:189)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.buildJdbcConnectionAccess(JdbcEnvironmentInitiator.java:145)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:66)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:234)
... 14 more
Caused by: java.sql.SQLException: The server time zone value 'AEST' 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:695)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:638)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:606)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:624)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:620)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:68)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1683)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:656)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:349)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:221)
at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:38)
... 29 more
Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone value 'AEST' 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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
at com.mysql.cj.jdbc.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:118)
at com.mysql.cj.mysqla.MysqlaSession.configureTimezone(MysqlaSession.java:308)
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2474)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1817)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1673)
... 33 more
I'm focused on this error here:
> The server time zone value 'AEST' 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.
I'm unsure how I fix it?
###Edit:
Printing out the Timezone (System.out.println(TimeZone.getDefault());) prints the following details:
> sun.util.calendar.ZoneInfo[id="Australia/Melbourne",offset=36000000,dstSavings=3600000,useDaylight=true,transitions=142,lastRule=java.util.SimpleTimeZone[id=Australia/Melbourne,offset=36000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=9,startDay=1,startDayOfWeek=1,startTime=7200000,startTimeMode=1,endMode=3,endMonth=3,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=1]]
###Edit:
So I looked at the question which was flagged, and tried to change my connection string, but it's still throwing the same stack trace. Below is my new connection url. As you can see I changed the serverTimezone to that of the printed Timezone.getDefault()
jdbc:mysql://localhost:3306/parking_hib?useLegacyDatetimeCode=false;serverTimezone=Australia/Melbourne;useSSL=false;
Java Solutions
Solution 1 - Java
try to add those parameters..
jdbc:mysql://localhost:3306/fussa?useLegacyDatetimeCode=false&serverTimezone=UTC
i'm using mysql-connector-java 6.0.4
Solution 2 - Java
Turns out 6.x wasn't compatible, moving to 5.1.39 fixed it.
Solution 3 - Java
The proper solution, IMHO for AEDT, is to change the URL as follows,
jdbc:mysql://localhost:3306/sampledb?serverTimezone=Australia/Sydney
Solution 4 - Java
jdbc:mysql://localhost:3306/fussa?useLegacyDatetimeCode=false&serverTimezone=UTC
Use & instead of &.
You can try change the timezone on mysql too:
SET GLOBAL time_zone = '00:00';
And see the current setting:
SELECT @@global.time_zone, @@session.time_zone;
Solution 5 - Java
The exception is thrown because the MySql server timezone (inherited from the system timezone) is in a different format than what the mysql connector expects. You need to set MySql timezone to be 'Australia/Sydney'. Look at this awesome response for details: https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc/19075291#19075291
- populate the mysql schema with the timezone information:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
- from mysql run:
SET GLOBAL time_zone = 'Australia/Sydney';
- restart mysql server
Solution 6 - Java
This is the problem of the version of the MySQLWorkbench. Upgrade to the latest version which is 8.0 and then add serverTimezone=UTC in your jdbc URL.
String url = "jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC";
Solution 7 - Java
build.gradle >
compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.11'
application.properties >
spring.datasource.url=jdbc:mysql://localhost:3306/bookworm?useLegacyDatetimeCode=false&serverTimezone=UTC
-
mysql --version
mysql Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)
This combination works for me.
Solution 8 - Java
Hi You need to specify in you URL specifier like below
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/jpahibernate?useLegacyDatetimeCode=false&serverTimezone=UTC");
dataSource.setUsername("root");
dataSource.setPassword("Password");
return dataSource;
}
In the above code snippet have a look on setUrl property
Solution 9 - Java
In MySQL: you may user bellow like:
-
First solution: add serverTimezone=UTC to connection url
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC -
Second solution: n MySQL my.ini file set default-time-zone value bellow like
# Set default time zone default-time-zone = '+08:00'
Solution 10 - Java
Using default
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/web_student_tracker?user=root&password=&useLegacyDatetimeCode=false&serverTimezone=UTC");
Solution 11 - Java
If you using Spring Boot try to add after your localhost parameter a question mark and this:
?serverTimezone=UTC&useLegacyDatetimeCode=false
So for example i have a mysql database with name: springboottutorial. In this example the param would be look like this:
spring.datasource.url=jdbc:mysql://localhost:3306/springboottutorial?serverTimezone=UTC&useLegacyDatetimeCode=false
Solution 12 - Java
I had a similar issue, i was using mysql-connector-java 8.0.15 (latest), but for some reaon it was still giving me a timezone error, i changed it for mysql-connector-java 8.0.13 and that just did it. Hope thats be usefull.
Solution 13 - Java
I have fixed the issue by adding "?serverTimezone=America/Los_Angeles" right after the database name. I am from West Coast and my zone name is "America/Los_Angeles". If you are not sure about your timezone name run below command on RHEL/UBUNTU/CENTOS
11:42 AM ✔ user1 sfo-server1 Δ [~] Ω timedatectl | grep "Time zone"
Time zone: America/Los_Angeles (PDT, -0700)
Solution:
jdbc_connection_string => "jdbc:mysql://localhost:3306/movielens**?serverTimezone=America/Los_Angeles**"
Solution 14 - Java
After loading the time zone data,
SET GLOBAL time_zone = 'Australia/Sydney';
worked for me.
In my case, I wanted the setting to be persistent and none of these worked for me to persist the time zone and finally I went for the option in mysql doc to set the start up parameter as below,
--default-time-zone=Australia/Sydney
The most common option was to set this in my.cnf and this didn't work for me, either.
NOTE: Mine is CentOS 8 & MySql 8
Solution 15 - Java
In case you're using Docker, and need to configure this via an override, consider "hard coding" the TZ to make it unambiguous, eg:
version: '3'
services:
mysql:
environment:
- TZ=GMT+11:00
# - TZ=Australia/Melbourne
Edit: actually, whilst this config doesn't throw an error any more, tomcat interprets the value correctly, whilst mysql interprets it as GMT :(