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

[29 Oct 2010 7:39] Rene' Cannao'
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
[4 Nov 2010 9:36] Rene' Cannao'
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").
[11 Nov 2010 0:21] Sveta Smirnova
Bug #56783 was marked as duplicate of this one.
[19 Nov 2010 9:40] Davi Arnaut
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.
[22 Nov 2010 1:50] Jimmy Yang
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
[22 Nov 2010 2:32] Jimmy Yang
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. "
[22 Nov 2010 2:54] Jimmy Yang
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
[22 Nov 2010 11:06] Rene' Cannao'
Should this at least be documented in http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ?
[22 Nov 2010 11:06] Davi Arnaut
Yes, could you report a documentation bug on it? Thanks.