Bug #5257 Calling a stored procedure that contains a cursor makes mysqld crash
Submitted: 27 Aug 2004 16:14 Modified: 28 Sep 2004 8:44
Reporter: NOT_FOUND NOT_FOUND Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:mysql-5.0.1-alpha-max OS:SunOS 5.8
Assigned to: Per-Erik Martin CPU Architecture:Any

[27 Aug 2004 16:14] NOT_FOUND NOT_FOUND
Description:
I create a stored procedure that uses a cursor to select data from a table.
When the data is read mysqld crashes:

mysqld got signal 11;
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.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

-e 
Number of processes running now: 0

How to repeat:
use     mysql;

drop    table if exists page_output;

create  table page_output(
msg     varchar(255) null);

insert  into page_output values("a");

drop    procedure if exists scan_page_output;

delimiter //

create  procedure scan_page_output()
sql security invoker
begin

declare msg     varchar(255);

declare lees cursor for
        select  msg
                from    page_output;

open    lees;

repeat
        fetch lees into msg;

until   done end repeat;

close   lees;
end
//

delimiter ;

call scan_page_output();
[29 Aug 2004 10:50] Alexander Keremidarski
(gdb) bt
#0  0x4e3f2d6c in memcpy () from /lib/tls/libc.so.6
#1  0x082a7504 in Protocol_cursor::write() (this=0x86ebf28) at protocol_cursor.cc:127
#2  0x081820f4 in select_send::send_data(List<Item>&) (this=0x86ec3a0, items=@0x86ebf28) at sql_class.cc:772
#3  0x081eb07d in end_send (join=0x86ec3b0, join_tab=0x86ed51c, end_of_records=false) at sql_select.cc:8399
#4  0x081e953a in do_select (join=0x86ec3b0, fields=0x86ed51c, table=0x0, procedure=0x86fac60) at sql_select.cc:7465
#5  0x081dbec7 in JOIN::exec() (this=0x86ec3b0) at sql_select.cc:1550
#6  0x081dcbb4 in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, select_result*, st_select_lex_unit*, st_select_lex*) (thd=0x86bd8e0, rref_pointer_array=0x86f60c8, tables=0x86f4118, wild_num=0, fields=@0x86fac60, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=8669696, result=0x86ec3a0, unit=0x86f5e3c, select_lex=0x86f5fc8) at sql_select.cc:1972
#7  0x081d8356 in handle_select(THD*, st_lex*, select_result*) (thd=0x86bd8e0, lex=0x86f5e30, result=0x86ec3a0) at sql_select.cc:209
#8  0x081a7c6a in mysql_execute_command(THD*) (thd=0x86bd8e0) at sql_parse.cc:2039
#9  0x082aec6f in sp_instr_stmt::exec_stmt(THD*, st_lex*) (this=0x86f4250, thd=0x86bd8e0, lex=0x86f5e30) at sp_head.cc:1167
#10 0x082affcb in sp_instr_copen::execute(THD*, unsigned*) (this=0x86f4250, thd=0x86bd8e0, nextp=0x86fac60) at sp_head.cc:1595
#11 0x082aca51 in sp_head::execute(THD*) (this=0x86f3e28, thd=0x86bd8e0) at sp_head.cc:464
#12 0x082ad2b3 in sp_head::execute_procedure(THD*, List<Item>*) (this=0x86f3e28, thd=0x86bd8e0, args=0x86bdcc8) at sp_head.cc:677
#13 0x081ac93c in mysql_execute_command(THD*) (thd=0x86bd8e0) at sql_parse.cc:3667
#14 0x081ae33a in mysql_parse(THD*, char*, unsigned) (thd=0x86bd8e0, inBuf=0x86ec060 "call scan_page_output()", length=141285652) at sql_parse.cc:4432
#15 0x081a66f7 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x86bd8e0, packet=0x86e4001 "call scan_page_output()", packet_length=24) at sql_parse.cc:1486
#16 0x081a5fec in do_command(THD*) (thd=0x86bd8e0) at sql_parse.cc:1296
#17 0x081a545d in handle_one_connection (arg=0x86fac60) at sql_parse.cc:1032
#18 0x4e5c798c in start_thread () from /lib/tls/libpthread.so.0
#19 0x4e44616a in clone () from /lib/tls/libc.so.6
[31 Aug 2004 0:17] Peter Gulutzan
The variable 'done' is not defined, so the correct response would be an error return. 
But the crash seems to be caused by something else: the fact that "msg" is both the name 
of a variable and a column. I see no crash if I say this (MySQL-5.0.2-alpha-debug): 
create  procedure scan_page_output() sql security invoker begin declare msg1 
varchar(255); declare lees cursor for         select  msg from    page_output; open lees; 
repeat fetch lees into msg1; until 0=0 end repeat; close   lees; end//
[28 Sep 2004 8:44] Per-Erik Martin
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

As noted, there are errors in the original test case.
However, running the test case as it is, no longer causes a crash.
A warning is given upon creation, indicating that 'msg' is referred
to unialized.
When calling it, an error is given, saying that 'done' is not define.
(The error message is slightly confusing, as it defaults to a column
name in the absence of a local variable, but in principle it's correct.)

When correcting the test case it works:

create  procedure scan_page_output()
    sql security invoker
begin
  declare msg varchar(255);
  declare done int default 0;
  declare lees cursor for
    select t.msg from page_output t;
  declare continue handler for not found set done = 1;

  open    lees;
  repeat
    fetch lees into msg;
  until done end repeat;
  close lees;
end//