重命名已锁定的表

Question

在将表迁移到新模式时，我希望确保使用复制和重命名过程将原子切换到新表。因此，我试图将一个锁定的表重命名如下：

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;

不幸的是，这导致了ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction。

应如何以不同的方式做到这一点？

这是和mariadb:10.1的。

Answer 1

虽然里克使用Percona工具是正确的(请参阅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；它负责细节。