Bug #79343 NPE in TimeUtil.loadTimeZoneMappings causing server time zone value unrecognized
Submitted: 19 Nov 2015 15:21 Modified: 22 Apr 2016 14:51
Reporter: Anibal P Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:From 5.1.33 to 5.1.37 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[19 Nov 2015 15:21] Anibal P
Description:
If you use mysql java connector with parameter useLegacyDatetimeCode=false and without forcing serverTimeZone, it raises always a NPE because /com/mysql/jdbc/TimeZoneMapping.properties is never found.

How to repeat:
Using connector with useLegacyDatetimeCode=false and without forcing serverTimeZone.

Suggested fix:
In line 484 of com.mysql.jdbc.TimeUtil.java, TimeZone.class has to be changed to TimeUtil.class. Properties file is inside TimeUtil package. That way properties file is never found and Properties.load raise a NPE.

timeZoneMappings.load(TimeUtil.class.getResourceAsStream(TIME_ZONE_MAPPINGS_RESOURCE));
[19 Nov 2015 16:40] Anibal P
This NPE is hidden behind error "The server time zone value X is unrecognized or represents more than one time zone."
[10 Dec 2015 15:53] Filipe Silva
Hi Anibal,

Thank you for this bug report.

I'm unable to reproduce this issue. Can you provide more details, please? Are you using Connector/J directly or through and application server? Which Java version are you using?

Thanks,
[10 Dec 2015 17:21] Anibal P
Well, IMHO that's not important. I think the error is clear, please check my solution, in loadTimeZoneMappings method the resource should be located relative to "this" class, cos both are in the same package.

I think in a standalone test you won't see the error cos there is only one classloader.
I'm running in a web application with several modules, using tomcat 7 & 8 with jdk 7 & 8.

Thanks in advance.
[10 Dec 2015 19:31] Filipe Silva
I need to reproduce this error to fix it and I couldn't do it so far.

I'm not saying that the suggested fix doesn't work, but, did you actually try it?

I wonder if this is somehow related with Tomcat's security manager instead... do you have some specific settings here?
[10 Dec 2015 22:17] Anibal P
Sample project with test case

Attachment: mysql-connector-test.zip (application/zip, text), 3.41 KiB.

[10 Dec 2015 22:23] Anibal P
Here it's a sample test case project.

Next is the stacktrace, masking timezone exception arise when run twice.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
	at com.mysql.jdbc.Util.getInstance(Util.java:383)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2326)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
	at java.sql.DriverManager.getConnection(DriverManager.java:571)
	at java.sql.DriverManager.getConnection(DriverManager.java:233)
	at HelloWorld.createConnection(HelloWorld.java:34)
...

Caused by: java.lang.NullPointerException
	at java.util.Properties$LineReader.readLine(Properties.java:434)
	at java.util.Properties.load0(Properties.java:353)
	at java.util.Properties.load(Properties.java:341)
	at com.mysql.jdbc.TimeUtil.loadTimeZoneMappings(TimeUtil.java:524)
	at com.mysql.jdbc.TimeUtil.getCanonicalTimezone(TimeUtil.java:442)
	at com.mysql.jdbc.ConnectionImpl.configureTimezone(ConnectionImpl.java:2024)
	at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3302)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2293)
	... 33 more

java.sql.SQLException: The server time zone value 'CET' 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.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
	at com.mysql.jdbc.TimeUtil.getCanonicalTimezone(TimeUtil.java:451)
	at com.mysql.jdbc.ConnectionImpl.configureTimezone(ConnectionImpl.java:2024)
	at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3302)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2293)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
	at java.sql.DriverManager.getConnection(DriverManager.java:571)
	at java.sql.DriverManager.getConnection(DriverManager.java:233)
	at HelloWorld.createConnection(HelloWorld.java:34)
...
[10 Dec 2015 22:29] Anibal P
I don't have any specific configuration, tomcat is brand new, running from Eclipse, tried several versions.
[10 Dec 2015 22:32] Anibal P
And yes, I tried the solution, I created a patched connector and it works.

<Sorry for so many messages.>
[11 Dec 2015 16:44] Filipe Silva
Hi Anibal,

