Details
Description
--source include/have_innodb.inc
|
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
|
|
CREATE TABLE t (pk INT PRIMARY KEY, c TEXT) ENGINE=InnoDB; |
INSERT t VALUES (1,'foo'),(2,'bar'),(3,'bar'); |
|
|
#--error ER_DUP_ENTRY |
ALTER TABLE t ADD UNIQUE (c); |
|
|
SHOW CREATE TABLE t; |
SELECT * FROM t; |
DROP TABLE t; |
In the above test case ALTER should fail (the error catch is commented to check that ALTER really worked and preserved duplicate values).
|
10.11 687c8be813429f03267ecfcc1de8f9215b060b4f |
ALTER TABLE t ADD UNIQUE (c); |
SHOW CREATE TABLE t; |
Table Create Table |
t CREATE TABLE `t` ( |
`pk` int(11) NOT NULL, |
`c` text DEFAULT NULL, |
PRIMARY KEY (`pk`), |
UNIQUE KEY `c` (`c`) USING HASH |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
SELECT * FROM t; |
pk c
|
1 foo
|
2 bar
|
3 bar
|
The problem appeared after this commit in 10.11.9
commit cc8eefb0dca1372378905fbae11044f20364c42d
|
Author: Thirunarayanan Balathandayuthapani
|
Date: Tue Jul 30 11:59:01 2024 +0530
|
|
|
MDEV-33087 ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently
|
It also affected 11.0 and 11.1 while they were alive, but not 11.2 and higher.
However here is another test case which does affect all of 10.11+ and does not require READ-COMMITTED (that is, it misbehaves under any transaction isolation), and started failing after the same commit:
--source include/have_innodb.inc
|
|
|
CREATE TABLE t (pk INT PRIMARY KEY, a INT, b TEXT) ENGINE=InnoDB; |
INSERT INTO t VALUES (5,3,'foo'), (1,NULL,'bar'), (3,3,'foo'); |
ALTER TABLE t ADD UNIQUE (a,b); |
|
|
SHOW CREATE TABLE t; |
SELECT * FROM t; |
|
|
DROP TABLE t; |
|
11.4 3109d994ebf867d6f3efc65a0e4c41195b8239f9 |
CREATE TABLE t (pk INT PRIMARY KEY, a INT, b TEXT) ENGINE=InnoDB; |
INSERT INTO t VALUES (5,3,'foo'), (1,NULL,'bar'), (3,3,'foo'); |
ALTER TABLE t ADD UNIQUE (a,b); |
SHOW CREATE TABLE t; |
Table Create Table |
t CREATE TABLE `t` ( |
`pk` int(11) NOT NULL, |
`a` int(11) DEFAULT NULL, |
`b` text DEFAULT NULL, |
PRIMARY KEY (`pk`), |
UNIQUE KEY `a` (`a`,`b`) USING HASH |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
SELECT * FROM t; |
pk a b
|
1 NULL bar |
3 3 foo
|
5 3 foo
|
Attachments
Issue Links
- is caused by
-
MDEV-33087 ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently
-
- Closed
-