Bug #50908 Assertion `handler_tables_hash.records == 0' failed in enter_locked_tables_mode
Submitted: 4 Feb 2010 10:45 Modified: 7 Mar 2010 1:03
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:mysql-next-4284 OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any

[4 Feb 2010 10:45] Philip Stoev
Description:
When executing the usual RQG MDL DDL workload, but with no transactions and with autocommit=on, mysqld asserted as follows:

mysqld: sql_class.h:2633: void THD::enter_locked_tables_mode(enum_locked_tables_mode): Assertion `handler_tables_hash.records == 0' failed.

#6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000069d275 in THD::enter_locked_tables_mode (this=0x2b66188, mode_arg=LTM_PRELOCKED) at sql_class.h:2633
#8  0x0000000000693cd8 in lock_tables (thd=0x2b66188, tables=0x2bbb2f8, count=4, flags=0, need_reopen=0x7f9068c3d03b) at sql_base.cc:5466
#9  0x000000000069b524 in open_and_lock_tables_derived (thd=0x2b66188, tables=0x2bbb2f8, derived=true, flags=0, prelocking_strategy=0x7f9068c3d080)
    at sql_base.cc:5109
#10 0x0000000000651c08 in open_and_lock_tables_derived (thd=0x2b66188, tables=0x2bbb2f8, derived=true, flags=0) at mysql_priv.h:1550
#11 0x0000000000651c43 in open_and_lock_tables (thd=0x2b66188, tables=0x2bbb2f8) at mysql_priv.h:1560
#12 0x00000000006499e8 in mysql_execute_command (thd=0x2b66188) at sql_parse.cc:3007
#13 0x000000000064ebc0 in mysql_parse (thd=0x2b66188, inBuf=0x2bbb198 "INSERT INTO testdb_N . t1_base1_N SELECT * FROM test.table10_int_autoinc", length=72,
    found_semicolon=0x7f9068c3ef10) at sql_parse.cc:5584
#14 0x000000000064f7d9 in dispatch_command (command=COM_QUERY, thd=0x2b66188,
    packet=0x2c131d9 " INSERT INTO testdb_N . t1_base1_N SELECT * FROM test.table10_int_autoinc", packet_length=73) at sql_parse.cc:1008
#15 0x0000000000650c3c in do_command (thd=0x2b66188) at sql_parse.cc:694
#16 0x000000000063f354 in handle_one_connection (arg=0x2b66188) at sql_connect.cc:1163
#17 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
#18 0x000000315a4e627d in clone () from /lib64/libc.so.6

(gdb) print handler_tables_hash.records
$1 = 1

See also bug #50907

How to repeat:
If this is repeatable, a simplified test case will be provided. In the meantime, the core and the binary will be made available.
[4 Feb 2010 10:49] Philip Stoev
Core and binary:

http://mysql-systemqa.s3.amazonaws.com/var-bug50908.zip

Source:

revision-id: dlenev@mysql.com-20100204062532-ptky4sdbl040e583
date: 2010-02-04 09:25:32 +0300
build-date: 2010-02-04 12:46:36 +0200
revno: 3072
branch-nick: mysql-next-4284
[4 Feb 2010 11:07] Philip Stoev
not reproducible on mysql-next-mr
[4 Feb 2010 12:03] Philip Stoev
Non-concurrent test case:

--disable_abort_on_error
CREATE TABLE `table1_int_autoinc` ( f1 integer);
CREATE TABLE IF NOT EXISTS t1_base1_N (f1 integer);
DELIMITER |;
CREATE TRIGGER tr1_1_N  BEFORE DELETE ON t1_base1_N FOR EACH ROW BEGIN REPLACE   t1_temp2_N  SELECT *  FROM t1_part1_N ; END|
DELIMITER ;|
CREATE TABLE IF NOT EXISTS t1_base2_N (f1 integer);
HANDLER t1_base2_N  OPEN  A;
INSERT INTO t1_base1_N SELECT * FROM test.table1_int_autoinc;
[6 Feb 2010 10:40] Jon Olav Hauglid
Simpler test case:

CREATE TABLE t1 (f1 INT);
CREATE TRIGGER t1_del BEFORE DELETE ON t1 FOR EACH ROW SET @a = 1;

HANDLER t1 OPEN;
INSERT INTO t1 VALUES (1);
[12 Feb 2010 7:06] 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/100073

3095 Dmitry Lenev	2010-02-12
      Fix for bug #50908 "Assertion `handler_tables_hash.records == 0'  
      failed in enter_locked_tables_mode".
      
      Server was aborted due to assertion failure when one tried to 
      execute statement requiring prelocking (i.e. firing triggers
      or using stored functions) while having open HANDLERs.
      
      The problem was that THD::enter_locked_tables_mode() method
      which was called at the beginning of execution of prelocked 
      statement assumed there are no open HANDLERs. It had to do 
      so because corresponding THD::leave_locked_tables_mode()
      method was unable to properly restore MDL sentinel when
      leaving LOCK TABLES/prelocked mode in the presence of open 
      HANDLERs.
      
      This patch solves this problem by changing the latter method
      to properly restore MDL sentinel and thus removing need for 
      this assumption. As a side-effect, it lifts unjustified
      limitation by allowing to keep HANDLERs open when entering 
      LOCK TABLES mode.
     @ mysql-test/include/handler.inc
        Adjusted tests after making LOCK TABLES not to close
        open HANDLERs. Added coverage for bug #50908
        "Assertion `handler_tables_hash.records == 0' failed
        in enter_locked_tables_mode".
     @ mysql-test/r/handler_innodb.result
        Updated test results (see include/handler.inc).
     @ mysql-test/r/handler_myisam.result
        Updated test results (see include/handler.inc).
     @ sql/mysql_priv.h
        Introduced mysql_ha_move_tickets_after_trans_sentinel()
        routine which allows to move tickets for metadata locks
        corresponding to open HANDLERs after transaction sentinel.
     @ sql/sql_class.cc
        Changed THD::leave_locked_tables_mode() to correctly restore 
        MDL sentinel value in the presence of open HANDLERs.
     @ sql/sql_class.h
        Removed assert from THD::enter_locked_tables_mode() as we
        no longer have to close HANDLERs when entering LOCK TABLES 
        or prelocked modes. Instead we keep them open and correctly
        restore MDL sentinel value after leaving them.
        Removal of assert also fixes problem from the bug report.
     @ sql/sql_handler.cc
        Introduced mysql_ha_move_tickets_after_trans_sentinel()
        routine which allows to move tickets for metadata locks
        corresponding to open HANDLERs after transaction sentinel.
     @ sql/sql_parse.cc
        We no longer have to close HANDLERs when entering LOCK TABLES 
        mode. Instead we keep them open and simply correctly restore 
        MDL sentinel value after leaving this mode.
[12 Feb 2010 8:09] Dmitry Lenev
Fix for this bug was pushed into mysql-next-4284 tree. Since it was repeatable only in the tree which is not publicly-available there is nothing to document.
So I simply closing this bug report.
[12 Feb 2010 14:23] 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/100136

3760 Dmitry Lenev	2010-02-12 [merge]
      Merged fix for bug #50908 "Assertion `handler_tables_hash.records==0'
      failed in enter_locked_tables_mode" into 6.0-based tree.
[16 Feb 2010 16:48] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:dlenev@mysql.com-20100212142135-79emefx954r1icyt) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:58] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:dlenev@mysql.com-20100212070543-fu7ppkftm0g3sen1) (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:03] Paul Dubois
No changelog entry needed.