Bug #55273 FLUSH TABLE tm WITH READ LOCK for Merge table causes assert failure.
Submitted: 15 Jul 2010 6:07 Modified: 20 Nov 2010 22:54
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.6-bzr OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any

[15 Jul 2010 6:07] Dmitry Lenev
Description:
Attempt to execute "FLUSH TABLE tm WITH READ LOCK" statement for table created using Merge engine causes debug build of server to abort due to assertion failure and crash of non-debug build.

Here is the stacktrace for assertion failure:

#7  0xb75b0098 in abort () from /lib/tls/i686/cmov/libc.so.6
#8  0xb75a75ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
#9  0x081b3f18 in get_table_share (thd=0x952bdc8, table_list=0x9587ce8, key=0xb0993942 "test", key_length=8, db_flags=8192, error=0xb099372c, 
    hash_value=1096967734) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:505
#10 0x081b42ea in get_table_share_with_create (thd=0x952bdc8, table_list=0x9587ce8, key=0xb0993942 "test", key_length=8, db_flags=8192, error=0xb099372c, 
    hash_value=1096967734) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:609
#11 0x081b5a34 in open_table (thd=0x952bdc8, table_list=0x9587ce8, mem_root=0xb0993bbc, ot_ctx=0xb0993b98)
    at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:2874
#12 0x081b6b7b in open_and_process_table (thd=0x952bdc8, lex=0x952cd40, tables=0x9587ce8, counter=0xb0993c54, flags=32, prelocking_strategy=0xb0993c94, 
    has_prelocking_list=false, ot_ctx=0xb0993b98, new_frm_mem=0xb0993bbc) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:4400
#13 0x081b71e2 in open_tables (thd=0x952bdc8, start=0xb0993c64, counter=0xb0993c54, flags=32, prelocking_strategy=0xb0993c94)
    at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:4805
#14 0x081b7694 in open_and_lock_tables (thd=0x952bdc8, tables=0x9587948, derived=false, flags=32, prelocking_strategy=0xb0993c94)
    at /home/dlenev/src/bzr/mysql-trunk/sql/sql_base.cc:5388
#15 0x081fa5a3 in flush_tables_with_read_lock (thd=0x952bdc8, all_tables=0x9587948) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_parse.cc:1824
#16 0x0820154a in mysql_execute_command (thd=0x952bdc8) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_parse.cc:4009
#17 0x0820426a in mysql_parse (thd=0x952bdc8, inBuf=0x9587898 "flush table tm with read lock", length=29, parser_state=0xb0994b48)
    at /home/dlenev/src/bzr/mysql-trunk/sql/sql_parse.cc:5911
#18 0x08204e6d in dispatch_command (command=COM_QUERY, thd=0x952bdc8, packet=0x9574619 "flush table tm with read lock", packet_length=29)
    at /home/dlenev/src/bzr/mysql-trunk/sql/sql_parse.cc:1135
#19 0x082064ab in do_command (thd=0x952bdc8) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_parse.cc:807
#20 0x082d09ac in do_handle_one_connection (thd_arg=0x952bdc8) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_connect.cc:1196
#21 0x082d0a6a in handle_one_connection (arg=0x952bdc8) at /home/dlenev/src/bzr/mysql-trunk/sql/sql_connect.cc:1135

...

Assert which fails:

  DBUG_ASSERT(thd->mdl_context.is_lock_owner(MDL_key::TABLE,
                                             table_list->db,
                                             table_list->table_name,
                                             MDL_SHARED));

tells us that this statement tries to open table on which it does
not have an appropriate metadata lock. Crash of production build
happens in different place but, AFAIU, stems from the same reason
absence of metadata lock on underlying table.

How to repeat:
create table t1 (i int);
create table tm (i int) engine=merge union=(t1);
# The below statement causes assertion failure or crash.
flush table tm with read lock;
[15 Jul 2010 6:11] Valeriy Kravchuk
Verified on Mac OS X:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.07 sec)

mysql> create table tm (i int) engine=merge union=(t1);
Query OK, 0 rows affected (0.11 sec)

