Bug #27430 Crash in subquery code when in PS and table DDL changed after PREPARE
Submitted: 26 Mar 2007 0:45 Modified: 12 Nov 2009 20:36
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.1.18-debug, 5.0.42 OS:Any
Assigned to: Konstantin Osipov
Tags: bfsm_2007_05_31, bfsm_2007_07_19, bfsm_2007_10_18, crash, DoS, prepared statements, PS, subqueries, subquery
Triage: D1 (Critical)

[26 Mar 2007 0:45] Sergey Petrunya
Description:
Subquery rewrites code may crash (or malfunction) in the following scenario:

- PREPARE a prepared statement with an IN-subquery S1.
- Modify the tables so that S1 remains valid but certain properties of expressions involved in the subquery are no longer true.
- EXECUTE the prepared statement.

How to repeat:
Run this:

drop table t1, t2;
create table t1 (a int not null, oref int not null, key(a));
insert into t1 values 
(1, 1),
(1, 1234),
(2, 3),
(2, 1234),
(3, 1234);
create table t2 (a int not null, oref int not null);
insert into t2 values (1, 1), (2,2), (1234, 3), (1234, 4);

prepare s1 from 
 ' select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2';

execute s1; 

drop table t1, t2;
create table t1 (a int, oref int, key(a));
insert into t1 values 
(1, 1),
(1, NULL),
(2, 3),
(2, NULL),
(3, NULL);
create table t2 (a int, oref int);
insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);

execute s1;

The last statement will crash the server:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1256596576 (LWP 31596)]
0x081fb634 in Item_in_subselect::set_cond_guard_var (this=0x8fb2a38, i=0, 
    v=false) at item_subselect.h:280
280       void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; }
(gdb) wher
#0  0x081fb634 in Item_in_subselect::set_cond_guard_var (this=0x8fb2a38, i=0, 
    v=false) at item_subselect.h:280
#1  0x081f1a9d in Item_in_optimizer::val_int (this=0x8fb2f38)
    at item_cmpfunc.cc:993
#2  0x081b5664 in Item::send (this=0x8fb2f38, protocol=0x8f64350, 
    buffer=0xb519b4cc) at item.cc:4902
#3  0x08259c32 in select_send::send_data (this=0x8fb2d30, items=@0x8fa7910)
    at sql_class.cc:1097
#4  0x082d4e87 in end_send (join=0x8fab4d8, join_tab=0x8facb70, 
    end_of_records=false) at sql_select.cc:11501
#5  0x082db23c in evaluate_join_record (join=0x8fab4d8, join_tab=0x8fac9f8, 
    error=0, report_error=0x8f64328 "") at sql_select.cc:10784
#6  0x082db469 in sub_select (join=0x8fab4d8, join_tab=0x8fac9f8, 
    end_of_records=false) at sql_select.cc:10673
#7  0x082e352f in do_select (join=0x8fab4d8, fields=0x8fa7910, table=0x0, 
    procedure=0x0) at sql_select.cc:10430
#8  0x082f8a2c in JOIN::exec (this=0x8fab4d8) at sql_select.cc:2021
#9  0x082f45fe in mysql_select (thd=0x8f64050, rref_pointer_array=0x8fa7980, 
    tables=0x8fb2b78, wild_num=0, fields=@0x8fa7910, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2416200192, result=0x8fb2d30, unit=0x8fa7640, 
    select_lex=0x8fa787c) at sql_select.cc:2186
#10 0x082f8ce4 in handle_select (thd=0x8f64050, lex=0x8fa75d8, 
---Type <return> to continue, or q <return> to quit---
    result=0x8fb2d30, setup_tables_done_option=0) at sql_select.cc:257
#11 0x0827e362 in execute_sqlcom_select (thd=0x8f64050, all_tables=0x8fb2b78)
    at sql_parse.cc:4418
#12 0x0827fe01 in mysql_execute_command (thd=0x8f64050) at sql_parse.cc:1842
#13 0x08308db2 in Prepared_statement::execute (this=0x8fa7348, 
    expanded_query=0xb519c5a8, open_cursor=false) at sql_prepare.cc:3043
#14 0x08309098 in mysql_sql_stmt_execute (thd=0x8f64050) at sql_prepare.cc:2383
#15 0x0827fe27 in mysql_execute_command (thd=0x8f64050) at sql_parse.cc:1851
#16 0x08288818 in mysql_parse (thd=0x8f64050, inBuf=0x8fab380 "execute s1", 
    length=10) at sql_parse.cc:5231
