Description:
Online add unique index is not incorrect in some cases.
SESSION1:
root@localhost : test 04:26:56> alter table t add unique key b(b);
Query OK, 0 rows affected (3 min 36.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
SESSION2:
root@localhost : test 04:26:45> insert into t (b,c) values (8,repeat('a',16));
Query OK, 1 row affected (38.86 sec)
root@localhost : test 04:28:21> update t set b=4 where a=1;
Query OK, 1 row affected (4.74 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost : test 04:29:35> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` char(255) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
1 row in set (3.49 sec)
root@localhost : test 04:30:55> select * from t;
+----+------+------------------+
| a | b | c |
+----+------+------------------+
| 1 | 4 | aaaaaaaaaaaaaaaa |
| 2 | 2 | aaaaaaaaaaaaaaaa |
| 3 | 3 | aaaaaaaaaaaaaaaa |
| 4 | 4 | aaaaaaaaaaaaaaaa |
| 5 | 5 | aaaaaaaaaaaaaaaa |
| 6 | 8 | aaaaaaaaaaaaaaaa |
+----+------+------------------+
6 rows in set (0.01 sec)
How to repeat:
CREATE TABLE `t` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` char(255) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t (b,c) values (1,1,repeat("a",16)),(2,2,repeat("a",16)),....
SESSION1:
you can set a breakpoint at row_merge_insert_index_tuples() or row_log_apply();
run: alter table t add unique key b(b);
wait at breakpoint, and then run SESSION2;
SESSION2:
insert into t (b,c) values (8,repeat('a',16));update t set b=4 where a=1;
SESSION1;
continue;
and then, you can get the table have a unique index in column "b", but "b" have two value "4", it's duplicated.
Suggested fix:
I think after read all data in cluster index, and before row_log_apply(), InnoDB should do more duplicating check.
Description: Online add unique index is not incorrect in some cases. SESSION1: root@localhost : test 04:26:56> alter table t add unique key b(b); Query OK, 0 rows affected (3 min 36.07 sec) Records: 0 Duplicates: 0 Warnings: 0 SESSION2: root@localhost : test 04:26:45> insert into t (b,c) values (8,repeat('a',16)); Query OK, 1 row affected (38.86 sec) root@localhost : test 04:28:21> update t set b=4 where a=1; Query OK, 1 row affected (4.74 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@localhost : test 04:29:35> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` char(255) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 1 row in set (3.49 sec) root@localhost : test 04:30:55> select * from t; +----+------+------------------+ | a | b | c | +----+------+------------------+ | 1 | 4 | aaaaaaaaaaaaaaaa | | 2 | 2 | aaaaaaaaaaaaaaaa | | 3 | 3 | aaaaaaaaaaaaaaaa | | 4 | 4 | aaaaaaaaaaaaaaaa | | 5 | 5 | aaaaaaaaaaaaaaaa | | 6 | 8 | aaaaaaaaaaaaaaaa | +----+------+------------------+ 6 rows in set (0.01 sec) How to repeat: CREATE TABLE `t` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` char(255) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t (b,c) values (1,1,repeat("a",16)),(2,2,repeat("a",16)),.... SESSION1: you can set a breakpoint at row_merge_insert_index_tuples() or row_log_apply(); run: alter table t add unique key b(b); wait at breakpoint, and then run SESSION2; SESSION2: insert into t (b,c) values (8,repeat('a',16));update t set b=4 where a=1; SESSION1; continue; and then, you can get the table have a unique index in column "b", but "b" have two value "4", it's duplicated. Suggested fix: I think after read all data in cluster index, and before row_log_apply(), InnoDB should do more duplicating check.