Bug #48508 re-execution of prepared statement crashes!
Submitted: 3 Nov 2009 16:41 Modified: 20 Jun 2010 17:49
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.18, 5.0.85, 5.1.37, 5.1.40, 5.1.41, 5.4.3 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: regression
Triage: Triaged: D1 (Critical)

[3 Nov 2009 16:41] Shane Bester
Description:
Version: '5.1.40-enterprise-gpl-advanced'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)
R6025
- pure virtual function call

5.1.40 release binary call stack:

mysqld.exe!my_sigabrt_handler()[mysqld.cc:2048]
mysqld.exe!raise()[winsig.c:597]
mysqld.exe!abort()[abort.c:78]
mysqld.exe!Item_cond::fix_fields()[item_cmpfunc.cc:3912]
mysqld.exe!setup_conds()[sql_base.cc:7998]
mysqld.exe!setup_without_group()[sql_select.cc:415]
mysqld.exe!JOIN::prepare()[sql_select.cc:501]
mysqld.exe!mysql_select()[sql_select.cc:2373]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5044]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
mysqld.exe!Prepared_statement::execute()[sql_prepare.cc:3579]
mysqld.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3253]
mysqld.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2526]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2248]
mysqld.exe!mysql_parse()[sql_parse.cc:5967]
mysqld.exe!dispatch_command()[sql_parse.cc:1226]
mysqld.exe!do_command()[sql_parse.cc:865]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()

5.1.40 debug call stack:

mysqld-debug.exe!Item_cond::copy_andor_arguments()[item_cmpfunc.cc:3869]
mysqld-debug.exe!Item_cond_and::copy_andor_structure()[item_cmpfunc.h:1651]
mysqld-debug.exe!reinit_stmt_before_use()[sql_prepare.cc:2329]
mysqld-debug.exe!Prepared_statement::execute()[sql_prepare.cc:3566]
mysqld-debug.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3253]
mysqld-debug.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2526]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2248]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5967]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1226]
mysqld-debug.exe!do_command()[sql_parse.cc:865]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

How to repeat:
see private.
[3 Nov 2009 16:41] Shane Bester
drop table if exists `t1`;
create table `t1`(`b` int)engine=myisam;
insert into `t1` values (0);

create or replace view `v1` AS 
select 1 as `a` from `t1` where `b`;

prepare stmt from "select *
from `v1` where a";
execute stmt;
execute stmt;
[3 Nov 2009 17:54] Valeriy Kravchuk
Verified just as described with recent 5.1.41-debug from bzr on Mac OS X. In the error log I've got:

Version: '5.1.41-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
091103 19:51:13 - 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=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337715 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x101c218
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb026af64 thread_stack 0x30000
0   mysqld                              0x0058919a my_print_stacktrace + 45
1   mysqld                              0x000fcfc6 handle_segfault + 882
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x00076b3a _ZN13Item_cond_and20copy_andor_structureEP3THD + 108
5   mysqld                              0x001c388c _Z22reinit_stmt_before_useP3THDP6st_lex + 260
6   mysqld                              0x001c6686 _ZN18Prepared_statement7executeEP6Stringb + 932
7   mysqld                              0x001c69c6 _ZN18Prepared_statement12execute_loopEP6StringbPhS2_ + 334
8   mysqld                              0x001c6d04 _Z22mysql_sql_stmt_executeP3THD + 492
9   mysqld                              0x001162cd _Z21mysql_execute_commandP3THD + 3011
10  mysqld                              0x001203c6 _Z11mysql_parseP3THDPKcjPS2_ + 592
11  mysqld                              0x0012117f _Z16dispatch_command19enum_server_commandP3THDPcj + 3053
12  mysqld                              0x0012258a _Z10do_commandP3THD + 666
13  mysqld                              0x0010cd8f handle_one_connection + 385
14  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
15  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x107bce8 = select *
from ***************
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
091103 19:51:13 mysqld_safe mysqld restarted
[5 Nov 2009 17:06] Valeriy Kravchuk
This is not a recent regression - 5.1.37 is also affected.
[10 Nov 2009 15:46] Jon Olav Hauglid
Reassigning this to optimizer based on discussion with jorgen and gluh.
[14 Nov 2009 10:28] Shane Bester
Another testcase for 5.1.40, without using views!

