Bug #56516 Optimizing MERGE table asserts in MDL_context::upgrade_shared_lock_to_exclusive
Submitted: 2 Sep 2010 19:03 Modified: 22 Sep 2010 8:22
Reporter: Matthias Leich Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.6, 5.5.7-bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: locking, mdl, regression

[2 Sep 2010 19:03] Matthias Leich
The crash happens in ../sql/mdl.cc:1941 :
    Do nothing if already upgraded. Used when we FLUSH TABLE under
    LOCK TABLES and a table is listed twice in LOCK TABLES list.
===>  if (mdl_ticket->m_type == MDL_EXCLUSIVE)

My script:
DROP TABLE IF EXISTS t_not_exists;

CREATE TABLE t1  ( pk int, primary key (pk));
ALTER TABLE t1 ENGINE = MERGE UNION ( t_not_exists );


Result on mysql-5.5-runtime revno: 3131 2010-09-01
The fix for the following bug which shows a similar
backtrace is included
   Bug #54105 assert in MDL_context::release_locks_stored_before
TEST                       RESULT   TIME (ms)

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.ml001                 [ fail ]
        Test ended at 2010-09-02 20:19:11
CURRENT_TEST: main.ml001
mysqltest: At line 8: query 'OPTIMIZE TABLE t1' failed: 2013: Lost connection to MySQL server during query

Thread 1 (process 31858):
#0  0x00007f5b663cbce6 in pthread_kill ()
    from /lib64/libpthread.so.0
#1  0x0000000000a0ffaf in my_write_core (sig=11)
    at ..../mysys/stacktrace.c:328
#2  0x0000000000547bcd in handle_segfault (sig=11) 
    at ..../sql/mysqld.cc:2505
#3  <signal handler called>
#4  0x00000000006b0436 in MDL_context::upgrade_shared_lock_to_exclusive (this=0x18824a0, mdl_ticket=0x0, lock_wait_timeout=31536000)
    at ..../sql/mdl.cc:1941
#5  0x000000000058c8a0 in wait_while_table_is_used (thd=0x18823e0, table=0x1943650, function=HA_EXTRA_PREPARE_FOR_RENAME) 
    at ..../sql/sql_base.cc:2231
#6  0x00000000006b268e in mysql_admin_table (thd=0x18823e0, tables=0x1907100, check_opt=0x18849c8, operator_name=0xae69ea "optimize", lock_type=TL_WRITE, open_for_modify=true, no_warnings_for_error=false, extra_open_options=0, prepare_func=0,
    operator_func=0x728fa2 <handler::ha_optimize(THD*, st_ha_check_opt*)>, view_operator_func=0) 
    at ..../sql/sql_admin.cc:491
#7  0x00000000006b3c41 in Optimize_table_statement::execute (this=0x1907610, thd=0x18823e0) 
    at ..../sql/sql_admin.cc:955
#8  0x00000000005dbd46 in mysql_execute_command (thd=0x18823e0)
    at ..../sql/sql_parse.cc:4375
#9  0x00000000005dc2d4 in mysql_parse (thd=0x18823e0, rawbuf=0x1907030 "OPTIMIZE TABLE t1", length=17, parser_state=0x40479950)
    at ..../sql/sql_parse.cc:5554
#10 0x00000000005dce67 in dispatch_command (command=COM_QUERY, thd=0x18823e0, packet=0x193f641 "", packet_length=17)
    at ..../sql/sql_parse.cc:1099
#11 0x00000000005de255 in do_command (thd=0x18823e0)
    at ..../sql/sql_parse.cc:771
#12 0x00000000006a6365 in do_handle_one_connection (thd_arg=0x18823e0)
    at ..../sql/sql_connect.cc:1191
#13 0x00000000006a642a in handle_one_connection (arg=0x18823e0)
    at ..../sql/sql_connect.cc:1130
#14 0x000000000094e273 in pfs_spawn_thread (arg=0x18ec900)
    at ..../storage/perfschema/pfs.cc:1015
#15 0x00007f5b663c7040 in start_thread ()
    from /lib64/libpthread.so.0
#16 0x00007f5b6548208d in clone ()
    from /lib64/libc.so.6
#17 0x0000000000000000 in ?? ()

Result on mysql-5.1 revno: 3468 2010-08-19
DROP TABLE IF EXISTS t_not_exists;
CREATE TABLE t1  ( pk int, primary key (pk));
ALTER TABLE t1 ENGINE = MERGE UNION ( t_not_exists );
Table	Op	Msg_type	Msg_text
test.t1	optimize	Error	Table 'test.t_not_exists' doesn't exist
test.t1	optimize	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.t1	optimize	error	Corrupt

How to repeat:
See above
[2 Sep 2010 19:36] Valeriy Kravchuk
Verified with current mysql-5.5 from bzr:

macbook-pro:5.5 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 1
Server version: 5.5.7-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  ( pk int, primary key (pk));
ERROR 1050 (42S01): Table 't1' already exists
mysql> ALTER TABLE t1 ENGINE = MERGE UNION ( t_not_exists );
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 100902 22:33:26 mysqld_safe mysqld restarted

mysql> exit
macbook-pro:5.5 openxs$ tail -100 var/macbook-pro.err 
100902 22:33:22 [Note] /Users/openxs/dbs/5.5/libexec/mysqld: ready for connections.
Version: '5.5.7-m3-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
100902 22:33:26 - mysqld got signal 10 ;
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.

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

thd: 0x1020000
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 = 0xb0576f34 thread_stack 0x30000
0   mysqld                              0x005c2b79 my_print_stacktrace + 44
1   mysqld                              0x00105c28 handle_segfault + 876
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x0016c822 _Z24wait_while_table_is_usedP3THDP5TABLE17ha_extra_function + 182
5   mysqld                              0x001d9cfb _Z18mysql_print_statusv + 4397
6   mysqld                              0x001db4ad _ZN24Optimize_table_statement7executeEP3THD + 321
7   mysqld                              0x00120da9 _Z21mysql_execute_commandP3THD + 33113
8   mysqld                              0x00121414 _Z11mysql_parseP3THDPcjP12Parser_state + 624
9   mysqld                              0x00121fa6 _Z16dispatch_command19enum_server_commandP3THDPcj + 2632
10  mysqld                              0x001233af _Z10do_commandP3THD + 621
11  mysqld                              0x00110c21 _Z24do_handle_one_connectionP3THD + 425
12  mysqld                              0x00110ce5 handle_one_connection + 37
13  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
14  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x109ca10 = OPTIMIZE TABLE t1

mysql-.5.1 does not crash, so this can be called a regression.
[6 Sep 2010 7:19] Jon Olav Hauglid
A different manifestation of Bug#56494 and fixed by the proposed patch for Bug#56494.
[22 Sep 2010 8:22] Jon Olav Hauglid
Closed as a duplicate of Bug#56494