java.sql.SQLException: The server time zone value ‘xx time’ is unrecognized
Making a JDBC connection to the MySQL server with the latest mysql-connector-java:8.0.16
and hits the following SQLException:
Tested with
- MySQL 5.7
- Java 8
- JDBC driver, mysql-connector-java 8.0.16
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test", "root", "password")) {
//...
} catch (Exception e) {
e.printStackTrace();
}
Output
java.sql.SQLException: The server time zone value 'Malay Peninsula Standard Time' 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:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at com.mkyong.jdbc.JDBCExample.main(JDBCExample.java:23)
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Malay Peninsula Standard Time' 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 java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132)
at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2243)
at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2267)
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1319)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:966)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
... 6 more
1. Solution
1.1 Find the MySQL configuration file, for example, my.ini
or my.cnf
. Add a default time zone at the end of the file:
my.ini
# Indicates the InnoDB Cluster Port.
# innodbclusterport=3306
# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load
# MySQL server's plugin configuration.
# loose_mysqlx_port=33060
# Set default time zone
default-time-zone = '+08:00'
1.2 Restart MySQL service.
P.S Read this MySQL option files to find out where is the file (my.ini
or my.cnf
) located.
2. Solution
Alternatively, pass a serverTimezone
property in the JDBC connection string directly.
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "password")) {
//...
} catch (Exception e) {
e.printStackTrace();
}
For windows 10 64 bit mysql default installation path:
C:/ProgramData/MySQL/MySQL Server 8.0/my.ini
Thanks a lot!!!
graciaas. funciono agregando ?serverTimezone=UTC
It worked. Thanks a lot.
I believe the issue was difference of timezone
Muito obrigado pela solução.
?serverTimezone=UTC
Thank you, it works adding serverTimezone=UTC
Thank you very much for u r support and help….!
Thank you so much! I have successfully fixed it
Thank you very very much. It’s really worked
Thank you sir. 2nd solution is a permanent solution .pass the server timezone in the url string.
example– DriverManager.getConnection(“jdbc:mysql://localhost:3306/databasename1?serverTimezone=UTC”, “root”,”root”);
very helpful , thanks
thank you very much.that was helpful
Could you please explain, what exactly serverTimezone does? There is not clear explanation what so ever.
my opinion is that it matches the timezone with whatever country you are in .