Bug #47649 crash during CALL procedure
Submitted: 25 Sep 2009 13:25 Modified: 12 Mar 2010 17:21
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1,6.0.14 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[25 Sep 2009 13:25] Matthias Leich
Description:
My script:
----------
--disable_abort_on_error
CREATE TABLE t1 ( f1 integer, primary key (f1));
CREATE TABLE t2 LIKE t1;
CREATE TEMPORARY TABLE t3 LIKE t1;
delimiter |;
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; END|
delimiter ;|
CALL p1;
CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
DROP TABLE t3;
CALL p1;
DROP TABLE t3;
CALL p1;

Result on mysql-6.0-codebase-bugfixing 2009-09-24 08:11:33
----------------------------------------------------------
CREATE TABLE t1 ( f1 integer, primary key (f1));
CREATE TABLE t2 LIKE t1;
CREATE TEMPORARY TABLE t3 LIKE t1;
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; END|
CALL p1;
ERROR HY000: Can't reopen table: 'A'
CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
DROP TABLE t3;
CALL p1;
f1
DROP TABLE t3;
ERROR 42S02: Unknown table 't3'
CALL p1;
ERROR HY000: Lost connection to MySQL server during query

Thread 1 (process 29168):
#0  0x00007f993e735ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b75224 in my_write_core (sig=11) at stacktrace.c:309
#2  0x00000000006f3e24 in handle_segfault (sig=11) at mysqld.cc:2755
#3  <signal handler called>
#4  0x0000000000617fe1 in Item_cache::get_cache (item=0x162ed70) at item.cc:7171
#5  0x000000000066647b in Item_in_optimizer::fix_left (this=0x181c850, thd=0x16170a8, ref=0x162ea40) at item_cmpfunc.cc:1486
#6  0x00000000006667d3 in Item_in_optimizer::fix_fields (this=0x181c850, thd=0x16170a8, ref=0x162ea40) at item_cmpfunc.cc:1521
#7  0x0000000000662f87 in Item_cond::fix_fields (this=0x162e938, thd=0x16170a8, ref=0x180ac80) at item_cmpfunc.cc:3980
.....

Result on mysql-5.1-bugteam 2009-09-16
--------------------------------------
Crash on the last statement (CALL p1;)
ERROR HY000: Lost connection to MySQL server during query

Thread 1 (process 29303):
#0  0x00007ff8b862ece6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b1a74b in my_write_core (sig=11) at stacktrace.c:310
#2  0x00000000006b97f4 in handle_segfault (sig=11) at mysqld.cc:2552
#3  <signal handler called>
#4  0x0000000000617407 in Item_func::fix_fields (this=0x12607e0, thd=0x11ac3b8, ref=0x122cf98) at item_func.cc:171
#5  0x000000000062c5bd in Item_cond::fix_fields (this=0x122ce80, thd=0x11ac3b8, ref=0x125d910) at item_cmpfunc.cc:3911
#6  0x0000000000713404 in setup_conds (thd=0x11ac3b8, tables=0x1225940, leaves=0x12302e8, conds=0x125d910) at sql_base.cc:7991
#7  0x0000000000763212 in setup_without_group (thd=0x11ac3b8, ref_pointer_array=0x1232b58, tables=0x1225940, leaves=0x12302e8, fields=@0x1213fc8, all_fields=@0x125d828, conds=0x125d910, order=0x0, group=0x0, hidden_group_fields=0x125d807)
    at sql_select.cc:412
#8  0x000000000075a80f in JOIN::prepare (this=0x125c248, rref_pointer_array=0x1214090, tables_init=0x1225940, wild_num=0, conds_init=0x122ce80, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0,
    select_lex_arg=0x1213ec0, unit_arg=0x12254d8) at sql_select.cc:494
#9  0x000000000066531b in subselect_single_select_engine::prepare (this=0x1225d98) at item_subselect.cc:1758
#10 0x000000000066a2d2 in Item_subselect::fix_fields (this=0x1225c80, thd_param=0x11ac3b8, ref=0x12605b8) at item_subselect.cc:158
#11 0x000000000066a66f in Item_in_subselect::fix_fields (this=0x1225c80, thd_arg=0x11ac3b8, ref=0x12605b8) at item_subselect.cc:1626
#12 0x000000000062fe94 in Item_in_optimizer::fix_fields (this=0x1260530, thd=0x11ac3b8, ref=0x122d0a8) at item_cmpfunc.cc:1526
#13 0x000000000062c5bd in Item_cond::fix_fields (this=0x122cfa0, thd=0x11ac3b8, ref=0x122e840) at item_cmpfunc.cc:3911
#14 0x0000000000713404 in setup_conds (thd=0x11ac3b8, tables=0x1213a78, leaves=0x122ede8, conds=0x122e840) at sql_base.cc:7991
#15 0x0000000000763212 in setup_without_group (thd=0x11ac3b8, ref_pointer_array=0x12324d8, tables=0x1213a78, leaves=0x122ede8, fields=@0x1212fa8, all_fields=@0x122e758, conds=0x122e840, order=0x0, group=0x0, hidden_group_fields=0x122e737)
    at sql_select.cc:412
