Bug #27420 A combination of PS and view operations cause error + assertion on shutdown
Submitted: 24 Mar 2007 22:18 Modified: 30 May 2008 23:33
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.1.18-debug OS:Linux (Linux)
Assigned to: Konstantin Osipov
Tags: prepared statement
Triage: D2 (Serious)

[24 Mar 2007 22:18] Sergey Petrunya
Description:
A specific combination of Prepared Statements and view operations can cause the server to produce an error that should not be there and fail an assertion on shutdown.
 

How to repeat:
# 1. Start the server
# 2. Connect with two command line clients, Client1 and Client2
# 3. From Client1, do:
  
create table t100 (a int);
insert into t100 values (1),(2);

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

create view v1 as select t100.a X, t101.a Y from t100, t101 where
t100.a=t101.a; 

prepare s1 from 'select * from v1';

execute s1;

# 4. From Client2, do:

drop view v1;

create table v1 (X int, Y int);

# 5. From Client1, do:

execute s1;

# 6. From Client2, do:

drop table v1;
create table v1 (a int, b int, filler char(200));

The last statment will fail with this error: 

ERROR 1050 (42S01): Table 'v1' already exists

...which is the first sign of a problem. 
( If you try to do "drop table v1" or "drop view v1" you will get a message that v1 doesn't exist. It also will not be listed in SHOW TABLES output. So Client2's view of whether table v1 exists is not consistent )

# 7. Run "mysqladmin shutdown".
You will see the server to crash like this: 

mysqld: table.cc:220: void free_table_share(TABLE_SHARE*): Assertion `share->ref_count == 0' failed.
[Thread -1256682592 (zombie) exited]

Program received signal SIGABRT, Aborted.
[Switching to Thread -1257284704 (LWP 22127)]
0xffffe410 in __kernel_vsyscall ()
(gdb) wher
#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7e04690 in raise () from /lib/libc.so.6
#2  0xb7e05cf0 in abort () from /lib/libc.so.6
#3  0xb7dfddab in __assert_fail () from /lib/libc.so.6
#4  0x082d07f7 in free_table_share (share=0x8fa69c0) at table.cc:220
#5  0x082b9a40 in table_def_free_entry (share=0x8fa69c0) at sql_base.cc:266
#6  0x0863f65c in hash_free_elements (hash=0x892cfe0) at hash.c:92
#7  0x0863f5e2 in hash_free (hash=0x892cfe0) at hash.c:113
#8  0x082c5ef8 in table_def_free () at sql_base.cc:291
#9  0x0826eec1 in clean_up (print_message=true) at mysqld.cc:1209
#10 0x082719c2 in unireg_end () at mysqld.cc:1156
#11 0x082741b9 in kill_server (sig_ptr=0x0) at mysqld.cc:1095
#12 0x082741d9 in kill_server_thread (arg=0xb51b9460) at mysqld.cc:1116
#13 0xb7f67264 in start_thread () from /lib/libpthread.so.0
#14 0xb7e9676e in clone () from /lib/libc.so.6

Suggested fix:
n/a
[24 Mar 2007 22:22] Sergey Petrunya
Doesn't occur in 5.0 for me
[24 Mar 2007 23:50] Miguel Solorzano
Thank you for the bug report. Verified as described.

[miguel@light 5.1]$ libexec/mysqld
070324 20:44:11  InnoDB: Started; log sequence number 0 50748
070324 20:44:11 [Note] libexec/mysqld: ready for connections.
Version: '5.1.18-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
070324 20:44:11 [Note] SCHEDULER: Loaded 0 events
070324 20:48:35 [Note] Got signal 15 to shutdown mysqld
070324 20:48:35 [Note] libexec/mysqld: Normal shutdown

070324 20:48:35 [Note] SCHEDULER: Purging queue. 0 events
mysqld: table.cc:220: void free_table_share(TABLE_SHARE*): Assertion `share->ref_count == 0' failed.
070324 20:48:35 - mysqld got signal 6;
[8 Apr 2008 16:04] Konstantin Osipov
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.
[20 May 2008 21:22] Bugs System
Pushed into 5.1.25-rc
[20 May 2008 21:25] Konstantin Osipov
Queued in 5.1-27430
[20 May 2008 21:26] Konstantin Osipov
Fixed in 5.1.25
[22 May 2008 9:49] Bugs System
Pushed into 6.0.6-alpha
[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:53] Bugs System
Pushed into 5.1.25-rc  (revid:sp1r-kostja@bodhi.(none)-20080520073817-17550) (version source revid:sp1r-kostja@bodhi.(none)-20080520073817-17550) (pib:3)