MySQL Replication Gap: REPLACE INTO’s Hidden AUTO_INCREMENT Trap

1. Background: The Mysterious Post-Failover Error​
A client reported a puzzling issue: after MySQL primary-replica switch, inserting data into the new primary (former replica) triggered ERROR 1062 - Duplicate entry ... for key 'PRIMARY'.

Investigations revealed that on the former replica, the actual maximum auto-increment ID (MAX(id)) exceeded the AUTO_INCREMENT value shown in SHOW CREATE TABLE. This contradicted expectations since MAX(id) should typically be 1 less than AUTO_INCREMENT.

​2. Initial Analysis: Who Tampered with the AUTO_INCREMENT?​​
The auto-increment ID (AUTO_INCREMENT) acts as the "next ticket" for new data. Normally:

• DELETE/UPDATE operations ​don’t​ reset AUTO_INCREMENT.
• Only TRUNCATE TABLE or explicit ALTER TABLE ... AUTO_INCREMENT=... resets it.

Why was MAX(id) > AUTO_INCREMENT? The client’s heavy use of REPLACE INTO became the prime suspect.

​3. Reproduction: How REPLACE INTO Secretly Skips Tickets​
We reproduced the issue in MySQL 5.7.39:

​3.1 Create a test table​ with auto-increment primary key and unique index:

CREATE TABLE tb_test (  
  id INT(11) NOT NULL AUTO_INCREMENT,  
  unique_code VARCHAR(20) COLLATE utf8mb4_bin DEFAULT NULL,  
  PRIMARY KEY (id),  
  UNIQUE KEY unique_code (unique_code)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;  

‍

3.2 Insert initial data:​

INSERT INTO tb_test(unique_code) VALUES ('code1'), ('code2'), ('code3'), ('code4');  
-- Query OK, 4 rows affected  

‍

3.3 Initial state (normal):​​

Both primary & replica:

SELECT MAX(id) FROM tb_test;          -- 4  
SELECT AUTO_INCREMENT FROM information_schema.tables   
WHERE table_name='tb_test';           -- 5  

‍

​3.4 Execute REPLACE INTO:​

REPLACE INTO tb_test(unique_code) VALUES ('code1');  -- 'code1' exists!  
-- Query OK, 2 rows affected  

‍

3.5 Post-execution state:​​

​Primary:

SELECT MAX(id) FROM tb_test;          -- 5 (new ID generated)  
SELECT AUTO_INCREMENT...;             -- 6 (ready for next)  

‍

Replica:

SELECT MAX(id) FROM tb_test;          -- 5 (new ID replicated)  
SELECT AUTO_INCREMENT...;             -- 5 (stalled  

‍

Gap identified:​​ The replica’s AUTO_INCREMENT remains unchanged.

​3.6 Simulate failover:​​
After promoting the replica to primary:

INSERT INTO tb_test(unique_code) VALUES ('code5');  
-- ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'  

‍

Conflict occurs because the new primary tries to reuse ID=5 (already taken by the REPLACE INTO operation).

​4. Root Cause: REPLACE INTO’s Dual Personality​
MySQL 5.7 documentation states:

REPLACE INTO works like INSERT but deletes conflicting rows first.

However, binlog analysis reveals the truth:

### UPDATE `test`.`tb_test`          -- Logs as UPDATE, not DELETE+INSERT!  
### WHERE  
###   @1=5                           -- Original ID=5 (conflicting row)  
###   @2='code1'  
### SET  
###   @1=6                           -- New ID=6 assigned  
###   @2='code1'  

‍

Critical divergence:​​

• ​Primary:​​ Executes as DELETE + INSERT → ​consumes new ID​ → AUTO_INCREMENT=6.
• ​Replica:​​ Applies binlog’s UPDATE → ​only modifies ID​ → AUTO_INCREMENT remains ​5.

This binlog logging discrepancy causes the AUTO_INCREMENT gap after REPLACE INTO.

​5. Solutions: Avoiding the Pitfall​

1. ​Avoid REPLACE INTO​ where possible (most robust solution).
2. ​Upgrade to MySQL 8.0+​​ (fixes binlog logging behavior).
3. ​Manually resync AUTO_INCREMENT​ (pre-failover):

ALTER TABLE your_table AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM your_table);  

‍

1. For high concurrency, use pt-online-schema-change.
2. ​Reevaluate table design: Avoid combining auto-increment PKs with unique indexes if possible.

​Conclusion​
In MySQL ≤5.7, REPLACE INTO causes replication divergence between primary (DELETE+INSERT) and replica (UPDATE), leading to AUTO_INCREMENT gaps and post-failover primary key conflicts. Understanding this mechanism is key to choosing the right mitigation.

‍