Bug #57002 Assert in upgrade_shared_lock_to_exclusive() for ALTER TABLE + MERGE tables
Submitted: 24 Sep 2010 12:33 Modified: 20 Nov 2010 23:02
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: Jon Olav Hauglid
Triage: Triaged: D1 (Critical)

[24 Sep 2010 12:33] Jon Olav Hauglid
Description:
While running RQG tests on mysql-5.5-runtime, I hit the following assert:

  /* Only allow upgrades from MDL_SHARED_NO_WRITE/NO_READ_WRITE */
  DBUG_ASSERT(mdl_ticket->m_type == MDL_SHARED_NO_WRITE ||
              mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE);

#6  0x00007f5c43bbc941 in *__GI___assert_fail (
    assertion=0xbca4d8 "mdl_ticket->m_type == MDL_SHARED_NO_WRITE || mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE", file=<value optimized out>, line=1946, 
    function=0xbca800 "bool MDL_context::upgrade_shared_lock_to_exclusive(MDL_ticket*, ulong)")
    at assert.c:81
#7  0x00000000006d57f0 in MDL_context::upgrade_shared_lock_to_exclusive (this=0x2a92890, 
    mdl_ticket=0x7f5c28256b50, lock_wait_timeout=2)
    at /export/home/x/mysql-5.5-runtime-rqg4/sql/mdl.cc:1946
#8  0x000000000059013e in wait_while_table_is_used (thd=0x2a927d0, table=0x2bd76d0, 
    function=HA_EXTRA_PREPARE_FOR_RENAME)
    at /export/home/x/mysql-5.5-runtime-rqg4/sql/sql_base.cc:2231
#9  0x0000000000670645 in mysql_alter_table (thd=0x2a927d0, new_db=0x2c54b68 "testdb_N", 
    new_name=0x2c54b78 "t1_base1_N", create_info=0x7f5c32f235f0, table_list=0x2c54bc0, 
    alter_info=0x7f5c32f236d0, order_num=0, order=0x0, ignore=false)
    at /export/home/x/mysql-5.5-runtime-rqg4/sql/sql_table.cc:6517
#10 0x00000000008b0503 in Alter_table_statement::execute (this=0x2c550d0, thd=0x2a927d0)
    at /export/home/x/mysql-5.5-runtime-rqg4/sql/sql_alter.cc:106
#11 0x00000000005ead8d in mysql_execute_command (thd=0x2a927d0)
    at /export/home/x/mysql-5.5-runtime-rqg4/sql/sql_parse.cc:4375
#12 0x00000000005ed7c8 in mysql_parse (thd=0x2a927d0, 
    rawbuf=0x2c54a90 "ALTER TABLE testdb_N . t1_base1_N ENGINE = MyISAM", length=49, 
    parser_state=0x7f5c32f25110) at /export/home/x/mysql-5.5-runtime-rqg4/sql/sql_parse.cc:5554

(gdb) p mdl_ticket->m_type
$5 = MDL_SHARED_READ

How to repeat:
./runall.pl
--mem
--grammar=conf/runtime/WL5004_sql_joh.yy
--gendata=conf/runtime/WL5004_data.zz
--mysqld=--lock-wait-timeout=2
--basedir= (mysql-5.5-runtime)
--queries=10M
--duration=36000 
--threads=30 
--reporter=Deadlock,Backtrace,Shutdown

+ luck

I use a custom version of the WL5004 grammar with more parts enabled.
The only part disabled in the grammar, is temporal tables.
[27 Sep 2010 11:42] Jon Olav Hauglid
MTR test case:

CREATE TABLE t1(a INT) engine=myisam;
CREATE TABLE m1(a INT) engine=merge UNION(t1);
LOCK TABLES t1 READ, m1 WRITE;
ALTER TABLE t1 engine=myisam;
[27 Sep 2010 11:46] Jon Olav Hauglid
Regression introduced by the patch for bug#56292.
[4 Oct 2010 9:25] 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/119799

