| 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: | |
| 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: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
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.