| Bug #101706 | check table can't find duplicate error in table | ||
|---|---|---|---|
| Submitted: | 21 Nov 2020 16:51 | Modified: | 3 Dec 2020 14:25 |
| Reporter: | Zongzhi Chen (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Nov 2020 14:58]
MySQL Verification Team
Hi Mr. zongzhi, Thank you for your bug report. I have patched latest 8.0 and repeated your test case, this being only the last part: mysql> select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 5 | | 2 | 5 | | 10 | 9 | +----+------+ 3 rows in set (0.00 sec) mysql> check table t1; +--------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+-------+----------+----------+ | test.t1 | check | status | OK | +--------+-------+----------+----------+ 1 row in set (0.01 sec) Verified as reported.
[30 Nov 2020 15:06]
MySQL Verification Team
Set correct version.
[3 Dec 2020 14:25]
MySQL Verification Team
Hi Mr. zongzhi, This bug can not be any longer reproduced with 8.0.24. Hence, we are closing this bug.
Description: I am trying to fix this issue #68021. And add these fix to my code diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index e7938a63879..6a97c49d519 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -1914,8 +1914,22 @@ static MY_ATTRIBUTE((warn_unused_result)) dberr_t found. This means it is possible for another transaction to insert a duplicate key value but MDL protection on DD tables will prevent insertion of duplicates into unique secondary indexes*/ - const ulint lock_type = + ulint lock_type = index->table->skip_gap_locks() ? LOCK_REC_NOT_GAP : LOCK_ORDINARY; + /* If the transaction isolation level is no stronger than + READ COMMITTED, then avoid gap locks. */ + + if (!page_rec_is_supremum(rec) + && thr_get_trx(thr)->isolation_level + <= TRX_ISO_READ_COMMITTED) { + lock_type = LOCK_REC_NOT_GAP; + } else { + lock_type = LOCK_ORDINARY; + } + + if (index->table->skip_gap_locks()) { + lock_type = LOCK_REC_NOT_GAP; + } if (page_rec_is_infimum(rec)) { continue; I know that change the lock_type from LOCK_ORDINARY to LOCK_REC_NOT_GAP will cause duplicate key in table. However, I find the "check table" can't find the duplicate key error in the table How to repeat: -- Prepare test data CREATE TABLE `t` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=10; insert into t values(10, 9); insert into t values(12, 5); -- session 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from t where c2=5; Query OK, 1 row affected (0.00 sec) -- session 2 mysql> insert into t select 2,5; -- session 3 mysql> insert into t select 1,5; -- session 1 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +----+------+ | c1 | c2 | +----+------+ | 1 | 5 | | 2 | 5 | | 10 | 9 | +----+------+ 3 rows in set (0.00 sec) mysql> check table t; +--------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+-------+----------+----------+ | test.t | check | status | OK | +--------+-------+----------+----------+ 1 row in set (0.00 sec)