MySQL Bugs: #27690: Re-execution of prepared statement after table was replaced with a view crashes

Bug #27690	Re-execution of prepared statement after table was replaced with a view crashes
Submitted:	6 Apr 2007 21:06	Modified:	30 May 2008 23:33
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	5.1.18-BK	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	bfsm_2007_10_18, prepared statement

Description:
Re-execution of prepared statement after table was replaced with a view causes server to crash.

How to repeat:
Run these queries: 

create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);

create table v1 as select * from t1;
create table v2 as select * from t1;

prepare s1 from 'select * from v1, v2'; 
execute s1;

drop table v1;
create view v1 as select A.a, A.b from t1 A, t1 B;

execute s1;

The last query will cause the server to crash:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1122433952 (LWP 25382)]
0x082db1b1 in make_cond_for_table (cond=0x0, tables=13835058055282163713, used_table=1) at sql_select.cc:12003
12003     if (used_table && !(cond->used_tables() & used_table))
(gdb) wher
#0  0x082db1b1 in make_cond_for_table (cond=0x0, tables=13835058055282163713, used_table=1) at sql_select.cc:12003
#1  0x082eb01b in make_join_select (join=0x8fb1848, select=0x8fb3440, cond=0x0) at sql_select.cc:6043
#2  0x082f5b8a in JOIN::optimize (this=0x8fb1848) at sql_select.cc:944
#3  0x082f8652 in mysql_select (thd=0x8f6a3c8, rref_pointer_array=0x8fb85f8, tables=0x8fb8bf0, wild_num=0, fields=@0x8fb8588, 
    conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x8fb8f88, 
    unit=0x8fb82b8, select_lex=0x8fb84f4) at sql_select.cc:2177
#4  0x082fcdfa in handle_select (thd=0x8f6a3c8, lex=0x8fb8250, result=0x8fb8f88, setup_tables_done_option=0) at sql_select.cc:257
#5  0x082805ee in execute_sqlcom_select (thd=0x8f6a3c8, all_tables=0x8fb8bf0) at sql_parse.cc:4438
#6  0x0828208d in mysql_execute_command (thd=0x8f6a3c8) at sql_parse.cc:1842
#7  0x0830cf2c in Prepared_statement::execute (this=0x8fb7fc0, expanded_query=0x42e6e588, open_cursor=false) at sql_prepare.cc:3043
#8  0x0830d212 in mysql_sql_stmt_execute (thd=0x8f6a3c8) at sql_prepare.cc:2383
#9  0x082820b3 in mysql_execute_command (thd=0x8f6a3c8) at sql_parse.cc:1851
#10 0x0828ab52 in mysql_parse (thd=0x8f6a3c8, inBuf=0x8fb1798 "execute s1", length=10) at sql_parse.cc:5255
#11 0x0828b63b in dispatch_command (command=COM_QUERY, thd=0x8f6a3c8, packet=0x8fa9739 "execute s1", packet_length=11)
    at sql_parse.cc:896
#12 0x0828c79d in do_command (thd=0x8f6a3c8) at sql_parse.cc:662
#13 0x0827a5b0 in handle_one_connection (arg=0x8f6a3c8) at sql_connect.cc:1089
#14 0x4005b264 in start_thread () from /lib/libpthread.so.0
#15 0x4018c76e in clone () from /lib/libc.so.6

Suggested fix:
1. Write up our strategy on dealing with cases like this (execution of 
   prepared statement when its underlying objects have changed since 
   the statement preparation)

2. Act on that stragegy and fix this and similar issues (One 
    known similar issue is BUG#27430).

The first attempt on such strategy is filed as WL#3813.

Thank you for a bug report. Verified just as described with latest 5.1.18-BK on Linux.

A simple test as following also causes server crashed.

mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (1,1),(2,2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create view v1 as select A.a, A.b from t1 A, t1 B;
Query OK, 0 rows affected (0.09 sec)

mysql> drop table if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table v1 (a int);
ERROR 1050 (42S01): Table 'v1' already exists

mysql> create table v1 as select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I think it should give an error message instead of server crash.

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.

Pushed into 5.1.25-rc

Queued in 5.1-27430

Pushed into 5.1.25

Pushed into 6.0.6-alpha

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.

Pushed into 5.1.25-rc  (revid:sp1r-kostja@bodhi.(none)-20080520073817-17550) (version source revid:sp1r-kostja@bodhi.(none)-20080520073817-17550) (pib:3)