How an Unexpected Primary Key Update Triggered a MySQL Auto‑Increment Bug

1. Introduction

Developers reported a duplicate key error when inserting records, with MySQL returning ERROR 1062 (Duplicate entry '2147483647' for key 'PRIMARY'). The test data reproduced the production issue.

2. Analysis

The test environment used Percona Server 5.7.20. Table t2 structure shows id as a signed INT reaching its maximum value (2147483647) and no timestamp column to record insertion time.

mysql> select * from t2 order by id desc limit 3;
+------------+------+------+ 
| id         | c1   | c2   |
+------------+------+------+ 
| 2147483647 | 101  | 101  |
| 100        | 100  | 100  |
| 4          | 4    | 4    |
3 rows in set (0.00 sec)

mysql> show create table t2\G
...
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Investigation showed the id column had reached the signed INT limit, but the record was originally inserted with id=101. Binlog analysis revealed an UPDATE statement that changed the primary key to 4147483647, causing overflow to 2147483647.

The mistaken primary‑key update did not adjust the table’s auto_increment value, which remained 101. Normal inserts continued to work until a later DDL operation recalculated auto_increment to 2147483647, at which point new inserts failed with duplicate‑key errors.

3. Conclusion

When a primary key is manually updated to a value larger than the current auto_increment, MySQL does not automatically adjust the auto‑increment counter, leaving a hidden “time bomb”. The issue appears in Percona Server 5.6.24 and 5.7.20, but is fixed in MySQL 8.0.11. The bug was originally reported in 2005 (Bug #12434) but remains unresolved due to low impact.

References

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

https://bugs.mysql.com/bug.php?id=12434