MySQL Bugs: #35935: CREATE TABLE under LOCK TABLES ignores FLUSH TABLES WITH READ LOCK

Bug #35935	CREATE TABLE under LOCK TABLES ignores FLUSH TABLES WITH READ LOCK
Submitted:	9 Apr 2008 12:37	Modified:	22 Jun 2010 13:37
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.52-bk, 5.1	OS:	Any
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Under LOCK TABLES one can successfully create table by using CREATE TABLE statement even if some other connection holds active global read lock on server (i.e. have successfully executed FLUSH TABLES WITH READ LOCK and have not yet called corresponding UNLOCK TABLES).
This, IMO, contradicts definition of global read lock and makes FLUSH TABLES WITH READ LOCK unusable for purposes of implementing robust backup mechanism (for example, one no longer can rely on binary log not changing under global read lock).

How to repeat:
# Here is the script for mysqltest tool which shows the problem

create table t1 (i int);

connect(locker,localhost,root,,);
# After this statement no one should be able to perform any DDL
flush tables with read lock;

connection default;
lock tables t1 read;
# Statement below succeeds although another connection still
# has a global read lock
create table t2 (j int);

Thank you for the bug report.

Fixed in Milestone#4 of WL#148 (6.1) by prohibiting create table under LOCK TABLES.

http://lists.mysql.com/commits/50845
2680 Dmitry Lenev	2008-08-03
      Patch for the 4th milestone of WL#148 "Foreign keys"
      ("Implement meta-data locking for foreign key definitions
      and make prelocking algorithm aware of foreign keys").
      
      Extended algortihm which calculates list of prelocked tables
      for the statement to take into account foreign keys.
      
      (Basically after opening each table we analyze foreign keys
       in which this table participates and depending on operation
       which is going to be carried on this table add child or parent
       tables of foreign key to the statement table list to be opened
       and locked with appropriate lock type.
       If we are going to INSERT into or UPDATE this table then for
       all foreign keys for which this table serves as child we add
       parent tables with request for read lock.
       If we are going to UPDATE or DELETE from this table we add
       child tables of foreign keys that reference this table and
       determine type of lock depending on type ON UPDATE/ON DELETE
       option for this foreign key. We avoid processing (and thus
       adding tables) for the same foreign key in the same role
       more than once by keeping set of already processed <foreign
       keys, role> pairs.)
      
      As a pre-requisite changed code responsible for table creation
      to store information about foreign keys on table being created
      also in .FRMs of parent tables (with appropriate meta-data
      locking).

Queued into 6.1-fk tree.

This bug was originally fixed in the 6.1-fk tree. The fix was later backported as part of the patch for Bug#42546 "Backup: RESTORE fails, thinking it finds an existing table".

This bug was therefore fixed in Ver 5.5.3, so I'm setting this bug to Documenting.

Test coverage (originally not backported) has now been backported in http://lists.mysql.com/commits/110795 and pushed to mysql-trunk-runtime (Ver 5.5.5).

Noted in 5.5.3 changelog.

A global read lock obtained with FLUSH TABLES WITH READ LOCK did not
prevent sessions from creating tables.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)