MySQL Bugs: #64058: DATETIME data type botched in queries.

Bug #64058	DATETIME data type botched in queries.
Submitted:	18 Jan 2012 14:14	Modified:	19 Jan 2012 0:27
Reporter:	Laurent Michel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.5.20	OS:	MacOS (10.7.2)
Assigned to:		CPU Architecture:	Any
Tags:	datetime, query

Description:
I have tables with column of type DATETIME. I am writing queries of the form:
select * 
from xxx
where day between t1 and t2

The queries are submitted through JDBC (from Java 1.6) and t1 and t2 are java.sql.Timestamp. The methods used on PreparedStatement are of course setTimestamp(fieldID1,t1) and setTimestamp(fieldID2,t2).

I noticed that the queries were returning the wrong number of rows when spanning time points where DST -> EST transition occurs (My timezone is US/Eastern). I double checked the values and made certain of it, looking at the value of the Java Date as a long (i.e., UTC). 

It finally downed on me that, apparently, the query processing must be converting the Timestamp java type back and forth between an actual Date and a string and _changing_ the value in the process. Let me explain:

If I pick Nov 2, 2009, in term of local timezone the time 1:59AM repeats _TWICE_. Once as the UTC time:
5:59AM and once as UTC 6:59AM (respectively, the value from epoch are 1257055140 and 1257058740).
The java object I pass to the setTimestamp for t2 -- the end of the time interval -- in the SQL query is a correct date and holds the value 5:59UTC (i.e., it holds 1257055140). However, this value is apparently rewritten by the SQL code into 6:59UTC! (1257058740). 

To confirm I just typed the following simple query in a query browser:

select 1257055140,
FROM_UNIXTIME(1257055140),
UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140))

Applying a function and its inverse to a value should return the value.

Yet, the output is:

HEADER:1257055140,FROM_UNIXTIME(1257055140),UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140))
ROW:1257055140,2009-11-01 01:59:00,1257058740

Clearly, the FROM_UNIXTIME that supposedly converts to a DATETIME value got confused and returned the WRONG UTC value. When converted back, this is obvious. Note that this shows the incorrect behavior without a need for Java or JDBC. Hence I suspect that the bug is at the level of MYSQL, not JDBC. 

Bottom line: my queries are not working. The only workaround I can envision is to change all the tables in the schema to store a long integer that represents the number of seconds since the epoch and do all the conversions manually. 

How to repeat:
See above. 

Suggested fix:
Get a correct behavior for the DATETIME type/representation that does not changes the date value when doing internal conversion. From a user standpoint, the query 

select day, ....
from xxx
where day between t1 and t2 

is well defined and the API used from Java uses the correct type (Timestamp), yet the values t1/t2 are apparently corrupted internally by MYSQL itself.

Please, check with a newer version, 5.5.20. Look:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.20-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1257055140, FROM_UNIXTIME(1257055140), UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140))\G
*************************** 1. row ***************************
                               1257055140: 1257055140
                FROM_UNIXTIME(1257055140): 2009-11-01 07:59:00
UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)): 1257055140
1 row in set (0.00 sec)

Valeriy,

Thanks a lot for getting back to me so quickly. I downloaded and installed 5.5.20 on my Mac (running Lion).

I then retested as you suggested. Here is the output:

slive:~ ldm$ /usr/local/mysql/bin/mysql -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1020
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1257055140, FROM_UNIXTIME(1257055140),
    -> UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140))
    -> ;
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 01:59:00       |                                1257058740 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 

I'm afraid it is _not_ fixed. 

Let me know what I can do to help.

Sincerely,

--
  Laurent

Thank you for the feedback.

Have you refreshed your timezone tables as described at http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html? Check also if your OS timezone tables are up to date. If you don't, please, reload and try again.

If problem still exists send us output of query SHOW VARIABLES LIKE '%time%' and dump of your mysql time_zone tables.

Thanks for the response.  To be clear: this was a fresh install of 5.5.20 from the DMG (install is 64bit). 
It did not fi the problem. Here is the session:

