Bug #69580 | Renaming a column in Online DDL requires table copy for binary data types | ||
---|---|---|---|
Submitted: | 26 Jun 2013 9:41 | Modified: | 15 Oct 2013 18:02 |
Reporter: | Przemyslaw Malkowski | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
Version: | 5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | ONLINE DDL ALTER TABLE |
[26 Jun 2013 9:41]
Przemyslaw Malkowski
[26 Jun 2013 12:55]
Dmitry Lenev
Hello! Thanks for reporting this issue! While it might be not very obvious this bug has the same underlying cause as bug #67141 "In-place ALTER TABLE is impossible for BINARY columns". Because of this I am marking this bug as a duplicate of bug #67141. The latter bug was fixed in version 5.7.0.
[27 Jun 2013 16:03]
MySQL Verification Team
Smaller part of the bug is a duplicate of the already stated bug, but the rest is a new and proper bug that needs to be addressed .
[1 Jul 2013 7:42]
Przemyslaw Malkowski
I should also add that not only those column renames require table copy, but also are full blocking ones - any concurrent writes to a table are "Waiting for table metadata lock".
[6 Aug 2013 6:37]
Marko Mäkelä
Side note: ALGORITHM=COPY blocking concurrent writes is by design. It is how MySQL worked before version 5.6. (Some earlier versions allowed renaming columns without involving the storage engine, but this caused trouble for InnoDB foreign key constraints. With version 5.6, column renaming will be communicated to the storage engine.) ALGORITHM=COPY implies LOCK=SHARED (allowing concurrent reads). If you additionally specify LOCK=EXCLUSIVE, it will also block concurrent reads. The keyword LOCK=NONE requires ALGORITHM=INPLACE. If the operation is refused by ALGORITHM=INPLACE, then LOCK=NONE will not work. With LOCK=DEFAULT (the default), MySQL will choose the weakest possible locking for ALTER TABLE.
[15 Oct 2013 18:02]
Paul DuBois
Noted in 5.6.15 changelog. For an ALTER TABLE statement that renamed or changed the default value of a BINARY column, the alteration was done using a table copy and not in place. (See also Bug#67141)