#16 0x000000000075a80f in JOIN::prepare (this=0x122d178, rref_pointer_array=0x1213070, tables_init=0x1213a78, wild_num=0, conds_init=0x122cfa0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0,
    select_lex_arg=0x1212ea0, unit_arg=0x1212a78) at sql_select.cc:494
#17 0x000000000075b791 in mysql_select (thd=0x11ac3b8, rref_pointer_array=0x1213070, tables=0x1213a78, wild_num=0, fields=@0x1212fa8, conds=0x122cfa0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147765760,
    result=0x122d158, unit=0x1212a78, select_lex=0x1212ea0) at sql_select.cc:2373
#18 0x0000000000761091 in handle_select (thd=0x11ac3b8, lex=0x12129d8, result=0x122d158, setup_tables_done_option=0) at sql_select.cc:268
#19 0x00000000006c9aa6 in execute_sqlcom_select (thd=0x11ac3b8, all_tables=0x1213a78) at sql_parse.cc:5022
#20 0x00000000006cbb24 in mysql_execute_command (thd=0x11ac3b8) at sql_parse.cc:2217
#21 0x0000000000899d0f in sp_instr_stmt::exec_core (this=0x1225dd8, thd=0x11ac3b8, nextp=0x416571c8) at sp_head.cc:2905
#22 0x0000000000899f51 in sp_lex_keeper::reset_lex_and_exec_core (this=0x1225e18, thd=0x11ac3b8, nextp=0x416571c8, open_tables=false, instr=0x1225dd8) at sp_head.cc:2734
#23 0x00000000008a0555 in sp_instr_stmt::execute (this=0x1225dd8, thd=0x11ac3b8, nextp=0x416571c8) at sp_head.cc:2848
#24 0x000000000089c469 in sp_head::execute (this=0x1212298, thd=0x11ac3b8) at sp_head.cc:1252
#25 0x000000000089d234 in sp_head::execute_procedure (this=0x1212298, thd=0x11ac3b8, args=0x11ae728) at sp_head.cc:1982
#26 0x00000000006d267c in mysql_execute_command (thd=0x11ac3b8) at sql_parse.cc:4363
#27 0x00000000006d47b1 in mysql_parse (thd=0x11ac3b8, inBuf=0x1214338 "CALL p1", length=7, found_semicolon=0x41658ef0) at sql_parse.cc:5942
#28 0x00000000006d55fc in dispatch_command (command=COM_QUERY, thd=0x11ac3b8, packet=0x12008f9 "CALL p1", packet_length=7) at sql_parse.cc:1224
#29 0x00000000006d69cd in do_command (thd=0x11ac3b8) at sql_parse.cc:865
#30 0x00000000006c2e99 in handle_one_connection (arg=0x11ac3b8) at sql_connect.cc:1127
#31 0x00007ff8b862a040 in start_thread () from /lib64/libpthread.so.0
#32 0x00007ff8b78d808d in clone () from /lib64/libc.so.6
#33 0x0000000000000000 in ?? ()

My environment:
---------------
---------------
- MySQL compiled from source
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[25 Sep 2009 13:49] Peter Laursen
I do not get crash with 5.1.39 (64 bit) on Windows. Only 'table does not exist'.

-- but I do not understand the meaning of the line reading 'f1' either so I skipped it.
[25 Sep 2009 22:19] Matthias Leich
Hi,
my mysql-5.1-bugteam shows this bug and says it is
of version "5.1.39-debug-log".
I guess you mean with "the line reading 'f1' "
the CREATE VIEW t3 AS SELECT f1 ...

The test is pure artificial.
A run of the RandomQueryGenerator (RQG) with a huge
grammar which runs aggressive DDL. After detecting
a problem the testcase gets shrinked
= statements and columns which are not required for the bad
  effect get removed. This makes debugging easier.
Therefore statements or table definitions look ugly.

The current bug is IMHO more important for testing with
RQG (I do not want to abort because of such a crash
and I cannot avoid that RQG invents such "crap") than
for a user with a production or development scenario.
RQG tests with a lot dense and aggressive DDL try to
simulate "accidents" which could happen if a DBA
makes typo's, is careless, whatever ...

Regards

Matthias
[25 Sep 2009 22:37] Peter Laursen
@Mathias .. your wrote an example like this:

CREATE TABLE t1 ( f1 integer, primary key (f1));
CREATE TABLE t2 LIKE t1;
CREATE TEMPORARY TABLE t3 LIKE t1;
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN (
SELECT f1 FROM t3 ) ; END|
CALL p1;
ERROR HY000: Can't reopen table: 'A'
CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2
);
DROP TABLE t3;
CALL p1;
f1
DROP TABLE t3;
ERROR 42S02: Unknown table 't3'
CALL p1;
ERROR HY000: Lost connection to MySQL server during query

There is a line reading "f1" only.
[28 Sep 2009 6:17] MySQL Verification Team
repeatable with following crash on 5.1.38 on win32.....

