| 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: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Jan 2010 17:37]
MySQL Verification Team
** 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)
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