Description:
if there is a uncommitted transaction in another session , executing "rename table" was not blocked in MySQL5.1
MySQL 5.5 was not affected
How to repeat:
root@(none) 01:30:41>select version();
+------------+
| version() |
+------------+
| 5.1.63-log |
+------------+
1 row in set (0.00 sec)
root@(none) 01:31:01>use test
Database changed
root@test 01:31:05>create table t2 (a int auto_increment primary key , b int);
Query OK, 0 rows affected (0.00 sec)
session 1:
root@test 01:30:33>set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
root@test 01:31:12>insert into t2 values (NULL,10);
Query OK, 1 row affected (0.00 sec)
session 2:
root@test 01:31:06>rename table t2 to tmp_t2;
Query OK, 0 rows affected (0.00 sec)
session 1:
root@test 01:31:41>commit;
Query OK, 0 rows affected (0.00 sec)
root@test 01:32:55>show binlog events;
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 113 | 106 | Server ver: 5.1.63-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 113 | 227 | use `test`; create table t2 (a int auto_increment primary key , b int) |
| mysql-bin.000001 | 227 | Query | 113 | 315 | use `test`; rename table t2 to tmp_t2 |
| mysql-bin.000001 | 315 | Query | 113 | 383 | BEGIN |
| mysql-bin.000001 | 383 | Intvar | 113 | 411 | INSERT_ID=1 |
| mysql-bin.000001 | 411 | Query | 113 | 505 | use `test`; insert into t2 values (NULL,10) |
| mysql-bin.000001 | 505 | Xid | 113 | 532 | COMMIT /* xid=36 */ |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------+
while t2 was renamed ,the followed statement was failed in slave
Suggested fix:
the implementation of mysql_rename_tables() is a little different between 5.1 and 5.5.
In MySQL 5.5，the rename statement is blocked by lock_table_names() called in mysql_rename_tables
