When MySQL Auto‑Increment IDs Hit Their Limit: Errors and Fixes

When the auto‑increment ID in MySQL reaches its maximum (2^31‑1 for signed INT or 2^32‑1 for unsigned INT), the database will either throw a duplicate‑key error or, if no primary key is defined, start reusing its internal row_id, potentially overwriting existing rows.

1. With Primary Key

If a table has a primary key defined as an INT and uses AUTO_INCREMENT, the range is limited to 2,147,483,647 (signed) or 4,294,967,295 (unsigned). The following example creates such a table and inserts a row that reaches the signed maximum:

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4;

INSERT INTO test1(name) VALUES('qq');

Attempting another insert produces the error:

1062 - Duplicate entry '2147483647' for key 'PRIMARY'

The solution is to change the primary key column to BIGINT (8 bytes) so the limit becomes effectively unreachable for most applications.

2. Without Primary Key

If a table lacks an explicit primary key, InnoDB automatically creates a 6‑byte unsigned row_id with a maximum of 2^48‑1. The following table demonstrates this case:

CREATE TABLE `test2` (
  `name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

By attaching a debugger to the MySQL process, the internal row_id can be manually set:

sudo gdb -p 2584 -ex 'p dict_sys->row_id=1' -batch

Inserting three rows then resetting row_id to its maximum (281474976710656) and inserting three more rows results in the new rows receiving row_id values that start from zero again, causing the later inserts to overwrite the earlier ones.

Summary

When an auto‑increment ID reaches its limit:

If a primary key is defined, MySQL throws a duplicate‑key error.

If no primary key exists, InnoDB reuses its internal row_id, and new rows may overwrite older data.

Best practice: always define a primary key and use BIGINT for the column type to avoid hitting the 2‑billion limit.