MySQL Bugs: #52484: Incorrect handling of column types causes fast ALTER TABLE failure on enum types

Bug #52484	Incorrect handling of column types causes fast ALTER TABLE failure on enum types
Submitted:	30 Mar 2010 17:56	Modified:	6 Jul 2010 14:00
Reporter:	chris lundberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.45, 5.1.46-bzr	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	ALTER TABLE, column type, enum

Description:
Enumerated column types are mis-typed in the source code when there is an int column ahead of it in a table.

One of the symptoms of the bug is that a Fast Alter Table is not possible with an enumerated column that is anywhere but first in the table.  This makes it impossible to change the metadata of large tables without rewriting the whole table, which is why it's an S2 level.

The example below better illustrates the problem:

How to repeat:
drop table enum_test;
create table enum_test (t enum('a','b'));
-- insert lots of dummy data to illustrate difference between fast and slow alter
insert into enum_test values ('a'),('b'),('a');
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
insert into enum_test select * from enum_test;
-- fast alter -- works real quick
alter table enum_test modify column t enum('a','b','c');
-- add a dummy int ahead of time
alter table enum_test add column blow_up int(16) first;
-- slow alter -- SHOULD JUST CHANGE metadata, but alters whole table
alter table enum_test modify column t enum('a','b','c','d');

--  results

developerUser[org]> -- fast alter
developerUser[org]> alter table enum_test modify column t enum('a','b','c');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

developerUser[org]> alter table enum_test add column blow_up int(16) first;
Query OK, 6291456 rows affected (32.11 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

developerUser[org]> -- slow alter -- SHOULD STILL BE FAST
developerUser[org]> alter table enum_test modify column t enum('a','b','c','d');
Query OK, 6291456 rows affected (33.73 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

Suggested fix:
In field.cc or sql_table.cc:

Appropriately construct the enumerated column with 'Field_enum', instead of  'Field_num', so that the method:

Field_enum::is_equal()

is called instead of 

Field_num::is_equal()

method when running the alter table.

Thank you for the problem report. Verified just as described with recent 5.1.46 from bzr on Mac OS X:

...
mysql> insert into enum_test select * from enum_test;
Query OK, 786432 rows affected (2.76 sec)
Records: 786432  Duplicates: 0  Warnings: 0

mysql> alter table enum_test modify column t enum('a','b','c');
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table enum_test add column blow_up int(16) first;
Query OK, 1572864 rows affected (1.99 sec)
Records: 1572864  Duplicates: 0  Warnings: 0

mysql> alter table enum_test modify column t enum('a','b','c','d');
Query OK, 1572864 rows affected (3.21 sec)
Records: 1572864  Duplicates: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.46-debug |
+--------------+
1 row in set (0.00 sec)

triage: setting tag to SR51MRU

I couldn't reproduce the issue using fresh bzr sources.
Apparently, the bug has been fixed somehow.

5.1.48 seems to resolve the issue, although 5.1.46-normal still has the issue.