slive:~ ldm$ /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | /usr/local/mysql/bin/mysql -u root mysql
Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
slive:~ ldm$ /usr/local/mysql/bin/mysql -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1257055140, FROM_UNIXTIME(1257055140),UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 01:59:00       |                                1257058740 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE '%time%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
| connect_timeout            | 10                |
| datetime_format            | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout     | 300               |
| flush_time                 | 0                 |
| innodb_lock_wait_timeout   | 50                |
| innodb_old_blocks_time     | 0                 |
| innodb_rollback_on_timeout | OFF               |
| interactive_timeout        | 28800             |
| lc_time_names              | en_US             |
| lock_wait_timeout          | 31536000          |
| long_query_time            | 10.000000         |
| net_read_timeout           | 30                |
| net_write_timeout          | 60                |
| slave_net_timeout          | 3600              |
| slow_launch_time           | 2                 |
| system_time_zone           | EST               |
| time_format                | %H:%i:%s          |
| time_zone                  | SYSTEM            |
| timed_mutexes              | OFF               |
| timestamp                  | 1326907203        |
| wait_timeout               | 28800             |
+----------------------------+-------------------+
21 rows in set (0.00 sec)

As you can see, I refreshed the table per the document you referred to and I stopped and restarted the MYSQL server. I then connected, tested and used the command you gave (SHOW VARIABLES LIKE '%time%';)

I'm attaching a dump of the timezone tables I could find.

time_zone table

Attachment: time_zone.csv (text/csv), 3.28 KiB.

time_zone_leap_second

Attachment: time_zone_leap_second.csv (text/csv), 27 bytes.

time_zone_leap_transition (empty!)

Attachment: time_zone_leap_transition_type.csv (text/csv), 17.88 KiB.

time_zone_name table

Attachment: time_zone_name.csv (text/csv), 10.67 KiB.

time_zone_transition table

Attachment: time_zone_transition.csv (text/csv), 15.31 KiB.

Thank you for the feedback.

I still can not repeat describe behavior. Do you build MySQL sources yourself or use binary package? Please provide name of the package you use.

Also content of the tables is interesting: I have more timezones in mine, thought it does not affect my installation. What query SET time_zone = 'EST'; outputs in your environment?

I used the pre-made DMG downloaded directly from the MYSQL site. I did _not_ compile from source.
I tested on two distinct Mac (both running Lion 10.7.2). Same behavior in both case. I'm on the east coast, so my timezone should be EST.

mysql> SET
    -> time_zone = 'EST';
Query OK, 0 rows affected (0.00 sec)

mysql> select 1257055140, FROM_UNIXTIME(1257055140),UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 00:59:00       |                                1257055140 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Aha!

That changes it. What I do not understand though is why. The SHOW VARIABLES like '%time%' I ran shows that the system timezone is EST (see the previous mail). How can I make this permanent?

I redid a session to make sure:

slive:~ ldm$ /usr/local/mysql/bin/mysql -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5074
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1257055140, FROM_UNIXTIME(1257055140),UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 01:59:00       |                                1257058740 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET time_zone='EST';
Query OK, 0 rows affected (0.00 sec)

mysql> select 1257055140, FROM_UNIXTIME(1257055140),UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 00:59:00       |                                1257055140 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Rerunning: SHOW VARIABLES LIKE '%time%';

shows that the relevant entry changed from SYSTEM to EST

| time_zone                  | SYSTEM            |

=>

| time_zone                  | EST               |

Note that 

| system_time_zone           | EST               |

was already at EST to begin with.

Insights ? How can I make this permanent?

Thank you for the feedback.

Good we found the workaround. You can add SET time_zone='EST'; into init_file or init_sql option or just start server with option timezone=EST.

Interesting on my box I got error when tried to set this timezone if used your timezone tables.

Regarding to bug itself this looks like platform specific, so I leave it open until find somebody who can test it on Lion.

I was able to reproduce the submitter's original test case, Mac OSX Lion 10.7.2, MySQL 5.5.20-community dmg installation, and switched my Mac timezone to EST (using "Date & Time Preferences").

server$ date
Wed 18 Jan 2012 18:34:33 EST

