Bug #57649 FLUSH TABLES under FLUSH TABLES <list> WITH READ LOCK leads to assert failure.
Submitted: 22 Oct 2010 5:01 Modified: 14 Apr 2011 13:28
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.7-bzr OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[22 Oct 2010 5:01] Dmitry Lenev
Description:
Debug build of server fails on assertion when one tries to do FLUSH TABLES in connection which has tables locked with FLUSH TABLES <table-list> WITH READ LOCK.

The assertion that fails is:

sql/mdl.cc:1555: bool MDL_context::try_acquire_lock_impl(MDL_request*, MDL_ticket**): Assertion `mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)' failed.

Non-debug builds seem to be unaffected by this bug.

Thanks to Weldon Whipple for reporting this issue!.

How to repeat:
create table t1 (i int);
create table t2 (j int);
flush tables t1, t2 with read lock;
# The below statement leads to assertion failure.
flush tables;
unlock tables;
[22 Oct 2010 6:17] Valeriy Kravchuk
Verified on Ubuntu 10.04:

openxs@ubuntu:~/dbs/5.5$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-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.03 sec)

mysql> create table t2 (j int);
Query OK, 0 rows affected (0.38 sec)

mysql> flush tables t1, t2 with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> # The below statement leads to assertion failure.
mysql> flush tables;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> unlock tables;101022 09:16:13 mysqld_safe Number of processes running now: 0
101022 09:16:13 mysqld_safe mysqld restarted

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
openxs@ubuntu:~/dbs/5.5$ tail -80 var/ubuntu.err 
101019 18:10:46 mysqld_safe mysqld from pid file /home/openxs/dbs/5.5/var/ubuntu.pid ended
101022 09:11:36 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.5/var
101022  9:11:37 [Note] Plugin 'FEDERATED' 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
101022  9:11:37  InnoDB: highest supported file format is Barracuda.
101022  9:11:38 InnoDB 1.1.2 started; log sequence number 1595675
101022  9:11:38 [Note] Event Scheduler: Loaded 0 events
101022  9:11:38 [Note] /home/openxs/dbs/5.5/libexec/mysqld: ready for connections.
Version: '5.5.7-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: mdl.cc:1563: bool MDL_context::try_acquire_lock_impl(MDL_request*, MDL_ticket**): Assertion `mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)' failed.
101022  9:16:13 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337925 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa6a7ce0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xa91bc35c thread_stack 0x30000
/home/openxs/dbs/5.5/libexec/mysqld(my_print_stacktrace+0x26)[0x863a838]
/home/openxs/dbs/5.5/libexec/mysqld(handle_segfault+0x2dd)[0x827be54]
[0x594400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182)[0xd86a82]
/lib/tls/i686/cmov/libc.so.6(__assert_fail+0xf8)[0xd7c718]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0x6f)[0x84a1b37]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0x60)[0x84a1fbc]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN11MDL_context32upgrade_shared_lock_to_exclusiveEP10MDL_ticketm+0x150)[0x84a264e]
/home/openxs/dbs/5.5/libexec/mysqld(_Z24wait_while_table_is_usedP3THDP5TABLE17ha_extra_function+0xa8)[0x82e00c0]
/home/openxs/dbs/5.5/libexec/mysqld(_Z19close_cached_tablesP3THDP10TABLE_LISTbm+0x2c3)[0x82dd94f]
/home/openxs/dbs/5.5/libexec/mysqld(_Z20reload_acl_and_cacheP3THDmP10TABLE_LISTPb+0x57e)[0x8333d16]
/home/openxs/dbs/5.5/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x5399)[0x8292031]
/home/openxs/dbs/5.5/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1d6)[0x8296ea5]
/home/openxs/dbs/5.5/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x9ce)[0x828b10f]
/home/openxs/dbs/5.5/libexec/mysqld(_Z10do_commandP3THD+0x242)[0x828a547]
/home/openxs/dbs/5.5/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x199)[0x82887b1]
/home/openxs/dbs/5.5/libexec/mysqld(handle_one_connection+0x28)[0x8288611]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e)[0xd1896e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e)[0xe26a4e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa6ce808 = flush tables
thd->thread_id=1
thd->killed=NOT_KILLED
...
[2 Dec 2010 9:54] Jon Olav Hauglid
The following test case also triggers the assert:

create table t1 (i int);
flush tables t1 with read lock;
create trigger t1_b1 before insert on t1 for each row set @a = 1;
[7 Jan 2011 11:41] 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/128152

3219 Jon Olav Hauglid	2011-01-07
      Bug #57649 FLUSH TABLES under FLUSH TABLES <list> WITH READ LOCK leads
                 to assert failure.
      
      This is a draft patch.
      
      The triggered assert checks that a connection already holds a global
      intention exclusive metadata lock if it tries to acquire an exclusive
      metadata lock. It was triggered by a connection first executing
      FLUSH TABLES <list> WITH READ LOCK and then later trying to execute
      a statement which caused an exclusive metadata lock to be acquired
      (e.g. FLUSH TABLES, CREATE TRIGGER).
      
      The cause of the problem is that FLUSH TABLES <list> WITH READ LOCK
      does not acquire a global intention exclusive metadata lock. This
      is done to make FLUSH TABLES <list> WITH READ LOCK compatible with
      FLUSH TABLES WITH READ LOCK. If FLUSH TABLES <list> WITH READ LOCK
      had acquired a global intention exclusive metadata lock, it would
      have caused FLUSH TABLES WITH READ LOCK (from other connections)
      to be blocked.
      
      This patch fixes the problem by acquiring a global intention
      exclusive metadata lock for statements trying to upgrade an
      existing metadata lock to exclusive inside LOCK TABLES mode if
      a global intention exclusive metadata lock is not already held.
      
      Test case added to flush.test.
[14 Apr 2011 13:28] Paul DuBois
Noted in 5.5.11, 5.6.3 changelogs.

An assertion was raised if a statement tried to upgrade a metadata
lock while there was a active FLUSH TABLE tbl_list WITH READ LOCK
statement. Now if a statement tries to upgrade a metadata lock in
this situation, the server returns an ER_TABLE_NOT_LOCKED_FOR_WRITE
error to the client.

CHANGESET - http://lists.mysql.com/commits/132524