Inconsistent AUTO_INCREMENT in MySQL 5.7 Replication Caused by REPLACE INTO

This article explains a common issue in MySQL 5.7 where the REPLACE INTO statement can cause the AUTO_INCREMENT values of the master and slave to become inconsistent. When a master‑slave switch occurs, normal insert operations on the new master may trigger primary‑key duplicate errors.

Problem Description

In MySQL 5.7, using REPLACE INTO on a table that has an auto‑increment primary key and a unique index may lead to a mismatch of AUTO_INCREMENT values between the master and the slave. If a failover happens while the values are out of sync, the application receives an error such as:

ERROR 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'

Impact Assessment

If the business logic relies on REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE , the inconsistency can cause repeated duplicate‑key errors after a failover. When the difference between master and slave AUTO_INCREMENT values is large, retries may exhaust and the service will be blocked.

Reproduction Steps

1. Environment setup: MySQL 5.7 community edition with a one‑master‑one‑slave architecture on CentOS 7.3.

Table definition:

CREATE TABLE `test_autoincrement` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert some test rows:

MySQL [test2023]> insert into test_autoincrement(name,uid) select '张三',1001;
Query OK, 1 row affected (0.08 sec)
MySQL [test2023]> insert into test_autoincrement(name,uid) select '李四',1002;
Query OK, 1 row affected (0.06 sec)
MySQL [test2023]> insert into test_autoincrement(name,uid) select '王五',1003;
Query OK, 1 row affected (0.08 sec)

At this point both master and slave have AUTO_INCREMENT = 4.

2. Simulate REPLACE INTO :

MySQL [test2023]> REPLACE INTO test_autoincrement (name,uid) values('张三丰',1001);
Query OK, 2 rows affected (0.01 sec)

After the replace, the slave shows a different AUTO_INCREMENT value, demonstrating the inconsistency.

3. Simulate master‑slave switch by stopping the slave, checking Executed_Gtid_Set , and re‑establishing replication (commands omitted for brevity).

4. Attempt a normal insert after the switch:

MySQL [test2023]> insert into test_autoincrement(name,uid) select '赵六',1004;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

The duplicate‑key error confirms the problem.

Root Cause Analysis

3.1 Why does the slave’s AUTO_INCREMENT not change?

The binlog for the REPLACE INTO operation is recorded as an UPDATE event. When the slave applies the update, it does not increment the auto‑increment counter because the operation does not involve inserting a new auto‑generated value.

3.2 Official definition of REPLACE INTO (excerpt from MySQL manual):

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

3.3 Why is the binlog recorded as an UPDATE ?

Inspection of sql_insert.cc and log_event.cc shows that MySQL may convert a REPLACE into an UPDATE when the conflicting key is the last unique key, there are no foreign‑key constraints, and no triggers are present. In our test table these conditions are met, so the slave receives an UPDATE event, leaving its AUTO_INCREMENT unchanged.

Additional experiments without the extra unique index ( uid ) confirm that the inconsistency disappears because the replace operation no longer triggers an auto‑increment change.

Solutions

4.1 Upgrade to MySQL 8.0 or later. Starting with 8.0, AUTO_INCREMENT values are persisted and updated even during replace‑style updates.

4.2 Manually adjust AUTO_INCREMENT values. A routine inspection can compare MAX(id) with AUTO_INCREMENT on the slave and issue ALTER TABLE ... AUTO_INCREMENT = new_value when necessary.

select TABLE_SCHEMA,TABLE_NAME,AUTO_INCREMENT from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') and AUTO_INCREMENT is not null \G

Lock the table, read MAX(id) , compare, and if MAX(id) >= AUTO_INCREMENT treat it as abnormal.

lock tables table_name write;
MAXID = select max(id) from table_name;
AUTO_INCREMENT = select AUTO_INCREMENT from information_schema.tables where TABLE_NAME='t1';
-- if MAXID >= AUTO_INCREMENT, raise alert or fix

4.3 Disable REPLACE INTO (or INSERT ... ON DUPLICATE KEY UPDATE ) in application code.

4.4 Avoid adding extra unique indexes to tables that use REPLACE INTO , because the presence of a unique key is what triggers the auto‑increment mismatch.

Summary

The REPLACE INTO statement on tables with an auto‑increment primary key and an additional unique index can cause master‑slave AUTO_INCREMENT divergence in MySQL 5.7. After a failover, inserts may fail with duplicate‑key errors. The issue is a known MySQL bug (not fixed in 5.7) and can be mitigated by upgrading to MySQL 8.0, adjusting AUTO_INCREMENT manually, or redesigning the application to avoid REPLACE INTO usage.