잠긴 테이블 이름 변경

잠긴 테이블 이름 변경

테이블을 새 스키마로 마이그레이션할 때 복사 및 이름 변경 절차를 사용하여 새 테이블로 원자성 스위치를 사용해야 합니다.따라서 잠긴 테이블의 이름을 다음과 같이 변경하려고 합니다.

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.

일반적으로 Rick이 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;

다음과 같이 할 수 있습니다.

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같은 테이블을 잠그고 있을 때.

바퀴를 다시 발명하지 마...Percona의 사용pt-online-schema-change; 디테일이 처리됩니다.

"LOCK과 UNLOCK 문 사이에 데이터를 쓰거나 읽으면 안 됩니다."

저도 같은 문제를 만나서 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. 

그런데 MySQL 5.7에서는 "LOCK tables tbl WRITE" 문으로 테이블이 잠기면 "ALTER TABLE tbl_0 RENAME TO tbl_1"을 실행했기 때문에 잠금이 해제되어 같은 세션과 새로운 세션에서 이상한 동작이 발생합니다.

# 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;

도움이 됐으면 좋겠다.

언급URL : https://stackoverflow.com/questions/36202298/renaming-a-locked-table