Bug #57298 ENUMs do not obey strict sql_mode and empty string can insert when not specified
Submitted: 7 Oct 2010 8:23 Modified: 7 Oct 2010 16:46
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.51 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[7 Oct 2010 8:23] Peter Laursen
Description:
ALTER TABLE changing the values-list of an ENUM may insert an empty string even if empty string if not in list and even if strict sql_mode is set (not sure if SET types have a similar problem)

How to repeat:
SET sql_mode = 'strict_all_tables';

CREATE TABLE `entest` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `e` ENUM('blah','bluh') NOT NULL,  -- note 'NOT NULL' 
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `entest` (`e`) VALUES ('blah'), ('bluh');

ALTER TABLE `entest` CHANGE `e` `e` ENUM('blah','blih');
/* get

Error Code : 1265
Data truncated for column 'e' at row 2
*/

SELECT * FROM `etest`; 
/*

    id  e     
------  ------
     1  blah  
     2        
*/

Suggested fix:
At least in strict mode this should not happen (but don't think in 'plain' mode either). The ALTER statement should return an error and *do nothing* to the table.

I know internals and the workaround (ALTER ENUM('blah','bluh') >> INTEGER >> ENUM('blah','blih')). But it should not (at least not in strict mode) be possible to insert a value to a NOT NULL column that is not specified and not even listed in the ENUM-list.
[7 Oct 2010 8:24] Peter Laursen
fixed typo in synopsis
[7 Oct 2010 16:44] Valeriy Kravchuk
Not repeatable with 5.5.7 from bzr:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET sql_mode = 'strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `entest` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `e` ENUM('blah','bluh') NOT NULL,  -- note 'NOT NULL' 
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO `entest` (`e`) VALUES ('blah'), ('bluh');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE `entest` CHANGE `e` `e` ENUM('blah','blih');
ERROR 1265 (01000): Data truncated for column 'e' at row 2
mysql> SELECT * FROM `etest`; 
ERROR 1146 (42S02): Table 'test.etest' doesn't exist
mysql> SELECT *, e is null FROM `entest`; 
+----+------+-----------+
| id | e    | e is null |
+----+------+-----------+
|  1 | blah |         0 |
|  2 | bluh |         0 |
+----+------+-----------+
2 rows in set (0.00 sec)
[7 Oct 2010 16:46] Valeriy Kravchuk
Also not repeatable with current 5.1.52 from bzr:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET sql_mode = 'strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `entest` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `e` ENUM('blah','bluh') NOT NULL,  -- note 'NOT NULL' 
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> INSERT INTO `entest` (`e`) VALUES ('blah'), ('bluh');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `entest` CHANGE `e` `e` ENUM('blah','blih');
ERROR 1265 (01000): Data truncated for column 'e' at row 2
mysql> SELECT *, e is null FROM `entest`; 
+----+------+-----------+
| id | e    | e is null |
+----+------+-----------+
|  1 | blah |         0 |
|  2 | bluh |         0 |
+----+------+-----------+
2 rows in set (0.00 sec)
[9 Jul 2012 3:23] Edmund Mui
I met the same situation

got totally the error
Version: 5.5.24-log
[9 Jul 2012 3:24] Edmund Mui
typo, got totally the same error