MySQL Bugs: #9953: CONVERT_TZ requires mysql.time_zone

Bug #9953	CONVERT_TZ requires mysql.time_zone_name to be locked
Submitted:	16 Apr 2005 15:13	Modified:	10 Apr 2007 17:30
Reporter:	Olav Vitters	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
When some tables are locked, CONVERT_TZ requires that the time_zone_name table is locked too. This is difficult if someone doesn't even have access to that table.

How to repeat:
Simple example:

LOCK TABLES
  bugs WRITE;
UPDATE bugs SET
  creation_ts = CONVERT_TZ(creation_ts, "America/New_York", "UTC");
UNLOCK TABLES;

Results in:
ERROR 1100 (HY000) at line 3: Table 'time_zone_name' was not locked with LOCK TABLES

Suggested fix:
Make CONVERT_TZ not require that table to be locked? Or maybe lock that table automatically.

Tomash, please notice the version number.

also present in 4.1.21

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16610

ChangeSet@1.2383, 2006-12-07 19:22:09+03:00, kroki@moonlight.intranet +23 -0
  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 by itself, it is forbidden
  to lock system table 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).
  
  TO THE REVIEWER:
    - main changes are in sql_base.cc, tztime.cc:my_tz_find(), table.cc,
      and sql_help.cc.
    - there are two 'KROKI FIXME' entries with questions in the patch.
    - note that inability to open more that one system table for writing
      makes it impossible to use some triggers (already enforced) or
      foreign keys on such tables (time zone and help tables actually have
      conceptual FKs).

Bug#19339 is a duplicate of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18606

ChangeSet@1.2383, 2007-01-23 12:10:32+03:00, kroki@moonlight.home +23 -0
  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 by itself, it is forbidden
  to lock system table 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).
  
  TO THE REVIEWER:
    - main changes are in sql_base.cc, tztime.cc:my_tz_find(), table.cc,
      and sql_help.cc.
    - there are two 'KROKI FIXME' entries with questions in the patch.
    - note that inability to open more that one system table for writing
      makes it impossible to use some triggers (already enforced) or
      foreign keys on such tables (time zone and help tables actually have
      conceptual FKs).

Sent a code review by email. Asked for some changes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20903

ChangeSet@1.2383, 2007-03-01 16:39:04+03:00, kroki@moonlight.home +34 -0
  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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20927

ChangeSet@1.2444, 2007-03-01 20:44:49+03:00, kroki@moonlight.home +35 -0
  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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21574

ChangeSet@1.2444, 2007-03-09 13:12:31+03:00, kroki@moonlight.home +35 -0
  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.

Queued to 5.1-runtime.

Fixed in 5.1.17

Fixed in 5.1.17

Paul, do you have the information you need about this bug?

Noted in 5.1.17 changelog.

For some operations, system tables in the mysql database must be
accessed. For example, the HELP statement requires the contents of
the server-side help tables, and CONVERT_TZ() might need to read the
time zone tables. However, to perform such operations while a LOCK
TABLES statement is in effect, the server required you to also lock
the requisite system tables explicitly or a lock error occurred:

mysql> LOCK TABLE t1 READ; 
Query OK, 0 rows affected (0.02 sec)
mysql> HELP HELP; 
ERROR 1100 (HY000) at line 4: Table 'help_topic'
was not locked with LOCK TABLES

Now, the server implicitly locks the system tables for reading as
necessary so that you need not lock them explicitly. These tables are
treated as just described: 

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.proc
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

If you want to explicitly place a WRITE lock on any of those tables
with a LOCK TABLES statement, the table must be the only one locked;
no other table can be locked with the same statement. 

I also updated the LOCK TABLES section with this information.

Updated the time zone support section with this
information:

mysqld caches time zone information that it looks up, so after
replacing the time zone tables, you should restart mysqld to make 
sure that it does not continue to serve outdated time zone data.

Bug: http://bugs.mysql.com/bug.php?id=55880 marked as duplicate of this one.