| 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: | |
| 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 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.
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);