BUG#9953: CONVERT_TZ requires mysql.time_zone_name to be locked

The problem was that some facilities (like CONVERT_TZ() function or
server HELP statement) may require implicit access to some tables in
'mysql' database.  This access was done by ordinary means of adding
such tables to the list of tables the query is going to open.
However, if we issued LOCK TABLES before that, we would get "table
was not locked" error trying to open such implicit tables.

The solution is to treat certain tables as MySQL system tables, like
we already do for mysql.proc.  Such tables may be opened for reading
at any moment regardless of any locks in effect.  The cost of this is
that system table may be locked for writing only together with other
system tables, it is disallowed to lock system tables for writing and
have any other lock on any other table.

After this patch the following tables are treated as MySQL system
tables:
  mysql.help_category
  mysql.help_keyword
  mysql.help_relation
  mysql.help_topic
  mysql.proc (it already was)
  mysql.time_zone
  mysql.time_zone_leap_second
  mysql.time_zone_name
  mysql.time_zone_transition
  mysql.time_zone_transition_type

These tables are now opened with open_system_tables_for_read() and
closed with close_system_tables(), or one table may be opened with
open_system_table_for_update() and closed with close_thread_tables()
(the latter is used for mysql.proc table, which is updated as part of
normal MySQL server operation).  These functions may be used when
some tables were opened and locked already.

NOTE: online update of time zone tables is not possible during
replication, because there's no time zone cache flush neither on LOCK
TABLES, nor on FLUSH TABLES, so the master may serve stale time zone
data from cache, while on slave updated data will be loaded from the
time zone tables.


mysql-test/r/help.result:
  Update result.
mysql-test/r/lock.result:
  Update result.
mysql-test/r/sp-error.result:
  Update result.
mysql-test/r/timezone2.result:
  Add result for bug#9953: CONVERT_TZ requires mysql.time_zone_name
  to be locked.
mysql-test/r/view.result:
  Update result: use table t3 rather than utilize MySQL system table.
mysql-test/t/help.test:
  Test that we can use HELP even under LOCK TABLES.
mysql-test/t/lock.test:
  Test LOCK TABLE on system tables.
mysql-test/t/timezone2.test:
  Add test case for bug#9953: CONVERT_TZ requires mysql.time_zone_name
  to be locked.
mysql-test/t/view.test:
  Update test: use table t3 rather that utilize MySQL system table.
sql/handler.h:
  Fix comment for 'count' parameter of check_if_locking_is_allowed().
  Add 'current' and 'system_count' parameters.
sql/item_create.cc:
  We no longer have LEX::add_time_zone_tables_to_query_tables().
sql/item_timefunc.cc:
  We no longer have LEX::time_zone_tables_used, so
  Item_func_convert_tz::fix_fields() became the same as base
  Item_date_func::fix_fields().
  
  my_tz_find() no longer takes table list, but takes THD pointer now.
sql/item_timefunc.h:
  Remove dead field and method.
sql/lock.cc:
  Pass values for 'current' and 'system_count' to
  check_if_locking_is_allowed().
sql/log_event.cc:
  We no longer have my_tz_find_with_opening_tz_tables(), its functions is
  performed by my_tz_find().
sql/mysql_priv.h:
  Add functions to work with MySQL system tables.
sql/set_var.cc:
  my_tz_find() no longer takes table list, but takes THD pointer now.
sql/sp.cc:
  Remove close_proc_table().  Use close_system_tables() instead.
  
  Use open_system_tables_for_read() and open_system_table_for_update().
sql/sp.h:
  Remove close_proc_table() declaration.
sql/sql_base.cc:
  Add implementation of open_system_tables_for_read(),
  close_system_tables(), open_system_table_for_update().
sql/sql_help.cc:
  Operate on MySQL system tables mysql.help_* with
  open_system_tables_for_read() and close_system_tables() to allow the
  usage of HELP statement under LOCK TABLES.
sql/sql_lex.cc:
  Remove LEX::time_zone_tables_used and
  LEX::add_time_zone_tables_to_query_tables() which are no longer used.
sql/sql_lex.h:
  Remove LEX::time_zone_tables_used and
  LEX::add_time_zone_tables_to_query_tables() which are no longer used.
sql/sql_parse.cc:
  Remove references to LEX::time_zone_tables_used and
  my_tz_check_n_skip_implicit_tables() which are no longer used.
sql/sql_show.cc:
  Use close_system_tables() instead of removed close_proc_table().
sql/sql_view.cc:
  LEX::time_zone_tables_used is no longer there.
sql/sql_yacc.yy:
  LEX::add_time_zone_tables_to_query_tables() is no longer there.
sql/table.cc:
  Add more tables that should be treated as MySQL system tables.
sql/share/errmsg.txt:
  Change the error message, as now we allow write-locking of several
  system tables if not mixed with ordinary tables.
sql/tztime.cc:
  Do not add time zone tables to the list of query tables in
  tz_init_table_list().
  
  Remove fake_time_zone_tables_list and my_tz_get_tables_list().
  
  In my_tz_init(), open mysql.time_zone_leap_second with
  simple_open_n_lock_tables(), but pass time zone name to my_tz_find(),
  which will open and close time zone tables as necessary.
  
  In tz_load_from_open_tables() do not call table->use_all_columns(), as
  this was already done in open_system_tables_for_read().
  
  my_tz_find() takes THD pointer instead of table list, and calls
  open_system_tables_for_read() and close_system_tables() as necessary.
  
  Remove my_tz_find_with_opening_tz_tables().
sql/tztime.h:
  Remove declarations of my_tz_get_table_list(),
  my_tz_find_with_opening_tz_tables(), fake_time_zone_tables_list,
  definition of my_tz_check_n_skip_implicit_tables().
  Update prototype for my_tz_find().
storage/csv/ha_tina.cc:
  Add new parameters to check_if_locking_is_allowed().
storage/csv/ha_tina.h:
  Add new parameters to check_if_locking_is_allowed().
storage/myisam/ha_myisam.cc:
  Add new parameters to check_if_locking_is_allowed().  In this
  function we count system tables.  If there are system tables, but
  there are also non-system tables, we report an error.
storage/myisam/ha_myisam.h:
  Add new parameters to check_if_locking_is_allowed().

35 files changed, 429 insertions, 364 deletions

diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result
index 85ca832828d..4b7e320454e 100644
--- a/mysql-test/r/help.result
+++ b/mysql-test/r/help.result
@@ -257,3 +257,12 @@ delete from mysql.help_relation where help_keyword_id=@keyword1_id and help_topi
 delete from mysql.help_relation where help_keyword_id=@keyword2_id and help_topic_id=@topic1_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic3_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic4_id;
+End of 4.1 tests.
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT);
+LOCK TABLES t1 WRITE;
+HELP no_such_topic;
+name	is_it_category
+UNLOCK TABLES;
+DROP TABLE t1;
+End of 5.1 tests.
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index 7cd223197e7..1a2099b7a94 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -68,6 +68,7 @@ ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
 delete t2 from t1,t2 where t1.a=t2.a;
 ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
 drop table t1,t2;
+End of 4.1 tests.
 drop table if exists t1;
 create table t1 (a int);
 lock table t1 write;
