Bug #47621 MySQL and InnoDB data dictionaries will become out of sync when renaming columns
Submitted: 24 Sep 2009 15:55 Modified: 11 Jan 2011 1:35
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Jimmy Yang
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[24 Sep 2009 15:55] Sveta Smirnova
Description:
This is split from bug #44571:

"[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."

How to repeat:
See bug #44571
[24 Sep 2009 18:17] Sergei Golubchik
a simple solution that doesn't require any changes in the API would be to not expose column names to storage engines. They could be getting generated names, for example f1, f2, ...f3000, ...

Incidentally it'll also solve any problems storage engine might have with column names colliding with internally reserved names (Bug#44369) or with different case sensitivity on column name comparison, as storage engine might use other rules internally (similar to Bug#27425)
[24 Sep 2009 18:19] Sergei Golubchik
also it'll solve InnoDB issue with automatically generated foreign key names getting too long (Bug #13942)
[24 Sep 2009 18:50] Sergei Golubchik
by the way, we're already doing it with savepoints
[24 Nov 2009 10:51] Sergey Vojtovich
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.
- R2/E3: remember fields that are to be renamed during ::check_if_incompatible_data(), rename on commit. Rather a fragile option, needs more analysis.
- R3/E3: implement solution suggested by Serg on InnoDB side. That is encode field sequence number to some nice name. Looks short-sighted as with 6.0 code base we likely won't need this. Upgrade problem stays.

Setting lowest R/E value. Suggested category: InnoDB.
[27 Nov 2009 11:04] Yasufumi Kinoshita
For now, how about the following patch?
Though 2nd rename causes table copy, passes mysql-test scripts.
And it isn't crushed in this case.

--- handler/ha_innodb.cc        2009-11-27 16:19:14.000000000 +0900
+++ handler/ha_innodb.cc        2009-11-27 19:49:21.000000000 +0900
@@ -9619,6 +9622,20 @@ ha_innobase::check_if_incompatible_data(
                return(COMPATIBLE_DATA_NO);
        }

+       /* Renaming column asynchronizes dictionary between mysqld and InnoDB...
+          If not synchronized, treat as COMPATIBLE_DATA_NO
+          until the bug http://bugs.mysql.com/47621 is fixed officially */
+       {
+               uint    i;
+
+               for (i = 0; i < table->s->fields; i++) {
+                       if (innobase_strcasecmp(table->field[i]->field_name,
+                               dict_table_get_col_name(prebuilt->table, i))) {
+                               return(COMPATIBLE_DATA_NO);
+                       }
+               }
+       }
+
        /* Check if a column participating in a foreign key is being renamed.
        There is no mechanism for updating InnoDB foreign key definitions. */
        if (foreign_key_column_is_being_renamed(prebuilt, table)) {
[30 Nov 2009 1:15] Yasufumi Kinoshita
The following may be more reasonable.

--- handler/ha_innodb.cc        2009-11-27 16:19:14.000000000 +0900
+++ handler/ha_innodb.cc        2009-11-27 19:49:21.000000000 +0900
@@ -9619,6 +9622,20 @@ ha_innobase::check_if_incompatible_data(
@@ -9619,6 +9622,20 @@
                return(COMPATIBLE_DATA_NO);
        }

+       /* Renaming column asynchronizes dictionary between mysqld and InnoDB...
+          If not synchronized, treat as COMPATIBLE_DATA_NO
+          until the bug http://bugs.mysql.com/47621 is fixed officialily */
+       {
+               uint    i;
+               for (i = 0; i < table->s->fields; i++) {
+                       if (table->field[i]->flags & FIELD_IN_ADD_INDEX
+                           && innobase_strcasecmp(table->field[i]->field_name,
+                                       dict_table_get_col_name(prebuilt->table, i))) {
+                               return(COMPATIBLE_DATA_NO);
+                       }
+               }
+       }
+
        /* Check if a column participating in a foreign key is being renamed.
        There is no mechanism for updating InnoDB foreign key definitions. */
        if (foreign_key_column_is_being_renamed(prebuilt, table)) {
[22 Jan 2010 10:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97834

3336 Sergey Vojtovich	2010-01-22
      Disabled innodb-autoinc-44030 due to BUG#47621.
     @ mysql-test/t/disabled.def
        Disabled innodb-autoinc-44030 due to BUG#47621.
[2 Feb 2010 5:37] Jimmy Yang
Simple fix for now is to return "COMPATIBLE_DATA_NO" for column rename operation as mentioned in the bug report.

The failure of innodb-autoinc-44030 is due to some other issue:

==============
(10:01:00 AM) sunny: The real reason for the test failure (recently) was actually Marko's change committed as r6424. He changed the DB_SUCCESS to a failure code.
==============
[2 Feb 2010 6:41] Jimmy Yang
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. 

Jimmy
[4 Feb 2010 10:19] Bugs System
Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:dao-gang.qu@sun.com-20100125025505-zqa9v2mgdcfza0v6) (merge vers: 5.1.43) (pib:16)
[5 Feb 2010 11:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100130201057-zm9nj1sy0xpz1ohp) (pib:16)
[5 Feb 2010 11:53] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 11:59] Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alik@sun.com-20100130182921-uva9w0cxpxqeylbf) (merge vers: 5.5.2-m2) (pib:16)
[9 Mar 2010 15:51] Jimmy Yang
r6783 | jyang | 2010-03-09 07:54:14 -0800 (Tue, 09 Mar 2010) | 9 lines

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.
[12 Mar 2010 14:11] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:27] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:42] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[24 Mar 2010 14:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104218

3416 Sergey Vojtovich	2010-03-24
      Applying InnoDB snapshot, fixes BUG#47621.
      
      Detailed revision comments:
      
      r6783 | jyang | 2010-03-09 17:54:14 +0200 (Tue, 09 Mar 2010) | 9 lines
      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.
[24 Mar 2010 19:19] Sergey Vojtovich
Fix for built-in InnoDB queued into 5.1-bugteam (ss6860).
[26 Mar 2010 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:56] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)
[12 Apr 2010 23:01] Paul Dubois
Noted in 5.1.46, 6.0.14 changelogs. (Not 5.5.x, which has InnoDB Plugin.)

Renaming a column of an InnoDB table caused the server to go out of
sync with the InnoDB data dictionary.
[5 May 2010 15:10] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 17:17] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:46] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:43] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:52] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:13] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:28] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:45] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:11] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[22 Nov 2010 4:38] Jimmy Yang
Please make sure following behavior is documented:

alter table rename column would still use table copy option (instead of Fast Alter Table) to avoid out of sync between InnoDB and MySQL dictionary.
[11 Jan 2011 1:35] John Russell
Added a statement that Fast Alter Table doesn't work for renaming columns in InnoDB tables:
- As a limitation in the Fast Index Creation chapter of the InnoDB manual.
- Under ALTER TABLE syntax in the Ref Man.