MySQL Bugs: #57832: Use Fast ALTER TABLE for column rename

Bug #57832	Use Fast ALTER TABLE for column rename
Submitted:	29 Oct 2010 7:39	Modified:	22 Nov 2010 2:54
Reporter:	Rene' Cannao'	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.46sp1 , 5.1.49sp1 , 5.1.50 , 5.1.51	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
Renaming a column should be a "fast" ALTER TABLE that does not require a temporary table.

This seems to be correct in MySQL 5.1.40 (see bug http://bugs.mysql.com/bug.php?id=46760) but is broken again in later versions of MySQL 5.1

Verified on 5.1.46sp1 , 5.1.49sp1 , 5.1.50 and 5.1.51 , with InnoDB built-in and Plugin .

How to repeat:
Execute the follows on MySQL 5.1.45 (with InnoDB built-in and/or Plugin), and it works correctly:

mysql> DROP TABLE IF EXISTS tbl1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `tbl1` (`id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl1(id) VALUES (NULL);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 8 rows affected, 1 warning (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tbl1 CHANGE c1 c2 INT NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

==============

Execute the follows on MySQL 5.1.46sp1 , 5.1.49sp1 , 5.1.50 and 5.1.51 (with InnoDB built-in and/or Plugin), and it does *not* works correctly:

mysql> DROP TABLE IF EXISTS tbl1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `tbl1` (`id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl1(id) VALUES (NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl1(id) SELECT NULL FROM tbl1;
Query OK, 8 rows affected, 1 warning (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tbl1 CHANGE c1 c2 INT NOT NULL;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

Suggested fix:
Fix the regression bug

Omer,
this is actually a regression from 5.1.45 .
In 5.1.40 bug 46760 was fixed, and fast ALTER TABLE worked fine till 5.1.45 (see "Hot to repeat").

Bug #56783 was marked as duplicate of this one.

This "regression" was introduced by:

branches/5.1: Fix bug #47621 "MySQL and InnoDB data dictionaries
will become out of sync when renaming columns". MySQL does not
provide new column name information to storage engine to
update the system table. To avoid column name mismatch, we shall
just request a table copy for now.

rb://246 approved by Marko.

But I suspect this is due to a lack of a proper handler interface. If InnoDB wants to fix it, we can discuss.

Hello, this is NOT a regression. We have turned off the FIC for alter table rename intentionally. 

The original design of using FIC for renaming is problematic, it will possible result in column name mismatch between InnoDB and MySQL. To fix this, the interface from MySQL needs to provide the new column name to InnoDB. We had corrected this through bug #47621, which is also suggested from Yasufumi. To fix this, we need MySQL provide column name as part of alter table operation:

"A bit more study on the issue find that although mysql passes info that sets the FIELD_IS_RENAMED bit for the column to be renamed, it does not pass the new column name. And we will not know the new column name until we open the table again after alter table operation completes. 

To fix this correctly, mysql should pass the complete info during alter table rename to storage, so we can update the system table. For now, we will adopt the first suggestion in Sergey's suggestion and return COMPATIBLE_DATA_NO:

 - R2/E2: rebuild table when column rename is requested. Though it is slow, this is what other engines like NDB and IBMDB2I do. "

Davi, could you please go through this and provide us the necessary information?

I also change this bug title and make it a feature request.

Thanks

Directly extract from bug #47621 description.

The bug is filed for following problem:

"[12 May 13:10] Marko Mäkelä

Case #1 (ut_error assertion in dict_index_find_cols()) is that the MySQL and InnoDB data dictionaries will become out of sync when renaming columns. Columns will be only renamed in the MySQL data dictionary (.frm file), not in the InnoDB system tables. Fixing that might require an API change in MySQL 5.1. I believe that the ALTER TABLE API in MySQL 6.0 does support renaming columns."

After detail analysis, it is not likely we can go around this. And we took the same solution described from Sergey Vojtovich and Yasufumi Kinoshita.

"Fixing this on server side is likely R5/E4. Even solution proposed by Serg has a few unresolved questions, at least upgrade from previous versions.

OTOH InnoDB has more freedom to fix this:
- R2/E2: rebuild table when column rename is requested. Though it is slow, this is what other engines like NDB and IBMDB2I do.
..."

From our understanding, a complete solution would be MySQL provide us the "new column name" through the alter API so we can make appropriate dictionary change:

" A bit more study on the issue find that although mysql passes info that sets the FIELD_IS_RENAMED bit for the column to be renamed, it does not pass the new column name. And we will not know the new column name until we open the table again after alter table operation completes. "

Filed #58368 for the feature request to enable "fast alter table for rename column". Close this bug as "won't fix" as suggested by Konstantin

Should this at least be documented in http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ?

Yes, could you report a documentation bug on it? Thanks.