Bug #27057 TZ lookup table update
Submitted: 12 Mar 2007 17:47 Modified: 13 Apr 2007 7:26
Reporter: Kayra Otaner Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.20 OS:Linux (RHEL4)
Assigned to: CPU Architecture:Any
Tags: DST, timezone

[12 Mar 2007 17:47] Kayra Otaner
Description:
I've updated tz tables under 'mysql' database and expected them to effect convert_tz functions right away. I did test it on rhel3 5.0.16 before and it worked fine right away. When I roll tz table updates to production I've noticed that convert_tz function doesn't work properly. I've even renamed, truncated time_zone_names table under 'mysql' db and convert_tz was still returning me incorrect values. I've cleared query cache, flushed tables, logs etc none worked until I restart MySQL service.

How to repeat:
Please see sequence below. As I've said, tz tables were updated prior to these SQL statements, query cache was cleared/purged:

mysql> rename table time_zone_name to time_zone_name2;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','US/Central','US/Eastern');
ERROR 1146 (42S02): Table 'mysql.time_zone_name' doesn't exist
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','US/Central','US/Eastern');
ERROR 1146 (42S02): Table 'mysql.time_zone_name' doesn't exist
mysql> select * from time_zone_name2;
Empty set (0.00 sec)

mysql> rename table time_zone_name2 to time_zone_name;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','US/Central','US/Eastern');
+-------------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','US/Central','US/Eastern') |
+-------------------------------------------------------------+
| 2004-01-01 13:00:00                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from time_zone_name;
Empty set (0.00 sec)

Suggested fix:
Restarting MySQL solved issue.
[13 Mar 2007 7:26] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat your actions with a newer version, 5.0.37, and inform about the results.
[13 Apr 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".