Bug #46748 Assertion in MDL_context::wait_for_locks() on INSERT + CREATE TRIGGER
Submitted: 16 Aug 2009 17:53 Modified: 7 Mar 2010 1:57
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.4 OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: crash, locking, mdl, metadata

[16 Aug 2009 17:53] Philip Stoev
Description:
When executing a CREATE TRIGGER concurrently with INSERT, mysqld will easily assert as follows:

mysqld: mdl.cc:1222: bool MDL_context::wait_for_locks(MDL_request_list*): Assertion `mdl_request->ticket == __null' failed.

# 20:49:06 #6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
# 20:49:06 #7  0x000000000089d6fa in MDL_context::wait_for_locks (this=0x7f9954cbef90, mdl_requests=0x7f9950b6fed0) at mdl.cc:1222
# 20:49:06 #8  0x00000000006d8dd5 in Open_table_context::recover_from_failed_open_table_attempt (this=0x7f9950b6fed0, thd=0x7f9954cbeeb8, table=0x2c05578)
# 20:49:06     at sql_base.cc:3624
# 20:49:06 #9  0x00000000006dadda in open_tables (thd=0x7f9954cbeeb8, start=0x7f9950b6ff70, counter=0x7f9950b6ffb0, flags=0, prelocking_strategy=0x7f9950b6fff0)
# 20:49:06     at sql_base.cc:3967
# 20:49:06 #10 0x00000000006db697 in open_and_lock_tables_derived (thd=0x7f9954cbeeb8, tables=0x2c04de0, derived=true, flags=0, prelocking_strategy=0x7f9950b6fff0)
# 20:49:06     at sql_base.cc:4650
# 20:49:06 #11 0x000000000068a29c in open_and_lock_tables_derived (thd=0x7f9954cbeeb8, tables=0x2c04de0, derived=true, flags=0) at ../mysql_priv.h:1525
# 20:49:06 #12 0x000000000068a2d7 in open_and_lock_tables (thd=0x7f9954cbeeb8, tables=0x2c04de0) at ../../sql/mysql_priv.h:1535
# 20:49:06 #13 0x000000000072f139 in mysql_insert (thd=0x7f9954cbeeb8, table_list=0x2c04de0, fields=@0x7f9954cc13a8, values_list=@0x7f9954cc13f0,
# 20:49:06     update_fields=@0x7f9954cc13d8, update_values=@0x7f9954cc13c0, duplic=DUP_ERROR, ignore=false) at sql_insert.cc:620
# 20:49:06 #14 0x000000000068171e in mysql_execute_command (thd=0x7f9954cbeeb8) at sql_parse.cc:3220
# 20:49:06 #15 0x000000000068726f in mysql_parse (thd=0x7f9954cbeeb8, inBuf=0x2c04ce0 "INSERT INTO `BB` (`int`) VALUES ( 4 )", length=37,
# 20:49:06     found_semicolon=0x7f9950b71f00) at sql_parse.cc:5945
# 20:49:06 #16 0x0000000000687e8a in dispatch_command (command=COM_QUERY, thd=0x7f9954cbeeb8, packet=0x7f9954cc9a39 "INSERT INTO `BB` (`int`) VALUES ( 4 )",
# 20:49:06     packet_length=37) at sql_parse.cc:1062
# 20:49:06 #17 0x0000000000689356 in do_command (thd=0x7f9954cbeeb8) at sql_parse.cc:744
# 20:49:06 #18 0x000000000067656b in handle_one_connection (arg=0x7f9954cbeeb8) at sql_connect.cc:1163
# 20:49:06 #19 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
# 20:49:06 #20 0x000000315a4e627d in clone () from /lib64/libc.so.6

Also see bug#45210 which is the same crash but with a completely different workload.

How to repeat:
RQG YY file:

thread1:
        CREATE TRIGGER { "trigger_".$prng->int(1,1234567890); } BEFORE INSERT ON _table FOR EACH ROW UPDATE _table SET _field = _digit;

thread2:
        INSERT INTO _table (`int`) VALUES ( _digit ) ;

Command line:

$ perl runall.pl \
  --grammar=conf/mdl_crash2.yy \
  --basedir=/build/bzr/azalea-bugfixing \
  --threads=2

The crash should happen immediately, if it does not , please try again. Note that the CREATE TRIGGER is only executed by the first connection, so there are never concurrent CREATE TRIGGERs happening. Each trigger has a unique name.
[20 Aug 2009 8:14] 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/81134

2871 Dmitry Lenev	2009-08-20
      Fix for bug #46748 "Assertion in MDL_context::wait_for_locks()
      on INSERT + CREATE TRIGGER".
      
      Concurrent execution of statements involving stored functions or triggers
      which were using several tables and DDL statements which affected those
      tables on debug build of server might have led to assertion failures in
      MDL_context::wait_for_locks(). Non-debug build was not affected.
      
      The problem was that during back-off which happens when open_tables()
      encounters conflicting metadata lock for one of the tables being open
      we didn't reset MDL_request::ticket value for requests which correspond
      to tables from extended prelocking set. Since these requests are part 
      of of list of requests to be waited for in Open_table_context this broke 
      assumption that ticket value for them is 0 in MDL_context::wait_for_locks() 
      and caused assertion failure.
      
      This fix ensures that close_tables_for_reopen(), which performs this back-off 
      resets MDL_request::ticket value not only for tables directly used by the
      statement but also for tables from extended prelocking set, thus satisfying
      assumption described above.
      
      QQ: Is there a better place for the test case for this bug?
     @ mysql-test/r/sp-threads.result
        Added test for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ mysql-test/t/sp-threads.test
        Added test for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ sql/sql_base.cc
        Since metadata lock requests for tables from extended part of prelocking 
        set are also part of list of requests to be waited for in Open_table_context
        in close_tables_for_reopen() we have to reset MDL_request::ticket values for 
        them to assumptions in MDL_context::wait_for_locks().
[21 Aug 2009 14:17] 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/81306

2873 Dmitry Lenev	2009-08-21
      Fix for bug #46748 "Assertion in MDL_context::wait_for_locks()
      on INSERT + CREATE TRIGGER".
      
      Concurrent execution of statements involving stored functions or triggers
      which were using several tables and DDL statements which affected those
      tables on debug build of server might have led to assertion failures in
      MDL_context::wait_for_locks(). Non-debug build was not affected.
      
      The problem was that during back-off which happens when open_tables()
      encounters conflicting metadata lock for one of the tables being open
      we didn't reset MDL_request::ticket value for requests which correspond
      to tables from extended prelocking set. Since these requests are part 
      of of list of requests to be waited for in Open_table_context this broke 
      assumption that ticket value for them is 0 in MDL_context::wait_for_locks() 
      and caused assertion failure.
      
      This fix ensures that close_tables_for_reopen(), which performs this back-off 
      resets MDL_request::ticket value not only for tables directly used by the
      statement but also for tables from extended prelocking set, thus satisfying
      assumption described above.
     @ mysql-test/r/mdl_sync.result
        Added test case for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ mysql-test/t/mdl_sync.test
        Added test case for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ sql/sql_base.cc
        Since metadata lock requests for tables from extended part of prelocking 
        set are also part of list of requests to be waited for in Open_table_context
        in close_tables_for_reopen() we have to reset MDL_request::ticket values for 
        them to assumptions in MDL_context::wait_for_locks().
[21 Aug 2009 14:18] Dmitry Lenev
Fix for this bug was queued into mysql-next-bugfixing tree.
[24 Aug 2009 13:53] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090824135126-2rngffvth14a8bpj) (version source revid:dlenev@mysql.com-20090821141702-lwfjokdsmscrcwt9) (merge vers: 5.4.4-alpha) (pib:11)
[28 Aug 2009 2:08] Paul DuBois
Noted in 5.4.4 changelog.

For debug builds, an assertion failure could result from concurrent
execution of statements involving stored functions or triggers that
were using several tables and DDL statements that affected those
tables.
[9 Dec 2009 9:44] 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/93289

3012 Konstantin Osipov	2009-12-09
      Backport of:
      ------------------------------------------------------------
      revno: 2617.69.37
      committer: Dmitry Lenev <dlenev@mysql.com>
      branch nick: mysql-next-bg46748
      timestamp: Fri 2009-08-21 18:17:02 +0400
      message:
        Fix for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
      
        Concurrent execution of statements involving stored functions or triggers
        which were using several tables and DDL statements which affected those
        tables on debug build of server might have led to assertion failures in
        MDL_context::wait_for_locks(). Non-debug build was not affected.
      
        The problem was that during back-off which happens when open_tables()
        encounters conflicting metadata lock for one of the tables being open
        we didn't reset MDL_request::ticket value for requests which correspond
        to tables from extended prelocking set. Since these requests are part
        of of list of requests to be waited for in Open_table_context this broke
        assumption that ticket value for them is 0 in MDL_context::wait_for_locks()
        and caused assertion failure.
      
        This fix ensures that close_tables_for_reopen(), which performs this back-off
        resets MDL_request::ticket value not only for tables directly used by the
        statement but also for tables from extended prelocking set, thus satisfying
        assumption described above.
     @ mysql-test/r/mdl_sync.result
        Added test case for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ mysql-test/t/mdl_sync.test
        Added test case for bug #46748 "Assertion in MDL_context::wait_for_locks()
        on INSERT + CREATE TRIGGER".
     @ sql/sql_base.cc
        Since metadata lock requests for tables from extended part of prelocking
        set are also part of list of requests to be waited for in Open_table_context
        in close_tables_for_reopen() we have to reset MDL_request::ticket 
        values for them to assumptions in MDL_context::wait_for_locks().
[16 Feb 2010 16:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:55] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091209094405-lt6tpg6yjxmmyaiu) (pib:16)
[6 Mar 2010 10:54] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:57] Paul DuBois
Noted in 5.5.3, 6.0.14 changelogs.
[12 Apr 2010 16:51] Paul DuBois
Correction: No in any released version. No changelog entry needed.