As the timezone database gets updated frequently, it's important to update the internal timezone database in MySQL. This is needed for the CONVERT_TZ() function for work properly.
It's not easy to determine if the data in mysql is older than the data from the tzdata database. It get's a bit better if you use this hack before importing the zone database:
ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;I've created Bug #60675 to get the system database updated.
Which database serves still need timezone info?
Use MySQL Enterprise Monitor with custom rules.
1. Add data query
Open /opt/mysql/enterprise/agent/share/mysql-monitor-agent/items/custom.xml in an editor and add this:
<class>
<namespace>mysql</namespace>
<classname>tzdata_available</classname>
<query><!--[CDATA[SELECT CONVERT_TZ(NOW(),'GMT','MET') IS NOT NULL]]--></query>
</class></blockquote>
2. Restart the mysql-monitor-agent
3. Create a custom rule in the MySQL Enterprise Dashboard (Advisor→Manage Rules→create rule):
Rule Name: Timezone data is not loaded.
Expression: %tzdata_avail% == THRESHOLD
Critical: 0
Variable Assignment
Variable: %tzdata_avail%
Data Item: mysql:tzdata_available:tzdata_available
Instance: local
Default Frequency: 30 minutes
Problem Description: "There is no timezone info loaded in mysql. This causes the CONVERT_TZ() function to return NULL instead of the requested conversion."
Advice: "None Specified"
Recommended Action: "Use the mysql_tzinfo_to_sql utility to convert the zoneinfo database to SQL and load the converted data into the mysql database."
Links and Further Reading:
* {moreInfo:mysql_tzinfo_to_sql — Load the Time Zone Tables|http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html}
* {moreInfo:MySQL Server Time Zone Support|http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html}
4. Add the rule to a schedule