server$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.20 MySQL Community Server (GPL)

...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%time%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
| connect_timeout            | 10                |
| datetime_format            | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout     | 300               |
| flush_time                 | 0                 |
| innodb_lock_wait_timeout   | 50                |
| innodb_old_blocks_time     | 0                 |
| innodb_rollback_on_timeout | OFF               |
| interactive_timeout        | 28800             |
| lc_time_names              | en_US             |
| lock_wait_timeout          | 31536000          |
| long_query_time            | 10.000000         |
| net_read_timeout           | 30                |
| net_write_timeout          | 60                |
| slave_net_timeout          | 3600              |
| slow_launch_time           | 2                 |
| system_time_zone           | PST               |
| time_format                | %H:%i:%s          |
| time_zone                  | SYSTEM            |
| timed_mutexes              | OFF               |
| timestamp                  | 1326929688        |
| wait_timeout               | 28800             |
+----------------------------+-------------------+
21 rows in set (0.00 sec)

mysql> select 1257055140, FROM_UNIXTIME(1257055140), UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 01:59:00       |                                1257058740 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1257055140), FROM_UNIXTIME(1257058740);
+---------------------------+---------------------------+
| FROM_UNIXTIME(1257055140) | FROM_UNIXTIME(1257058740) |
+---------------------------+---------------------------+
| 2009-11-01 01:59:00       | 2009-11-01 01:59:00       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> set time_zone='EST';
Query OK, 0 rows affected (0.02 sec)

mysql> select 1257055140, FROM_UNIXTIME(1257055140), UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 00:59:00       |                                1257055140 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1257055140), FROM_UNIXTIME(1257058740);
+---------------------------+---------------------------+
| FROM_UNIXTIME(1257055140) | FROM_UNIXTIME(1257058740) |
+---------------------------+---------------------------+
| 2009-11-01 00:59:00       | 2009-11-01 01:59:00       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

I can't reproduce it for PST.

Note in my reproduction above I had changed the OS timezone without restarting MySQL so MySQL shows system_timezone = PST though it's really EST. Can still reproduce after restarting MySQL:

mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
...
| system_time_zone           | EST               |
| time_format                | %H:%i:%s          |
| time_zone                  | SYSTEM            |
...
+----------------------------+-------------------+
21 rows in set (0.00 sec)

mysql> select 1257055140, FROM_UNIXTIME(1257055140),    -> UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140));
+------------+---------------------------+-------------------------------------------+
| 1257055140 | FROM_UNIXTIME(1257055140) | UNIX_TIMESTAMP(FROM_UNIXTIME(1257055140)) |
+------------+---------------------------+-------------------------------------------+
| 1257055140 | 2009-11-01 01:59:00       |                                1257058740 |
+------------+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)

I can reproduce it as well in Oracle Linux 6.1

mysql> SET @TIME=1257055140;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @TIME, FROM_UNIXTIME(@TIME), UNIX_TIMESTAMP(FROM_UNIXTIME(@TIME)), UNIX_TIMESTAMP(FROM_UNIXTIME(@TIME))-@TIME;
+------------+----------------------+--------------------------------------+--------------------------------------------+   
| @TIME      | FROM_UNIXTIME(@TIME) | UNIX_TIMESTAMP(FROM_UNIXTIME(@TIME)) | UNIX_TIMESTAMP(FROM_UNIXTIME(@TIME))-@TIME |   
+------------+----------------------+--------------------------------------+--------------------------------------------+   
| 1257055140 | 2009-11-01 01:59:00  |                           1257058740 |                                       3600 |   
+------------+----------------------+--------------------------------------+--------------------------------------------+   
1 row in set (0.00 sec)                                                                                                     
Using US EST as the system time zone

Tests on OEL 6.1

Attachment: bug_64058.sql (application/octet-stream, text), 13.32 KiB.

The tests in bug_64058.sql show that the issue happens just around end of daylight savings for US EST 2009, but not at the equivalent time at the end of daylight savings 2011.

I was not able to reproduce it for the Australia/Sydney timezone either.