Alternative testcase

SET autocommit=0,unique_checks=0,foreign_key_checks=0;

CREATE TABLE t (i INT UNIQUE);

INSERT INTO t VALUES (0),(0);

CHECK TABLE t;

Interestingly, this testcase produces no errors in the CLI output:

SET sql_mode='',autocommit=0,unique_checks=0,foreign_key_checks=0;

CREATE TABLE t (c INT AUTO_INCREMENT KEY,c2 CHAR(1) NOT NULL,UNIQUE INDEX uc2 (c2));

INSERT INTO t VALUES(),();

DELETE FROM t;

Yet there is clearly corruption recorded in the log:

2021-11-01  9:03:57 0 [Note] /test/MD301021-mariadb-10.7.1-linux-x86_64-opt/bin/mysqld: ready for connections.

Version: '10.7.1-MariaDB'  socket: '/test/MD301021-mariadb-10.7.1-linux-x86_64-opt/socket.sock'  port: 31608  MariaDB Server

2021-11-01  9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

2021-11-01  9:04:03 4 [ERROR] InnoDB: Record in index `uc2` of table `test`.`t` was not found on update: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000002)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` of table `test`.`t` was not found on rollback, trying to insert: TUPLE (info_bits=0, 2 fields): {[1] (0x20),[4]    (0x80000001)} at: COMPACT RECORD(info_bits=0, 1 fields): {[8]infimum (0x696E66696D756D00)}

2021-11-01  9:04:04 4 [Warning] InnoDB: Record in index `uc2` was not found on rollback, and a duplicate exists

Thus, besides fixing the bug, a secondary action item would be to improve the error flow so the CLI output shows a corrupt table message, alike to the examples above.

I think that you got what you asked for, by executing SET unique_checks=0 and then inserting data that violates a UNIQUE constraint.

InnoDB started to honor the session parameter unique_checks in MySQL 4.0.18. The parameter had been introduced in MySQL 4.0.3.

Even before MDEV-24621 or MDEV-515, it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help.

For compatibility with files generated by mysqldump, serg suggested that we use foreign_key_checks=0,unique_checks=0 for enabling the bulk insert feature. This was implemented in MDEV-24818. A more proper solution is still waiting to be implemented, in MDEV-25036.

All that said, I agree that the CHECK TABLE output for index c2 looks strange. Why does it say contains 0 entries, instead of 1 or 2?

I think the problem here is previous user experience, or a lack thereof.

Because at least in recent history hardly anyone could ever make this option work, users might not realize that it's far from innocent and can corrupt a table, and the option description is way too light for this to to expected. MySQL's a bit better as it at least mentions user's responsibility for the input data, but still, no mention of actual damage.

Facing the question what effect I myself would expect, I couldn't come up with any sensible answer right away. Indeed, come to think of it, that's the only outcome, but it never occurred to me before. After some thinking, I would say that as a user, I'd expect the table somehow complain about data inconsistency (so errors in CHECK are fine, as long as they are accurate), but then I'd expect to still be able to handle the data – select it and remove the non-unique values or ALTER-IGNORE the table to get rid of them; and currently it's apparently impossible to do either, instead one should fix the structure – dropping the index should fix it, I presume. I guess it needs to be documented somehow now when the option works.

As per discussion with Marko added comment in MDEV-25036 to improve the cryptic error message.

I wrote earlier in this ticket:

Even before MDEV-24621 or MDEV-515, it should have been theoretically possible to have InnoDB skip the unique key check, if the affected unique secondary index leaf page is not located in the buffer pool and the change buffer is being used. It might be difficult in practice, but the debug parameter innodb_change_buffering_debug=1 could help.

Yesterday, mleich produced an rr replay trace while testing MDEV-30100 on MariaDB Server 10.6. In that trace, an INSERT IGNORE that would violate a UNIQUE INDEX constraint did not lead to a rollback of the duplicate row, because unique_checks=0 allowed the InnoDB change buffer to be used. The duplicate key error was caught some time later during the execution, by a failure of ALTER TABLE…FORCE.