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:

<code>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');
</code>

Attempting another insert produces the error:

<code>1062 - Duplicate entry '2147483647' for key 'PRIMARY'</code>

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:

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

By attaching a debugger to the MySQL process, the internal

row_id

can be manually set:

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

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.