| 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: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 5.1.18-debug, 5.0.42 | OS: | Any |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_31, bfsm_2007_07_19, bfsm_2007_10_18, crash, DoS, prepared statements, PS, subqueries, subquery | ||
[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.
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.