Bug #45567 Fast ALTER TABLE broken for enum and set
Submitted: 17 Jun 2009 19:07 Modified: 20 Dec 2009 0:49
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.1.35 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: regression

[17 Jun 2009 19:07] Kolbe Kegel 
Description:
Fast ALTER TABLE does not seem to allow new enum/set values to be appended to the end of an existing enum/set column as described in the MySQL reference manual at http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

This regression was introduced some time between 5.1.25 and 5.1.30. Affects both MyISAM and InnoDB.

How to repeat:
DROP TABLE enum_test;
CREATE TABLE enum_test (enum_col enum('1','2'));
INSERT INTO test.enum_test VALUES ('1'),('2');
INSERT INTO test.enum_test SELECT enum_col FROM test.enum_test;
INSERT INTO test.enum_test SELECT enum_col FROM test.enum_test;
INSERT INTO test.enum_test SELECT enum_col FROM test.enum_test;
INSERT INTO test.enum_test SELECT enum_col FROM test.enum_test;
ALTER TABLE enum_test MODIFY COLUMN enum_col enum('1','2','3');

Suggested fix:
Fast ALTER TABLE should permit the addition of new values to enum columns as described in the MySQL reference manual.
[22 Sep 2009 15:07] 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/84156

3118 Davi Arnaut	2009-09-22
      Bug#45567: Fast ALTER TABLE broken for enum and set
      
      The problem was that appending values to the end of an existing
      ENUM or SET column was being treated as table data modification,
      preventing a immediately (fast) table alteration that occurs when
      only table metadata is being modified.
      
      The cause was twofold: adding a enumeration or set members to the 
      end of the list of valid member values was not being considered
      a "compatible" table alteration, and for SET columns, the check
      was being done upon the max display length and not the underlying
      (pack) length of the field.
      
      The solution is to augment the function that checks wether two ENUM
      or SET fields are compatible -- by comparing the pack lengths and
      performing a limited comparison of the member values.
     @ mysql-test/r/alter_table.result
        Add test case result for Bug#45567
     @ mysql-test/t/alter_table.test
        Add test case for Bug#45567
     @ sql/field.cc
        Check whether two fields can be considered 'equal' for table
        alteration purposes. Fields are equal if they retain the same
        pack length and if new members are added to the end of the list.
[22 Sep 2009 15:15] 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/84160

3118 Davi Arnaut	2009-09-22
      Bug#45567: Fast ALTER TABLE broken for enum and set
      
      The problem was that appending values to the end of an existing
      ENUM or SET column was being treated as table data modification,
      preventing a immediately (fast) table alteration that occurs when
      only table metadata is being modified.
      
      The cause was twofold: adding a enumeration or set members to the 
      end of the list of valid member values was not being considered
      a "compatible" table alteration, and for SET columns, the check
      was being done upon the max display length and not the underlying
      (pack) length of the field.
      
      The solution is to augment the function that checks wether two ENUM
      or SET fields are compatible -- by comparing the pack lengths and
      performing a limited comparison of the member values.
     @ mysql-test/r/alter_table.result
        Add test case result for Bug#45567
     @ mysql-test/t/alter_table.test
        Add test case for Bug#45567
     @ sql/field.cc
        Check whether two fields can be considered 'equal' for table
        alteration purposes. Fields are equal if they retain the same
        pack length and if new members are added to the end of the list.
[24 Sep 2009 14:03] 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/84507

3127 Davi Arnaut	2009-09-24
      Bug#45567: Fast ALTER TABLE broken for enum and set
      
      The problem was that appending values to the end of an existing
      ENUM or SET column was being treated as table data modification,
      preventing a immediately (fast) table alteration that occurs when
      only table metadata is being modified.
      
      The cause was twofold: adding a enumeration or set members to the 
      end of the list of valid member values was not being considered
      a "compatible" table alteration, and for SET columns, the check
      was being done upon the max display length and not the underlying
      (pack) length of the field.
      
      The solution is to augment the function that checks wether two ENUM
      or SET fields are compatible -- by comparing the pack lengths and
      performing a limited comparison of the member values.
     @ mysql-test/r/alter_table.result
        Add test case result for Bug#45567
     @ mysql-test/t/alter_table.test
        Add test case for Bug#45567
     @ sql/field.cc
        Check whether two fields can be considered 'equal' for table
        alteration purposes. Fields are equal if they retain the same
        pack length and if new members are added to the end of the list.
     @ sql/field.h
        Add comment and remove method.
[29 Sep 2009 10:59] 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/84972

3146 Davi Arnaut	2009-09-29
      Bug#45567: Fast ALTER TABLE broken for enum and set
      
      The problem was that appending values to the end of an existing
      ENUM or SET column was being treated as table data modification,
      preventing a immediately (fast) table alteration that occurs when
      only table metadata is being modified.
      
      The cause was twofold: adding a enumeration or set members to the 
      end of the list of valid member values was not being considered
      a "compatible" table alteration, and for SET columns, the check
      was being done upon the max display length and not the underlying
      (pack) length of the field.
      
      The solution is to augment the function that checks wether two ENUM
      or SET fields are compatible -- by comparing the pack lengths and
      performing a limited comparison of the member values.
     @ mysql-test/r/alter_table.result
        Add test case result for Bug#45567
     @ mysql-test/t/alter_table.test
        Add test case for Bug#45567
     @ sql/field.cc
        Check whether two fields can be considered 'equal' for table
        alteration purposes. Fields are equal if they retain the same
        pack length and if new members are added to the end of the list.
     @ sql/field.h
        Add comment and remove method.
[29 Sep 2009 11:41] Davi Arnaut 
Queued to 5.1-bugteam
[6 Oct 2009 9:01] Bugs System 
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20091002112748-2xmjv846dk323nc3) (merge vers: 5.1.40) (pib:11)
[13 Oct 2009 13:25] Paul DuBois 
Noted in 5.1.40 changelog.

Appending values to an ENUM or SET definition is a metadata change
for which ALTER TABLE need not rebuild the table, but it was being
rebuilt anyway. 

Setting report to NDI pending push into 5.5.x, etc.
[22 Oct 2009 6:35] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:07] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 19:49] Paul DuBois 
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:36] Bugs System 
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:52] Bugs System 
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:07] Bugs System 
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:21] Bugs System 
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)