#17 0x082892fb in dispatch_command (command=COM_QUERY, thd=0x8f64050, 
    packet=0x8fa3321 "", packet_length=11) at sql_parse.cc:896
#18 0x0828a45d in do_command (thd=0x8f64050) at sql_parse.cc:662
#19 0x08278506 in handle_one_connection (arg=0x8f64050) at sql_connect.cc:1089
#20 0xb7f98264 in start_thread () from /lib/libpthread.so.0
#21 0xb7ec776e in clone () from /lib/libc.so.6
(gdb) print i
$1 = 0
(gdb) print pushed_cond_guards
$2 = (bool *) 0x0

Suggested fix:
(I've found this problem when working on [WL#3740| http://forge.mysql.com/worklog/task.php?id=3740], we'll need to keep that WL in mind when working on this bug)

Bug analysis
============
IN-subqueries need special treatment depending on whether IN-compared expressions may be NULL. For prepared statements, we check for need for special treatment in the PREPARE phase.

One may change the properties of underlying table columns so the check results will be invalid when the PS is executed. This may manifest itself in crash or incorrect query result.

Possible solutions
==================
1. [For this bug only] Let the IN-subquery+NULLs code assume that as if all IN-compared expressions may be NULL. This will cause "garbage" of NULL-handling hooks in the generated QEPs.

2. [Overall] Let the "permanent" query rewrites record the assumptions they've made about the queried tables and check them on execution. If we detect that some assumption is not true anymore, fail the PS execution with a "PS invalidated by changes in the underlying table" error.

3. [Not sure if its ok/feasible] Disallow re-execution of a PS if the DDLs of the underlying tables have changed.
[4 Jun 2007 15:40] Konstantin Osipov
Another manifestation of 27690.
Needs metadata versioning.
Taking over.
[8 Apr 2008 16:01] 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/45076

ChangeSet@1.2588, 2008-04-08 20:01:20+04:00, kostja@dipika.(none) +21 -0
  Tentative implementation of
  WL#4165 Prepared statements: validation 
  WL#4166 Prepared statements: automatic re-prepare
  Fixes
  Bug#27430 Crash in subquery code when in PS and table DDL changed after PREPARE
  Bug#27690 Re-execution of prepared statement after table was replaced with a view crashes
  Bug#27420 A combination of PS and view operations cause error + assertion on shutdown
  
  The basic idea of the patch is to keep track of table metadata between
  prepared statement prepare and execute. If some table used in the statement
  has changed, the prepared statement is re-prepared before execution.
  
  See WL#4165 and WL#4166 contents and comments in the code for details
  of the implementation.
[17 May 2008 21:53] 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/46807

ChangeSet@1.2569, 2008-05-18 01:51:18+04:00, kostja@bodhi.(none) +8 -0
  Implement some code review fixes for the fix for Bug#27430 
  "Crash in subquery code when in PS and table DDL changed after PREPARE"
