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

Question

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;

e.g., in PHP or Windows ODBC or unixODBC. What does the property `user.timezone` (`TimeZone.getDefault()`) on the client and server say? — Michael-O, Jun 09 '16 at 07:50
You need a "real" time zone. The same happens with CEST ("Central European Standard Time") - here you have to decide on Europe/Berlin or Europe/Paris etc. For AEST this seems to be one of Australia/Sydney, Australia/Tasmania or Australia/Victoria. — René, Jun 09 '16 at 07:53
Possible duplicate of [How to change mysql timezone in java connection](http://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-java-connection) — guido, Jun 09 '16 at 07:56
Possible duplicate of http://stackoverflow.com/q/26515700/1809799 — René, Jun 09 '16 at 08:00
@René Seems to me like this could be a bug with the jdbc...As mentioned in one of the comments in that link, it doesn't 6.0.2 — James111, Jun 09 '16 at 08:03
@James111 Have you tried Version 5.1.39 of the connector? https://dev.mysql.com/doc/connector-j/6.0/en/connector-j-versions.html mentions version 6 to be a developer milestone ... — René, Jun 09 '16 at 08:10
Was just trying that out before you commented...fixed the issue :) Thanks — James111, Jun 09 '16 at 08:13

score 96 · Answer 1 · answered Sep 17 '16 at 17:02

96

try to add those parameters..

jdbc:mysql://localhost:3306/fussa?useLegacyDatetimeCode=false&serverTimezone=UTC

i'm using mysql-connector-java 6.0.4

answered Sep 17 '16 at 17:02

FuSsA

4,223
7
39
60

2

This worked for me. I updated my Spring application.properties file. I'd like to set this to my local timezone though (Mountain Time) and would like it to be smart enough to handle daylight savings, (i.e., MDT becomes MST and back again throughout the year). – BikerJared Jul 17 '17 at 18:58
3

This parameter can be added to phpStorm database manager too to connect. – gael Apr 27 '18 at 02:36
I updated my Linux server's /etc/localtime from /usr/share/zoneinfo/US/Pacific to /usr/share/zoneinfo/America/Los_Angeles then restarted mysql service and this fixed the issue for me. – vinnyjames Jan 31 '19 at 22:18
I had the same problem and this post solved it. Anyway, if I start application in debug mode, I have this exception: "Exception in thread "main" com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server". Any idea? – Bia Jun 15 '20 at 18:14

score 26 · Accepted Answer · answered Jun 09 '16 at 08:13

26

Turns out 6.x wasn't compatible, moving to 5.1.39 fixed it.

answered Jun 09 '16 at 08:13

James111

15,378
15
78
121

1

Upgrading to 8.0.11 also fixes this. – Nikhil Sahu May 16 '18 at 10:12
Connector 8.0.15 seems broken and needs the above fix. – Volksman Mar 12 '19 at 12:03
1

8.0.23 by default uses the local timezone, and so does not have this problem by default either https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-23.html – Brecht Yperman May 06 '21 at 12:37

score 23 · Answer 3 · answered Apr 06 '19 at 06:10

23

The proper solution, IMHO for AEDT, is to change the URL as follows,

jdbc:mysql://localhost:3306/sampledb?serverTimezone=Australia/Sydney

answered Apr 06 '19 at 06:10

Gunith D

1,843
1
31
36

score 6 · Answer 4 · edited Mar 16 '17 at 13:57

6

jdbc:mysql://localhost:3306/fussa?useLegacyDatetimeCode=false&amp;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;

edited Mar 16 '17 at 13:57

Martin Evans

45,791
17
81
97

answered Mar 16 '17 at 13:39

user2087635

69
1
1

@LawrenceDol actually you are incorrect. It is embedded in XML so still needs to be escaped. – MikeKulls Jan 27 '22 at 21:48

score 4 · Answer 5 · edited May 23 '17 at 12:18

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: Should MySQL have its timezone set to UTC?

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

score 4 · Answer 6 · answered Mar 25 '19 at 14:07

4

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";

answered Mar 25 '19 at 14:07

2rahulsk

488
4
10

score 2 · Answer 7 · answered Jul 13 '18 at 10:53

2

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.

answered Jul 13 '18 at 10:53

catch22

1,564
1
18
41

Works for me, on SpringBoot with mysql 8.0 – José Castillo Mar 21 '19 at 14:27

score 2 · Answer 8 · answered Mar 23 '19 at 10:45

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

score 2 · Answer 9 · answered Jul 07 '20 at 15:43

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'
```

score 1 · Answer 10 · answered Oct 05 '18 at 06:29

1

Using default

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/web_student_tracker?user=root&password=&useLegacyDatetimeCode=false&serverTimezone=UTC");

answered Oct 05 '18 at 06:29

antelove

3,216
26
20

score 1 · Answer 11 · answered Apr 24 '20 at 10:54

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

score 0 · Answer 12 · answered Feb 17 '20 at 21:38

0

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.

answered Feb 17 '20 at 21:38

NUKE

71
1
6

score 0 · Answer 13 · edited Aug 26 '20 at 22:43

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**"

score 0 · Answer 14 · answered Mar 04 '21 at 14:33

0

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

answered Mar 04 '21 at 14:33

NIK

1,089
13
22

I need to contact you sir, need little help in CentOS. Could you plz DM me on Twitter @ankitmishraR or you give me something where I can talk to you. – Ankit Mishra Mar 16 '21 at 12:37

ptim · Answer 15 · 2021-10-27T03:16:39.960

0

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 :(

edited Oct 27 '21 at 03:16

answered Oct 27 '21 at 03:10

ptim

14,902
10
83
103

score 0 · Answer 16 · answered May 11 '23 at 21:00

0

edit /etc/my.cnf.d/server.cnf on server section

[server]
default-time-zone = "Europe/Lisbon"

answered May 11 '23 at 21:00

Sérgio

6,966
1
48
53

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

Edit:

Edit:

16 Answers16

Linked

Related