MySQL Bugs: #60675: timestamps for time zone databases

Bug #60675	timestamps for time zone databases
Submitted:	28 Mar 2011 13:16	Modified:	28 Mar 2011 13:19
Reporter:	Daniël van Eeden	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	timezone

Description:
mysql_tzinfo_to_sql generates data for the timezone databases.

The zoneinfo databases comes from the frequently updated tzdata package.

It's not easily to determine how old the imported data is.

How to repeat:
Load timezone info.
Try to determine how old the imported data is.

Suggested fix:
Easy fix:
1. Add a timestamp column to the system table time_zone_transition
ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. Better fix: Let mysql_tzinfo_to_sql determine the version of the data which it imports and add that to the database. I couldn't determine the version of the data by reading the zoneinfo database, so a change to the tzdata package could be needed. Using rpm/dpkg/pkginfo could be a way to determine the version, but that's not portable.

Thank you for the feature request.

As with the new SYS schema in 5.7 add an extra table which is a view with a version number.  Then it's easy to do a select on this version "view/table" to figure out if it's the right one.