[18 May 2008 6: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/46810

ChangeSet@1.2570, 2008-05-18 10:28:36+04:00, kostja@bodhi.(none) +1 -0
  Fix mysql_client_test failure in pushbuild 5.1-27430
  (Bug#27430)
[19 May 2008 16:39] 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/46822

ChangeSet@1.2571, 2008-05-19 13:39:31-03:00, davi@skynet.(none) +2 -0
  Bug#27430 Crash in subquery code when in PS and table DDL changed after PREPARE
  
  Add test target to the makefile that will cause all
  statements to be re-prepared before execution.
[20 May 2008 7:29] 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/46852

ChangeSet@1.2571, 2008-05-20 11:29:16+04:00, kostja@bodhi.(none) +7 -0
  Bug#27430 "Crash in subquery code when in PS and table DDL changed after 
  PREPARE": rename members, methods, classes to follow the spec 
  (a code review request)
[20 May 2008 16:36] 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/46873

ChangeSet@1.2648, 2008-05-20 20:36:26+04:00, kostja@bodhi.(none) +8 -0
  Bug#27430 "Crash in subquery code when in PS and table DDL changed after
   PREPARE", review fixes:
  - make the patch follow the specification of WL#4166 and remove  
  the new error that was originally introduced.
  Now the client never gets an error from reprepare, unless it failed.
  I.e. even if the statement at hand returns a completely different
  result set, this is not considered a server error.
  The C API library, that can not handle this situation, was modified to
  return a client error.
  Added additional test coverage.
[20 May 2008 17:06] 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/46879

ChangeSet@1.2649, 2008-05-20 21:07:11+04:00, kostja@bodhi.(none) +1 -0
  Bug#27430 "Crash in subquery code when in PS and table DDL changed 
  after PREPARE"
  Update test results after a merge with the main tree: the new minimum for 
  the table definition cache is 256.
[20 May 2008 18:42] 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/46890

ChangeSet@1.2651, 2008-05-20 22:43:26+04:00, kostja@bodhi.(none) +4 -0
  Update test coverage for Bug#27430 "Crash in subquery code when in PS 
  and table DDL changed after PREPARE" to pass in embedded mode.
[20 May 2008 21:22] Bugs System
Pushed into 5.1.25-rc
[20 May 2008 21:26] Konstantin Osipov
Queued in 5.1-27430
[20 May 2008 21:26] Konstantin Osipov
Pushed into 5.1.25
[22 May 2008 9:49] Bugs System
Pushed into 6.0.6-alpha
[22 May 2008 22:34] 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/46976
[22 May 2008 22:41] 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/46978
[24 May 2008 18:08] 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/47029
[26 May 2008 12:07] 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/47051
[26 May 2008 12: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/commits/47052
[30 May 2008 23:33] Paul Dubois
Noted in 5.1.25, 6.0.6 changelogs.

Changelog comment for Bugs #27420, #27430, #27690:

Incompatible change.

A change has been made to the way that the server handles prepared
statements.  This affects prepared statements processed at the SQL
level (using the PREPARE statement) and those processed using the
binary client-server protocol (using the mysql_stmt_prepare() C API
function).

Previously, changes to metadata of tables or views referred to in 
a prepared statement could cause a server crash when the statement
was next executed, or perhaps an error at execute time with a crash
occurring later. For example, this could happen after dropping a
table and recreating it with a different definition.

Now metadata changes to tables or views referred to by prepared
statements are detected and cause automatic repreparation of the
statement when it is next executed.  Metadata changes occur for
DDL statements such as those that create, drop, alter, rename,
or truncate tables, or that analyze, optimize, or repair tables.
Repreparation also occurs after referenced tables or views are
flushed from the table definition cache, either implicitly to
make room for new entries in the cache, or explicitly due to
FLUSH TABLES.

Repreparation is automatic, but to the extent that it occurs,
performance of prepared statements is diminished.

Table content changes (for example, with INSERT or UPDATE) do not
cause repreparation, nor do SELECT statements.
An incompatibility with previous versions of MySQL is that a prepared
statement may now return a different set of columns or different
column types from one execution to the next.  For example, if the
prepared statement is "SELECT * FROM t1", altering t1 to contain a
different number of columns causes the next execution to return a
number of columns different from the previous execution.

Older versions of the client library cannot handle this change in
behavior.  For applications that use prepared statements with the
new server, an upgrade to the new client library is strongly
recommended.

Along with this change to statement repreparation, the default value
of the table_definition_cache system variable has been increased
from 128 to 256.  The purpose of this increase is to lessen the 
chance that prepared statements will need repreparation due to
referred-to tables/views having been flushed from the cache to make
room for new entries.

A new status variable, Com_stmt_reprepare, has been introduced to
track the number of repreparations.
[28 Jul 2008 16:52] Bugs System
Pushed into 5.1.25-rc  (revid:sp1r-kostja@bodhi.(none)-20080520184326-10676) (version source revid:sp1r-kostja@bodhi.(none)-20080520184326-10676) (pib:3)
[13 Oct 2009 19:05] 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/86732

2914 Konstantin Osipov	2009-10-13
      Backport of:
      -----------------------------------------------------------
      revno: 2630.2.4
      committer: Konstantin Osipov <konstantin@mysql.com>
      branch nick: mysql-6.0-runtime
      timestamp: Fri 2008-05-23 02:42:32 +0400
      message:
        Bug#27430 "Crash in subquery code when in PS and table DDL changed after
        PREPARE"
        Add a test case for the situation with small TDC and many merge children.
      
      from 6.0-codebase.
     @ mysql-test/r/merge.result
        Update results (Bug#27430)
     @ mysql-test/t/merge.test
        Add test case (Bug#27430)
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 15:33] Paul Dubois
Already fixed in earlier 6.0.x release.
[12 Nov 2009 8:17] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 20:36] Paul Dubois
Noted in 5.5.0 changelog.