| Bug #89758 | Conversion from ENUM to VARCHAR fails because mysql adds prefix index | ||
|---|---|---|---|
| Submitted: | 22 Feb 2018 4:23 | Modified: | 22 Feb 2018 17:06 |
| Reporter: | monty solomon | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.17.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Feb 2018 4:29]
monty solomon
If I drop and recreate the PRIMARY (or UNIQUE) KEY during the ALTER TABLE then it will succeed but it still adds a column prefix of 1 to any indexes than contain the field.
CREATE TABLE monty (
p int(10) unsigned NOT NULL,
u int(10) unsigned NOT NULL,
o bigint(20) unsigned NOT NULL,
t enum('CONTACT','COMPANY','DEAL') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'CONTACT',
c bigint(20) unsigned NOT NULL,
s enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (p,u,t,o),
KEY o (o,p,t)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
INSERT INTO monty VALUES (1,2,3,'CONTACT',4,'OWNER');
INSERT INTO monty VALUES (1,2,3,'COMPANY',4,'OWNER');
mysql> ALTER TABLE monty DROP PRIMARY KEY, MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL, ADD PRIMARY KEY (p,u,t,o);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
Table: monty
Create Table: CREATE TABLE `monty` (
`p` int(10) unsigned NOT NULL,
`u` int(10) unsigned NOT NULL,
`o` bigint(20) unsigned NOT NULL,
`t` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`c` bigint(20) unsigned NOT NULL,
`s` enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (`p`,`u`,`t`,`o`),
KEY `o` (`o`,`p`,`t`(1))
) ENGINE=InnoDB DEFAULT CHARSET=ascii
1 row in set (0.00 sec)
[22 Feb 2018 4:40]
monty solomon
It fails when the starting character set for the ENUM is ascii or latin1. It does not fail when the starting character set for the ENUM is utf8 or utf8mb4. It does not add the column prefix to the index when the starting character set for the ENUM is utf8 or utf8mb4.
DROP TABLE IF EXISTS monty;
CREATE TABLE monty (
p int(10) unsigned NOT NULL,
u int(10) unsigned NOT NULL,
o bigint(20) unsigned NOT NULL,
t enum('CONTACT','COMPANY','DEAL') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'CONTACT',
c bigint(20) unsigned NOT NULL,
s enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (p,u,t,o),
KEY o (o,p,t)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
INSERT INTO monty VALUES (1,2,3,'CONTACT',4,'OWNER');
INSERT INTO monty VALUES (1,2,3,'COMPANY',4,'OWNER');
mysql> ALTER TABLE monty MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL;
ERROR 1062 (23000): Duplicate entry '1-2-C-3' for key 'PRIMARY'
[22 Feb 2018 4:41]
monty solomon
Starting with utf8
DROP TABLE IF EXISTS monty;
CREATE TABLE monty (
p int(10) unsigned NOT NULL,
u int(10) unsigned NOT NULL,
o bigint(20) unsigned NOT NULL,
t enum('CONTACT','COMPANY','DEAL') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'CONTACT',
c bigint(20) unsigned NOT NULL,
s enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (p,u,t,o),
KEY o (o,p,t)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
INSERT INTO monty VALUES (1,2,3,'CONTACT',4,'OWNER');
INSERT INTO monty VALUES (1,2,3,'COMPANY',4,'OWNER');
mysql> ALTER TABLE monty MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
Table: monty
Create Table: CREATE TABLE `monty` (
`p` int(10) unsigned NOT NULL,
`u` int(10) unsigned NOT NULL,
`o` bigint(20) unsigned NOT NULL,
`t` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`c` bigint(20) unsigned NOT NULL,
`s` enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (`p`,`u`,`t`,`o`),
KEY `o` (`o`,`p`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii
1 row in set (0.00 sec)
[22 Feb 2018 4:43]
monty solomon
Starting with utf8mb4
DROP TABLE IF EXISTS monty;
CREATE TABLE monty (
p int(10) unsigned NOT NULL,
u int(10) unsigned NOT NULL,
o bigint(20) unsigned NOT NULL,
t enum('CONTACT','COMPANY','DEAL') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'CONTACT',
c bigint(20) unsigned NOT NULL,
s enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (p,u,t,o),
KEY o (o,p,t)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
INSERT INTO monty VALUES (1,2,3,'CONTACT',4,'OWNER');
INSERT INTO monty VALUES (1,2,3,'COMPANY',4,'OWNER');
mysql> ALTER TABLE monty MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
Table: monty
Create Table: CREATE TABLE `monty` (
`p` int(10) unsigned NOT NULL,
`u` int(10) unsigned NOT NULL,
`o` bigint(20) unsigned NOT NULL,
`t` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`c` bigint(20) unsigned NOT NULL,
`s` enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (`p`,`u`,`t`,`o`),
KEY `o` (`o`,`p`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii
1 row in set (0.00 sec)
[22 Feb 2018 17:06]
MySQL Verification Team
Hi!
Thank you for your bug report.
I have got the same result as you have:
p u o t c s
1 2 3 CONTACT 4 OWNER
1 2 3 COMPANY 4 OWNER
ERROR 1062 (23000) at line 20: Duplicate entry '1-2-C-3' for key 'PRIMARY'
p u o t c s
1 2 3 CONTACT 4 OWNER
*************************** 1. row ***************************
Table: monty
Create Table: CREATE TABLE `monty` (
`p` int(10) unsigned NOT NULL,
`u` int(10) unsigned NOT NULL,
`o` bigint(20) unsigned NOT NULL,
`t` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`c` bigint(20) unsigned NOT NULL,
`s` enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL',
PRIMARY KEY (`p`,`u`,`t`(1),`o`),
KEY `o` (`o`,`p`,`t`(1))
) ENGINE=InnoDB DEFAULT CHARSET=ascii
Verified.
Description: Converting an ENUM field to a VARCHAR field in a PRIMARY KEY or a UNIQUE KEY fails when multiple ENUM values start with the same letter because the server is adding a column prefix of (1) to the index. How to repeat: CREATE DATABASE test ; USE test ; CREATE TABLE monty ( p int(10) unsigned NOT NULL, u int(10) unsigned NOT NULL, o bigint(20) unsigned NOT NULL, t enum('CONTACT','COMPANY','DEAL') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'CONTACT', c bigint(20) unsigned NOT NULL, s enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL', PRIMARY KEY (p,u,t,o), KEY o (o,p,t) ) ENGINE=InnoDB DEFAULT CHARSET=ascii; INSERT INTO monty VALUES (1,2,3,'CONTACT',4,'OWNER'); INSERT INTO monty VALUES (1,2,3,'COMPANY',4,'OWNER'); mysql> SELECT * FROM monty\G *************************** 1. row *************************** p: 1 u: 2 o: 3 t: CONTACT c: 4 s: OWNER *************************** 2. row *************************** p: 1 u: 2 o: 3 t: COMPANY c: 4 s: OWNER 2 rows in set (0.00 sec) mysql> ALTER TABLE monty MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL; ERROR 1062 (23000): Duplicate entry '1-2-C-3' for key 'PRIMARY' mysql> DELETE FROM monty WHERE t='COMPANY'; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM monty\G *************************** 1. row *************************** p: 1 u: 2 o: 3 t: CONTACT c: 4 s: OWNER 1 row in set (0.00 sec) mysql> ALTER TABLE monty MODIFY COLUMN t VARCHAR(40) CHARSET ascii COLLATE ascii_bin NOT NULL; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE monty\G *************************** 1. row *************************** Table: monty Create Table: CREATE TABLE `monty` ( `p` int(10) unsigned NOT NULL, `u` int(10) unsigned NOT NULL, `o` bigint(20) unsigned NOT NULL, `t` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `c` bigint(20) unsigned NOT NULL, `s` enum('MANUAL','OWNER') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MANUAL', PRIMARY KEY (`p`,`u`,`t`(1),`o`), KEY `o` (`o`,`p`,`t`(1)) ) ENGINE=InnoDB DEFAULT CHARSET=ascii 1 row in set (0.00 sec) Observe the unexpected column prefix lengths in both indexes. Suggested fix: Don't add column prefix lengths to the index(es) when converting from ENUM to VARCHAR.