| Bug #50348 | mysql connector/j 5.1.10 render the wrong value for dateTime column in GMT DB | ||
|---|---|---|---|
| Submitted: | 14 Jan 2010 21:53 | Modified: | 20 Aug 2015 20:30 |
| Reporter: | shaoxian yang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | trunk | OS: | Any |
| Assigned to: | Filipe Silva | CPU Architecture: | Any |
| Tags: | dateTime column, DB in GMT zone, JVM timezone different | ||
[14 Jan 2010 22:44]
shaoxian yang
contains test file, sql scripts and executing scripts.
Attachment: bug_test.zip (application/x-zip-compressed, text), 3.52 KiB.
[29 Jan 2010 7:55]
Tonci Grgin
Hi Shaoxian and thanks for your report. These kind of things can be tricky to grasp as nothing's clear and straight forward. But I'll do my best.
[29 Jan 2010 12:03]
Tonci Grgin
Test case for our test framework, working in JDK 1.5.
Attachment: TestBug50348.java (text/x-java), 4.00 KiB.
[29 Jan 2010 12:04]
Tonci Grgin
Shaoxian, as I said, it took whole morning... o Set remote MySQL server to GMT o Start JVM with -Duser.timezone=America/Chicago on CET box (MySQL TZ id 92) mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | GMT | GMT | +--------------------+---------------------+ mysql> select * from bug50348; +----+---------------------+---------------------+ | ID | AD_END_DATE | CREATED_TIMESTAMP | +----+---------------------+---------------------+ | 1 | 2010-12-20 23:59:59 | 2010-01-29 10:27:09 | +----+---------------------+---------------------+ Test case produces: From DB end: 2010-12-21 05:59:59.0 From DB created: 2010-01-29 16:27:09.0 JVM converted TS for GMT: 2010-12-20 17:59:59.0 What puzzles me is this: I expected c/J to convert the date to same value as JVM does (see test case) but it's actually on the "other" side (+6). Now, this might not be a bug but intentional behavior. In any case it needs better documenting. Mark?
[29 Jan 2010 18:43]
shaoxian yang
Hi Toni, It sounds like you and I are on the same page and getting the same test result. Hopefully, we soon know what the reason behind this. Thanks. Shaoxian
[22 Jun 2010 0:04]
Gisella Saavedra
Not just server in GMT. java.sql.Timestamp, when retrieving such a field, has an internal value (long Epoch) incorrect.
[4 Feb 2011 14:28]
Tonci Grgin
Using same test case against latest c/J sources still show a bug for me:
o Set remote MySQL 5.1.31 to GMT, check the settings.
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| GMT | GMT |
+--------------------+---------------------+
o Set local (CET box) JVM to -Duser.region=US -Duser.language=en -Duser.timezone=America/Chicago
o INSERT literal value into DB (2010-12-20T23:59:59.000). Get second value as current_timestamp from server (...`CREATED_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP..., obviously in GMT)
o Time of the test is around 15:00 CET (14:00 GMT, 08:00 CST)
o Calculate JVM converted values for comparison:
String tzdateTimeString = "2010-12-20T23:59:59.000";
String tzdateTimeString2 = "2011-02-04T14:50:50.000";
String tzstring = "GMT";
java.util.Date date = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS");
sdf.setTimeZone(TimeZone.getTimeZone(tzstring));
date = sdf.parse(tzdateTimeString);
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone(tzstring));
cal.setTime(date);
java.sql.Timestamp tztimestamp = new java.sql.Timestamp(date.getTime());
System.out.println("JVM converted TS for GMT: " + tztimestamp.toString());
date = sdf.parse(tzdateTimeString2);
cal.setTime(date);
java.sql.Timestamp tztimestamp2 = new java.sql.Timestamp(date.getTime());
System.out.println("JVM converted NOW TS for GMT: " + tztimestamp2.toString());
o Select the data:
1) useTimezone, JDBC compliant shift and noTimezoneConversionForTimeType set to TRUE
INSERTED DB value: 2010-12-20T23:59:59.000
From DB, INSERTED value: 2010-12-20 23:59:59.0
From DB created TS (server TZ, GMT!): 2011-02-04 13:59:48.0
JVM converted TS for GMT: 2010-12-20 17:59:59.0
JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
2) useTimezone and JDBC compliant shift set to TRUE, noTimezoneConversionForTimeType = false
INSERTED DB value: 2010-12-20T23:59:59.000
From DB, INSERTED value: 2010-12-20 23:59:59.0
From DB created TS (server TZ, GMT!): 2011-02-04 13:59:48.0
JVM converted TS for GMT: 2010-12-20 17:59:59.0
JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
3) useTimezone = true, JDBC compliant shift = false, noTimezoneConversionForTimeType = true
INSERTED DB value: 2010-12-20T23:59:59.000
From DB, INSERTED value: 2010-12-21 05:59:59.0
From DB created TS (server TZ, GMT!): 2011-02-04 19:58:01.0 <<< Here, 7 hours +
JVM converted TS for GMT: 2010-12-20 17:59:59.0
JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
4) useTimezone = true, JDBC compliant shift & noTimezoneConversionForTimeType = false
INSERTED DB value: 2010-12-20T23:59:59.000
From DB, INSERTED value: 2010-12-21 05:59:59.0
From DB created TS (server TZ, GMT!): 2011-02-04 20:01:21.0
JVM converted TS for GMT: 2010-12-20 17:59:59.0
JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
5) ALL options OFF:
INSERTED DB value: 2010-12-20T23:59:59.000
From DB, INSERTED value: 2010-12-20 23:59:59.0
From DB created TS (server TZ, GMT!): 2011-02-04 14:03:32.0 << GMT value
JVM converted TS for GMT: 2010-12-20 17:59:59.0
JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
[20 Aug 2015 20:30]
Daniel So
Added the following entry to the Connector/J 5.1.37 changelog: "When the time zone on the MySQL server was configured to 'GMT' but the client was in a different time zone, Connector/J would make wrong adjustments for event timestamps when working with the server."
Description: This problem happens in the following environment: 1. JVM timezone is America/Chicago by enforcing java user.timezone properties: -Duser.timezone=America/Chicago 2. DB server is runngin in GMT timezone by using --default-time-zone=GMT. DB server is running on linux and timezone table is prepopulate with whatever value from linux timezone files: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql 3. In SQL, Running a query which compares the DB dateTime column with a timestamp mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | GMT | GMT | +--------------------+---------------------+ 1 row in set (0.00 sec) mysql> select ad_end_date from ad where id=1; +---------------------+ | ad_end_date | +---------------------+ | 2010-12-20 23:59:59 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from ad where ad_end_date>='2010-12-21T00:00:01'; Empty set (0.00 sec) In Java, Running the same query to compare the DB dateTime column with a java.sql.Timestamp. However, this time, it will return non empty result, which should be wrong. private static Connection getConnection() { Connection conn = null; Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://10.37.2.24:3306/campaign_mgr?useTimezone=true&noTimezoneConversionForTimeType=true", "ad", "ad"); } private static void getAdStartEndTimeByJDBC() { conn = getConnection(); pst = conn.prepareStatement("select * from ad where id=1"); rs = pst.executeQuery(); while (rs.next()) { java.sql.Timestamp endDate = rs.getTimestamp("ad_end_date"); System.out.println("endDate: " + endDate); } } private static void isDateInRangeByJdbc (java.sql.Timestamp timestamp) { conn = getConnection(); pst = conn.prepareStatement("select * from ad where ad_end_date>=?"); pst.setTimestamp(1, timestamp); rs = pst.executeQuery(); while (rs.next()) { adId = rs.getInt("id"); startDate = rs.getTimestamp("ad_start_date"); endDate = rs.getTimestamp("ad_end_date"); } } private static void testDateTimeColumnInRange() { String dateTimeString = "2010-12-21T00:00:01.000"; String timezone = "GMT"; Timestamp timestamp = getTimestamp(dateTimeString, timezone); isDateInRangeByJdbc(timestamp); } private static Timestamp getTimestamp(String dateTimeString, String timezone) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS"); sdf.setTimeZone(TimeZone.getTimeZone(timezone)); java.util.Date date = null; try { date = sdf.parse(dateTimeString); } catch (ParseException pex) { logger_debug("unexpected Date parsingException: ", pex); throw new RuntimeException(pex); } Calendar cal = Calendar.getInstance(TimeZone.getTimeZone(timezone)); cal.setTime(date); logger_debug("created calendar: " + cal); //java.sql.Timestamp timestamp = new java.sql.Timestamp(cal.getTimeInMillis()); java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime()); return timestamp; } C:\temp\test>java -Duser.timezone=America/Chicago -classpath ".;.\c3p0-0.9.1.2.j ar;.\mysql-connector-java-5.1.10-bin.jar" TimeZoneTest =============== Print out some system property and default timezone ================== userTimezone: America/Chicago tz display name: Central Standard Time tz id: America/Chicago ============ query Ad which id=1 using JDBC ================== startDate: 2009-12-20 06:00:00.0 endDate: 2010-12-21 05:59:59.0 ============ Create a java Calenar and java.sql.Timestamp based on "2010-12-21T00:00:01.000 GMT" created calendar: java.util.GregorianCalendar[time=1292918401000,areFieldsSet=tr ue,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="PST",of fset=-28800000,dstSavings=3600000,useDaylight=true,transitions=185,lastRule=java .util.SimpleTimeZone[id=PST,offset=-28800000,dstSavings=3600000,useDaylight=true ,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200 000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=720000 0,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2010,MONT H=11,WEEK_OF_YEAR=52,WEEK_OF_MONTH=4,DAY_OF_MONTH=21,DAY_OF_YEAR=355,DAY_OF_WEEK =3,DAY_OF_WEEK_IN_MONTH=3,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=1,MILLISE COND=0,ZONE_OFFSET=-28800000,DST_OFFSET=0] Created timestamp: 2010-12-21 02:00:01.0 ================= isDateInRangeByJdbc ==================== adId: 1 adStartDate: 2009-12-20 06:00:00.0 adEndDate: 2010-12-21 05:59:59.0 As you can see, the ad_end_date value printed by jdbc ("select * from ad where id=1") is wrong based on the JVM timezone already. In DB, the dateTime column is "2010-12-20 23:59:59" based on GMT. Therefore, the printout of java.sql.Timestamp based on JVM timezone (America/Chicago, wich is GMT-06:00) should be "2012-12-20 17:59:59". However, as the program printout shows, it is not converting by GMT-06:00, instead it is doing GMT+06:00. Maybe that can also explain why the jdbc query ("select * from ad where ad_end_date>=?") return the wrong result. Strangely, when DB timezone is not GMT, e.g, but something like "America/Los_Angeles", then everything works fine. I can see java.sql.Timestamp based on jvm timezone matches the moment described in DB timezone. I think there is bug in terms of GMT. Maybe logic to do plus or minus is messed up. Thanks. I will attach sample code as well. How to repeat: JVM timezone is America/Chicago DB timezone is GMT And then follow the description steps. Suggested fix: I think there is bug in terms of GMT. Maybe logic to do plus or minus is messed up.