drop table if exists t1,t2;
create table t1(a bigint);
create table t2(b tinyint);
insert into t2 values (null);
prepare stmt from "select 1 from t1 join  t2 on a xor b where b > 1  and a =1";
execute stmt;
execute stmt;

mysqld.exe!Item_cond::fix_fields()[item_cmpfunc.cc:3912]
mysqld.exe!Item_cond::fix_fields()[item_cmpfunc.cc:3924]
mysqld.exe!setup_conds()[sql_base.cc:7998]
mysqld.exe!setup_without_group()[sql_select.cc:415]
mysqld.exe!JOIN::prepare()[sql_select.cc:501]
mysqld.exe!mysql_select()[sql_select.cc:2373]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5044]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
mysqld.exe!Prepared_statement::execute()[sql_prepare.cc:3579]
mysqld.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3253]
mysqld.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2526]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2248]
mysqld.exe!mysql_parse()[sql_parse.cc:5967]
mysqld.exe!dispatch_command()[sql_parse.cc:1226]
mysqld.exe!do_command()[sql_parse.cc:865]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()
ariables.
 invalid and cause the dump to abort...
00042D05C8=select 1 from t1 join  t2 on a xor b where b > 1  and a =1
[14 Nov 2009 10:41] Shane Bester
4.1.25 is not affected, therefor a regression.
[30 Nov 2009 11:51] 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/92063

2850 Evgeny Potemkin	2009-11-30
      Bug#48508: Crash on prepared statement re-execution.
      
      Actually there is two different bugs.
      The first one caused crash on queries with WHERE condition over views
      containing WHERE condition. A wrong check for prepared statement phase led
      to items for view fields being allocated in the execution memory and freed
      at the end of execution. Thus the optimized WHERE condition refers to
      unallocated memory on the second execution and server crashed.
      The second one caused by the Item_cond::compile function not saving changes
      it made to the item tree. Thus on the next execution changes weren't
      reverted and server crashed on dereferencing of unallocated space.
      
      The Query_arena::is_stmt_prepare_or_first_sp_execute function now correctly
      do its check.
      The Item_cond::compile function now saves changes it makes to item tree.
     @ mysql-test/r/ps.result
        Added a test case for the bug#48508.
     @ mysql-test/t/ps.test
        Added a test case for the bug#48508.
     @ sql/item_cmpfunc.cc
        Bug#48508: Crash on prepared statement re-execution.
        The Item_cond::compile function now saves changes it makes to item tree.
     @ sql/sql_class.h
        Bug#48508: Crash on prepared statement re-execution.
        The Query_arena::is_stmt_prepare_or_first_sp_execute function now correctly
        do its check.
[1 Dec 2009 9:12] Øystein Grøvlen
Sent feedback on patch.
Setting Jørgen as 2nd reviewer.  He has worked a bit with prepared statements lately.
[1 Dec 2009 18:28] 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/92340

2850 Evgeny Potemkin	2009-12-01
      Bug#48508: Crash on prepared statement re-execution.
      
      Actually there is two different bugs.
      The first one caused crash on queries with WHERE condition over views
      containing WHERE condition. A wrong check for prepared statement phase led
      to items for view fields being allocated in the execution memory and freed
      at the end of execution. Thus the optimized WHERE condition refers to
      unallocated memory on the second execution and server crashed.
      The second one caused by the Item_cond::compile function not saving changes
      it made to the item tree. Thus on the next execution changes weren't
      reverted and server crashed on dereferencing of unallocated space.
      
      The new helper function called is_stmt_prepare_or_first_stmt_execute
      is added to the Query_arena class.
      The find_field_in_view function now uses
      is_stmt_prepare_or_first_stmt_execute() to check whether
      newly created view items should be freed at the end of the query execution.
      The Item_cond::compile function now saves changes it makes to item tree.
     @ mysql-test/r/ps.result
        Added a test case for the bug#48508.
     @ mysql-test/t/ps.test
        Added a test case for the bug#48508.
     @ sql/item_cmpfunc.cc
        Bug#48508: Crash on prepared statement re-execution.
        The Item_cond::compile function now saves changes it makes to item tree.
     @ sql/sql_base.cc
        Bug#48508: Crash on prepared statement re-execution.
        The find_field_in_view function now uses
        is_stmt_prepare_or_first_stmt_execute() to check whether
        newly created view items should be freed at the end of the query execution.
     @ sql/sql_class.h
        Bug#48508: Crash on prepared statement re-execution.
        The Query_arena::is_stmt_prepare_or_first_sp_execute function now correctly
        do its check.