@@ -75,3 +76,23 @@ flush tables with read lock;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 unlock tables;
 drop table t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT);
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc READ;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;
+UNLOCK TABLES;
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
+DROP TABLE t1;
+End of 5.1 tests.
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 0ed92aa9e7a..abf64640e94 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -292,9 +292,9 @@ call p()|
 unlock tables|
 drop procedure p|
 lock tables t1 read, mysql.proc write|
-ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 lock tables mysql.proc write, mysql.user write|
-ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
+ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 lock tables t1 read, mysql.proc read|
 unlock tables|
 lock tables mysql.proc write|
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index f7631e9657a..32db7ea7fa9 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -285,3 +285,14 @@ ldt	ldt2
 drop table t1;
 drop function f1;
 SET GLOBAL log_bin_trust_function_creators = 0;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (t TIMESTAMP);
+INSERT INTO t1 VALUES (NULL), (NULL);
+LOCK TABLES t1 WRITE;
+SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
+CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL
+0
+UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
+UNLOCK TABLES;
+DROP TABLE t1;
+End of 5.1 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 5e81df99d18..c18042be854 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -900,6 +900,7 @@ drop view v1;
 drop table t1;
 create table t1 (col1 int);
 create table t2 (col1 int);
+create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
@@ -1004,8 +1005,8 @@ ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
 ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'.
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
-insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
-ERROR 23000: Column 'Use_leap_seconds' cannot be null
+insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+ERROR 23000: Column 'col1' cannot be null
 create algorithm=temptable view v4 as select * from t1;
 insert into t1 values (1),(2),(3);
 insert into t1 (col1) values ((select max(col1) from v4));
@@ -1017,7 +1018,7 @@ NULL
 3
 3
 drop view v4,v3,v2,v1;
-drop table t1,t2;
+drop table t1,t2,t3;
 create table t1 (s1 int);
 create view v1 as select * from t1;
 handler v1 open as xx;
diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test
index ff431fb4ebd..5f234d7c462 100644
--- a/mysql-test/t/help.test
+++ b/mysql-test/t/help.test
@@ -114,4 +114,25 @@ delete from mysql.help_relation where help_keyword_id=@keyword2_id and help_topi
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic3_id;
 delete from mysql.help_relation where help_keyword_id=@keyword3_id and help_topic_id=@topic4_id;
 
-# End of 4.1 tests
+--echo End of 4.1 tests.
+
+#
+# Test that we can use HELP even under LOCK TABLES.  See bug#9953:
+# CONVERT_TZ requires mysql.time_zone_name to be locked.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+LOCK TABLES t1 WRITE;
+
+HELP no_such_topic;
+
+UNLOCK TABLES;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests.
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index fb5e45433e9..2b8b430f096 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -92,7 +92,8 @@ delete from t2 using t1,t2 where t1.a=t2.a;
 delete t2 from t1,t2 where t1.a=t2.a;
 drop table t1,t2;
 
-# End of 4.1 tests
+--echo End of 4.1 tests.
+
 
 #
 # Bug#18884 "lock table + global read lock = crash"
@@ -108,3 +109,44 @@ flush tables with read lock;
 unlock tables;
 drop table t1;
 
+
+#
+# Test LOCK TABLE on system tables.  See bug#9953: CONVERT_TZ requires
+# mysql.time_zone_name to be locked.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone READ, mysql.proc READ;
+UNLOCK TABLES;
+
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;
+UNLOCK TABLES;
+
+# If at least one system table is locked for WRITE, then all other
+# tables should be system tables locked also for WRITE.
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;
+
+--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
+LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests.
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 5e1f74d388f..4f70539ca8d 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -246,3 +246,31 @@ drop function f1;
 SET GLOBAL log_bin_trust_function_creators = 0;
 
 # End of 5.0 tests
+
+
+#
+# BUG#9953: CONVERT_TZ requires mysql.time_zone_name to be locked
+# BUG#19339: CONVERT_TZ(): overly aggressive in locking time_zone_name
+# table
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (t TIMESTAMP);
+INSERT INTO t1 VALUES (NULL), (NULL);
+
+LOCK TABLES t1 WRITE;
+
+# The following two queries should not return error that time zone
+# tables aren't locked.  We use IS NULL below to supress timestamp
+# result.
+SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
+UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
+
+UNLOCK TABLES;
+
+DROP TABLE t1;
+
+
+--echo End of 5.1 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 520babafb7e..abb4a803107 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -832,6 +832,7 @@ drop table t1;
 #
 create table t1 (col1 int);
 create table t2 (col1 int);
+create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
@@ -938,7 +939,7 @@ insert into v3 (col1) values ((select max(col1) from v2));
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
 -- error 1048
-insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
 # temporary table algorithm view should be equal to subquery in the from clause
 create algorithm=temptable view v4 as select * from t1;
 insert into t1 values (1),(2),(3);
@@ -946,7 +947,7 @@ insert into t1 (col1) values ((select max(col1) from v4));
 select * from t1;
 
 drop view v4,v3,v2,v1;
-drop table t1,t2;
+drop table t1,t2,t3;
 
 #
 # HANDLER with VIEW
diff --git a/sql/handler.h b/sql/handler.h
index 82970cc1ac6..2b74b28d9ee 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -974,7 +974,11 @@ public:
       check_if_locking_is_allowed()
         thd     Handler of the thread, trying to lock the table
         table   Table handler to check
-        count   Number of locks already granted to the table
+        count   Total number of tables to be locked
+        current Index of the current table in the list of the tables
+                to be locked.
+        system_count Pointer to the counter of system tables seen thus
+                     far.
         called_by_privileged_thread TRUE if called from a logger THD
                                     (general_log_thd or slow_log_thd)
                                     or by a privileged thread, which
@@ -993,7 +997,8 @@ public:
   */
   virtual bool check_if_locking_is_allowed(uint sql_command,
                                            ulong type, TABLE *table,
-                                           uint count,
+                                           uint count, uint current,
+                                           uint *system_count,
                                            bool called_by_privileged_thread)
   {
     return TRUE;
diff --git a/sql/item_create.cc b/sql/item_create.cc
index ff5825ef389..6a1e87a3aae 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -2877,9 +2877,6 @@ Create_func_convert_tz Create_func_convert_tz::s_singleton;
 Item*
 Create_func_convert_tz::create(THD *thd, Item *arg1, Item *arg2, Item *arg3)
 {
-  if (thd->lex->add_time_zone_tables_to_query_tables(thd))
-    return NULL;
-
   return new (thd->mem_root) Item_func_convert_tz(arg1, arg2, arg3);
 }
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 00f077839c3..bfb2e8c33cc 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1927,19 +1927,6 @@ void Item_func_convert_tz::fix_length_and_dec()
 }
 
 
