Why MySQL Unique Index Fails with unique_checks=0 and How to Resolve It

Problem

In a MySQL 5.7.38 master‑slave setup, inserting a row that duplicates a value on a unique index (which is not the primary key) triggers error 1062 on all slaves, causing the SQL thread to stop. The binlog position shows the duplicate insert.

Cause

When the server variable unique_checks is set to 0, MySQL skips physical reads for unique‑index maintenance and only checks the in‑memory index. If a conflict exists in memory, error 1062 is raised; otherwise the insert succeeds without loading the index pages from disk. This behavior is documented as bug #106121, and MySQL’s response was “IMHO this is not a bug”.

Solution

Temporary fix

On the slave, start a session and execute SET sql_log_bin=0.

Drop the problematic unique index.

Restart the replication threads.

Drawback: the duplicate data remains, and queries that rely on the original unique index may suffer performance degradation, but replication can resume.

Permanent fix

Ensure the application does not insert duplicate rows (deduplicate before insert).

Keep unique_checks set to 1 (default).

Coordinate with business owners to decide how to handle existing duplicate data.

Reproduction Steps

1. Table definition

mysql> create database wl;
mysql> show create table wl.lgf\G
*************************** 1. row ***************************
       Table: lgf
Create Table: CREATE TABLE `lgf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8

2. Generate random data

import random
import os
while True:
    i = str(random.randint(1000, 8000000))
    a = str(random.randint(1000000000000000, 8000000000000000))
    b = str(random.randint(1000000000000000, 8000000000000000))
    c = str(random.randint(100000, 800000))
    sql = "insert ignore into lgf(id,k,c,pad) values(%s,%s,%s,%s) " % (i, c, a, b)
    os.system('mysql -uroot -p123456 -h127.0.0.1 -P3306 -e "use wl;%s"' % sql)

3. Verify initial rows

SELECT * FROM wl.lgf ORDER BY id LIMIT 10;
+------+--------+------------------+------------------+
| id   | k      | c                | pad              |
+------+--------+------------------+------------------+
| 1058 | 162327 | 1693367460515515 | 4503256156555111 |
| ...  | ...    | ...              | ...              |
+------+--------+------------------+------------------+

4. Insert a duplicate unique‑index row

INSERT INTO wl.lgf(id,c,pad) VALUES(10000000,'3344825394389018','7962994492618902');

5. Restart MySQL to clear caches (optional)

Modify my.cnf to set innodb_buffer_pool_load_at_startup=0 and innodb_buffer_pool_dump_at_shutdown=0, then restart the instance.

6. Re‑insert with unique_checks=0

SET unique_checks=0;
INSERT INTO wl.lgf(id,c,pad) VALUES(10000000,'3344825394389018','7962994492618902');
-- Query OK, 1 row affected

7. Verify both rows exist using FORCE INDEX

SELECT * FROM wl.lgf FORCE INDEX(primary) WHERE c='3344825394389018' AND pad='7962994492618902';
+----------+--------+------------------+------------------+
| id       | k      | c                | pad              |
+----------+--------+------------------+------------------+
| 1360    | 403078 | 3344825394389018 | 7962994492618902 |
| 10000000| 0      | 3344825394389018 | 7962994492618902 |
+----------+--------+------------------+------------------+

References

MySQL Bugs: #106121 – Unique key constraint invalid – https://bugs.mysql.com/bug.php?id=106121

MySQL 8.0 Reference Manual – Server System Variables – unique_checks – https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_unique_checks