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

Auto‑Increment Primary Key Limits

MySQL defines an auto‑increment column with a starting value and step size, but the underlying storage type imposes a maximum (e.g., UNSIGNED INT maxes at 2^32‑1). When the limit is reached, requesting the next ID returns the same value, causing a duplicate‑key error on subsequent inserts.

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 expected to generate many rows should use BIGINT UNSIGNED to avoid exhausting the range.

InnoDB System Row_ID

If an InnoDB table lacks an explicit primary key, the engine creates an invisible 6‑byte row_id . A global dict_sys->row_id is incremented for each inserted row. The stored value occupies only the lower 6 bytes of an 8‑byte BIGINT UNSIGNED, giving a range of 0 to 2^48‑1.

When the counter reaches 2^48‑1, it wraps to 0. If a new row receives a row_id that already exists, the new row overwrites the previous one, leading to data loss.

Verification using GDB (shown in the images) sets dict_sys.row_id to 2^48, inserts rows, and observes that the first insert after wrap writes to the first physical row (row_id 0), and subsequent inserts overwrite earlier rows.

Xid Generation

Both the redo log and binlog contain an Xid field that links a statement to its transaction. MySQL maintains a global in‑memory variable global_query_id. Each statement increments this counter; the first statement of a transaction also copies the value to the transaction’s Xid.

Because global_query_id is reset on server restart, different transactions in the same instance may share the same Xid, but a new binlog file guarantees uniqueness within that file. The counter is an 8‑byte integer (max 2^64‑1), so a duplicate Xid in a single binlog would require an astronomically large number of statements and is practically impossible.

InnoDB trx_id

InnoDB maintains a separate transaction identifier max_trx_id. Each time a new transaction needs a trx_id, the current max_trx_id value is assigned and then incremented. Read‑only transactions do not allocate a trx_id, reducing the size of the active‑transaction array and avoiding unnecessary lock contention.

The visible trx_id shown in information_schema.innodb_trx is calculated as the pointer address of the internal transaction structure plus a constant 248. This makes read‑only trx_id values appear large and distinct from read‑write trx_id values.

Thread ID Counter

MySQL tracks connections with a global thread_id_counter, a 4‑byte integer that wraps at 2^32‑1. When a new client connects, the counter value is assigned to the connection’s new_id. Internally MySQL stores thread IDs in a unique array, ensuring that SHOW PROCESSLIST never displays duplicate thread IDs even after wrap‑around.

Summary of Behaviors

When a table’s auto‑increment column reaches its maximum, further inserts receive the same ID, leading to duplicate‑key errors.

InnoDB’s invisible row_id wraps to 0 after 2^48‑1; duplicate row_id values cause later rows to overwrite earlier ones.

Xid values are unique per binlog file; duplicates across restarts are theoretically possible but negligible.

InnoDB’s max_trx_id persists across restarts; if it reaches 2^48‑1, it wraps to 0, potentially exposing a rare dirty‑read bug.

Read‑only transactions skip trx_id allocation to improve performance and reduce memory usage.

Thread IDs wrap at 2^32‑1 but remain unique in observable output due to internal bookkeeping.