| Bug #61361 | Possible of duplicated primary key by enum type | ||
|---|---|---|---|
| Submitted: | 31 May 2011 10:59 | Modified: | 15 Oct 2011 17:34 |
| Reporter: | Dennis Cheung | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.5.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2011 11:27]
Peter Laursen
A slightly elaborated test case (in my understanding it has nothing wiht NULL to do, but something weird happens.
DROP TABLE IF EXISTS `runtime_config`;
CREATE TABLE `runtime_config` (
`dummy_id` ENUM('') COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`dummy_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `runtime_config` VALUES(''); -- reports success
INSERT INTO `runtime_config` VALUES(''); -- Error Code : 1062 - Duplicate entry '' for key 'PRIMARY'
INSERT INTO `runtime_config` VALUES(0); -- reports success
SHOW WARNINGS; -- Warning Code : 1265 - Data truncated for column 'dummy_id' at row 1
INSERT INTO `runtime_config` VALUES(0); -- Error Code : 1062 - Duplicate entry '' for key 'PRIMARY'
INSERT INTO `runtime_config` VALUES(NULL); -- Error Code : 1048 - Column 'dummy_id' cannot be null
SELECT COUNT(dummy_id) FROM runtime_config; -- 2
SELECT COUNT(dummy_id) FROM runtime_config WHERE dummy_id = ''; -- 1
SELECT HEX(dummy_id) FROM runtime_config;
+---------------+
| HEX(dummy_id) |
+---------------+
| |
| |
+---------------+
2 rows in set (0.00 sec)
I am mystified what the 3rd INSERT here actually inserts!
Peter
(not a MySQL person)
[31 May 2011 11:31]
Peter Laursen
To me it looks like it INSERTS 'empty string' to *data* twice, but different data are UPDATED to *index*. (I am using 5.5.13 BTW)
[31 May 2011 11:46]
Peter Laursen
add to test case:
ALTER TABLE `test`.`runtime_config` CHANGE `dummy_id` `dummy_id` ENUM('') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, DROP PRIMARY KEY; -- success
ALTER TABLE `test`.`runtime_config` CHANGE `dummy_id` `dummy_id` ENUM('') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, ADD PRIMARY KEY(`dummy_id`); -- success
SHOW WARNINGS; -- empty set
(and SELECT output is unchaged)
[31 May 2011 18:46]
Peter Laursen
add:
INSERT INTO `runtime_config` VALUES(''); -- success
INSERT INTO `runtime_config` VALUES(0); -- success with warning
INSERT INTO `runtime_config` VALUES(1); -- duplicate entry
INSERT INTO `runtime_config` VALUES(X); -- Error Code : 1054 - Unknown column 'x' in 'field list'
If "0" (without quotes) truncates (almost! :-) ) to '' why does not "X" (also without quotes) ?
[1 Jun 2011 8:56]
MySQL Verification Team
Trying to figure out if the docs are explaining it: "If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. More about this later." http://dev.mysql.com/doc/refman/5.6/en/enum.html On 5.6.3 i get: mysql> select dummy_id+0 from runtime_config where dummy_id=''; +------------+ | dummy_id+0 | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> select dummy_id+0 from runtime_config where dummy_id=0; +------------+ | dummy_id+0 | +------------+ | 0 | +------------+ 1 row in set (0.00 sec)
[1 Jun 2011 9:38]
Peter Laursen
Why is this implemented like that? What is special about ENUM's as compared to other datatypes, so that it needs a 'special error value' INSERTED? HEX() on both columns does not reveal any difference!
[1 Jun 2011 10:49]
Peter Laursen
Compare:
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (id CHAR(3) PRIMARY KEY) ENGINE=INNODB;
INSERT INTO tmp VALUES('the');
INSERT INTO tmp VALUES('there'); -- Error Code : 1062 - Duplicate entry 'the' for key 'PRIMARY'
No INSERT happens of the truncated value ("the") with a 'special error code marker'. I do not understand why truncation when inserting ENUM's isn't handled exactly the same way as is the case for other datatypes.
[15 Oct 2011 17:34]
Valeriy Kravchuk
As Shane explained, it seems internally ENUM is stored as numeric value (index of item). So, you can insert (once!) some value not present in table's definition - PRIMARY key will get value 0. This is not a bug.
[15 Oct 2011 17:44]
Peter Laursen
the implementation is still crazy! :-)
[16 Jun 2023 3:06]
MySQL Verification Team
this bug makes tables impossible to dump/restore with mysqldump.
mysql> create table t(a enum('a','','b') primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(''),('aaa');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from t;
+---+
| a |
+---+
| |
| |
+---+
2 rows in set (0.00 sec)
mysql> select md5(a) from t;
+----------------------------------+
| md5(a) |
+----------------------------------+
| d41d8cd98f00b204e9800998ecf8427e |
| d41d8cd98f00b204e9800998ecf8427e |
+----------------------------------+
2 rows in set (0.00 sec)
mysql> select a+0 from t;
+-----+
| a+0 |
+-----+
| 0 |
| 2 |
+-----+
Description: In a table with primary key type ENUM, is expected only valid key will exists in table. However, you can insert null enum into the table. How to repeat: CREATE TABLE `runtime_config` ( `dummy_id` enum('') COLLATE utf8_bin NOT NULL, PRIMARY KEY (`dummy_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$ INSERT INTO `runtime_config` VALUES(''); INSERT INTO `runtime_config` VALUES(0); SELECT dummy_id FROM runtime_config;