Bug #51240 ALTER TABLE of a locked MERGE table fails
Submitted: 17 Feb 2010 12:21 Modified: 13 Mar 2010 23:48
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.5.99-m3 (next-4284) OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[17 Feb 2010 12:21] Ingo Strüwing
Description:
CREATE TABLE t1 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
LOCK TABLE m1 WRITE;
ALTER TABLE m1 ADD INDEX (c1);
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

The error message is wrong. 't1' was locked with a WRITE lock (implicilty through the MERGE table).

Other ALTER TABLE operations (RENAME, ADD COLUMN, ...) suffer from the same problem.

After back porting of some 6.0 MERGE features, TEMPORARY MERGE tables can have permanent children. The above applies in this scenario too.

However, for TEMPORARY MERGE with TEMPORARY children the problem does *not* show up.

The error message seems to be emitted by the '+' marked code snippet in open_table():

  if (thd->locked_tables_mode &&
      ! (flags & MYSQL_OPEN_GET_NEW_TABLE))
  {						// Using table locks
...
    if (best_table)
    {
+      if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+          table_list->lock_type >= TL_WRITE_ALLOW_WRITE &&
+          ! best_table->mdl_ticket->is_upgradable_or_exclusive())
+      {
+        my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), alias);
+        DBUG_RETURN(TRUE);
+      }
      table= best_table;
      table->query_id= thd->query_id;
      DBUG_PRINT("info",("Using locked table"));
      goto reset;
    }

Taken from the mysql-next-4284 tree revid:kostja@sun.com-20100215163553-c0cwx9y2ugzarey2

How to repeat:
CREATE TABLE t1 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
LOCK TABLE m1 WRITE;
ALTER TABLE m1 ADD INDEX (c1);
[17 Feb 2010 14:28] Ingo Strüwing
I just found the same problem in the newest mysql-6.0-codebase too.
[17 Feb 2010 14:51] Ingo Strüwing
FLUSH TABLE t1 is affected too.
[22 Feb 2010 10:47] Bugs System
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/101038

3105 Jon Olav Hauglid	2010-02-22
      Bug #51240 ALTER TABLE of a locked MERGE table fails
      
      The problem was that ALTER TABLE on a merge table which was locked 
      using LOCK TABLE ... WRITE, by mistake gave 
      ER_TABLE_NOT_LOCKED_FOR_WRITE.
      
      During opening of the table to be ALTERed, open_table() tried to
      get an upgradable metadata lock. In LOCK TABLEs mode, this lock
      must already exist (i.e. taken by LOCK TABLE) as new locks of this
      type cannot be acquired for fear of deadlock.
      
      The problem was that open_table() also tried to find upgradable
      metadata locks for children of merge tables even if no such
      locks are needed to execute ALTER TABLE on merge tables.
      
      This patch fixes the problem by making sure that open tables code
      only searches for upgradable metadata locks for the merge table
      and not for the merge children tables.
      
      Test case added to merge.test
[25 Feb 2010 15:58] Bugs System
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/101477

3108 Jon Olav Hauglid	2010-02-25
      Bug #51240 ALTER TABLE of a locked MERGE table fails
      
      The problem was that ALTER TABLE on a merge table which was locked 
      using LOCK TABLE ... WRITE, by mistake gave 
      ER_TABLE_NOT_LOCKED_FOR_WRITE.
      
      During opening of the table to be ALTERed, open_table() tried to
      get an upgradable metadata lock. In LOCK TABLEs mode, this lock
      must already exist (i.e. taken by LOCK TABLE) as new locks of this
      type cannot be acquired for fear of deadlock.
      
      The problem was that open_table() also tried to find upgradable
      metadata locks for children of merge tables even if no such
      locks are needed to execute ALTER TABLE on merge tables.
      
      This patch fixes the problem by making sure that open tables code
      only searches for upgradable metadata locks for the merge table
      and not for the merge children tables. 
      
      The patch also fixes a related bug where an upgradable metadata
      lock was aquired outside of LOCK TABLEs mode even if the table in
      question was temporary. This bug meant that LOCK TABLES or DDL on
      temporary tables by mistake could be blocked/aborted by locks held
      on base tables with the same table name by other connections.
      
      Test case added to merge.test