mysql> # The below statement causes assertion failure or crash.
mysql> flush table tm with read lock;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> bin/mysqld_safe: line 144: 74862 Abort trap              nohup /Users/openxs/dbs/trunk/libexec/mysqld --basedir=/Users/openxs/dbs/trunk --datadir=/Users/openxs/dbs/trunk/var --plugin-dir=/Users/openxs/dbs/trunk/lib/mysql/plugin --user=mysql --log-error=/Users/openxs/dbs/trunk/var/macbook-pro.err --pid-file=/Users/openxs/dbs/trunk/var/macbook-pro.pid < /dev/null >> /Users/openxs/dbs/trunk/var/macbook-pro.err 2>&1
100715 09:09:13 mysqld_safe mysqld restarted

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:trunk openxs$ tail -80 var/macbook-pro.err 
100630 17:39:20  InnoDB: Starting shutdown...
100630 17:39:24  InnoDB: Shutdown completed; log sequence number 305248634
100630 17:39:24 [Note] /Users/openxs/dbs/trunk/libexec/mysqld: Shutdown complete

100630 17:39:24 mysqld_safe mysqld from pid file /Users/openxs/dbs/trunk/var/macbook-pro.pid ended
100715 09:08:35 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/trunk/var
100715  9:08:36 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/trunk/var/ is case insensitive
100715  9:08:36 [Warning] One can only use the --user switch if running as root

100715  9:08:36 [Note] Buffered information: Performance schema disabled (reason: start parameters).

100715  9:08:36 [Note] Plugin 'FEDERATED' is disabled.
100715  9:08:36 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
100715  9:08:36  InnoDB: highest supported file format is Barracuda.
100715  9:08:36  InnoDB: Warning: allocated tablespace 4, old maximum was 0
100715  9:08:36 InnoDB 1.1.1 started; log sequence number 305248634
100715  9:08:36 [Note] Event Scheduler: Loaded 0 events
100715  9:08:36 [Note] /Users/openxs/dbs/trunk/libexec/mysqld: ready for connections.
Version: '5.5.6-m3-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (thd->mdl_context.is_lock_owner(MDL_key::TABLE, table_list->db, table_list->table_name, MDL_SHARED)), function get_table_share, file sql_base.cc, line 505.
100715  9:09:12 - mysqld got signal 6 ;
...
[9 Sep 2010 8:55] 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/117819

3134 Dmitry Lenev	2010-09-09
      Fix for bug #55273 "FLUSH TABLE tm WITH READ LOCK for Merge
      table causes assert failure".
      
      Attempt to use FLUSH TABLE table_list WITH READ LOCK statement
      for MERGE table led to assertion failure if one of its children
      was not present in list of tables to be flushed. The problem
      was not visible in non-debug builds.
      
      The assertion failure was caused by the fact that in such
      situation FLUSH TABLES table_list WITH READ LOCK implementation
      has tried to use (e.g. lock) such child table without acquiring
      metadata lock on it. This has happened because when opening 
      tables we assumed metadata locks on all tables were already
      pre-acquired earlier during statement execution and such
      assumption was false for MERGE children.
      
      This patch fixes the problem by ensuring at open_tables() time
      that we try to acquire metadata locks on all tables to be open. 
      For normal tables such requests are satisfied instantly since
      locks are already pre-acquired for them. For MERGE children 
      metadata locks are acquired in normal fashion.
      
      Note that FLUSH TABLES merge_table WITH READ LOCK will lock for
      read both MERGE table and its children but will flush only MERGE
      table. To flush children one has to mention them in table list
      explicitly. This is expected behavior and it is consistent with 
      usage patterns for this statement (e.g. in mysqlhotcopy script).
     @ mysql-test/r/flush.result
        Added test case for bug #55273 "FLUSH TABLE tm WITH READ LOCK
        for Merge table causes assert failure".
     @ mysql-test/t/flush.test
        Added test case for bug #55273 "FLUSH TABLE tm WITH READ LOCK
        for Merge table causes assert failure".
     @ sql/sql_base.cc
        Changed lock_table_names() to support newly introduced
        MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag.
     @ sql/sql_base.h
        Introduced MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag for
        open_tables() and lock_table_names() which allows to skip
        acquiring of global and schema-scope locks when SNW, SNRW or
        X metadata locks are acquired.
     @ sql/sql_reload.cc
        Changed "FLUSH TABLES table_list WITH READ LOCK" code not to
        cause assert about missing metadata locks when MERGE table is
        flushed without one of its underlying tables.
        To achieve this we no longer call open_and_lock_tables() with
        MYSQL_OPEN_HAS_MDL_LOCK flag so this function automatically
        acquires metadata locks on MERGE children if such lock has
        not been already acquired at earlier stage. Instead we call
        this function with MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag to
        suppress acquiring of global IX lock in order to keep FLUSH
        TABLES  table_list WITH READ LOCK compatible with FLUSH TABLE
        WITH READ LOCK.
        Also changed implementation to use lock_table_names() function
        for pre-acquiring of metadata locks instead of custom code.
        To implement this change moved setting of open_type member for
        table list elements to parser.
     @ sql/sql_yacc.yy
        Now we set acceptable type of table for FLUSH TABLES table_list
        WITH READ LOCK at parsing time instead of execution time.
