Bug #12228 Crash happens during calling specific SP in multithread environment
Submitted: 27 Jul 2005 20:58 Modified: 12 Aug 2005 19:44
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.11pre OS:Any (All)
Assigned to: Sergey Petrunya CPU Architecture:Any

[27 Jul 2005 20:58] Alexey Stroganov
Description:
Running following test case in multithread environment (stress test) for some time (less than 1 min) I got crash:

test case:

                --disable_warnings
                drop procedure if exists sp_5_401041//
                drop procedure if exists sp_5_401042//
                --enable_warnings
                create procedure sp_5_401042(in var2 decimal (33,8))
                begin
                        set var2 = 808.16;
                end//

                create procedure sp_5_401041()
                begin
                        declare var1 decimal (53,8) default 999.99;
                        call sp_5_401042(var1);
                        select var1;
                end//
                call sp_5_401041()//

back trace:

#0  0x082e504e in sp_cache_routines_and_add_tables_aux (thd=0x42e65750, lex=0x8ca0570,
    start=0x8e5add0) at sp.cc:1340
#1  0x082e5263 in sp_cache_routines_and_add_tables (thd=0x42e65750, lex=0x8ca0570)
    at sp.cc:1397
#2  0x081d3e33 in open_tables (thd=0x42e65750, start=0x431dd294, counter=0x431dd284)
    at sql_base.cc:1871
#3  0x081d46b1 in open_and_lock_tables (thd=0x42e65750, tables=0x0) at sql_base.cc:2148
#4  0x081acc70 in mysql_execute_command (thd=0x42e65750) at sql_parse.cc:4127
#5  0x082dd26f in sp_instr_stmt::exec_core (this=0x8e5ae80, thd=0x42e65750, nextp=0x431dd910)
    at sp_head.cc:1569
#6  0x082dd013 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8e5aea8, thd=0x42e65750,
    nextp=0x431dd910, open_tables=false, instr=0x8e5ae80) at sp_head.cc:1495
#7  0x082dd1b6 in sp_instr_stmt::execute (this=0x8e5ae80, thd=0x42e65750, nextp=0x431dd910)
    at sp_head.cc:1546
#8  0x082daa1f in sp_head::execute (this=0x8e5a9a8, thd=0x42e65750) at sp_head.cc:667
#9  0x082db6c8 in sp_head::execute_procedure (this=0x8e5a9a8, thd=0x42e65750, args=0x42e65bb8)
    at sp_head.cc:934
#10 0x081acefc in mysql_execute_command (thd=0x42e65750) at sql_parse.cc:4201
#11 0x081b01f3 in mysql_parse (thd=0x42e65750, inBuf=0x8ce96d0 "call sp_5_401041()",
    length=18) at sql_parse.cc:5384
#12 0x081a59fe in dispatch_command (command=COM_QUERY, thd=0x42e65750,
    packet=0x429d51b1 "call sp_5_401041()", packet_length=19) at sql_parse.cc:1664
#13 0x081a5206 in do_command (thd=0x42e65750) at sql_parse.cc:1467
#14 0x081a430c in handle_one_connection (arg=0x42e65750) at sql_parse.cc:1116
#15 0x40049aa7 in start_thread () from /lib/tls/libpthread.so.0
#16 0x4017ac2e in clone () from /lib/tls/libc.so.6

(gdb) frame 0
#0  0x082e504e in sp_cache_routines_and_add_tables_aux (thd=0x42e65750, lex=0x8ca0570, start=0x8e5add0) at sp.cc:1340
1340        int type= rt->key.str[0];

(gdb) p *rt
$1 = {key = {str = 0x0, length = 0}, next = 0x0}

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

Until it will be integrated to regular mysql-test suite please contact me and I will provide all necessary information.
[5 Aug 2005 23:04] 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/27944
[8 Aug 2005 19:19] 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/28025
[9 Aug 2005 6:04] 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/28053
[10 Aug 2005 17:12] 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/28119
[10 Aug 2005 17:40] Sergey Petrunya
Patch approved by Dmitri
[10 Aug 2005 17:54] Sergey Petrunya
Fix pushed into 5.0.12 tree.

Note for the changelog: The bug was that if one thread deletes a stored routine while another thread is executing a routine (either the same or a different routine), the server could crash (whether it would crash or not depended on execution races between the threads).

This patch introduces new behavior (please amend the manual):
When a client issues a statement, mysqld calculates a list of routines (procedures, functions, triggers) that may be invoked during the statement execution, loads them,
and then proceeds to execute the statement. 

While the statement is being executed, other threads may drop or alter (or drop+create)
the routines used by the statement. These changes will not affect execution of the statement in question - it will use a "snapshot" of routines it created when it loaded the routines.
[12 Aug 2005 19:44] Paul Dubois
Noted in 5.0.12 changelog.
Added note about routines using snapshots
to routine limitations section of manual.