WL#4908: FLUSH TIMEZONES to reload timezone info into MySQL Server

Affects: Server-6.0   —   Status: Un-Assigned   —   Priority: Medium

Sometimes (rarely; the known case happened in 2006) there are changes in
timezones (in 2006, DST lasted much longer than usual per a decision of Congress
as explained in 
http://www.npr.org/templates/story/story.php?storyId=6393658 ).
For the MySQL Server (mysqld) to learn about the corrected timezone, depending
on the setup it needs to be notified either after an upgrade of the OS' timezone
info or after an upgrade of the mysql.time_zone* tables. A simple way to do this
is to restart mysqld but it also kills any mysqld's cache (MyISAM key cache,
Query Cache, etc) so mysqld will be slow after restart, which should be avoided
under production.
To avoid this, Mark Leith proposes a patch which implements a "FLUSH TIMEZONES"
command, which one can issue instead of restarting mysqld.
Mark's patch:

===== include/mysql_com.h 1.112 vs edited =====
--- 1.112/include/mysql_com.h	2006-12-23 19:04:05 +00:00
+++ edited/include/mysql_com.h	2007-03-14 11:14:15 +00:00
@@ -116,6 +116,7 @@
 #define REFRESH_QUERY_CACHE_FREE 0x20000L /* pack query cache */
 #define REFRESH_DES_KEY_FILE	0x40000L
 #define REFRESH_USER_RESOURCES	0x80000L
+#define REFRESH_TIMEZONES       0x100000L
 
 #define CLIENT_LONG_PASSWORD	1	/* new more secure passwords */
 #define CLIENT_FOUND_ROWS	2	/* Found instead of affected rows */
===== sql/lex.h 1.145 vs edited =====
--- 1.145/sql/lex.h	2006-12-23 19:04:25 +00:00
+++ edited/sql/lex.h	2007-03-14 12:07:51 +00:00
@@ -483,6 +483,7 @@
   { "TIMESTAMP",	SYM(TIMESTAMP)},
   { "TIMESTAMPADD",     SYM(TIMESTAMP_ADD)},
   { "TIMESTAMPDIFF",    SYM(TIMESTAMP_DIFF)},
+  { "TIMEZONES",	SYM(TIMEZONES_SYM)},
   { "TINYBLOB",		SYM(TINYBLOB)},
   { "TINYINT",		SYM(TINYINT)},
   { "TINYTEXT",		SYM(TINYTEXT)},
===== sql/mysqld.cc 1.592 vs edited =====
--- 1.592/sql/mysqld.cc	2007-03-01 07:41:12 +00:00
+++ edited/sql/mysqld.cc	2007-03-14 00:37:00 +00:00
@@ -2392,7 +2392,7 @@
 	reload_acl_and_cache((THD*) 0,
 			     (REFRESH_LOG | REFRESH_TABLES | REFRESH_FAST |
 			      REFRESH_GRANT |
-			      REFRESH_THREADS | REFRESH_HOSTS),
+			      REFRESH_THREADS | REFRESH_HOSTS | REFRESH_TIMEZONES),
 			     (TABLE_LIST*) 0, ¬_used); // Flush logs
       }
       break;
===== sql/sql_parse.cc 1.605 vs edited =====
--- 1.605/sql/sql_parse.cc	2007-02-27 08:46:45 +00:00
+++ edited/sql/sql_parse.cc	2007-03-14 12:57:34 +00:00
@@ -6943,6 +6943,11 @@
 #endif
  if (options & REFRESH_USER_RESOURCES)
    reset_mqh((LEX_USER *) NULL);
+ if (options & REFRESH_TIMEZONES)
+ {
+   tmp_write_to_binlog= 0;
+   tzset();
+ }
  *write_to_binlog= tmp_write_to_binlog;
  return result;
 }
===== sql/sql_yacc.yy 1.505 vs edited =====
--- 1.505/sql/sql_yacc.yy	2007-03-01 07:47:47 +00:00
+++ edited/sql/sql_yacc.yy	2007-03-14 11:54:47 +00:00
@@ -804,6 +804,7 @@
 %token  TIMESTAMP_ADD
 %token  TIMESTAMP_DIFF
 %token  TIME_SYM
+%token  TIMEZONES_SYM
 %token  TINYBLOB
 %token  TINYINT
 %token  TINYTEXT
@@ -7113,7 +7114,8 @@
         | SLAVE         { Lex->type|= REFRESH_SLAVE; }
         | MASTER_SYM    { Lex->type|= REFRESH_MASTER; }
 	| DES_KEY_FILE	{ Lex->type|= REFRESH_DES_KEY_FILE; }
- 	| RESOURCES     { Lex->type|= REFRESH_USER_RESOURCES; };
+ 	| RESOURCES     { Lex->type|= REFRESH_USER_RESOURCES; }
+ 	| TIMEZONES_SYM { Lex->type|= REFRESH_TIMEZONES; };
 
 opt_table_list:
 	/* empty */  {;}
@@ -8102,6 +8104,7 @@
 	| TIMESTAMP_ADD		{}
 	| TIMESTAMP_DIFF	{}
 	| TIME_SYM		{}
+	| TIMEZONES_SYM		{}    
 	| TYPES_SYM		{}
         | TYPE_SYM              {}
         | UDF_RETURNS_SYM       {}