3152 Jon Olav Hauglid	2010-10-04
      Bug #57002 Assert in upgrade_shared_lock_to_exclusive()
                 for ALTER TABLE + MERGE tables
      
      The patch for Bug#56292 changed how metadata locks are taken for MERGE
      tables. After the patch, locking the MERGE table will also lock the
      children tables with the same metadata lock type. This means that 
      LOCK TABLES on a MERGE table also will implicitly do LOCK TABLES on
      the children tables.
      
      A consequence of this change, is that it is possible to do LOCK TABLES
      on a child table both explicitly and implicitly with the same statement
      and that these two locks can be of different strength. For example,
      LOCK TABLES child READ, merge WRITE.
      
      In LOCK TABLES mode, we are not allowed to take new locks and each
      statement must therefore try to find an existing TABLE instance with
      a suitable lock. The code that searched for a suitable TABLE instance,
      only considered table level locks. If a child table was locked twice,
      it was therefore possible for this code to find a TABLE instance with
      suitable table level locks but without suitable metadata lock.
      
      This problem caused the assert in upgrade_shared_lock_to_exclusive()
      to be triggered as it tried to upgrade a MDL_SHARED lock to
      EXCLUSIVE. The problem was a regression caused by the patch for
      Bug#56292.
      
      This patch fixes the problem by taking metadata lock type into account
      when trying to find a suitable TABLE instance in LOCK TABLES mode.
      
      Test case added to merge.test.
[5 Oct 2010 10:27] 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/119975

3153 Jon Olav Hauglid	2010-10-05
      Bug #57002 Assert in upgrade_shared_lock_to_exclusive()
                 for ALTER TABLE + MERGE tables
      
      The patch for Bug#56292 changed how metadata locks are taken for MERGE
      tables. After the patch, locking the MERGE table will also lock the
      children tables with the same metadata lock type. This means that 
      LOCK TABLES on a MERGE table also will implicitly do LOCK TABLES on
      the children tables.
      
      A consequence of this change, is that it is possible to do LOCK TABLES
      on a child table both explicitly and implicitly with the same statement
      and that these two locks can be of different strength. For example,
      LOCK TABLES child READ, merge WRITE.
      
      In LOCK TABLES mode, we are not allowed to take new locks and each
      statement must therefore try to find an existing TABLE instance with
      a suitable lock. The code that searched for a suitable TABLE instance,
      only considered table level locks. If a child table was locked twice,
      it was therefore possible for this code to find a TABLE instance with
      suitable table level locks but without suitable metadata lock.
      
      This problem caused the assert in upgrade_shared_lock_to_exclusive()
      to be triggered as it tried to upgrade a MDL_SHARED lock to
      EXCLUSIVE. The problem was a regression caused by the patch for
      Bug#56292.
      
      This patch fixes the problem by partially reverting the changes
      done by Bug#56292. Now, the children tables will only use the
      same metadata lock as the MERGE table for MDL_SHARED_NO_WRITE
      when not in locked tables mode. This means that LOCK TABLE
      on a MERGE table will not implicitly lock the children tables.
      This still fixes the original problem in Bug#56292 without
      causing a regression.
      
      Test case added to merge.test.
[6 Oct 2010 7: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/120057

3153 Jon Olav Hauglid	2010-10-06
      Bug #57002 Assert in upgrade_shared_lock_to_exclusive()
                 for ALTER TABLE + MERGE tables
      
      The patch for Bug#56292 changed how metadata locks are taken for MERGE
      tables. After the patch, locking the MERGE table will also lock the
      children tables with the same metadata lock type. This means that 
      LOCK TABLES on a MERGE table also will implicitly do LOCK TABLES on
      the children tables.
      
      A consequence of this change, is that it is possible to do LOCK TABLES
      on a child table both explicitly and implicitly with the same statement
      and that these two locks can be of different strength. For example,
      LOCK TABLES child READ, merge WRITE.
      
      In LOCK TABLES mode, we are not allowed to take new locks and each
      statement must therefore try to find an existing TABLE instance with
      a suitable lock. The code that searched for a suitable TABLE instance,
      only considered table level locks. If a child table was locked twice,
      it was therefore possible for this code to find a TABLE instance with
      suitable table level locks but without suitable metadata lock.
      
      This problem caused the assert in upgrade_shared_lock_to_exclusive()
      to be triggered as it tried to upgrade a MDL_SHARED lock to
      EXCLUSIVE. The problem was a regression caused by the patch for
      Bug#56292.
      
      This patch fixes the problem by partially reverting the changes
      done by Bug#56292. Now, the children tables will only use the
      same metadata lock as the MERGE table for MDL_SHARED_NO_WRITE
      when not in locked tables mode. This means that LOCK TABLE
      on a MERGE table will not implicitly lock the children tables.
      This still fixes the original problem in Bug#56292 without
      causing a regression.
      
      Test case added to merge.test.
[6 Oct 2010 7:58] Jon Olav Hauglid
Pushed to mysql-5.5-runtime (5.5.7-rc).
[4 Nov 2010 1:37] Paul Dubois
Noted in 5.5.7 changelog.

ALTER TABLE on a MERGE table could result in deadlock with other
connections.
[9 Nov 2010 19:47] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:19] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:37] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)