MySQL Bugs: #27334: data disconsistency while alter modify column to enum

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:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	data consistency, enum

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)

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.

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?

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.