What Happens When MySQL Auto‑Increment IDs Reach Their Limit?

Auto‑Increment ID Overflow

MySQL defines an auto_increment column with an initial value and step, but the maximum is limited by the column's byte size. For an INT UNSIGNED (4 bytes) the limit is 2^32‑1 (4294967295). When this limit is reached, the next request returns the same value, causing a duplicate‑key error.

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;

mysql> insert into t values (null);   -- succeeds, id = 4294967295

mysql> insert into t values (null);   -- ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

Therefore, tables that may exhaust the range should use BIGINT UNSIGNED (8 bytes).

InnoDB Invisible row_id

If an InnoDB table lacks a primary key, InnoDB creates an invisible 6‑byte row_id. Internally it is stored as an 8‑byte BIGINT UNSIGNED, but only the lower 6 bytes are written, giving a range of 0 … 2^48‑1. When the maximum is reached the counter wraps to 0, so new rows can reuse existing row_id values and overwrite previous rows.

Example verification (using gdb to set dict_sys.row_id to 2^48) shows that subsequent inserts start from row_id=0 and replace earlier rows.

Xid Generation

Both the redo log and binlog contain an Xid that identifies a transaction. MySQL maintains a global variable global_query_id. Each statement increments this variable and assigns the new value to query_id. If the statement is the first of a transaction, the same value is also stored as the transaction’s Xid. global_query_id is an 8‑byte counter that resets to zero after a server restart, so the same Xid can appear in different server lifetimes, but not within the same binlog file. The theoretical collision requires 2^64 statements, which is practically impossible.

InnoDB trx_id

InnoDB also tracks a separate transaction identifier called trx_id. A global variable max_trx_id is incremented each time a new transaction requests an ID. The value is stored in every row; visibility checks compare a row’s trx_id with the transaction’s snapshot.

Read‑only transactions do **not** obtain a trx_id to reduce the size of the active‑transaction array and avoid unnecessary lock contention. Consequently, the growth of trx_id is slower than that of row_id.

mysql> use information_schema;
mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+-----------------+---------------------+
| trx_id          | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 | 70                  |
+-----------------+---------------------+

Only write transactions receive a non‑zero trx_id; read‑only ones show 0.

Potential Dirty‑Read Bug When max_trx_id Wraps

max_trx_id

is persisted across restarts. If it reaches 2^48‑1 and wraps to 0, the low‑water‑mark of a running transaction can become larger than the trx_id of newly inserted rows. This makes the engine consider those rows visible, producing a dirty read.

Reproducing the bug:

Set max_trx_id to 2^48‑1 (e.g., via gdb).

Start a transaction (session A). Its low‑water‑mark is now 2^48‑1.

In another session (B) execute an UPDATE; the new row gets trx_id=0.

Session A reads the row; because 0 < 2^48‑1, the row is deemed visible – a dirty read.

Although reaching this state would require many years of continuous high‑throughput operation, it demonstrates the importance of monitoring max_trx_id.

Thread ID Counter

MySQL maintains a global thread_id_counter (4 bytes). Each new connection receives the current counter value as its thread_id, after which the counter increments. When the counter reaches 2^32‑1 it wraps to 0, but MySQL’s internal array guarantees that duplicate IDs never appear in SHOW PROCESSLIST.

Summary

When a table’s auto_increment reaches its limit, further inserts return the same value, causing primary‑key conflicts.

InnoDB row_id wraps to 0 after 2^48‑1, potentially overwriting existing rows. Xid collisions are theoretically possible after 2^64 statements but are negligible in practice. max_trx_id persists across restarts; if it wraps, a dirty‑read bug can appear, though it would take many years of operation.

Read‑only transactions skip trx_id allocation to reduce overhead. thread_id also wraps after 2^32‑1 but MySQL ensures uniqueness in the process list.