Risks of Using REPLACE INTO with Auto-Increment Primary Keys in MySQL

1 Introduction

This section explains that the example will illustrate the potential data‑quality risks introduced by REPLACE INTO when the target table contains an auto‑increment primary key, especially after a master‑slave switch.

2 Case Analysis

Operations performed on the master database are shown, followed by checks on both master and replica tables.

root@test 12:36:51>show create table t1 \G
*************************** 1. row ***************************
        Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:37:41>insert into t1(name) values('a')

Both master and replica show AUTO_INCREMENT=2 after the insert.

root@test 12:37:51>show create table t1 \G
*************************** 1. row ***************************
        Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Now REPLACE INTO is executed twice on the master.

root@test 12:37:58>replace into t1(name) values('a');
root@test 12:38:40>replace into t1(name) values('a');
root@test 12:38:49>select * from t1;
+----+------+
| id | name |
+----+------+
| 3  | a    |
+----+------+
1 row in set (0.00 sec)

After the replaces, the master table’s AUTO_INCREMENT becomes 4.

root@test 12:38:51>show create table t1 \\G
*************************** 1. row ***************************
        Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The replica still shows AUTO_INCREMENT=2 .

oot@test 12:39:35>show create table t1 \G
*************************** 1. row ***************************
        Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:39:43>select * from t1;
+----+------+
| id | name |
+----+------+
| 3  | a    |
+----+------+
1 row in set (0.00 sec)

Analysis

The replica’s AUTO_INCREMENT remains 2 while the master’s is 4. Although REPLACE with a unique‑key conflict internally performs a delete‑plus‑insert (which would normally advance the auto‑increment), the binlog records the operation as an UPDATE . Since an UPDATE does not modify the auto‑increment counter, the replica does not change its counter when replaying the binlog.

3 Risk Points

If a master‑slave switch occurs and the replica becomes the new master, subsequent REPLACE INTO operations can produce unexpected primary‑key values.

root@test 12:40:46>replace into t1(name) values('a');  Query OK, 2 rows affected (0.00 sec)
root@test 12:40:48>select * from t1;
+----+------+
| id | name |
+----+------+
| 2  | a    |  --- id changed from 3 to 2
+----+------+
1 row in set (0.00 sec)

If the table already contains multiple rows, a switch may cause primary‑key conflicts when new writes are performed.

4 Summary

Because REPLACE INTO modifies the primary‑key value when a conflict occurs, it should not be used in applications that rely on stable auto‑increment IDs; even in normal environments it is discouraged, as it can lead to primary‑key re‑organization and data‑consistency issues.