-bool
-Item_func_convert_tz::fix_fields(THD *thd_arg, Item **ref)
-{
-  String str;
-  if (Item_date_func::fix_fields(thd_arg, ref))
-    return TRUE;
-
-  tz_tables= thd_arg->lex->time_zone_tables_used;
-
-  return FALSE;
-}
-
-
 String *Item_func_convert_tz::val_str(String *str)
 {
   TIME time_tmp;
@@ -1974,16 +1961,17 @@ bool Item_func_convert_tz::get_date(TIME *ltime,
 {
   my_time_t my_time_tmp;
   String str;
+  THD *thd= current_thd;
 
   if (!from_tz_cached)
   {
-    from_tz= my_tz_find(args[1]->val_str(&str), tz_tables);
+    from_tz= my_tz_find(thd, args[1]->val_str(&str));
     from_tz_cached= args[1]->const_item();
   }
 
   if (!to_tz_cached)
   {
-    to_tz= my_tz_find(args[2]->val_str(&str), tz_tables);
+    to_tz= my_tz_find(thd, args[2]->val_str(&str));
     to_tz_cached= args[2]->const_item();
   }
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index e53826ce3df..3a59abf1bda 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -641,8 +641,6 @@ class Time_zone;
 */
 class Item_func_convert_tz :public Item_date_func
 {
-  /* Cached pointer to list of pre-opened time zone tables. */
-  TABLE_LIST *tz_tables;
   /*
     If time zone parameters are constants we are caching objects that
     represent them (we use separate from_tz_cached/to_tz_cached members
@@ -658,7 +656,6 @@ class Item_func_convert_tz :public Item_date_func
   double val_real() { return (double) val_int(); }
   String *val_str(String *str);
   const char *func_name() const { return "convert_tz"; }
-  bool fix_fields(THD *, Item **);
   void fix_length_and_dec();
   bool get_date(TIME *res, uint fuzzy_date);
   void cleanup();
diff --git a/sql/lock.cc b/sql/lock.cc
index 533307c6b85..20ff8db691d 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -692,6 +692,7 @@ static MYSQL_LOCK *get_lock_data(THD *thd, TABLE **table_ptr, uint count,
 
   DBUG_PRINT("info", ("count %d", count));
   *write_lock_used=0;
+  uint system_count= 0;
   for (i=tables=lock_count=0 ; i < count ; i++)
   {
     if (table_ptr[i]->s->tmp_table != TMP_TABLE)
@@ -705,7 +706,7 @@ static MYSQL_LOCK *get_lock_data(THD *thd, TABLE **table_ptr, uint count,
     */
     if (!table_ptr[i]-> file->
           check_if_locking_is_allowed(thd->lex->sql_command, thd->lex->type,
-                                      table_ptr[i], count,
+                                      table_ptr[i], count, i, &system_count,
                                       (thd == logger.get_general_log_thd()) ||
                                       (thd == logger.get_slow_log_thd()) ||
                                       (thd == logger.get_privileged_thread())))
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 54d75449cd5..89b3b18e4d5 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -1974,8 +1974,7 @@ int Query_log_event::exec_event(struct st_relay_log_info* rli,
       if (time_zone_len)
       {
         String tmp(time_zone_str, time_zone_len, &my_charset_bin);
-        if (!(thd->variables.time_zone=
-              my_tz_find_with_opening_tz_tables(thd, &tmp)))
+        if (!(thd->variables.time_zone= my_tz_find(thd, &tmp)))
         {
           my_error(ER_UNKNOWN_TIME_ZONE, MYF(0), tmp.c_ptr());
           thd->variables.time_zone= global_system_variables.time_zone;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 3b00149c63a..25b9c625168 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1389,6 +1389,12 @@ int abort_and_upgrade_lock(ALTER_PARTITION_PARAM_TYPE *lpt);
 void close_open_tables_and_downgrade(ALTER_PARTITION_PARAM_TYPE *lpt);
 void mysql_wait_completed_table(ALTER_PARTITION_PARAM_TYPE *lpt, TABLE *my_table);
 
+/* Functions to work with system tables. */
+bool open_system_tables_for_read(THD *thd, TABLE_LIST *table_list,
+                                 Open_tables_state *backup);
+void close_system_tables(THD *thd, Open_tables_state *backup);
+TABLE *open_system_table_for_update(THD *thd, TABLE_LIST *one_table);
+
 bool close_cached_tables(THD *thd, bool wait_for_refresh, TABLE_LIST *tables, bool have_lock = FALSE);
 void copy_field_from_tmp_record(Field *field,int offset);
 bool fill_record(THD *thd, Field **field, List<Item> &values,
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 667abc0af18..7b6301c480b 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -2857,8 +2857,7 @@ bool sys_var_thd_time_zone::check(THD *thd, set_var *var)
   String str(buff, sizeof(buff), &my_charset_latin1);
   String *res= var->value->val_str(&str);
 
-  if (!(var->save_result.time_zone=
-        my_tz_find(res, thd->lex->time_zone_tables_used)))
+  if (!(var->save_result.time_zone= my_tz_find(thd, res)))
   {
     my_error(ER_UNKNOWN_TIME_ZONE, MYF(0), res ? res->c_ptr() : "NULL");
     return 1;
@@ -2919,8 +2918,7 @@ void sys_var_thd_time_zone::set_default(THD *thd, enum_var_type type)
        We are guaranteed to find this time zone since its existence
        is checked during start-up.
      */
-     global_system_variables.time_zone=
-       my_tz_find(&str, thd->lex->time_zone_tables_used);
+     global_system_variables.time_zone= my_tz_find(thd, &str);
    }
    else
      global_system_variables.time_zone= my_tz_SYSTEM;
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 29fde49bbd6..c5bda4ac7a6 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5518,8 +5518,7 @@ ER_M_BIGGER_THAN_D 42000 S1009
         eng "For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '%-.64s')."
         ger "Für FLOAT(M,D), DOUBLE(M,D) oder DECIMAL(M,D) muss M >= D sein (Feld '%-.64s')"
 ER_WRONG_LOCK_OF_SYSTEM_TABLE
-        eng "You can't combine write-locking of system '%-.64s.%-.64s' table with other tables"
-        ger "Sie können Schreibsperren auf der Systemtabelle '%-.64s.%-.64s' nicht mit anderen Tabellen kombinieren"
+        eng "You can't combine write-locking of system tables with other tables or lock types"
 ER_CONNECT_TO_FOREIGN_DATA_SOURCE
         eng "Unable to connect to foreign data source: %.64s"
         ger "Kann nicht mit Fremddatenquelle verbinden: %.64s"
diff --git a/sql/sp.cc b/sql/sp.cc
index 3a7bea6a4b1..b0bf0d4a805 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -69,24 +69,6 @@ enum
 
 
 /*
-  Close mysql.proc, opened with open_proc_table_for_read().
-
-  SYNOPSIS
-    close_proc_table()
-      thd     Thread context
-      backup  Pointer to Open_tables_state instance which holds
-              information about tables which were open before we
-              decided to access mysql.proc.
-*/
-
-void close_proc_table(THD *thd, Open_tables_state *backup)
-{
-  close_thread_tables(thd);
-  thd->restore_backup_open_tables_state(backup);
-}
-
-
-/*
   Open the mysql.proc table for read.
 
   SYNOPSIS
@@ -96,13 +78,6 @@ void close_proc_table(THD *thd, Open_tables_state *backup)
               currently open tables will be saved, and from which will be
               restored when we will end work with mysql.proc.
 
-  NOTES
-    Thanks to restrictions which we put on opening and locking of
-    this table for writing, we can open and lock it for reading
-    even when we already have some other tables open and locked.
-    One must call close_proc_table() to close table opened with
-    this call.
-
   RETURN
     0	Error
     #	Pointer to TABLE object of mysql.proc
@@ -110,38 +85,18 @@ void close_proc_table(THD *thd, Open_tables_state *backup)
 
 TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
 {
-  TABLE_LIST tables;
-  TABLE *table;
-  bool not_used;
-  DBUG_ENTER("open_proc_table");
-
-  thd->reset_n_backup_open_tables_state(backup);
-
-  bzero((char*) &tables, sizeof(tables));
-  tables.db= (char*) "mysql";
-  tables.table_name= tables.alias= (char*)"proc";
-  if (!(table= open_table(thd, &tables, thd->mem_root, &not_used,
-                          MYSQL_LOCK_IGNORE_FLUSH)))
-  {
-    thd->restore_backup_open_tables_state(backup);
-    DBUG_RETURN(0);
-  }
-  table->use_all_columns();
+  DBUG_ENTER("open_proc_table_for_read");
 
-  DBUG_ASSERT(table->s->system_table);
+  TABLE_LIST table;
+  bzero((char*) &table, sizeof(table));
+  table.db= (char*) "mysql";
+  table.table_name= table.alias= (char*)"proc";
+  table.lock_type= TL_READ;
 
-  table->reginfo.lock_type= TL_READ;
-  /*
-    We have to ensure we are not blocked by a flush tables, as this
-    could lead to a deadlock if we have other tables opened.
-  */
-  if (!(thd->lock= mysql_lock_tables(thd, &table, 1,
-                                     MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
-  {
-    close_proc_table(thd, backup);
+  if (!open_system_tables_for_read(thd, &table, backup))
+    DBUG_RETURN(table.table);
+  else
     DBUG_RETURN(0);
-  }
-  DBUG_RETURN(table);
 }
 
 
@@ -162,20 +117,15 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
 
 static TABLE *open_proc_table_for_update(THD *thd)
 {
-  TABLE_LIST tables;
-  TABLE *table;
-  DBUG_ENTER("open_proc_table");
-
-  bzero((char*) &tables, sizeof(tables));
-  tables.db= (char*) "mysql";
-  tables.table_name= tables.alias= (char*)"proc";
-  tables.lock_type= TL_WRITE;
+  DBUG_ENTER("open_proc_table_for_update");
 
-  table= open_ltable(thd, &tables, TL_WRITE);
-  if (table)
-    table->use_all_columns();
+  TABLE_LIST table;
+  bzero((char*) &table, sizeof(table));
+  table.db= (char*) "mysql";
+  table.table_name= table.alias= (char*)"proc";
+  table.lock_type= TL_WRITE;
 
-  DBUG_RETURN(table);
+  DBUG_RETURN(open_system_table_for_update(thd, &table));
 }
 
 
@@ -364,7 +314,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
   chistics.comment.str= ptr;
   chistics.comment.length= length;
 
-  close_proc_table(thd, &open_tables_state_backup);
+  close_system_tables(thd, &open_tables_state_backup);
   table= 0;
 
   ret= db_load_routine(thd, type, name, sphp,
@@ -373,7 +323,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
                        
  done:
   if (table)
-    close_proc_table(thd, &open_tables_state_backup);
+    close_system_tables(thd, &open_tables_state_backup);
   DBUG_RETURN(ret);
 }
 
@@ -1126,7 +1076,7 @@ sp_routine_exists_in_table(THD *thd, int type, sp_name *name)
   {
     if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
       ret= SP_KEY_NOT_FOUND;
-    close_proc_table(thd, &open_tables_state_backup);
+    close_system_tables(thd, &open_tables_state_backup);
   }
   return ret;
 }
diff --git a/sql/sp.h b/sql/sp.h
index 330360fc1aa..24c0756c426 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -100,7 +100,6 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);
   we already have some tables open and locked.
 */
 TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup);
-void close_proc_table(THD *thd, Open_tables_state *backup);
 
 
 /*
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 67e8d79b2e8..4a4fcfb0da3 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -6675,3 +6675,122 @@ has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables)
   }
   return 0;
 }
+
+
+/*
+  Open and lock system tables for read.
+
+  SYNOPSIS
+    open_system_tables_for_read()
+      thd         Thread context.
+      table_list  List of tables to open.
+      backup      Pointer to Open_tables_state instance where
+                  information about currently open tables will be
+                  saved, and from which will be restored when we will
+                  end work with system tables.
+
+  NOTES
+    Thanks to restrictions which we put on opening and locking of
+    system tables for writing, we can open and lock them for reading
+    even when we already have some other tables open and locked.  One
+    must call close_system_tables() to close systems tables opened
+    with this call.
+
+  RETURN
+    FALSE   Success
+    TRUE    Error
+*/
+
+bool
+open_system_tables_for_read(THD *thd, TABLE_LIST *table_list,
+                            Open_tables_state *backup)
+{
+  DBUG_ENTER("open_system_tables_for_read");
+
+  thd->reset_n_backup_open_tables_state(backup);
+
+  uint count= 0;
+  bool not_used;
+  for (TABLE_LIST *tables= table_list; tables; tables= tables->next_global)
+  {
+    TABLE *table= open_table(thd, tables, thd->mem_root, &not_used,
+                             MYSQL_LOCK_IGNORE_FLUSH);
+    if (!table)
+      goto error;
+
+    DBUG_ASSERT(table->s->system_table);
+
+    table->use_all_columns();
+    table->reginfo.lock_type= tables->lock_type;
+    tables->table= table;
+    count++;
+  }
+
+  {
+    TABLE **list= (TABLE**) thd->alloc(sizeof(TABLE*) * count);
+    TABLE **ptr= list;
+    for (TABLE_LIST *tables= table_list; tables; tables= tables->next_global)
+      *(ptr++)= tables->table;
+
+    thd->lock= mysql_lock_tables(thd, list, count,
+                                 MYSQL_LOCK_IGNORE_FLUSH, &not_used);
+  }
+  if (thd->lock)
+    DBUG_RETURN(FALSE);
+
+error:
+  close_system_tables(thd, backup);
+
+  DBUG_RETURN(TRUE);
+}
+
+
+/*
+  Close system tables, opened with open_system_tables_for_read().
+
+  SYNOPSIS
+    close_system_tables()
+      thd     Thread context
+      backup  Pointer to Open_tables_state instance which holds
+              information about tables which were open before we
+              decided to access system tables.
+*/
+
+void
+close_system_tables(THD *thd, Open_tables_state *backup)
+{
+  close_thread_tables(thd);
+  thd->restore_backup_open_tables_state(backup);
+}
+
+
+/*
+  Open and lock one system table for update.
+
+  SYNOPSIS
+    open_system_table_for_update()
+      thd        Thread context.
+      one_table  Table to open.
+
+  NOTES
+    Table opened with this call should closed using close_thread_tables().
+
+  RETURN
+    0	Error
+    #	Pointer to TABLE object of system table
+*/
+
+TABLE *
+open_system_table_for_update(THD *thd, TABLE_LIST *one_table)
+{
+  DBUG_ENTER("open_system_table_for_update");
+
+  TABLE *table= open_ltable(thd, one_table, one_table->lock_type);
+  if (table)
+  {
+    DBUG_ASSERT(table->s->system_table);
+    table->use_all_columns();
+  }
+
+  DBUG_RETURN(table);
+}
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index 7b7f7602163..aebf0b0e0fe 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -655,8 +655,9 @@ bool mysqld_help(THD *thd, const char *mask)
   tables[3].lock_type= TL_READ;
   tables[0].db= tables[1].db= tables[2].db= tables[3].db= (char*) "mysql";
 
-  if (open_and_lock_tables(thd, tables))
-    goto error;
+  Open_tables_state open_tables_state_backup;
+  if (open_system_tables_for_read(thd, tables, &open_tables_state_backup))
+    goto error2;
 
   /*
     Init tables and fields to be usable from items
@@ -779,8 +780,13 @@ bool mysqld_help(THD *thd, const char *mask)
   }
   send_eof(thd);
 
+  close_system_tables(thd, &open_tables_state_backup);
   DBUG_RETURN(FALSE);
+
 error:
+  close_system_tables(thd, &open_tables_state_backup);
+
+error2:
   DBUG_RETURN(TRUE);
 }
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index fe6b5f547eb..74040811dc6 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -158,7 +158,6 @@ void lex_start(THD *thd, const uchar *buf, uint length)
   lex->lock_option= TL_READ;
   lex->found_semicolon= 0;
   lex->safe_to_cache_query= 1;
-  lex->time_zone_tables_used= 0;
   lex->leaf_tables_insert= 0;
   lex->parsing_options.reset();
   lex->empty_field_list_on_rset= 0;
@@ -2060,31 +2059,6 @@ void st_lex::first_lists_tables_same()
 
 
 /*
-  Add implicitly used time zone description tables to global table list
-  (if needed).
-
-  SYNOPSYS
-    st_lex::add_time_zone_tables_to_query_tables()
-      thd - pointer to current thread context
-
-  RETURN VALUE
-   TRUE  - error
-   FALSE - success
-*/
-
-bool st_lex::add_time_zone_tables_to_query_tables(THD *thd_arg)
-{
-  /* We should not add these tables twice */
-  if (!time_zone_tables_used)
-  {
-    time_zone_tables_used= my_tz_get_table_list(thd_arg, &query_tables_last);
-    if (time_zone_tables_used == &fake_time_zone_tables_list)
-      return TRUE;
-  }
-  return FALSE;
-}
-
-/*
   Link table back that was unlinked with unlink_first_table()
 
   SYNOPSIS
@@ -2153,7 +2127,6 @@ void st_lex::cleanup_after_one_table_open()
     /* remove underlying units (units of VIEW) subtree */
     select_lex.cut_subtree();
   }
-  time_zone_tables_used= 0;
 }
 
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 68399b188a1..f797d08b034 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1086,11 +1086,6 @@ typedef struct st_lex : public Query_tables_list
   bool prepared_stmt_code_is_varref;
   /* Names of user variables holding parameters (in EXECUTE) */
   List<LEX_STRING> prepared_stmt_params;
-  /*
-    Points to part of global table list which contains time zone tables
-    implicitly used by the statement.
-  */
-  TABLE_LIST *time_zone_tables_used;
   sp_head *sphead;
   sp_name *spname;
   bool sp_lex_in_use;	/* Keep track on lex usage in SPs for error handling */
@@ -1177,7 +1172,6 @@ typedef struct st_lex : public Query_tables_list
   TABLE_LIST *unlink_first_table(bool *link_to_local);
   void link_first_table_back(TABLE_LIST *first, bool link_to_local);
   void first_lists_tables_same();
-  bool add_time_zone_tables_to_query_tables(THD *thd);
 
   bool can_be_merged();
   bool can_use_merged();
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index bfe71ce271c..e7a8a5aaa88 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2663,8 +2663,7 @@ mysql_execute_command(THD *thd)
     Don't reset warnings when executing a stored routine.
   */
   if ((all_tables || &lex->select_lex != lex->all_selects_list ||
-       lex->sroutines.records) && !thd->spcont ||
-      lex->time_zone_tables_used)
+       lex->sroutines.records) && !thd->spcont)
     mysql_reset_errors(thd, 0);
 
 #ifdef HAVE_REPLICATION
@@ -5687,9 +5686,7 @@ check_table_access(THD *thd, ulong want_access,TABLE_LIST *tables,
      */
     tables->grant.orig_want_privilege= (want_access & ~SHOW_VIEW_ACL);
     if (tables->derived || tables->schema_table ||
-        (tables->table && (int)tables->table->s->tmp_table) ||
-        my_tz_check_n_skip_implicit_tables(&tables,
-                                           thd->lex->time_zone_tables_used))
+        (tables->table && (int)tables->table->s->tmp_table))
       continue;
     thd->security_ctx= sctx;
     if ((sctx->master_access & want_access) ==
@@ -7466,14 +7463,12 @@ bool multi_update_precheck(THD *thd, TABLE_LIST *tables)
   /*
     Is there tables of subqueries?
   */
-  if (&lex->select_lex != lex->all_selects_list || lex->time_zone_tables_used)
+  if (&lex->select_lex != lex->all_selects_list)
   {
     DBUG_PRINT("info",("Checking sub query list"));
     for (table= tables; table; table= table->next_global)
     {
-      if (!my_tz_check_n_skip_implicit_tables(&table,
-                                              lex->time_zone_tables_used) &&
-          !table->table_in_first_from_clause)
+      if (!table->table_in_first_from_clause)
       {
 	if (check_access(thd, SELECT_ACL, table->db,
 			 &table->grant.privilege, 0, 0,
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 99fb0fd4236..888d7951b87 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3514,7 +3514,7 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
 
 err:
   proc_table->file->ha_index_end();
-  close_proc_table(thd, &open_tables_state_backup);
+  close_system_tables(thd, &open_tables_state_backup);
   DBUG_RETURN(res);
 }
 
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 7675c854b81..7d534ba243f 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1302,8 +1302,6 @@ ok:
     (st_select_lex_node**)&old_lex->all_selects_list;
 
 ok2:
-  if (!old_lex->time_zone_tables_used && thd->lex->time_zone_tables_used)
-    old_lex->time_zone_tables_used= thd->lex->time_zone_tables_used;
   DBUG_ASSERT(lex == thd->lex);
   thd->lex= old_lex;                            // Needed for prepare_security
   result= !table->prelocking_placeholder && table->prepare_security(thd);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 2b8747e6a25..26429177bd7 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10272,14 +10272,7 @@ internal_variable_name:
 	      YYABORT;
 	    $$.var= tmp;
 	    $$.base_name= null_lex_str;
-            /*
-              If this is time_zone variable we should open time zone
-              describing tables 
-            */
-            if (tmp == &sys_time_zone &&
-                lex->add_time_zone_tables_to_query_tables(YYTHD))
-              YYABORT;
-            else if (spc && tmp == &sys_autocommit)
+            if (spc && tmp == &sys_autocommit)
             {
               /*
                 We don't allow setting AUTOCOMMIT from a stored function
diff --git a/sql/table.cc b/sql/table.cc
index 0fe1f37e9b1..093d4b46143 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -245,6 +245,50 @@ void free_table_share(TABLE_SHARE *share)
 }
 
 
+/**
+  Return TRUE if a table name matches one of the system table names.
+  Currently these are:
+
+  help_category, help_keyword, help_relation, help_topic,
+  proc,
+  time_zone, time_zone_leap_second, time_zone_name, time_zone_transition,
+  time_zone_transition_type
+
+  This function trades accuracy for speed, so may return false
+  positives. Presumably mysql.* database is for internal purposes only
+  and should not contain user tables.
+*/
+
+inline bool is_system_table_name(const char *name, uint length)
+{
+  CHARSET_INFO *ci= system_charset_info;
+
+  return (
+          /* mysql.proc table */
+          length == 4 &&
+          my_tolower(ci, name[0]) == 'p' && 
+          my_tolower(ci, name[1]) == 'r' &&
+          my_tolower(ci, name[2]) == 'o' &&
+          my_tolower(ci, name[3]) == 'c' ||
+
+          length > 4 &&
+          (
+           /* one of mysql.help* tables */
+           my_tolower(ci, name[0]) == 'h' &&
+           my_tolower(ci, name[1]) == 'e' &&
+           my_tolower(ci, name[2]) == 'l' &&
+           my_tolower(ci, name[3]) == 'p' ||
+
+           /* one of mysql.time_zone* tables */
+           my_tolower(ci, name[0]) == 't' &&
+           my_tolower(ci, name[1]) == 'i' &&
+           my_tolower(ci, name[2]) == 'm' &&
+           my_tolower(ci, name[3]) == 'e'
+          )
+         );
+}
+
+
 /*
   Read table definition from a binary / text based .frm file
   
@@ -365,11 +409,9 @@ int open_table_def(THD *thd, TABLE_SHARE *share, uint db_flags)
         allow to lock such tables for writing with any other tables (even with
         other system tables) and some privilege tables need this.
       */
-      if (!(lower_case_table_names ?
-            my_strcasecmp(system_charset_info, share->table_name.str, "proc") :
-            strcmp(share->table_name.str, "proc")))
-        share->system_table= 1;
-      else
+      share->system_table= is_system_table_name(share->table_name.str,
+                                                share->table_name.length);
+      if (!share->system_table)
       {
         share->log_table= check_if_log_table(share->db.length, share->db.str,
                                              share->table_name.length,
diff --git a/sql/tztime.cc b/sql/tztime.cc
index 2cdc863565a..c92d603461a 100644
--- a/sql/tztime.cc
+++ b/sql/tztime.cc
@@ -1488,26 +1488,20 @@ extern "C" byte* my_offset_tzs_get_key(Time_zone_offset *entry, uint *length,
 
 
 /*
-  Prepare table list with time zone related tables from preallocated array
-  and add to global table list.
+  Prepare table list with time zone related tables from preallocated array.
 
   SYNOPSIS
     tz_init_table_list()
       tz_tabs         - pointer to preallocated array of MY_TZ_TABLES_COUNT
                         TABLE_LIST objects
-      global_next_ptr - pointer to variable which points to global_next member
-                        of last element of global table list (or list root
-                        then list is empty) (in/out).
 
   DESCRIPTION
     This function prepares list of TABLE_LIST objects which can be used
-    for opening of time zone tables from preallocated array. It also links
-    this list to the end of global table list (it will read and update
-    accordingly variable pointed by global_next_ptr for this).
+    for opening of time zone tables from preallocated array.
 */
 
 static void
-tz_init_table_list(TABLE_LIST *tz_tabs, TABLE_LIST ***global_next_ptr)
+tz_init_table_list(TABLE_LIST *tz_tabs)
 {
   bzero(tz_tabs, sizeof(TABLE_LIST) * MY_TZ_TABLES_COUNT);
 
@@ -1524,64 +1518,6 @@ tz_init_table_list(TABLE_LIST *tz_tabs, TABLE_LIST ***global_next_ptr)
     if (i != 0)
       tz_tabs[i].prev_global= &tz_tabs[i-1].next_global;
   }
-
-  /* Link into global list */
-  tz_tabs[0].prev_global= *global_next_ptr;
-  **global_next_ptr= tz_tabs;
-  /* Update last-global-pointer to point to pointer in last table */
-  *global_next_ptr= &tz_tabs[MY_TZ_TABLES_COUNT-1].next_global;
-}
-
-
-/*
-  Fake table list object, pointer to which is returned by
-  my_tz_get_tables_list() as indication of error.
-*/
-TABLE_LIST fake_time_zone_tables_list;
-
-/*
-  Create table list with time zone related tables and add it to the end
-  of global table list.
-
-  SYNOPSIS
-    my_tz_get_table_list()
-      thd             - current thread object
-      global_next_ptr - pointer to variable which points to global_next member
-                        of last element of global table list (or list root
-                        then list is empty) (in/out).
-
-  DESCRIPTION
-    This function creates list of TABLE_LIST objects allocated in thd's
-    memroot, which can be used for opening of time zone tables. It will also
-    link this list to the end of global table list (it will read and update
-    accordingly variable pointed by global_next_ptr for this).
-
-  NOTE
-    my_tz_check_n_skip_implicit_tables() function depends on fact that
-    elements of list created are allocated as TABLE_LIST[MY_TZ_TABLES_COUNT]
-    array.
-
-  RETURN VALUES
-    Returns pointer to first TABLE_LIST object, (could be 0 if time zone
-    tables don't exist) and &fake_time_zone_tables_list in case of error.
-*/
-
-TABLE_LIST *
-my_tz_get_table_list(THD *thd, TABLE_LIST ***global_next_ptr)
-{
-  TABLE_LIST *tz_tabs;
-  DBUG_ENTER("my_tz_get_table_list");
-
-  if (!time_zone_tables_exist)
-    DBUG_RETURN(0);
-
-  if (!(tz_tabs= (TABLE_LIST *)thd->alloc(sizeof(TABLE_LIST) *
-                                          MY_TZ_TABLES_COUNT)))
-    DBUG_RETURN(&fake_time_zone_tables_list);
-
-  tz_init_table_list(tz_tabs, global_next_ptr);
-
-  DBUG_RETURN(tz_tabs);
 }
 
 
@@ -1614,8 +1550,8 @@ my_bool
 my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
 {
   THD *thd;
-  TABLE_LIST *tables= 0;
-  TABLE_LIST tables_buff[1+MY_TZ_TABLES_COUNT], **last_global_next_ptr;
+  TABLE_LIST tz_tables[1+MY_TZ_TABLES_COUNT];
+  Open_tables_state open_tables_state_backup;
   TABLE *table;
   Tz_names_entry *tmp_tzname;
   my_bool return_val= 1;
@@ -1677,19 +1613,23 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
   */
 
   thd->set_db(db, sizeof(db)-1);
-  bzero((char*) &tables_buff, sizeof(TABLE_LIST));
-  tables_buff[0].alias= tables_buff[0].table_name=
+  bzero((char*) &tz_tables[0], sizeof(TABLE_LIST));
+  tz_tables[0].alias= tz_tables[0].table_name=
     (char*)"time_zone_leap_second";
-  tables_buff[0].lock_type= TL_READ;
-  tables_buff[0].db= db;
+  tz_tables[0].table_name_length= 21;
+  tz_tables[0].db= db;
+  tz_tables[0].db_length= sizeof(db)-1;
+  tz_tables[0].lock_type= TL_READ;
+
+  tz_init_table_list(tz_tables+1);
+  tz_tables[0].next_global= tz_tables[0].next_local= &tz_tables[1];
+  tz_tables[1].prev_global= &tz_tables[0].next_global;
+
   /*
-    Fill TABLE_LIST for the rest of the time zone describing tables
-    and link it to first one.
+    We need to open only mysql.time_zone_leap_second, but we try to
+    open all time zone tables to see if they exist.
   */
-  last_global_next_ptr= &(tables_buff[0].next_global);
-  tz_init_table_list(tables_buff + 1, &last_global_next_ptr);
-
-  if (simple_open_n_lock_tables(thd, tables_buff))
+  if (open_system_tables_for_read(thd, tz_tables, &open_tables_state_backup))
   {
     sql_print_warning("Can't open and lock time zone table: %s "
                       "trying to live without them", thd->net.last_error);
@@ -1697,7 +1637,6 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
     return_val= time_zone_tables_exist= 0;
     goto end_with_setting_default_tz;
   }
-  tables= tables_buff + 1;
 
   /*
     Now we are going to load leap seconds descriptions that are shared
@@ -1713,7 +1652,7 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
     goto end_with_close;
   }
 
-  table= tables_buff[0].table;
+  table= tz_tables[0].table;
   /*
     It is OK to ignore ha_index_init()/ha_index_end() return values since
     mysql.time_zone* tables are MyISAM and these operations always succeed
@@ -1770,7 +1709,12 @@ end_with_setting_default_tz:
   if (default_tzname)
   {
     String tmp_tzname2(default_tzname, &my_charset_latin1);
-    if (!(global_system_variables.time_zone= my_tz_find(&tmp_tzname2, tables)))
+    /*
+      Time zone tables may be open here, and my_tz_find() may open
+      most of them once more, but this is OK for system tables open
+      for READ.
+    */
+    if (!(global_system_variables.time_zone= my_tz_find(thd, &tmp_tzname2)))
     {
       sql_print_error("Fatal error: Illegal or unknown default time zone '%s'",
                       default_tzname);
@@ -1779,8 +1723,11 @@ end_with_setting_default_tz:
   }
 
 end_with_close:
-  thd->version--; /* Force close to free memory */
-  close_thread_tables(thd);
+  if (time_zone_tables_exist)
+  {
+    thd->version--; /* Force close to free memory */
+    close_system_tables(thd, &open_tables_state_backup);
+  }
 
 end_with_cleanup:
 
@@ -1889,7 +1836,6 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables)
   */
   table= tz_tables->table;
   tz_tables= tz_tables->next_local;
-  table->use_all_columns();
   table->field[0]->store(tz_name->ptr(), tz_name->length(),
                          &my_charset_latin1);
   /*
@@ -1922,7 +1868,6 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables)
     using the only index in this table).
   */
   table= tz_tables->table;
-  table->use_all_columns();
   tz_tables= tz_tables->next_local;
   table->field[0]->store((longlong) tzid, TRUE);
   (void)table->file->ha_index_init(0, 1);
@@ -1950,7 +1895,6 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables)
     Right - using special index.
   */
   table= tz_tables->table;
-  table->use_all_columns();
   tz_tables= tz_tables->next_local;
   table->field[0]->store((longlong) tzid, TRUE);
   (void)table->file->ha_index_init(0, 1);
@@ -2024,7 +1968,6 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables)
     in ascending order by index scan also satisfies us.
   */
   table= tz_tables->table; 
-  table->use_all_columns();
   table->field[0]->store((longlong) tzid, TRUE);
   (void)table->file->ha_index_init(0, 1);
 
@@ -2234,8 +2177,8 @@ str_to_offset(const char *str, uint length, long *offset)
 
   SYNOPSIS
     my_tz_find()
+      thd  - pointer to thread THD structure
       name - time zone specification
-      tz_tables - list of opened'n'locked time zone describing tables
 
   DESCRIPTION
     This function checks if name is one of time zones described in db,
@@ -2257,11 +2200,10 @@ str_to_offset(const char *str, uint length, long *offset)
     values as parameter without additional external check and this property
     is used by @@time_zone variable handling code).
 
-    It will perform lookup in system tables (mysql.time_zone*) if needed
-    using tz_tables as list of already opened tables (for info about this
-    list look at tz_load_from_open_tables() description). It won't perform
-    such lookup if no time zone describing tables were found during server
-    start up.
+    It will perform lookup in system tables (mysql.time_zone*),
+    opening and locking them, and closing afterwards. It won't perform
+    such lookup if no time zone describing tables were found during
+    server start up.
 
   RETURN VALUE
     Pointer to corresponding Time_zone object. 0 - in case of bad time zone
@@ -2269,7 +2211,7 @@ str_to_offset(const char *str, uint length, long *offset)
 
 */
 Time_zone *
-my_tz_find(const String * name, TABLE_LIST *tz_tables)
+my_tz_find(THD *thd, const String *name)
 {
   Tz_names_entry *tmp_tzname;
   Time_zone *result_tz= 0;
@@ -2277,8 +2219,6 @@ my_tz_find(const String * name, TABLE_LIST *tz_tables)
   DBUG_ENTER("my_tz_find");
   DBUG_PRINT("enter", ("time zone name='%s'",
                        name ? ((String *)name)->c_ptr_safe() : "NULL"));
-  DBUG_ASSERT(!time_zone_tables_exist || tz_tables ||
-              current_thd->slave_thread);
 
   if (!name)
     DBUG_RETURN(0);
@@ -2310,8 +2250,19 @@ my_tz_find(const String * name, TABLE_LIST *tz_tables)
                                                    (const byte *)name->ptr(),
                                                    name->length())))
       result_tz= tmp_tzname->tz;
-    else if (time_zone_tables_exist && tz_tables)
-      result_tz= tz_load_from_open_tables(name, tz_tables);
+    else if (time_zone_tables_exist)
+    {
+      TABLE_LIST tz_tables[MY_TZ_TABLES_COUNT];
+      Open_tables_state open_tables_state_backup;
+
+      tz_init_table_list(tz_tables);
+      if (!open_system_tables_for_read(thd, tz_tables,
+                                       &open_tables_state_backup))
+      {
+        result_tz= tz_load_from_open_tables(name, tz_tables);
+        close_system_tables(thd, &open_tables_state_backup);
+      }
+    }
   }
 
   VOID(pthread_mutex_unlock(&tz_LOCK));
@@ -2320,58 +2271,6 @@ my_tz_find(const String * name, TABLE_LIST *tz_tables)
 }
 
 
-/*
-  A more standalone version of my_tz_find(): will open tz tables if needed.
-  This is so far only used by replication, where time zone setting does not
-  happen in the usual query context.
-
-  SYNOPSIS
-    my_tz_find_with_opening_tz_tables()
-      thd  - pointer to thread's THD structure
-      name - time zone specification
-
-  DESCRIPTION
-    This function tries to find a time zone which matches the named passed in
-    argument. If it fails, it will open time zone tables and re-try the
-    search.
-    This function is needed for the slave SQL thread, which does not do the
-    addition of time zone tables which is usually done during query parsing
-    (as time zone setting by slave does not happen in mysql_parse() but
-    before). So it needs to open tz tables by itself if needed.
-    See notes of my_tz_find() as they also apply here.
-
-  RETURN VALUE
-    Pointer to corresponding Time_zone object. 0 - in case of bad time zone
-    specification or other error.
-*/
-
-Time_zone *my_tz_find_with_opening_tz_tables(THD *thd, const String *name)
-{
-  Time_zone *tz;
-  DBUG_ENTER("my_tz_find_with_opening_tables");
-  DBUG_ASSERT(thd);
-  DBUG_ASSERT(thd->slave_thread); // intended for use with slave thread only
-
-  if (!(tz= my_tz_find(name, 0)) && time_zone_tables_exist)
-  {
-    /*
-      Probably we have not loaded this time zone yet so let us look it up in
-      our time zone tables. Note that if we don't have tz tables on this
-      slave, we don't even try.
-    */
-    TABLE_LIST tables[MY_TZ_TABLES_COUNT];
-    TABLE_LIST *dummy;
-    TABLE_LIST **dummyp= &dummy;
-    tz_init_table_list(tables, &dummyp);
-    if (simple_open_n_lock_tables(thd, tables))
-      DBUG_RETURN(0);
-    tz= my_tz_find(name, tables);
-    /* We need to close tables _now_ to not pollute coming query */
-    close_thread_tables(thd);
-  }
-  DBUG_RETURN(tz);
-}
-
 #endif /* !defined(TESTTIME) && !defined(TZINFO2SQL) */
 
 
diff --git a/sql/tztime.h b/sql/tztime.h
index 248a638074b..b6af4b37468 100644
--- a/sql/tztime.h
+++ b/sql/tztime.h
@@ -59,15 +59,11 @@ public:
 
 extern Time_zone * my_tz_UTC;
 extern Time_zone * my_tz_SYSTEM;
-extern TABLE_LIST * my_tz_get_table_list(THD *thd, TABLE_LIST ***global_next_ptr);
-extern Time_zone * my_tz_find(const String *name, TABLE_LIST *tz_tables);
-extern Time_zone * my_tz_find_with_opening_tz_tables(THD *thd, const String *name);
+extern Time_zone * my_tz_find(THD *thd, const String *name);
 extern my_bool     my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap);
 extern void        my_tz_free();
 extern my_time_t   sec_since_epoch_TIME(TIME *t);
 
-extern TABLE_LIST fake_time_zone_tables_list;
-
 /*
   Number of elements in table list produced by my_tz_get_table_list()
   (this table list contains tables which are needed for dynamical loading
@@ -77,34 +73,5 @@ extern TABLE_LIST fake_time_zone_tables_list;
 
 static const int MY_TZ_TABLES_COUNT= 4;
 
-/*
-  Check if we have pointer to the begining of list of implicitly used time
-  zone tables, set SELECT_ACL for them and fast-forward to its end.
-
-  SYNOPSIS
-    my_tz_check_n_skip_implicit_tables()
-      table     - (in/out) pointer to element of table list to check
-      tz_tables - list of implicitly used time zone tables received
-                  from my_tz_get_table_list() function.
-
-  NOTE
-    This function relies on my_tz_get_table_list() implementation.
-
-  RETURN VALUE
-    TRUE  - if table points to the beggining of tz_tables list
-    FALSE - otherwise.
-*/
-inline bool my_tz_check_n_skip_implicit_tables(TABLE_LIST **table,
-                                               TABLE_LIST *tz_tables)
-{
-  if (*table == tz_tables)
-  {
-    for (int i= 0; i < MY_TZ_TABLES_COUNT; i++)
-      (*table)[i].grant.privilege= SELECT_ACL;
-    (*table)+= MY_TZ_TABLES_COUNT - 1;
-    return TRUE;
-  }
-  return FALSE;
-}
 
 #endif /* !defined(TESTTIME) && !defined(TZINFO2SQL) */
diff --git a/storage/csv/ha_tina.cc b/storage/csv/ha_tina.cc
index afe8e5f1b27..07a4ffc65c5 100644
--- a/storage/csv/ha_tina.cc
+++ b/storage/csv/ha_tina.cc
@@ -787,7 +787,8 @@ void ha_tina::update_status()
 
 bool ha_tina::check_if_locking_is_allowed(uint sql_command,
                                           ulong type, TABLE *table,
-                                          uint count,
+                                          uint count, uint current,
+                                          uint *system_count,
                                           bool called_by_privileged_thread)
 {
   if (!called_by_privileged_thread)
diff --git a/storage/csv/ha_tina.h b/storage/csv/ha_tina.h
index 0c667237c0f..c096f21fca2 100644
--- a/storage/csv/ha_tina.h
+++ b/storage/csv/ha_tina.h
@@ -128,7 +128,8 @@ public:
 
   virtual bool check_if_locking_is_allowed(uint sql_command,
                                            ulong type, TABLE *table,
-                                           uint count,
+                                           uint count, uint current,
+                                           uint *system_count,
                                            bool called_by_logger_thread);
   int open(const char *name, int mode, uint open_options);
   int close(void);
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index 764c53d2f75..3c6e9f9b9a0 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -565,7 +565,8 @@ err:
 
 bool ha_myisam::check_if_locking_is_allowed(uint sql_command,
                                             ulong type, TABLE *table,
-                                            uint count,
+                                            uint count, uint current,
+                                            uint *system_count,
                                             bool called_by_privileged_thread)
 {
   /*
@@ -574,11 +575,13 @@ bool ha_myisam::check_if_locking_is_allowed(uint sql_command,
     we have to disallow write-locking of these tables with any other tables.
   */
   if (table->s->system_table &&
-      table->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&
-      count != 1)
+      table->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE)
+    (*system_count)++;
+
+  /* 'current' is an index, that's why '<=' below. */
+  if (*system_count > 0 && *system_count <= current)
   {
-    my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0), table->s->db.str,
-             table->s->table_name.str);
+    my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0));
     return FALSE;
   }
 
diff --git a/storage/myisam/ha_myisam.h b/storage/myisam/ha_myisam.h
index 882900bd35f..175a61bd97c 100644
--- a/storage/myisam/ha_myisam.h
+++ b/storage/myisam/ha_myisam.h
@@ -62,7 +62,8 @@ class ha_myisam: public handler
 
   virtual bool check_if_locking_is_allowed(uint sql_command,
                                            ulong type, TABLE *table,
-                                           uint count,
+                                           uint count, uint current,
+                                           uint *system_count,
                                            bool called_by_logger_thread);
   int open(const char *name, int mode, uint test_if_locked);
   int close(void);