MySQL Bugs: #19399: Stored Procedures 'Lost Connection' when dropping/creating tables

Bug #19399	Stored Procedures 'Lost Connection' when dropping/creating tables
Submitted:	27 Apr 2006 10:40	Modified:	15 Aug 2006 11:15
Reporter:	Giles McArdell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	4.1/5.0.22BK, 5.0.20/5.1BK	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
This is a correction to Bug report #18422, the bug tended to occur differently, or dissapear alltogether, when unrelated changes were made to the stored procedures, thus leading to my incorrect analysis of what was causing the Bug.

Most often the Bug manifests during a run as a spurious 'Error Code : 2013 Lost connection to MySQL server during query'. But it has shown up both as an error caught by an error handler, and as Error 124 (which makes it similar to bug #14616, which is supposedly fixed).

In the example that follows the act of repeatedly creating and dropping tables seems to be what causes the bug (if you comment out the DROP TABLE statements and insert the TRUNCATE TABLE statements instead it should work fine).

The table type does not seem to matter, I have seen the bug with MyISAM, INNODB and MEMORY tables, although using INNODB tables seemed to make the bug occur somewhat later. The bug occurs whether the tables are Temporary or not.

The bug can be made more intermittent by removing the DELETE statements and by not nesting the procedures.

I have now, after some work, been able to pin down the bug into the following code (This was written using SQLYog, it might need some fiddling to get it to work in the command line client): 

How to repeat:
DELIMITER $$;

DROP PROCEDURE IF EXISTS `spt_BugTest1`$$

CREATE PROCEDURE `spt_BugTest1` (n int)
SP:BEGIN

	DECLARE v_Count INT;
	DECLARE v_Max INT;

	CREATE TEMPORARY TABLE IF NOT EXISTS a ( a1 int auto_increment, a2 decimal(19,12), a3 varchar(100), INDEX(a1) );
	CREATE TEMPORARY TABLE IF NOT EXISTS b ( b1 varchar(100), b2 int auto_increment, b3 decimal(19,12), INDEX(b2) );

	SET v_Count = n;

	INSERT a (a2,a3) SELECT n, 'a' ;

	WHILE v_Count < n + 100 DO

		INSERT b (b1,b3) SELECT CONCAT('b', a.a3), v_Count + 1 FROM a WHERE a.a2 = v_Count ;

		SET v_Count = v_Count + 1;

		INSERT a (a2,a3) SELECT v_Count, CONCAT('a', b.b1) FROM b WHERE b.b3 = v_Count ;

	END WHILE;

	DELETE a FROM a WHERE (CAST(a2/3 AS UNSIGNED) * 3) = a2;
	DELETE b FROM b WHERE (CAST(b3/4 AS UNSIGNED) * 4) <> b3;

	UPDATE a,b SET a3 = b1 WHERE a1 = b2;

	SELECT MAX(a1) INTO v_Max FROM a;

	INSERT c SELECT n, a3 FROM a WHERE a1 = v_Max;

 	DROP TEMPORARY TABLE a;
 	DROP TEMPORARY TABLE b;
-- 	TRUNCATE TABLE a;
-- 	TRUNCATE TABLE b;
END$$

DELIMITER ;$$

DELIMITER $$;

DROP PROCEDURE IF EXISTS `spt_BugTestRun`$$

CREATE PROCEDURE `spt_BugTestRun` ()
SP:BEGIN

	DECLARE v_Count INT;

	CREATE TEMPORARY TABLE IF NOT EXISTS c ( n int, r varchar(100) );

	SET v_Count = 1;

	WHILE v_Count < 100 DO

		CALL spt_BugTest1( v_Count );

		SET v_Count = v_Count + 1;
		
	END WHILE;

	SELECT * FROM c;

	DROP TEMPORARY TABLE c;

END$$

DELIMITER ;$$

call spt_BugTestRun();

Suggested fix:
As suggested above, do not drop tables in stored procedures (particularly if they are called repeatedly from another procedure). 

Instead just truncate the tables.

Thank you for a bug report. Verified just as described with 5.0.22-BK on SuSE 9.3:

mysql> delimiter //
mysql> CREATE PROCEDURE `spt_BugTest1` (n int)
    -> SP:BEGIN
    ->   DECLARE v_Count INT;
    ->   DECLARE v_Max INT;
    ->
    ->   CREATE TEMPORARY TABLE IF NOT EXISTS a ( a1 int auto_increment, a2
    -> decimal(19,12), a3 varchar(100), INDEX(a1) );
    ->   CREATE TEMPORARY TABLE IF NOT EXISTS b ( b1 varchar(100), b2 int
    -> auto_increment, b3 decimal(19,12), INDEX(b2) );
    ->
    ->   SET v_Count = n;
    ->   INSERT a (a2,a3) SELECT n, 'a' ;
    ->
    ->   WHILE v_Count < n + 100 DO
    ->     INSERT b (b1,b3) SELECT CONCAT('b', a.a3), v_Count + 1
    ->     FROM a WHERE a.a2 = v_Count;
    ->
    ->     SET v_Count = v_Count + 1;
    ->
    ->     INSERT a (a2,a3) SELECT v_Count, CONCAT('a', b.b1)
    ->     FROM b WHERE b.b3 = v_Count;
    ->   END WHILE;
    ->
    ->   DELETE a FROM a WHERE (CAST(a2/3 AS UNSIGNED) * 3) = a2;
    ->   DELETE b FROM b WHERE (CAST(b3/4 AS UNSIGNED) * 4) <> b3;
    ->
    ->   UPDATE a,b SET a3 = b1 WHERE a1 = b2;
    ->   SELECT MAX(a1) INTO v_Max FROM a;
    ->
    ->   INSERT c SELECT n, a3 FROM a WHERE a1 = v_Max;
    ->
    ->   DROP TEMPORARY TABLE a;
    ->   DROP TEMPORARY TABLE b;
    -> END//
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE `spt_BugTestRun` ()
    -> SP:BEGIN
    ->   DECLARE v_Count INT;
    ->
    ->   CREATE TEMPORARY TABLE IF NOT EXISTS c ( n int, r varchar(100) );
    ->
    ->   SET v_Count = 1;
    ->
    ->   WHILE v_Count < 100 DO
    ->     CALL spt_BugTest1( v_Count );
    ->     SET v_Count = v_Count + 1;
    ->   END WHILE;
    ->
    ->   SELECT * FROM c;
    ->   DROP TEMPORARY TABLE c;
    ->
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> call spt_BugTestRun()//
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select version();
    -> //
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: test

+-----------+
| version() |
+-----------+
| 5.0.22    |
+-----------+
1 row in set (0.18 sec)

In the error log of server I've got:

0x8182b76
0xffffe420
(nil)
0x82afe8f
0x82b00db
0x82b0702
0x82b22d0
0x82b28a3
0x81a0a48
0x82afe8f
0x82b00db
0x82b0702
0x82b22d0
0x82b28a3
0x81a0a48
0x81a279a
0x81a4c91
0x81a5eda
0x4004eaa7
0x40249c2e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8b7d5c0 = DELETE a FROM a WHERE (CAST(a2/3 AS UNSIGNED) * 3) = a2

The resolved stack trace is:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld50.sym -n 19399.sta
ck
0x8182b76 handle_segfault + 566
0xffffe420 _end + -140458232
(nil)
0x82afe8f _ZN13sp_instr_stmt9exec_coreEP3THDPj + 15
0x82b00db _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 171
0x82b0702 _ZN13sp_instr_stmt7executeEP3THDPj + 1042
0x82b22d0 _ZN7sp_head7executeEP3THD + 1136
0x82b28a3 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 819
0x81a0a48 _Z21mysql_execute_commandP3THD + 32136
0x82afe8f _ZN13sp_instr_stmt9exec_coreEP3THDPj + 15
0x82b00db _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 171
0x82b0702 _ZN13sp_instr_stmt7executeEP3THDPj + 1042
0x82b22d0 _ZN7sp_head7executeEP3THD + 1136
0x82b28a3 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 819
0x81a0a48 _Z21mysql_execute_commandP3THD + 32136
0x81a279a _Z11mysql_parseP3THDPcj + 522
0x81a4c91 _Z16dispatch_command19enum_server_commandP3THDPcj + 3393
0x81a5eda handle_one_connection + 1802
0x4004eaa7 _end + 933612943
0x40249c2e _end + 935690006

Adding 5.1.XX:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.10-beta-debug | 
+-------------------+
1 row in set (0.01 sec)

mysql> call spt_BugTestRun();
ERROR 2013 (HY000): Lost connection to MySQL server during query

This is a minimal test case for the bug:

drop procedure if exists bug19399|
create procedure bug19399()
begin
-- temporary table is essential
  create temporary table if not exists t1 (a1 int);
-- exact delete syntax is essential
  delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1;
  drop temporary table t1;
end|
call bug19399()|
call bug19399()|

A test case that does not use stored procedures:

  create temporary table if not exists t1 (a1 int);
-- exact delete syntax is essential
  prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
  drop temporary table t1;
  create temporary table if not exists t1 (a1 int);
-- crash
  execute stmt;
  drop temporary table t1;
  deallocate prepare stmt;

The bug is repeatable in 4.1, but doesn't cause a crash.
Also, in 4.1 you need to execute the statement twice before a valgrind warning will pop up:

create temporary table if not exists t1 (a1 int);
-- exact delete syntax is essential
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
drop temporary table t1;
create temporary table if not exists t1 (a1 int);
-- crash
execute stmt;
drop temporary table t1;
create temporary table if not exists t1 (a1 int);
-- crash
execute stmt;
drop temporary table t1;
deallocate prepare stmt;

==28572== Thread 3:
==28572== Invalid read of size 4
==28572==    at 0x8151B8A: check_table_access(THD*, unsigned long, st_table_list*, bool) (sql_parse.cc:3990)
==28572==    by 0x8151DE0: multi_delete_precheck(THD*, st_table_list*, unsigned*) (sql_parse.cc:5620)
==28572==    by 0x8156F2E: mysql_execute_command(THD*) (sql_parse.cc:3025)
==28572==    by 0x8199DE9: execute_stmt(THD*, Prepared_statement*, String*, bool) (sql_prepare.cc:1931)
==28572==    by 0x819BF34: mysql_sql_stmt_execute(THD*, st_lex_string*) (sql_prepare.cc:1887)
==28572==    by 0x8155231: mysql_execute_command(THD*) (sql_parse.cc:2217)
==28572==    by 0x8159422: mysql_parse(THD*, char*, unsigned) (sql_parse.cc:4362)
==28572==    by 0x8159C76: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1524)
==28572==    by 0x815AEB7: do_command(THD*) (sql_parse.cc:1327)
==28572==    by 0x815B97C: handle_one_connection (sql_parse.cc:1059)
==28572==    by 0x404D340: start_thread (in /lib/tls/i686/cmov/libpthread-2.3.6.so)
==28572==    by 0x41884ED: clone (in /lib/tls/i686/cmov/libc-2.3.6.so)
==28572==  Address 0x4B6D63C is 332 bytes inside a block of size 1,340 free'd
==28572==    at 0x401CFCF: free (vg_replace_malloc.c:235)
==28572==    by 0x83268EB: _myfree (safemalloc.c:314)
==28572==    by 0x817119C: close_temporary(st_table*, bool) (sql_base.cc:480)
==28572==    by 0x8171222: close_temporary_table(THD*, char const*, char const*) (sql_base.cc:700)
==28572==    by 0x81E43C5: mysql_rm_table_part2(THD*, st_table_list*, bool, bool, bool) (sql_table.cc:224)
==28572==    by 0x81E4B4D: mysql_rm_table(THD*, st_table_list*, char, char) (sql_table.cc:121)
==28572==    by 0x8157265: mysql_execute_command(THD*) (sql_parse.cc:3111)
==28572==    by 0x8159422: mysql_parse(THD*, char*, unsigned) (sql_parse.cc:4362

Bug#20540 is a duplicate of this bug.

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

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

Pushed into 4.1 tree currently tagged 4.1.21

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

Pushed into 5.0-runtime

Before 5.0.25 clone off, that's for sure. 
To not create 5.0->5.1 merge logjam I try to queue more patches in the runtime tree before a merge.
The next merge is planned next week.

Bug #21186 was marked as a duplicate of/related to this one.

Pushed into 5.0.25

Merged into 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.21/5.0.25/5.1.12 changelogs.