Bug #12845 Stress test: Server crashes on CREATE .. SELECT statement
Submitted: 29 Aug 2005 16:43 Modified: 15 Sep 2005 17:03
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:the latest 5.0.13pre OS:Linux (Linux)
Assigned to: Ingo Strüwing

[29 Aug 2005 16:43] Alexey Stroganov
Description:
Running following test case in multithread environment(stress test) for some
time (less than 10 sec) with at least 2 threads will lead to crash:

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT);

CREATE TABLE t2 SELECT name FROM t1;

DROP TABLE t2;
DROP TABLE t1;

backtrace:

#0  0xffffe410 in ?? ()
#1  0x42831dd8 in ?? ()
#2  0x00000006 in ?? ()
#3  0x00004c89 in ?? ()
#4  0x400e92c1 in raise () from /lib/tls/libc.so.6
#5  0x400eab75 in abort () from /lib/tls/libc.so.6
#6  0x0840ee57 in safe_mutex_lock (mp=0x8f8f8f9b, file=0x84f32d3 "thr_lock.c", line=741)
    at thr_mutex.c:104
#7  0x0840d79a in thr_unlock (data=0x8c4f1cc) at thr_lock.c:741
#8  0x0840e0b1 in thr_multi_unlock (data=0x8c50b10, count=1) at thr_lock.c:996
#9  0x0818d68d in mysql_unlock_tables (thd=0x42901948, sql_lock=0x8c50b00) at lock.cc:239
#10 0x081d4e93 in close_thread_tables (thd=0x42901948, lock_in_use=false, skip_derived=false) at sql_base.cc:488
#11 0x081aadb7 in dispatch_command (command=COM_QUERY, thd=0x42901948, packet=0x42907879 "CREATE TABLE t2 SELECT name FROM t1", packet_length=36)
at sql_parse.cc:2017
#12 0x081a95a2 in do_command (thd=0x42901948) at sql_parse.cc:1464
#13 0x081a8682 in handle_one_connection (arg=0x42901948) at sql_parse.cc:1116
#14 0x4004aaa7 in start_thread () from /lib/tls/libpthread.so.0
#15 0x4017bc2e in clone () from /lib/tls/libc.so.6

How to repeat:
Run stress test with test case above.

Until stress test will be integrated to regular mysql-test suite please contact me and I will provide all necessary information.
[31 Aug 2005 8:51] Ingo Strüwing
This is not exactly the same what I had lately, but it is also table locking related.

The problem is that the select table t1 is dropped by another thread between the locking of t1 and opening the freshly created table t2. In open_table() this is detected as a refresh.  Up to now, the open tables of the thread were closed and open_table() left with an error code. Normally this is a good thing, as all tables are opened in a row before any of them is locked. So the loop of opening all tables is restarted, until all succeed (or fail with a different error). But in create_table_from_items(), all select tables are already locked and there is no chance to repeat the opening of all tables. If open_table() closes all tables marked for refresh and returns with an error, the whole command fails. The bug in this situation is that the tables are still locked. The locks rely on data structures THR_LOCK_DATA, which are part of the table structure, which gets freed on close. At the end of each command, mysql_unlock_tables() is called and tries to access the freed data. (This might not crash on non-debug binaries as they do not re-initialize the freed memory.)

I suggest a solution to this bug, which ignores the refresh of the tables in open_table(), when it is called from create_table_from_items(). The disadvantage of this solution is that the refreshing tread (drop table in this case) must wait until the CREATE ... SELECT command is complete. But since this would also be the case if the drop came after the newly created table was opened, the difference might be very small.
[31 Aug 2005 17:25] 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/internals/29122
[7 Sep 2005 8:27] 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/internals/29410
[7 Sep 2005 13:34] 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/internals/29426
[13 Sep 2005 12:37] 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/internals/29738
[13 Sep 2005 18:12] Ingo Strüwing
Pushed the 5.0 version to 5.0.3.
[13 Sep 2005 18:20] 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/internals/29764
[15 Sep 2005 9:38] Ingo Strüwing
Pushed version for 4.0 and 4.1 to 4.0.27 and 4.1.15.
[15 Sep 2005 17:03] Paul Dubois
Noted in 4.0.27, 4.1.15, 5.0.13 changelogs.