[26 Feb 2010 11:15] Bugs System
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/101578

3110 Jon Olav Hauglid	2010-02-26
      Bug #51240 ALTER TABLE of a locked MERGE table fails
      
      The problem was that ALTER TABLE on a merge table which was locked 
      using LOCK TABLE ... WRITE, by mistake gave 
      ER_TABLE_NOT_LOCKED_FOR_WRITE.
      
      During opening of the table to be ALTERed, open_table() tried to
      get an upgradable metadata lock. In LOCK TABLEs mode, this lock
      must already exist (i.e. taken by LOCK TABLE) as new locks of this
      type cannot be acquired for fear of deadlock. So in LOCK TABLEs
      mode, open_table() tried to find an existing upgradable lock for
      the table to be altered.
      
      The problem was that open_table() also tried to find upgradable
      metadata locks for children of merge tables even if no such
      locks are needed to execute ALTER TABLE on merge tables.
      
      This patch fixes the problem by making sure that open tables code
      only searches for upgradable metadata locks for the merge table
      and not for the merge children tables. 
      
      The patch also fixes a related bug where an upgradable metadata
      lock was aquired outside of LOCK TABLEs mode even if the table in
      question was temporary. This bug meant that LOCK TABLES or DDL on
      temporary tables by mistake could be blocked/aborted by locks held
      on base tables with the same table name by other connections.
      
      Test cases added to merge.test and lock_multi.test.
[26 Feb 2010 12:36] Dmitry Lenev
Approved with a couple of minor comments which were sent by e-mail.
[26 Feb 2010 12:40] Bugs System
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/101619

3110 Jon Olav Hauglid	2010-02-26
      Bug #51240 ALTER TABLE of a locked MERGE table fails
      
      The problem was that ALTER TABLE on a merge table which was locked 
      using LOCK TABLE ... WRITE, by mistake gave 
      ER_TABLE_NOT_LOCKED_FOR_WRITE.
      
      During opening of the table to be ALTERed, open_table() tried to
      get an upgradable metadata lock. In LOCK TABLEs mode, this lock
      must already exist (i.e. taken by LOCK TABLE) as new locks of this
      type cannot be acquired for fear of deadlock. So in LOCK TABLEs
      mode, open_table() tried to find an existing upgradable lock for
      the table to be altered.
      
      The problem was that open_table() also tried to find upgradable
      metadata locks for children of merge tables even if no such
      locks are needed to execute ALTER TABLE on merge tables.
      
      This patch fixes the problem by making sure that open tables code
      only searches for upgradable metadata locks for the merge table
      and not for the merge children tables. 
      
      The patch also fixes a related bug where an upgradable metadata
      lock was aquired outside of LOCK TABLEs mode even if the table in
      question was temporary. This bug meant that LOCK TABLES or DDL on
      temporary tables by mistake could be blocked/aborted by locks held
      on base tables with the same table name by other connections.
      
      Test cases added to merge.test and lock_multi.test.
[26 Feb 2010 12:41] Jon Olav Hauglid
Pushed to mysql-next-4284.
[6 Mar 2010 10:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)
[6 Mar 2010 10:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100306102638-qna09hbjb5gm940h) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (pib:16)
[6 Mar 2010 11:06] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (merge vers: 5.5.99-m3) (pib:16)
[13 Mar 2010 23:48] Paul Dubois
Noted in 5.5.3, 6.0.14 changelogs.

ALTER TABLE on a MERGE table that has been locked using LOCK TABLES
... WRITE incorrectly produced an ER_TABLE_NOT_LOCKED_FOR_WRITE
error.