Bug #27334 data disconsistency while alter modify column to enum
Submitted: 21 Mar 2007 14:39 Modified: 22 Mar 2007 8:51
Reporter: Hans Ginzel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.26-community-nt OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: data consistency, enum
Triage: D5 (Feature request)

[21 Mar 2007 14:39] Hans Ginzel
Description:
Silently data deletion while alter table modify column from char to enum.
Without any warning!

How to repeat:

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.26-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a (a char(1));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into a values ('0'), ('a'),('b');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from a;
+------+
| a    |
+------+
| 0    |
| a    |
| b    |
+------+
3 rows in set (0.00 sec)

mysql>  alter table a modify a enum ('a','b');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from a;
+------+
| a    |
+------+
|      |
| a    |
| b    |
+------+
3 rows in set (0.00 sec)
[21 Mar 2007 19:23] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/enum.html and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php.
[22 Mar 2007 8:37] Hans Ginzel
It is a design bug.
There is a data loss without any warnings also under the STRICT_ALL_TABLES sql_mode.

Wich sql_mode to set to disable this behaviour from ttp://dev.mysql.com/doc/refman/5.0/en/enum.html:
If you store '3', it does not match any enumeration value, so it is treated as an index and becomes '2' (the value with index 3)?

Under strict mode, if such automatic conversion char to index is taken, warnings should be generated.

How to disable char to index conversion?
[22 Mar 2007 8:51] Valeriy Kravchuk
I agree with you that this is a problem. But as it is explicitely described in the manual (that is, "by design"), I consider your request to prevent this by setting some sql_mode as a reasonable feature request. I hope it will be implemented eventually.