MySQL Timezone support: Updating and Monitoring

How old is your timezone info?
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