MySQL Bugs: #21136: CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash

Bug #21136	CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
Submitted:	19 Jul 2006 8:29	Modified:	23 Oct 2007 19:39
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1.12-bk	OS:	Linux (Linux)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
CREATE TEMPORARY TABLE ... SELECT which is invoked from stored function which in its turn called from CREATE TABLE SELECT causes server crash.  See "how to repeat" for more info. Note that crash does not happen in 5.0, also note that this is bug is not duplicate of bug#21039 as initial analysis shows that crash is caused by different reason.

How to repeat:
# Test for mysqltest program
delimiter |;
create function f1() returns int
begin
 declare res int;
 drop temporary table if exists t1;
 create temporary table t1 select 1 i;
 set res:= (select count(*) from t1);
 drop temporary table t1;
 return res;
end|
create table t2 as select 1|
--disable_warnings
# This crashes server (but crash won't be reported until next statement)
create table t3 as select f1() from t2|
--enable_warnings
# Here crash will be reported
select 1|

Verified as described.

(gdb) bt
#0  0x081f7bcd in mysql_unlock_tables (thd=0x87ec3f8, sql_lock=0x0) at lock.cc:268
#1  0x08273695 in select_create::send_eof (this=0x8816740) at sql_insert.cc:3006
#2  0x082627de in do_select (join=0x883b508, fields=0x87ec770, table=0x0, procedure=0x0) at sql_select.cc:9744
#3  0x08253f00 in JOIN::exec (this=0x883b508) at sql_select.cc:1837
#4  0x082542d3 in mysql_select (thd=0x87ec3f8, rref_pointer_array=0x87ec82c, tables=0x8821a40, wild_num=0, fields=@0x87ec770, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2418297344, result=0x8816740, unit=0x87ec4a0, select_lex=0x87ec6e0) at sql_select.cc:1999
#5  0x0824fd49 in handle_select (thd=0x87ec3f8, lex=0x87ec438, result=0x8816740, setup_tables_done_option=0) at sql_select.cc:242
#6  0x08216734 in mysql_execute_command (thd=0x87ec3f8) at sql_parse.cc:2997
#7  0x0821d791 in mysql_parse (thd=0x87ec3f8, inBuf=0x88139b8 "create table t3 as select f1() from t2", length=38) at sql_parse.cc:6062
#8  0x08214375 in dispatch_command (command=COM_QUERY, thd=0x87ec3f8, packet=0x880b7f1 "", packet_length=39) at sql_parse.cc:1828
#9  0x08213d16 in do_command (thd=0x87ec3f8) at sql_parse.cc:1612
#10 0x08212fe4 in handle_one_connection (arg=0x40329b90) at sql_parse.cc:1227
#11 0x40053aa7 in start_thread () from /lib/tls/libpthread.so.0
#12 0x40184c2e in clone () from /lib/tls/libc.so.6

While analyzing this bug, I found that the inner CREATE SELECT unlocks prematurely thd->extra_lock. According to Dmitri Lenev the inner CREATE SELECT should not get a lock. Using thd->in_sub_stmt in select_create::send_eof() is not an option as actually the outter thd->extra_lock will be lost (leaked) when the inner CREATE SELECT is being locked.

Adding 5.0.26.  Original bug is not reproducible in 5.0, but if you replace the last line of the test with CREATE TEMPORARY TABLE t1 AS SELECT f1() FROM t2; the crash will happen.

Doesn't crash any more. Need to investigate why.

The issue reported by Tomash Brechko is a different one and easier to
reproduce, it happens when the table being created is dropped inside
the stored function. I'll file a separate bug report on it.

It doesn't crash any more becase the changset 1.261.3.1 introduced a if
branch which avoids the crash (by not calling mysql_unlock_tables with
a NULL lock object). Now, the overwritten lock is leaked.

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/34025

ChangeSet@1.2609, 2007-09-11 01:29:33-03:00, davi@moksha.local +3 -0
  Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
  
  When CREATE TEMPORARY TABLE .. SELECT is invoked from a stored function
  which in turn is called from CREATE TABLE SELECT causes a memory leak
  because the inner create temporary table overrides the outter extra_lock
  reference when locking the table.
  
  The solution is to simply not overrride the extra_lock reference if the
  table being created/locked is a temporary table.

Sent code review by email.

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/34642

ChangeSet@1.2609, 2007-09-28 10:26:10-03:00, davi@moksha.local +3 -0
  Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
  
  When CREATE TEMPORARY TABLE .. SELECT is invoked from a stored function
  which in turn is called from CREATE TABLE SELECT causes a memory leak
  because the inner create temporary table overrides the outter extra_lock
  reference when locking the table.
  
  The solution is to simply not overrride the extra_lock reference and merge
  the the new lock into the extra_lock array.

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/34657

ChangeSet@1.2612, 2007-09-28 16:22:46-03:00, davi@moksha.local +3 -0
  Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
  
  When CREATE TEMPORARY TABLE .. SELECT is invoked from a stored function
  which in turn is called from CREATE TABLE SELECT causes a memory leak
  because the inner create temporary table overrides the outter extra_lock
  reference when locking the table.
  
  The solution is to simply not overrride the extra_lock by only using the
  extra_lock for a non-temporary table lock.

Sent code review by email.

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.