Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
INSERT IGNORE can insert a value into ENUM which for most practical purposes is treated as an empty string.
It can further mysqldump / restore, because it is written in a dump or outfile as a regular empty string, and upon further INSERT causes a duplicate key error for a unique field.
CREATE TABLE t (f enum('','a','b') NOT NULL PRIMARY KEY, i INT); |
INSERT IGNORE INTO t VALUES (NULL,1),('',2); |
SELECT i, f, f = '' FROM t; |
SELECT * FROM t WHERE f = ''; |
SELECT * FROM t WHERE f = 0; |
SELECT * FROM t WHERE f = 1; |
|
|
SELECT * INTO OUTFILE 't.data' FROM t; |
TRUNCATE TABLE t; |
LOAD DATA INFILE 't.data' INTO TABLE t; |
SELECT * FROM t; |
|
|
# Cleanup
|
DROP TABLE t; |
--let $datadir= `select @@datadir`
|
--remove_file $datadir/test/t.data |
|
10.3 7d96cb470 |
MariaDB [test]> INSERT IGNORE INTO t VALUES (NULL,1),('',2); |
Query OK, 2 rows affected, 1 warning (0.008 sec) |
Records: 2 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [test]> SELECT i, f, f = '' FROM t; |
+------+---+--------+ |
| i | f | f = '' | |
+------+---+--------+ |
| 1 | | 1 |
|
| 2 | | 1 |
|
+------+---+--------+ |
2 rows in set (0.014 sec) |
So, the row with NULL is actually inserted, and the value in the ENUM column is said to be equal to ''. But not quite:
MariaDB [test]> SELECT * FROM t WHERE f = ''; |
+---+------+ |
| f | i |
|
+---+------+ |
| | 2 |
|
+---+------+ |
|
|
MariaDB [test]> SELECT * FROM t WHERE f = 0; |
+---+------+ |
| f | i |
|
+---+------+ |
| | 1 |
|
+---+------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> SELECT * FROM t WHERE f = 1; |
+---+------+ |
| f | i |
|
+---+------+ |
| | 2 |
|
+---+------+ |
However, upon selecting, these values become indistinguishable, so an outfile would contain two rows with an empty string in the f position, and INSERT in the dump will also try to insert two regular empty strings. Naturally both fail, already regardless strict mode or IGNORE, because they violate the unique key.
Same happens with any invalid value, not just NULL.
MariaDB [test]> SELECT * INTO OUTFILE 't.data' FROM t; |
Query OK, 2 rows affected (0.001 sec) |
|
|
MariaDB [test]> TRUNCATE TABLE t; |
Query OK, 0 rows affected (0.079 sec) |
|
|
MariaDB [test]> LOAD DATA INFILE 't.data' INTO TABLE t; |
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY' |
In mysqldump:
CREATE TABLE `t` ( |
`f` enum('','a','b') NOT NULL, |
`i` int(11) DEFAULT NULL, |
PRIMARY KEY (`f`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
|
--
|
-- Dumping data for table `t`
|
--
|
|
|
LOCK TABLES `t` WRITE;
|
/*!40000 ALTER TABLE `t` DISABLE KEYS */; |
INSERT INTO `t` VALUES ('',1),('',2); |
/*!40000 ALTER TABLE `t` ENABLE KEYS */; |
UNLOCK TABLES;
|
Thus the whole dump becomes unloadable.
Reproducible on all MariaDB and on MySQL (tried 8.0).