Bug #87467 Lift limitation on ALTER TABLE RENAME + tables with foreign keys + LOCK TABLES

• Submitted: 18 Aug 2017 8:16
• Modified: 13 Oct 2017 17:19
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.3-wl6049
• OS: Any
• CPU Architecture: Any

[18 Aug 2017 8:16] Dmitry Lenev

Description:
WL#6049 "Metadata locking for foreign keys" adds the following
limitation on ALTER TABLE RENAME which is executed under LOCK TABLES.
It is no longer allowed on tables which participate in foreign keys
as child or parent.

Such limitation is rather sever since ALTER TABLE RENAME is often
used under LOCK TABLES by various online-ALTER tools.

This is also regression from 5.7.

This bug report is placeholder for work which is needed to lift
(at least partially) this limitation.

How to repeat:
create table t1 (pk int primary key);
create table t2 (fk int, foreign key (fk) references t1 (pk));
lock tables t1 write, t2 write;
# The below ALTER TABLE RENAME fails after wl6049.
alter table t1 rename to t3;

Suggested fix:
Change locking policy for ALTER TABLE RENAME under LOCK TABLES,
to allow renaming of tables with FKs without breaking any LOCK
TABLES invariants.

[13 Oct 2017 17:19] Paul DuBois

Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

MySQL 8.0.3 imposed a restriction against ALTER TABLE RENAME on
tables in a foreign key relationship if a LOCK TABLES was active.
This restriction has been lifted.