Rename a locked table

Question

When transferring a table to a new scheme, I want to make sure that the atomic switch in the new table uses the copy and rename procedure. Therefore, I am trying to rename a locked table as follows:

CREATE TABLE foo_new (...)

-- copy data to new table, might take very long
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id <= 3;

LOCK TABLES foo WRITE, foo_new WRITE;

-- quickly copy the tiny rest over
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id > 3;

-- now switch to the new table
RENAME TABLE foo TO foo_old, foo_new TO foo;

UNLOCK TABLES;

Unfortunately, this leads to ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction.

How to do it differently?

This is with mariadb:10.1.

Answer 1

, Percona Tools (. 1 2), ALTER TABLE. , RENAME , , .

, :

CREATE TABLE foo_new (...)

-- copy data to new table, might take very long
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id <= 3;

LOCK TABLES foo WRITE, foo_new WRITE;

-- quickly copy the tiny rest over
INSERT INTO foo_new (id,created_at,modified_at)
  SELECT * FROM foo WHERE id > 3;

-- now switch to the new table
ALTER TABLE foo RENAME TO foo_old;
ALTER TABLE foo_new RENAME TO foo;

UNLOCK TABLES;

Answer 2

:

CREATE TABLE foo_old (...)
LOCK TABLES foo WRITE; 
INSERT INTO foo_old (id,created_at,modified_at)
  SELECT * FROM foo;
DELETE FROM foo WHERE id <= 3;
UNLOCK TABLES;

, RENAME TABLE, .

Answer 3

... Percona pt-online-schema-change; .

Answer 4

" ."

I met the same thing and found a reason in MySQL Docs:

MySQL8.0

As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES statement, provided that they are locked with a WRITE lock or are the product of renaming WRITE-locked tables from earlier steps in a multiple-table rename operation.

MySQL5.7

To execute RENAME TABLE, there must be no active transactions or tables locked with LOCK TABLES. 

By the way, in MySQL 5.7, when the table is locked with the LOCK tables tbl WRITE statement, the lock will be released due to the ALTER TABLE tbl_0 RENAME TO tbl_1 command and strange behavior will occur in the same session and new session.

# MySQL 5.7

# session 0
mysql> lock tables tbl_0 WRITE;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE tbl_0 RENAME TO tbl_1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tbl_1;
ERROR 1100 (HY000): Table 'tbl_1' was not locked with LOCK TABLES

# then start new session 
# session 1
mysql> select * from tbl_1;
...
1 row in set (0.01 sec)

# session 0
mysql> unlock tables;

Hope this helps.