MySQL Bugs: #13673: PROCEDURE ANALYSE() in stored procedure/prepared statements crashes MySQL

Bug #13673	PROCEDURE ANALYSE() in stored procedure/prepared statements crashes MySQL
Submitted:	30 Sep 2005 23:18	Modified:	15 Nov 2005 16:59
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	4.1	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

If PROCEDURE ANALYSE() is used inside a stored proc, mysql will crash during second call to the proc.

Regards,
  Jocelyn

How to repeat:
delimiter ;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int);
delimiter |
CREATE PROCEDURE test () BEGIN SELECT * FROM t1 PROCEDURE ANALYSE(); END |
CALL test()|
CALL test()|

Stack trace :

0x814d7cc handle_segfault + 356
0x4004f825 _end + 934398613
0x82146e3 analyse::~analyse() + 91
0x820fee5 proc_analyse_init(THD*, st_order*, select_result*, List<Item>&) + 229
0x81b587d setup_procedure(THD*, st_order*, select_result*, List<Item>&, int*) + 117
0x818b8a5 JOIN::prepare(Item***, st_table_list*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) + 1041
0x818f2e1 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 689
0x818b48a handle_select(THD*, st_lex*, select_result*, unsigned long) + 234
0x816103d mysql_execute_command(THD*) + 761
0x823ae92 sp_instr_stmt::exec_core(THD*, unsigned int*) + 14
0x823ad2e sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) + 294
0x823ae2c sp_instr_stmt::execute(THD*, unsigned int*) + 168
0x8238a4e sp_head::execute(THD*) + 634
0x823984f sp_head::execute_procedure(THD*, List<Item>*) + 1143
0x81657a7 mysql_execute_command(THD*) + 19043
0x8167c22 mysql_parse(THD*, char*, unsigned int) + 294
0x815f82e dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1186
0x815f351 do_command(THD*) + 129
0x815e84e handle_one_connection + 550
0x40049e51 _end + 934375617
0x401d06ea _end + 935975258

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter ;
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter |
mysql> CREATE PROCEDURE test () BEGIN SELECT * FROM t1 PROCEDURE ANALYSE(); END |
Query OK, 0 rows affected (0.01 sec)

mysql> CALL test()|
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL test()|
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

prepared statements affected by this bug, too. Following script lead to crash of the server:
CREATE TABLE t1 (a int);
prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

repeatable on last 4.1 with PS

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

See also Bug#14138 "ROLLUP and PROCEDURE ANALYSE() hang server (kill -SIGINT doesn't help)"

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

An addition of the test case now should be all that is needed to be done for this bug.
Sanja, please add the test case to 4.1 and close the bug.

Thank you for bugreport!
This bug was fixed by the bugfix of the BUG#14138, but test suite of this bug is pushed into the source tree.