| Bug #29251 | MySQL coerces special 0 enum values to '' when ALTERing the column | ||
|---|---|---|---|
| Submitted: | 20 Jun 2007 21:32 | Modified: | 11 Jul 2007 22:27 |
| Reporter: | Neil Skrypuch | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.42, 5.1, 4.1 | OS: | Linux (Gentoo) |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[20 Jun 2007 21:36]
Neil Skrypuch
SQL to repeat the bug
Attachment: mysqlenumbug.sql (application/octet-stream, text), 982 bytes.
[21 Jun 2007 7:15]
Sveta Smirnova
Thank you for the report. Verified as described.
[27 Jun 2007 2:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/29652 ChangeSet@1.2670, 2007-06-27 03:38:14+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #29251. Sometimes special 0 ENUM values was ALTERed to normal empty string ENUM values. Special 0 ENUM value has the same string representation as normal ENUM value defined as '' (empty string). The do_field_string function was used to convert ENUM data at an ALTER TABLE request, but this function doesn't care about numerical "indices" of ENUM values, i.e. do_field_string doesn't distinguish a special 0 value from an empty string value. A new copy function called do_field_enum has been added to copy special 0 ENUM values without conversion to an empty string.
[1 Jul 2007 19:57]
Bugs System
Pushed into 5.1.21-beta
[1 Jul 2007 20:02]
Bugs System
Pushed into 5.0.46
[1 Jul 2007 20:03]
Bugs System
Pushed into 4.1.24
[11 Jul 2007 22:27]
Paul DuBois
Noted in 4.1.24, 5.0.46, 5.1.21 changelogs. If an ENUM column contained '' as one of its members (represented with numeric value greater than 0), and the column contained error values (represented as 0 and displayed as ''), using ALTER TABLE to modify the column definition caused the 0 values to be given the numeric value of the non-zero '' member.
Description: When ALTERing an enum column with an '' element in its definition, MySQL will coerce all of the special 0 enum values into ''. This causes data loss when invalid entries are inserted into the column (due to a typo in the column definition, for example), and then the user attempts to ALTER the column definition appropriately. After the ALTERing of the column, the user attempts to update all of the rows with the special 0 enum value, only to find that they've been replaced with ''s. For example: mysql> DROP TABLE `testenum`; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `testenum` (`project` enum('org.eclipse.emf','org.eclipse.emf.ecore.sdo','org.eclipse.emft','org.eclipse.m2t','org.eclipse.mdt','org.eclipse.uml2','org.eclipse.xsd') NOT NULL, `component` enum('','a','b') NOT NULL, PRIMARY KEY(`project`, `component`)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `testenum` (`project`, `component`) VALUES ('org.eclipse.emf', 'abc'), ('org.eclipse.emf', ''); Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1 Warning (Code 1265): Data truncated for column 'component' at row 1 mysql> SELECT COUNT(*), `component` FROM `testenum` GROUP BY `component`; +----------+-----------+ | COUNT(*) | component | +----------+-----------+ | 1 | | | 1 | | +----------+-----------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM `testenum` WHERE `component` = ''; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM `testenum` WHERE `component` = 0; +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b'); ERROR 1062 (23000): Duplicate entry 'org.eclipse.emf-' for key 1 mysql> mysql> -- The duplicates are one problem, although it can be worked around, but with negative side effects... mysql> mysql> UPDATE `testenum` SET `component` = 'b' WHERE `component` != 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> UPDATE `testenum` SET `component` = 'abc' WHERE `component` = 0; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> SELECT * FROM `testenum`; +-----------------+-----------+ | project | component | +-----------------+-----------+ | org.eclipse.emf | | | org.eclipse.emf | b | +-----------------+-----------+ 2 rows in set (0.00 sec) How to repeat: DROP TABLE `testenum`; CREATE TABLE `testenum` (`project` enum('org.eclipse.emf','org.eclipse.emf.ecore.sdo','org.eclipse.emft','org.eclipse.m2t','org.eclipse.mdt','org.eclipse.uml2','org.eclipse.xsd') NOT NULL, `component` enum('','a','b') NOT NULL, PRIMARY KEY(`project`, `component`)); INSERT INTO `testenum` (`project`, `component`) VALUES ('org.eclipse.emf', 'abc'), ('org.eclipse.emf', ''); SELECT COUNT(*), `component` FROM `testenum` GROUP BY `component`; SELECT COUNT(*) FROM `testenum` WHERE `component` = ''; SELECT COUNT(*) FROM `testenum` WHERE `component` = 0; ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b'); -- The duplicates are one problem, although it can be worked around, but with negative side effects... UPDATE `testenum` SET `component` = 'b' WHERE `component` != 0; ALTER TABLE `testenum` CHANGE `component` `component` enum('','abc','b'); UPDATE `testenum` SET `component` = 'abc' WHERE `component` = 0; SELECT * FROM `testenum`; Suggested fix: MySQL should preserve the special 0 values for an enum column when ALTERing it's definition, instead of coercing it into a regular '' value. In other words, for the above case, the end result should be: mysql> SELECT * FROM `testenum`; +-----------------+-----------+ | project | component | +-----------------+-----------+ | org.eclipse.emf | abc | | org.eclipse.emf | b | +-----------------+-----------+ 2 rows in set (0.00 sec)