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();
}
Muito obrigado pela solução.
?serverTimezone=UTC
Thank you, it works adding serverTimezone=UTC
For windows 10 64 bit mysql default installation path:
C:/ProgramData/MySQL/MySQL Server 8.0/my.ini
Thanks a lot!!!
Thank you very much for u r support and help….!
graciaas. funciono agregando ?serverTimezone=UTC
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
It worked. Thanks a lot.
I believe the issue was difference of timezone
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 .