MySQL Bugs: #31409: RENAME TABLE causes server crash or deadlock when used with HANDLER statements

Bug #31409	RENAME TABLE causes server crash or deadlock when used with HANDLER statements
Submitted:	4 Oct 2007 20:32	Modified:	23 Oct 2007 0:16
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0 BK	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	crash, deadlock, handler, rename table

Description:
When one connection holds a handler table, and the table is renamed, a subsequent handler open statement may crash or deadlock the server.

How to repeat:
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
create table t1 (c1 int);
create table t2 (c1 int);
insert into t1 values (1);
insert into t2 values (2);
--echo connection: default
handler t1 open;
handler t1 read first;
connect (flush,localhost,root,,);
connection flush;
--echo connection: flush
--send rename table t1 to t2;
connection default;
--echo connection: default
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table" and info = "rename table t1 to t2";
--source include/wait_condition.inc
handler t2 open;
handler t2 read first;
handler t1 read next;
handler t1 close;
handler t2 close;
connection flush;
reap;
connection default;
drop table t1,t2;
disconnect flush;

Thank you for the bug report.

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/35036

ChangeSet@1.2528, 2007-10-06 01:21:55-03:00, davi@moksha.local +2 -0
Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements

This deadlock occurs when a client issues a HANDLER ... OPEN statement
that tries to open a table that has a pending name-lock on it by another
client that also needs a name-lock on some other table which is already
open and associated to a HANDLER instance owned by the first client.
The deadlock happens because the open_table() function will back-off
and wait until the name-lock goes away, causing a circular wait if some
other name-lock is also pending for one of the open HANDLER tables.

Such situation, for example, can be easily repeated by issuing a RENAME
TABLE command in such a way that the existing table is already open
as a HANDLER table by another client and this client tries to open
a HANDLER to the new table name.

The solution is to allow handler tables with older versions (makred for
flush) to be closed before waiting for the name-lock completion. This is
safe because no other name-lock can be issued between the flush and the
check for pending name-locks.

The test case for this bug is going to be committed into 5.1 because it
requires a test feature only avaiable in 5.1 (wait_condition).

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/35202

ChangeSet@1.2528, 2007-10-09 12:02:59-03:00, davi@moksha.local +2 -0
Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements

This deadlock occurs when a client issues a HANDLER ... OPEN statement
that tries to open a table that has a pending name-lock on it by another
client that also needs a name-lock on some other table which is already
open and associated to a HANDLER instance owned by the first client.
The deadlock happens because the open_table() function will back-off
and wait until the name-lock goes away, causing a circular wait if some
other name-lock is also pending for one of the open HANDLER tables.

Such situation, for example, can be easily repeated by issuing a RENAME
TABLE command in such a way that the existing table is already open
as a HANDLER table by another client and this client tries to open
a HANDLER to the new table name.

The solution is to allow handler tables with older versions (marked for
flush) to be closed before waiting for the name-lock completion. This is
safe because no other name-lock can be issued between the flush and the
check for pending name-locks.

The test case for this bug is going to be committed into 5.1 because it
requires a test feature only avaiable in 5.1 (wait_condition).

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/35477

ChangeSet@1.2540, 2007-10-12 10:55:46-03:00, davi@moksha.com.br +1 -0
  Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements
  
  If mysql_lock_tables fails because the lock was aborted, we need to
  reset thd->some_tables_delete, otherwise we might loop indefinitely
  because handler's tables are not closed in a standard way, meaning
  that close_thread_tables() (which resets some_tables_deleted) is not
  used.
  
  This patch fixes sporadical failures of handler_myisam/innodb tests
  which were introduced by previous fix for this bug.

Pushed into 5.1.23-beta

Pushed into 5.0.52

Noted in 5.0.52, 5.1.23 changelogs.

Executing RENAME while tables were open for use with HANDLER
statements could cause a server crash.