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:23]
Peter Laursen
[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