mysql: MySQL_Server_Time_Zone_Support

Published by

Posted on August 31, 2011

[[Category:MySQL]]

Reference: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Some customer will attempt to use the SET SESSION time_zone query to set the time zone for their database. This will only work if we have loaded the zone data into the mysql.time_zone_name Table.

One of the error will be
mysql> SET SESSION time_zone = ‘US/Central’;
ERROR 1298 (HY000): Unknown or incorrect time zone: ‘US/Central’ <*missing zone data*>

Log into the server as root

Type Command : mysql
Run this query to check if the zone data already exist
mysql> SELECT COUNT(*) FROM mysql.time_zone_name; *if the count returns 0 , then the zone data will need to be loaded*

Type exist to leave mysql
at the prompt run
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql **you may see some errors unable to load** these can be ignored

This will import the zone data from the system into mysql
Additional details: The mysql_tzinfo_to_sql utility reads your system’s time zone files and generates SQL statements from them. Using the pipe to send those SQL statements to the mysql client, it then processes those statements to load the data into the time zone tables.

now run

mysql> SELECT COUNT(*) FROM mysql.time_zone_name; * you should see a higher number indicating how zone records were imported into mysql *

log into mysql as the user
mysql -u username -p

run the set command to verify setting the time zone works as the user
mysql> SET SESSION time_zone = ‘US/Central’;
Query OK, 0 rows affected (0.20 sec)

The user does not need admin rights to the server to perform the SESSION time_zone option. They just need grant rights to their db.

reference
also Reference

Categories: MySQL