Bug #44196 Data inconsistency adding elements to a ENUM column

• Submitted: 9 Apr 2009 20:54
• Modified: 10 Apr 2009 9:01
• Reporter: Eber M. Duarte
• Status: Can't repeat
• Category: MySQL Server: DDL
• Severity: S1 (Critical)
• Version: 5.1.23-rc-log
• OS: Linux (Linux 2.6.18-92.1.13.el5 #1 SMP Thu Sep 4 03:51:21 EDT 2008 x86_64 x86_64 x86_64 G)
• CPU Architecture: Any

[9 Apr 2009 20:54] Eber M. Duarte

Description:
Altering a ENUM column adding new elements and putting data in an order different from the original order, makes MySQL to show data with wrong value after the ALTER TABLE.

It doesn't happen on MySQL 4.1.21-max-log as shown bellow.

How to repeat:
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.1.23-rc-log | 
+---------------+
1 row in set (0.00 sec)

mysql> create table x (
    -> id int not null, 
    -> status enum ('X', 'A') not null
    -> ) engine=myisam;

mysql> insert into x values (1, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (2, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (3, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (4, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (5, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (6, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> select status, count(*) from x group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| X      |        2 | 
| A      |        4 | 
+--------+----------+
2 rows in set (0.00 sec)

mysql> alter table x modify status enum ('A', 'B', 'X') not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select status, count(*) from x group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| A      |        2 | 
| B      |        4 | 
+--------+----------+
2 rows in set (0.00 sec)

==== On MySQL 4.1 ====
mysql> select version();
+----------------+
| version()      |
+----------------+
| 4.1.21-max-log | 
+----------------+
1 row in set (0.00 sec)

mysql> create table x (
    -> id int not null, 
    -> status enum ('X', 'A') not null
    -> ) engine=myisam;

mysql> insert into x values (1, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (2, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (3, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (4, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (5, 'A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into x values (6, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> select status, count(*) from y group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| X      |        2 | 
| A      |        4 | 
+--------+----------+
2 rows in set (0.00 sec)

mysql> alter table y modify status enum ('A', 'B', 'X') not null;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select status, count(*) from y group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| A      |        4 | 
| X      |        2 | 
+--------+----------+
2 rows in set (0.00 sec)

[10 Apr 2009 9:01] Valeriy Kravchuk

This is not repeatable with recent versions of 5.1:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.32-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table x (
    -> id int not null,
    -> status enum ('X', 'A') not null
    -> ) engine=myisam;
Query OK, 0 rows affected (0.39 sec)

mysql> insert into x values (1, 'X');
Query OK, 1 row affected (0.06 sec)

mysql> insert into x values (2, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (3, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (4, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (5, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (6, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> select status, count(*) from x group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| X      |        2 |
| A      |        4 |
+--------+----------+
2 rows in set (0.09 sec)

mysql> alter table x modify status enum ('A', 'B', 'X') not null;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select status, count(*) from x group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
| A      |        4 |
| X      |        2 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> exit

Please, upgrade.