MySQL's auto-increment limit is easy to overlook—until a table actually reaches it. The behavior is straightforward for explicitly defined integer keys, but tables without a declared primary key have a less obvious risk.

Testing the limit with an unsigned INT

Start with a minimal table containing only an auto-incrementing ID, then insert one row:

create table t0(id int unsigned auto_increment primary key) ;
insert into t0 values(null);

The table definition can be inspected with show create table t0;:

CREATE TABLE t0 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

After the first insert, the next auto-increment value has already advanced to 2. Since the column is declared as int unsigned, its maximum value is:

2^32 - 1 = 4294967295

That range is large enough for many applications. To test what happens near the upper boundary, the initial auto-increment value can be specified when the table is created:

create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967295;
insert into t1 values(null);

Checking the definition again shows that the current auto-increment value has reached 4294967295:

CREATE TABLE t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

If another row is inserted, MySQL does not produce a new value beyond the column's maximum. Instead, it attempts to use 4294967295 again and the insert fails with a primary-key conflict:

insert into t1 values(null) Error Code: 1062. Duplicate entry '4294967295' for key 'PRIMARY' 0.00054 sec

In other words, once the unsigned INT range is exhausted, the next auto-increment attempt can reuse the maximum value rather than extending the range, resulting in a duplicate primary-key error.

4294967295 is sufficient for most ordinary workloads. However, services that insert and delete rows continuously may eventually face the same limit. In that situation, using bigint unsigned provides a much larger range and is generally the safer choice.

The less obvious case: no declared primary key

A different issue appears when a table is created without explicitly declaring a primary key. For an InnoDB table in this situation, InnoDB creates an invisible six-byte row_id internally.

InnoDB maintains this value globally through dictsys.row_id, so tables without their own primary key share the same counter. Each inserted row receives the current global row_id, after which the counter is incremented.

The implementation uses a bigint unsigned-style value, but only six bytes are reserved for the actual row ID. This means the effective range is limited to 48 bits. If the global counter keeps increasing until it reaches 2^48 - 1, incrementing it again causes the lower 48 bits to wrap to zero. A newly inserted row may then receive row_id 0, creating the possibility of a primary-key conflict.

For that reason, every table should define its own primary key instead of relying on InnoDB's hidden row ID. Explicitly choosing an appropriate key type—such as bigint unsigned when the workload requires a very large range—also avoids the limits and ambiguity of implicit identifiers.