[9 Sep 2010 14:17] Jon Olav Hauglid
Patch approved with minor notes about test case and code/changeset comments.
[9 Sep 2010 14:31] 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/117891

3134 Dmitry Lenev	2010-09-09
      Fix for bug #55273 "FLUSH TABLE tm WITH READ LOCK for Merge
      table causes assert failure".
      
      Attempting to use FLUSH TABLE table_list WITH READ LOCK
      statement for a MERGE table led to an assertion failure if
      one of its children was not present in the list of tables
      to be flushed. The problem was not visible in non-debug builds.
      
      The assertion failure was caused by the fact that in such
      situations FLUSH TABLES table_list WITH READ LOCK implementation
      tried to use (e.g. lock) such child tables without acquiring
      metadata lock on them. This happened because when opening tables
      we assumed metadata locks on all tables were already acquired
      earlier during statement execution and a such assumption was
      false for MERGE children.
      
      This patch fixes the problem by ensuring at open_tables() time
      that we try to acquire metadata locks on all tables to be opened. 
      For normal tables such requests are satisfied instantly since
      locks are already acquired for them. For MERGE children metadata
      locks are acquired in normal fashion.
      
      Note that FLUSH TABLES merge_table WITH READ LOCK will lock for
      read both the MERGE table and its children but will flush only 
      the MERGE table. To flush children one has to mention them in table
      list explicitly. This is expected behavior and it is consistent with
      usage patterns for this statement (e.g. in mysqlhotcopy script).
     @ mysql-test/r/flush.result
        Added test case for bug #55273 "FLUSH TABLE tm WITH READ LOCK
        for Merge table causes assert failure".
     @ mysql-test/t/flush.test
        Added test case for bug #55273 "FLUSH TABLE tm WITH READ LOCK
        for Merge table causes assert failure".
     @ sql/sql_base.cc
        Changed lock_table_names() to support newly introduced
        MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag.
     @ sql/sql_base.h
        Introduced MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag for
        open_tables() and lock_table_names() which allows to skip
        acquiring of global and schema-scope locks when SNW, SNRW or
        X metadata locks are acquired.
     @ sql/sql_reload.cc
        Changed "FLUSH TABLES table_list WITH READ LOCK" code not to
        cause assert about missing metadata locks when MERGE table is
        flushed without one of its underlying tables.
        To achieve this we no longer call open_and_lock_tables() with
        MYSQL_OPEN_HAS_MDL_LOCK flag so this function automatically
        acquires metadata locks on MERGE children if such lock has
        not been already acquired at earlier stage. Instead we call
        this function with MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK flag to
        suppress acquiring of global IX lock in order to keep FLUSH
        TABLES  table_list WITH READ LOCK compatible with FLUSH TABLE
        WITH READ LOCK.
        Also changed implementation to use lock_table_names() function
        for pre-acquiring of metadata locks instead of custom code.
        To implement this change moved setting of open_type member for
        table list elements to parser.
     @ sql/sql_yacc.yy
        Now we set acceptable type of table for FLUSH TABLES table_list
        WITH READ LOCK at parsing time instead of execution time.
[9 Sep 2010 14:48] Dmitry Lenev
Fix for this bug was queued into mysql-5.5-runtime tree.
[9 Nov 2010 19:44] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[12 Nov 2010 0:29] Paul DuBois
Noted in 5.5.7 changelog.

In debug builds, FLUSH TABLE table_list WITH READ LOCK for a MERGE
table led to an assertion failure if one of the table's children was
not present in the list of tables to be flushed.
[13 Nov 2010 16:08] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:31] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)