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.
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.