Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.4.3, 10.5, 10.6, 10.11, 11.2(EOL), 11.4
-
None
Description
In the following test case, the REPLACE statement should successfully insert data, instead of returning an error.
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY); |
CREATE UNIQUE INDEX i1 ON t0(c1); |
INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01'); |
INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10'); |
REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); -- Duplicate entry '0' for key 'i1' |
The reference test case can be obtained by defining the UNIQUE constraint in CREATE TABLE, and the same REPLACE statement successfully executed.
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL UNIQUE, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY); |
INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01'); |
INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10'); |
REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); -- no errors |
As a result, the final table content differs because of the different ways of creating UNIQUE constraints.
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
CREATE UNIQUE INDEX i1 ON t0(c1);
INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');
INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');
REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11');
After unique index creation, .frm file has been rebuild with the ALTER_INDEX_ORDER flag. So it made i1 as 1st secondary key and c2 as 2nd
secondary index. But there is only addition of secondary index inside innodb. i.e. i1 has been added as 2nd secondary index and c2 as 1st secondary index
During replace statement, InnoDB fails with DB_DUPLICATE_KEY while inserting into c2 index. write_row() has the check whether it is
last_uniq_index(). Since .frm file has different key order compared to InnoDB. It wrongly assumes that it is last unique index and does
update_row() and fails with DB_DUPLICATE_KEY in 2nd secondary index (i1).
Fix should be avoid enabling ALTER_INDEX_ORDER flag if the unique index already exist for InnoDB table.