| Bug #9380 | ENUM column in UNIQUE key not properly managed with InnoDB storage engine | ||
|---|---|---|---|
| Submitted: | 24 Mar 2005 11:13 | Modified: | 4 Apr 2005 19:42 |
| Reporter: | Bastien Duclaux | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.10a | OS: | Linux (Linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[25 Mar 2005 0:05]
Heikki Tuuri
Hi! The problem seems to be that MySQL stores the enum value as the code number of the value. 'V1' is 0x01, 'V2' is 0x02. But those code numbers are then compared as character strings, like they would be UTF-8 character strings! They are not character strings, but numbers. I do not know why MyISAM works in this case. Regards, Heikki
[25 Mar 2005 1:01]
Heikki Tuuri
Hi! Ok, I see. InnoDB should use field->real_type() to notice in CREATE TABLE that a column is ENUM, and is internally stored as an integer. MyISAM works because MySQL uses the method of a derived 'string' type 'enum' in compare, and that method looks at the 'int_val' of the stored value. The bug has not surfaced before, because if there are less than about 100 distinct values for the enum, then the latin1 sorting order for a one-byte integer is actually the right one! Regards, Heikki
[4 Apr 2005 19:42]
Heikki Tuuri
This bug is a duplicate of Bug #9526, and a patch has been approved for that bug. Now this works: heikki@hundin:~/mysql-5.0/client> ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.4-beta-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `T1` ( -> `ID` int(10) unsigned NOT NULL auto_increment, -> `ID2` int(10) unsigned NOT NULL default '0', -> `ID3` int(10) unsigned NOT NULL default '0', -> `ENUM1` enum('V1','V2','V3') collate utf8_unicode_ci NOT NULL default ' V1', -> `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update -> CURRENT_TIMESTAMP, -> PRIMARY KEY (`ID`), -> UNIQUE KEY `AK_UNIQUE` (`ID2`,`ID3`,`ENUM1`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci -> ; Query OK, 0 rows affected (0.04 sec) mysql> insert into T4 values ( null, 1, 1, 'V1', NOW() ); ERROR 1146 (42S02): Table 'test.T4' doesn't exist mysql> insert into T1 values ( null, 1, 1, 'V1', NOW() ); Query OK, 1 row affected (0.00 sec) mysql> insert into T1 values ( null, 1, 1, 'V2', NOW() ); Query OK, 1 row affected (0.01 sec) mysql> insert into T1 values ( null, 1, 1, 'V2', NOW() ); ERROR 1062 (23000): Duplicate entry '1-1-V2' for key 2 mysql> Fixed in 5.0.4. Regards, Heikki
Description: UNIQUE keys including a ENUM column don't work as expected when using InnoDB storage engine : the engine does not take into account the distinct values of the ENUM column into the unique key. How to repeat: mysql> CREATE TABLE `T1` ( `ID` int(10) unsigned NOT NULL auto_increment, `ID2` int(10) unsigned NOT NULL default '0', `ID3` int(10) unsigned NOT NULL default '0', `ENUM1` enum('V1','V2','V3') collate utf8_unicode_ci NOT NULL default 'V1', `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID`), UNIQUE KEY `AK_UNIQUE` (`ID2`,`ID3`,`ENUM1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci mysql> insert into T1 values ( null, 1, 1, 'V1', NOW() ); Query OK, 1 row affected (0.15 sec) mysql> insert into T1 values ( null, 1, 1, 'V2', NOW() ); ERROR 1062 (23000): Duplicate entry '1-1-V2' for key 2 This INSERT statement should not fail, as V2!=V1 in ENUM1 column. This bug does not occur with MyISAM tables: mysql>CREATE TABLE `T4` ( `ID` int(10) unsigned NOT NULL auto_increment, `ID2` int(10) unsigned NOT NULL default '0', `ID3` int(10) unsigned NOT NULL default '0', `ENUM1` enum('V1','V2','V3') collate utf8_unicode_ci NOT NULL default 'V1', `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID`), UNIQUE KEY `AK_UNIQUE` (`ID2`,`ID3`,`ENUM1`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci mysql> insert into T4 values ( null, 1, 1, 'V1', NOW() ); Query OK, 1 row affected (0.01 sec) mysql> insert into T4 values ( null, 1, 1, 'V2', NOW() ); Query OK, 1 row affected (0.01 sec) Suggested fix: 3 options : - Fix bug in InnoDB storage engine. - Use MyISAM - Use INTEGER column to simulate ENUM column as follows : mysql> CREATE TABLE `T2` ( `ID` int(10) unsigned NOT NULL auto_increment, `ID2` int(10) unsigned NOT NULL default '0', `ID3` int(10) unsigned NOT NULL default '0', `FAKEENUM1` int(10) unsigned NOT NULL default '1', `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID`), UNIQUE KEY `AK_UNIQUE` (`ID2`,`ID3`,`FAKEENUM1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci mysql> insert into T2 values ( null, 1, 1, 1, NOW() ); Query OK, 1 row affected (0.04 sec) mysql> insert into T2 values ( null, 1, 1, 2, NOW() ); Query OK, 1 row affected (0.01 sec)