Bug #50058 alter enum column gives duplicate key error
Submitted: 4 Jan 2010 17:28 Modified: 8 Jan 2010 17:37
Reporter: Lig Isler-Turmelle Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.40 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[4 Jan 2010 17:28] Lig Isler-Turmelle
Description:
When altering a PK from ENUM to VARCHAR get duplicate key errors.  Seems to be linked to the size of the ENUM element list and the elements that are in use in the list (see least test).

How to repeat:
mysql> CREATE TABLE MyTable(MyEnum ENUM ('aib', 'aic'), primary key(MyEnum));
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO MyTable VALUES ('aib');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyTable VALUES ('aic');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE MyTable(MyEnum ENUM ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", "174", "175", "176", "177", "178", "179", "180", "181", "182", "183", "184", "185", "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", "199", "200", "201", "202", "203", "204", "205", "206", "207", "208", "209", "210", "211", "212", "213", "214", "215", "216", "217", "218", "219", "220", "221", "222", "223", "224", "225", "226", "227", "228", "229", "230", "231", "232", "233", "234", "235", "236", "237", "238", "239", "240", "241", "242", "243", "244", "245", "246", "247", "248", "249", "250", "251", "252", "253", "254", "255", "256", "257", "258", "259", "260", "261", "262", "263", "264", "265", "266", "267", "268", "269", "270", "271", "272", "273", "274", "275", "276", "277", "278", "279", "280", "281", "282", "283", "284", "285", "286", "287", "288", "289", "290", "291", "292", "293", "294", "295", "296", "297", "298", "299", "300"), primary key(MyEnum));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO MyTable VALUES ('5');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MyTable VALUES ('10');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE MyTable(MyEnum ENUM ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", "174", "175", "176", "177", "178", "179", "180", "181", "182", "183", "184", "185", "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", "199", "200", "201", "202", "203", "204", "205", "206", "207", "208", "209", "210", "211", "212", "213", "214", "215", "216", "217", "218", "219", "220", "221", "222", "223", "224", "225", "226", "227", "228", "229", "230", "231", "232", "233", "234", "235", "236", "237", "238", "239", "240", "241", "242", "243", "244", "245", "246", "247", "248", "249", "250", "251", "252", "253", "254", "255", "256", "257", "258", "259", "260", "261", "262", "263", "264", "265", "266", "267", "268", "269", "270", "271", "272", "273", "274", "275", "276", "277", "278", "279", "280", "281", "282", "283", "284", "285", "286", "287", "288", "289", "290", "291", "292", "293", "294", "295", "296", "297", "298", "299", "300"), primary key(MyEnum));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO MyTable VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyTable VALUES ('10');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE MyTable(MyEnum ENUM ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", "174", "175", "176", "177", "178", "179", "180", "181", "182", "183", "184", "185", "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", "199", "200", "201", "202", "203", "204", "205", "206", "207", "208", "209", "210", "211", "212", "213", "214", "215", "216", "217", "218", "219", "220", "221", "222", "223", "224", "225", "226", "227", "228", "229", "230", "231", "232", "233", "234", "235", "236", "237", "238", "239", "240", "241", "242", "243", "244", "245", "246", "247", "248", "249", "250", "251", "252", "253", "254", "255", "256", "257", "258", "259", "260", "261", "262", "263", "264", "265", "266", "267", "268", "269", "270", "271", "272", "273", "274", "275", "276", "277", "278", "279", "280", "281", "282", "283", "284", "285", "286", "287", "288", "289", "290", "291", "292", "293", "294", "295", "296", "297", "298", "299", "300"), primary key(MyEnum));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO MyTable VALUES ('222');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO MyTable VALUES ('223');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'

Suggested fix:
Unknown
[8 Jan 2010 17:37] Lig Isler-Turmelle
** bug appears when table is in latin1 charset but not utf8 charset - so it may very well be a character set issue.

(Original report was in the latin1 charset)

mysql> CREATE TABLE MyTable(MyEnum ENUM ('aib', 'aic'), primary key(MyEnum)) character set utf8;
Query OK, 0 rows affected (0.39 sec)

mysql> INSERT INTO MyTable VALUES ('aib');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyTable VALUES ('aic');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
Query OK, 2 rows affected (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 0

** When there is no primary key in latin1 - there is no problem:

mysql> CREATE TABLE MyTable(MyEnum ENUM ('aib', 'aic'));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO MyTable VALUES ('aib');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyTable VALUES ('aic');
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

** What is inside:

mysql> CREATE TABLE MyTable(MyEnum ENUM ("0", "1", "2", "3", "4", "5", "6", "7", "8","9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23","24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53","54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68","69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83","84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98","99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111","112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123","124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135","136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147","148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159","160", "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171","172", "173", "174", "175", "176", "177", "178", "179", "180", "181", "182", "183","184", "185", "186", "187", "188", "189", "190", "191", "192", "193", "194", "195","196", "197", "198", "199", "200", "201", "202", "203", "204", "205", "206", "207","208", "209", "210", "211", "212", "213", "214", "215", "216", "217", "218", "219","220", "221", "222", "223", "224", "225", "226", "227", "228", "229", "230", "231","232", "233", "234", "235", "236", "237", "238", "239", "240", "241", "242", "243","244", "245", "246", "247", "248", "249", "250", "251", "252", "253", "254", "255","256", "257", "258", "259", "260", "261", "262","263", "264", "265", "266", "267","268", "269", "270", "271", "272", "273", "274", "275", "276", "277", "278", "279","280", "281", "282", "283", "284", "285", "286", "287", "288", "289", "290", "291","292", "293", "294", "295", "296", "297", "298", "299", "300"), primary key(MyEnum)) character set latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO MyTable VALUES ('222');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO MyTable VALUES ('223');
Query OK, 1 row affected (0.00 sec)

mysql> select MyEnum+0 from MyTable;
+----------+
| MyEnum+0 |
+----------+
|      223 |
|      224 |
+----------+
2 rows in set (0.00 sec)

mysql> select HEX(MyEnum) from MyTable;
+-------------+
| HEX(MyEnum) |
+-------------+
| 323232      |
| 323233      |
+-------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE MyTable MODIFY COLUMN MyEnum varchar(5);
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'

mysql>
[24 Aug 2012 12:34] Arpit Agrawal
I had a table called tbl_jobs with the schema:

type enum('USER_STATUS','INVITE'),
run_time datetime,
records text NULL,
PRIMARY KEY(type,run_time)

Later I altered the column `type` to varchar(30) to include two more types:
MAILER_UNLOCKED and MAILER_ALMOST_UNLOCKED

I got the same error whenever there was an entry of the two new types with the same `run_time` value.

On research I found that sub_part field in show indexes from tbl_jobs was modified to 1(initially null) for the column `type` because of which only the first character was getting indexed which happened to be the same(M) for both the types and thus the error.

> show indexes from tbl_jobs;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_jobs |          0 | PRIMARY  |            1 | type        | A         |          12 |     1| NULL   |      | BTREE      |         |               |
| tbl_jobs |          0 | PRIMARY  |            2 | last_run_on | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)