[2 Dec 2009 13:19] Øystein Grøvlen
Approved, but I hope for a better name for the new function.
[2 Dec 2009 13:47] 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/92452

2858 Evgeny Potemkin	2009-12-02 [merge]
      Auto-merged fix for the bug#48508.
[3 Dec 2009 13: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/92677

2851 Evgeny Potemkin	2009-12-03
      Bug#48508: Crash on prepared statement re-execution.
      
      Test case cleanup.
     @ mysql-test/r/ps.result
        Test case cleanup for bug#48508.
     @ mysql-test/t/ps.test
        Test case cleanup for bug#48508.
[9 Dec 2009 15:38] Shane Bester
hi evgeny!

can you please let me know if this fix fixes the following crash. stack trace of 5.1.41:

drop table if exists `t1`;
create table `t1`(`a` int primary key)engine=myisam ;
insert into `t1` values (0),(1);
prepare `stmt` from 
"select 1  from `t1` join `t1` `t2` 
using(`a`) group by `t2`.`a`,`t1`.`a`";
execute `stmt`;
execute `stmt`;

Version: '5.1.41-enterprise-gpl-advanced-debug-log'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: !(order->used & map), file .\sql_select.cc, line 6976
mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2048]
mysqld-debug.exe!raise()[winsig.c:597]
mysqld-debug.exe!abort()[abort.c:78]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!eq_ref_table()[sql_select.cc:6976]
mysqld-debug.exe!only_eq_ref_tables()[sql_select.cc:7007]
mysqld-debug.exe!remove_const()[sql_select.cc:7131]
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:1228]
mysqld-debug.exe!mysql_select()[sql_select.cc:2421]
mysqld-debug.exe!handle_select()[sql_select.cc:269]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5051]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2246]
mysqld-debug.exe!Prepared_statement::execute()[sql_prepare.cc:3581]
mysqld-debug.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3256]
mysqld-debug.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2529]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2256]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5974]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:872]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
[9 Dec 2009 15:56] Evgeny Potemkin
Shane:

No, it's a different bug. Your example causes failed assertion, this bug is using a freed pointer.
[19 Dec 2009 8:27] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:30] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211164058-ycpe0f20d1c4h1gl) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[11 Jan 2010 17:37] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.

Re-execution of a prepared statement could cause a server crash.

Setting report to NDI pending push to 5.1.x, Celosia.
[14 Jan 2010 8:26] Bugs System
Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:aelkin@mysql.com-20091214144410-630vanwyllvvacad) (merge vers: 5.0.89) (pib:16)
[14 Jan 2010 18:23] Paul Dubois
Noted in 5.0.90 changelog.

Setting report to NDI pending push to 5.1.x, Celosia.
[15 Jan 2010 9:01] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:epotemkin@mysql.com-20091203132450-h8stvio43t90fe85) (merge vers: 5.1.42) (pib:16)
[15 Jan 2010 19:05] Paul Dubois
Noted in 5.1.43 changelog.

Setting report to NDI pending push to Celosia.
[17 Feb 2010 16:28] Paul Dubois
Noted in 5.0.87sp1 changelog.
[20 Feb 2010 17:10] Bugs System
Pushed into 5.0.91 (revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (version source revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)
[1 Mar 2010 8:43] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joro@sun.com-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)
[1 Mar 2010 17:22] Paul Dubois
Setting report to Need Merge pending push of Celosia to release tree.
[2 Mar 2010 14:32] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:38] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alik@sun.com-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301094536-2zc4uqyy3os8san7) (pib:16)
[2 Mar 2010 21:04] Paul Dubois
Setting report to Need Merge pending push of Celosia to release tree.
[12 Mar 2010 14:10] 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:26] 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:40] 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 16:54] Paul Dubois
Fixed in earlier 5.1.x, 5.5.x.
[14 Jun 2010 15:40] Shane Bester
see also bug #54489
[17 Jun 2010 12:04] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:48] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:31] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)