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

Table‑defined auto‑increment ID

When a table's AUTO_INCREMENT reaches its maximum (e.g., 2^32‑1 for UNSIGNED INT), MySQL returns the same value for subsequent inserts, causing duplicate‑key errors.

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.00 sec)

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

Therefore, tables that may exhaust the limit should use BIGINT UNSIGNED instead.

InnoDB system row_id

If an InnoDB table has no explicit primary key, InnoDB creates an invisible 6‑byte row_id. The global dict_sys->row_id is incremented for each inserted row. The stored value ranges from 0 to 2^48‑1; after reaching the limit it wraps to 0, causing later rows to overwrite earlier ones.

Demonstration using gdb shows that setting dict_sys.row_id to 2^48 makes the next insert receive row_id 0, overwriting the first row, and subsequent inserts continue overwriting.

Xid generation

Both redo log and binlog share the Xid field to identify a transaction. MySQL maintains a global variable global_query_id; each statement increments it and the first statement of a transaction copies the value to the transaction's Xid.

global_query_id

is an in‑memory 8‑byte counter that resets on server restart, so Xid may repeat across restarts, but within a single binlog file Xid values are unique.

InnoDB trx_id

InnoDB also has its own transaction identifier ( trx_id) maintained by the max_trx_id global variable. Read‑only transactions do not allocate a trx_id, which reduces overhead. trx_id values are stored with each row and used for visibility checks.

Large trx_id numbers shown in information_schema.innodb_trx are derived from the address of the transaction structure plus a constant (248), ensuring read‑only transactions have high visible IDs.

Thread ID

MySQL assigns a thread_id to each connection from a global 4‑byte counter. When the counter reaches 2^32‑1 it wraps to 0, but the server ensures uniqueness in the thread‑id array, so duplicate IDs are not shown in SHOW PROCESSLIST.

Summary

Different auto‑increment mechanisms behave differently at their limits: table AUTO_INCREMENT stops increasing and causes duplicate‑key errors; InnoDB row_id wraps to 0 and overwrites data; Xid repeats only across binlog files; max_trx_id persists across restarts and can eventually trigger a theoretical dirty‑read bug when it wraps.