Thank you for all the information. Please allow me some time to analyze and try it.
[11 Dec 2015 19:43] Filipe Silva
This issue was verified and the suggested fix was confirmed to work correctly.

Once again, thank you for helping reproduce it.
[14 Dec 2015 13:19] Filipe Silva
For the time being the alternative is to place Connector/J jar somewhere where its classes get loaded by the bootstrap class loader, for example, in $CATALINA_HOME/endorsed.

I hope that helps.
[18 Mar 2016 15:57] Filipe Silva
BTW, this report cannot be considered a S1 severity because there is a workaround, as already explained.
[22 Apr 2016 14:51] Daniel So
Added the following entry to the MySQL Connector/J 5.1.39 changelog:

"With some Tomcat web applications, when Connector/J connects to the server with useLegacyDatetimeCode=false without setting serverTimeZone, a NullPointerException was returned. This was because the timezone property file for Connector/J was loaded by the bootstrap class loader, which did not know the location of the property file and thus failed to load it. This fix avoids the problem by making Connector/J use the same class loader for both the property file and the Connector/J classes."
[10 May 2016 10:19] Ephraim Khantsis
The issue still exists with J/Connector 6.0.2
[10 May 2016 11:36] Filipe Silva
Posted by developer:
 
Hi Ephraim,

That's right, it does. But mind that Connector/J 6.0 and Connector/J 5.1 have different release cycles. This fix was made in Connector/J 5.1 and published only yesterday. As soon as both trees are merged once again, the fix will be propagated to Connector/J 6.0 too.
If in a future release of Connector/J 6.0 it still doesn't work for you then please report it again and mention this Bug# in your report.

Thank you.
[11 May 2016 14:04] Ephraim Khantsis
Well, is the bugfix in the pipeline for 6.0 branch?
[14 Jun 2016 19:49] Daniel So
Posted by developer:
 
Added the above-mentioned entry to the Connector/J 6.0.3 changelog, as the fix has been pushed into the 6.0 tree.
[4 Jul 2016 20:12] Daniel So
Posted by developer:
 
The same bug have different bug numbers for Connector/J 6.0.2: Bug #23197026  and Bug #81214.
[23 Sep 2018 4:52] Rafik BOUGHANI
The problem still persist on mysql-connector-java 8.0.12
How to fix it ?
Thank you
[24 Sep 2018 8:19] Filipe Silva
Hi Rafik,

Could you elaborate, please?
[8 Oct 2018 18:31] Adam Latuszek
Hi,
I confirm that the problem re-occurs in 8.0.12.
Previously I've used mysql-connector-java v5.1.47 - everything works OK in this version.
When I've switched to 8.0.12, I'm getting this error when I'm trying to connect to my mysql instance:

SQL State  : 01S00
Error Code : 0
Message    : 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.
[11 Oct 2018 10:31] Filipe Silva
Hi,

This is so because Connector/J 8.0.12 does time offset adjustments by default. This is the same behavior as in Connector/J 5.1.47 if you had set `useLegacyDatetimeCode=false`.

So, the same error occurs in Connector/J 5.1.47 if you set either `useLegacyDatetimeCode=false` or `useTimezone=true`.

In order to prevent this error from happening you have to:
1. Configure your mysql server with a canonical time zone that can be recognized in Java (e.g. Europe/Paris, GMT-5, UTC, etc)
2. Override the server time zone by setting the connection property `serverTimezone`, e.g., `serverTimezone=Europe/Paris`
3. Override the server time zone by setting the connection property `serverTimezone` with the client time zone, e.g., `serverTimezone=Europe/London` (for a client running in UK and the server in France).

Note that options 1 and 2 cause time offset adjustments between the client and server time zones in temporal data. Use 3 if you don't want to have automatic time offset adjustments. Also note that this only affects the ResultSet methods that handle temporal data (getDate(), getTimestamp(), etc), which also have versions that take a calendar that you can use to override these time zone settings.
[11 Oct 2018 11:25] Adam Latuszek
Thanks for helpful info Filipe.
It will be good to have such information in the documentation (https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-upgrading-to-8.0.html).
[18 Oct 2018 10:32] Rafik BOUGHANI
excusme me for my late response Filipe Silva
Thank you for your helpful comment,
actually i use MysqlJ 5