| 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 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)
Description: While testing Online DDL in MySQL 5.6 I noticed that changing column name involves table copy for certain type of column definitions. I identified it happens for binary data types like BINARY and BLOB, but also for CHAR when binary collation is set. Surprisingly the same column rename for VARCHAR with binary collation works as expected. How to repeat: mysql [localhost] {msandbox} (test) > select @@version; +-----------+ | @@version | +-----------+ | 5.6.12 | +-----------+ mysql [localhost] {msandbox} (test) > select @@old_alter_table; +-------------------+ | @@old_alter_table | +-------------------+ | 0 | +-------------------+ For a table table1 having these example columns: `test_col3` blob, `test_col4` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `test_col6` binary(1) DEFAULT NULL, `test_col7` char(20) CHARACTER SET utf8 COLLATE utf8_polish_ci DEFAULT NULL, `test_col8` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col3 test_col33 blob; Query OK, 1479949 rows affected (2 min 17.36 sec) Records: 1479949 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col6 test_col66 binary(1) DEFAULT NULL; Query OK, 1479949 rows affected (2 min 21.43 sec) Records: 1479949 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col4 test_col44 char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL; Query OK, 1479949 rows affected (1 min 37.26 sec) Records: 1479949 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col7 test_col77 char(20) CHARACTER SET utf8 COLLATE utf8_polish_ci DEFAULT NULL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col8 test_col88 varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 When I tried to force ALGORITHM=INPLACE explicitly: mysql [localhost] {msandbox} (test) > ALTER TABLE table1 CHANGE COLUMN test_col3 test_col33 blob, ALGORITHM=INPLACE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. Except I am not changing the data type, it's still blob as it was before... I can't see this limitation about binary date types described in documentation. Suggested fix: Make online DDL work as expected for binary type columns.