Bug #34594 'handler open' can refer to outdated table names until flush tables is executed
Submitted: 15 Feb 2008 13:10 Modified: 8 Feb 2018 22:37
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.56, 5.0.66a OS:Any
Assigned to: CPU Architecture:Any
Tags: handler, table cache

[15 Feb 2008 13:10] Shane Bester
Description:
if you open a table using a handler, then rename the table and close the handler, then reopen the table again using the old table name, it still works.  this is confusing because the table no longer exists.

5.0.56 shows the buggy behaviour while 5.1.23 reports the expected error.

Server version: 5.0.56-enterprise-gpl-debug MySQL Enterprise Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `t1_`;
Query OK, 0 rows affected (0.02 sec)

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

mysql> create table `t1` (`a` int,key `k`(`a`))engine=myisam;
Query OK, 0 rows affected (0.00 sec)

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

mysql> handler `t1` open;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table `t1` to `t1_`;
Query OK, 0 rows affected (0.00 sec)

mysql> handler `t1` close;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1_            |
+----------------+
1 row in set (0.02 sec)

mysql> handler `t1` open; #there is no table t1, but it still works
Query OK, 0 rows affected (0.00 sec)

mysql> handler `t1` read `k` first;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> handler `t1` close;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
flush tables;
drop table if exists `t1_`;
drop table if exists `t1`;
create table `t1` (`a` int,key `k`(`a`))engine=myisam;
insert into `t1` values (1),(2);
handler `t1` open;
rename table `t1` to `t1_`;
handler `t1` close;
show tables;
handler `t1` open; #there is no table t1, but it still works
handler `t1` read `k` first;
handler `t1` close;

Suggested fix:
as a workaround you have to execute 'flush tables' after renaming the table.
[15 Feb 2008 13:43] Davi Arnaut
Probably a duplicate of Bug#31397
[15 Feb 2008 15:46] Susanne Ebrecht
mysql> select version()\G
*************************** 1. row ***************************
version(): 5.0.58-debug

Verified as described.
[8 Feb 2018 22:37] Roy Lyseng
Posted by developer:
 
Fixed in 5.6 and up.