Why REPLACE INTO Breaks Auto‑Increment After Master‑Slave Switch

Background: Strange error after master‑slave switch

A client reported that after promoting a replica to master, inserting new rows caused a duplicate‑key error (1062) because the primary key conflicted with the AUTO_INCREMENT value.

Initial investigation: Who changed my auto‑increment ID?

In MySQL the AUTO_INCREMENT column works like a ticket number; normal DELETE or UPDATE operations do not affect it, only TRUNCATE or ALTER TABLE … AUTO_INCREMENT reset it.

On‑site reproduction: How REPLACE INTO works behind the scenes

1. Create test table

CREATE TABLE tb_test (
  id INT 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;

2. Insert base data

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

3. Initial state (normal)

On both master and replica:

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

Thus MAX(id)=4 and AUTO_INCREMENT=5 are consistent.

4. Magic moment: Execute REPLACE INTO

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

After this operation:

Master:

SELECT MAX(id) FROM tb_test;          -- result: 5 (new ID created)
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name='tb_test';
-- result: 6 (next ID prepared)

Replica:

SELECT MAX(id) FROM tb_test;          -- result: 5 (new ID synced)
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name='tb_test';
-- result: 5 (still unchanged)

The replica’s AUTO_INCREMENT did not advance, so when the replica is promoted, an INSERT tries to use ID=5, which already exists, producing the duplicate‑key error.

Truth revealed: REPLACE INTO’s hidden behavior

According to the MySQL 5.7 documentation, REPLACE INTO works like INSERT, but if the new row conflicts on a PRIMARY KEY or UNIQUE index, MySQL first deletes the existing row and then inserts the new one.

However, the binary log records the operation as an UPDATE, not as a DELETE+INSERT. Therefore the replica replays an UPDATE that changes the existing row’s ID from 5 to 6 without requesting a new AUTO_INCREMENT value.

Key points

In the binlog REPLACE INTO is logged as UPDATE, not as DELETE+INSERT.

On the master, REPLACE INTO consumes a new auto‑increment value (e.g., 6); on the replica, the UPDATE only modifies the existing row, leaving its AUTO_INCREMENT unchanged.

This discrepancy causes the replica’s AUTO_INCREMENT to lag behind the master, leading to primary‑key conflicts after failover.

Mitigation strategies

Avoid using REPLACE INTO in high‑traffic tables; prefer INSERT … ON DUPLICATE KEY UPDATE or explicit DELETE+INSERT.

Upgrade to MySQL 8.0, which improves binlog recording for REPLACE INTO.

Manually align the replica’s AUTO_INCREMENT after a switch, e.g.:

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

Design tables to avoid combining an auto‑increment primary key with additional unique indexes when possible; if a unique index is required, consider alternative conflict‑resolution strategies.

Conclusion

In MySQL 5.7 and earlier replication environments, REPLACE INTO behaves as DELETE+INSERT on the master but is logged as UPDATE in the binlog, causing the replica’s AUTO_INCREMENT to fall behind. Understanding this mechanism allows you to choose appropriate avoidance or correction measures to prevent primary‑key conflicts after master‑slave promotion.