MySQL Bugs: #106121: Unique key constraint invalid

Bug #106121	Unique key constraint invalid
Submitted:	10 Jan 2022 3:46	Modified:	12 Jan 2022 1:47
Reporter:	chengqing hu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	MySQL8.0.27、8.0	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
If set unique_checks=0 and the data of the DML operation is not in the innodb buffer pool, it will not be uniquely detected by reading the primary key and unique index on the disk. At this time, just like the ordinary secondary index, the data writing is directly completed in the change buffer. The uniqueness check will not be performed, and duplicate data can be inserted.

How to repeat:
1. my.cnf
innodb_buffer_pool_size = 64M
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_dump_pct = 0

2. make some data
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  unique key `a` (`a`)
);
insert into t(a,k,c,pad) select * from sbtest1 where id <= 5000;

3. restart mysqld

4. insert duplicated data
set session unique_checks=0;
insert into t(a,k,c,pad) values(1,439502,'59488447928-69619891934-66520244141-26804756589-33623161608-43445073345-17979281186-83118017446-98119924781-27868548476','0000');

If the unique key is an int data type, there may be some duplicate values that cannot be inserted. But if it is a char data type, both can be inserted.

I know that the principle of set unique_checks=0 is to speed up the insertion of unique keys through the change buffer, which does not seem to fully guarantee uniqueness. If this is really not a bug, I hope the manual can be written more clearly, rather than vaguely described.

Hello chengqing hu,

Thank you for the report.
IMHO this is not a bug, unique_checks=0 is only intended to be used when you know that data doesn't have duplicates and documentation is pretty clear on this. Quoting from the manual " If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, storage engines are permitted to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0 to speed up large table imports to InnoDB.

Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them. " - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_unique_checks

regards,
Umesh

"Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them. "

It is this sentence that is confusing, and perhaps it would be more appropriate to add this description to the manual:
If the inserted data, the corresponding unique index page is not in the innodb buffer pool, the uniqueness detection will not be performed

This function can cause a series of serious data problems, such as 
unique key failure: allowed to insert two data of the same uk;

replication interruption: the data inserted successfully by the primary may fail to be inserted in the replica.

inconsistent query results through uk and pk.