| Bug #49700 | URL parameter sessionVariables (time_zone=GMT) ignored in pooled connection | ||
|---|---|---|---|
| Submitted: | 14 Dec 2009 23:26 | Modified: | 8 Jan 2010 11:10 |
| Reporter: | Joachim Buechse | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.9 | OS: | Any |
| Assigned to: | Tony Bedford | CPU Architecture: | Any |
| Tags: | GMT, PooledConnection, sessionVariables, time_zone, timestamp, timezone, UTC | ||
[14 Dec 2009 23:29]
Joachim Buechse
Sample application inserting dates once through a non-pooled and once through a pooled connection
Attachment: Main.java (application/octet-stream, text), 6.24 KiB.
[14 Dec 2009 23:30]
Joachim Buechse
Minimalistic implemenation of a connection pool based on MysqlConnectionPoolDataSource
Attachment: ConnPool.java (application/octet-stream, text), 5.26 KiB.
[21 Dec 2009 19:50]
Mark Matthews
Fixed for 5.1.11.
[22 Dec 2009 8:00]
Tonci Grgin
Mark, could this be related to Bug#49759?
[8 Jan 2010 11:11]
Tony Bedford
An entry has been added to the 5.1.11 changelog: For pooled connections, Connector/J did not process the session variable time_zone when set via the URL, resulting in incorrect timestamp values being stored.
Description: Connector/J ignores or misinterprets the the time_zone=GMT session setting given through the URL for pooled connections (it works for non pooled connections). Newer version of Connector/J can be configured to correctly store java.sql.Timestamp (or java.util.Date) objects in the MySQL database by turning of the legacy datetime code using URL parameters. This is of particular importance when working with time spans crossing a a daylight savings time switch. I.e. one timestamp before and one timestamp after the switch. However the functionality is broken for pooled connections. Using the URL parameters useLegacyDatetimeCode=false&sessionVariables=time_zone=GMT to create a regular (non-pooled) connection (ie via com.mysql.jdbc.Driver.connect()) creates a connection that stores Timestamp/Date objects with their correct unix timestamp value. Using the same parameters to setup a MysqlConnectionPoolDataSource, creates connections that do not have the time_zone session setting applied and hence store incorrect timestamp values. This has been tested using mysql-connector-java-5.1.9-bin.jar and the server versions 5.0.45 (Mac OS X) and 5.0.51a-24+lenny2 (Debian). How to repeat: 0) Set the MySQL servers global/system time zone to CET (or some other time zone != GMT). 1) Open a non-pooled connection with com.mysql.jdbc.Driver.connect(). 2) Store the following dates into the DB (ie in table test.tztest) SimpleDateFormat UTC= new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ"); Date s1= UTC.parse("2009-03-29 01:59:59+0100"); Date e1= UTC.parse("2009-03-29 03:00:01+0200"); Date s2= UTC.parse("2009-10-25 02:59:59+0200"); Date e2= UTC.parse("2009-10-25 02:00:02+0100"); ... ps.setTimestamp(1, new Timestamp(s1.getTime())); ... 3) Verify the stored dates using the mysql command line tool: SET SESSION time_zone = UTC; SELECT @@global.time_zone, @@session.time_zone; SHOW VARIABLES LIKE '%zone%'; SELECT * FROM test.tztest; 4) Repeat the date insertion with a pooled connection ie MysqlConnectionPoolDataSource.getPooledConnection().getConnection(). Repeat the verification and notice the differences in the stored dates.