Attachment: bug47649_5.1.38_crash.txt (text/plain), 3.71 KiB.

[28 Sep 2009 6:34] MySQL Verification Team
5.1.39 results under valgrind - look at all those invalid reads/writes!!

Attachment: bug47649_5.1.39_valgrind_errors.txt (text/plain), 41.16 KiB.

[28 Sep 2009 10:37] Matthias Leich
Hi Peter,

now I understand what you mean. You refer to the protocol
and not to my script.
The single line with "f1" is no statement. This is the
empty result set produced by the preceding "CALL p1".

Regards

Matthias
[17 Nov 2009 11:15] 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/commits/90642

3147 Sergey Glukhov	2009-11-17
      Bug#47649 crash during CALL procedure
      If first call of the procedure is failed on
      the open_table stage stmt_arena->state is set to
      EXECUTED state. On second call(if no errors on
      open_table stage) it leads to use of worng memory arena
      in find_field_in_view() function as
      thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
      returns FALSE for EXECUTED state. The item is created 
      not in its own arena and it leads to crash on further
      calls of the procedure.
      The fix: 
      change state of arena only if
      no errors on open_table stage happens.
     @ mysql-test/r/sp.result
        test result
     @ mysql-test/t/sp.test
        test case
     @ sql/sp_head.cc
        If first call of the procedure is failed on
        the open_table stage stmt_arena->state is set to
        EXECUTED state. On second call(if no errors on
        open_table stage) it leads to use of worng memory arena
        in find_field_in_view() function as
        thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
        returns FALSE for EXECUTED state. The item is created 
        not in its own arena and it leads to crash on further
        calls of the procedure.
        The fix: 
        change state of arena only if
        no errors on open_table stage happens.
[1 Dec 2009 16:20] Georgi Kodinov
Bug #48760 is a marked as a duplicate of this one.
[23 Dec 2009 11:26] 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/commits/95490

3147 Sergey Glukhov	2009-12-23
      Bug#47649 crash during CALL procedure
      If first call of the procedure is failed on
      the open_table stage stmt_arena->state is set to
      EXECUTED state. On second call(if no errors on
      open_table stage) it leads to use of worng memory arena
      in find_field_in_view() function as
      thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
      returns FALSE for EXECUTED state. The item is created 
      not in its own arena and it leads to crash on further
      calls of the procedure.
      The fix: 
      change state of arena only if
      no errors on open_table stage happens.
     @ mysql-test/t/sp.test
        If first call of the procedure is failed on
        the open_table stage stmt_arena->state is set to
        EXECUTED state. On second call(if no errors on
        open_table stage) it leads to use of worng memory arena
        in find_field_in_view() function as
        thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
        returns FALSE for EXECUTED state. The item is created 
        not in its own arena and it leads to crash on further
        calls of the procedure.
        The fix: 
        change state of arena only if
        no errors on open_table stage happens.
[23 Dec 2009 13:44] 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/commits/95540

3294 Sergey Glukhov	2009-12-23
      Bug#47649 crash during CALL procedure
      If first call of the procedure is failed on
      the open_table stage stmt_arena->state is set to
      EXECUTED state. On second call(if no errors on
      open_table stage) it leads to use of worng memory arena
      in find_field_in_view() function as
      thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
      returns FALSE for EXECUTED state. The item is created 
      not in its own arena and it leads to crash on further
      calls of the procedure.
      The fix: 
      change state of arena only if
      no errors on open_table stage happens.
     @ mysql-test/r/sp.result
        test result
     @ mysql-test/t/sp.test
        test case
     @ sql/sp_head.cc
        If first call of the procedure is failed on
        the open_table stage stmt_arena->state is set to
        EXECUTED state. On second call(if no errors on
        open_table stage) it leads to use of worng memory arena
        in find_field_in_view() function as
        thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
        returns FALSE for EXECUTED state. The item is created 
        not in its own arena and it leads to crash on further
        calls of the procedure.
        The fix: 
        change state of arena only if
        no errors on open_table stage happens.
[15 Jan 2010 9:01] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:sergey.glukhov@sun.com-20091223134403-p8lee8eve2riql83) (merge vers: 5.1.42) (pib:16)
[16 Jan 2010 1:25] Paul DuBois
Noted in 5.1.43 changelog.

If an invocation of a stored procedure failed in the table-open
stage, subsequent invocations that did not fail in that stage could
cause a crash. 

Setting report to NDI pending push to 5.5.x+.
[5 Feb 2010 11:46] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (pib:16)
[5 Feb 2010 11:52] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 11:58] Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20091225105650-qletdbs0wz9sx5nc) (merge vers: 5.5.1-m2) (pib:16)
[5 Feb 2010 16:58] Paul DuBois
Noted in 5.5.2, 6.0.14 changelogs.

Setting report to Need Merge pending push to Celosia.
[12 Mar 2010 14:12] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:28] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:44] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 17:21] Paul DuBois
Fixed in earlier 5.1.x, 5.5.x.