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

Table AUTO_INCREMENT overflow

MySQL tables using INT UNSIGNED AUTO_INCREMENT have a maximum value of 2^32‑1 (4294967295). When this limit is reached, the next INSERT returns the same value, causing a duplicate‑key error as demonstrated:

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 approach this limit should use BIGINT UNSIGNED (8‑byte) instead.

InnoDB hidden 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 counter reaches 2^48‑1 it wraps to 0, and subsequent inserts reuse existing row_id values, overwriting earlier rows.

Verification using gdb to set dict_sys.row_id to 2^48 shows the first new row receives row_id=0, the next receives row_id=1, and so on, confirming the wrap‑around.

Xid generation

Each statement receives a global global_query_id (8‑byte, max 2^64‑1). The first statement of a transaction copies this value to the transaction’s Xid. The counter resets to zero after a MySQL restart, so Xid values can repeat across restarts, but within a single binlog file they remain unique. Theoretical duplication would require 2^64 statements, which is practically impossible.

InnoDB trx_id and visibility

InnoDB maintains a global max_trx_id. Each new transaction obtains the current max_trx_id as its trx_id and then increments the counter. Rows store the trx_id that created them; a transaction sees a row only if its own view’s low‑water‑mark is greater than the row’s trx_id.

Read‑only transactions do not allocate a trx_id, which reduces the size of the active‑transaction array and avoids unnecessary lock contention. Consequently, read‑only trx_id values appear large (derived from pointer addresses plus an offset of 248) and differ from read‑write transaction IDs.

Because max_trx_id persists across restarts, a long‑running instance could eventually wrap at 2^48‑1. When this occurs, a bug can cause dirty reads: a transaction with a low‑water‑mark near the wrap point may consider rows with trx_id=0 as visible, leading to inconsistent results.

Thread ID allocation

MySQL tracks a global thread_id_counter (4‑byte). Each new connection receives the current counter value as its thread_id and then increments the counter. When the counter reaches 2^32‑1 it wraps to 0. The server maintains a unique‑id array, so duplicate thread_id values never appear in SHOW PROCESSLIST.

Summary of overflow behaviors

Table AUTO_INCREMENT stops increasing at its maximum and raises a duplicate‑key error on further inserts.

InnoDB row_id wraps to zero, causing later rows to overwrite earlier ones if the counter overflows.

Xid values are unique within a binlog file; cross‑restart duplication is possible but extremely unlikely. max_trx_id persists across restarts; overflow can trigger a theoretical dirty‑read bug after many years of continuous operation. thread_id wraps at 2^32‑1 but the server’s internal tracking prevents visible duplicates.

Understanding these limits helps design schemas that avoid overflow (e.g., using BIGINT for primary keys) and anticipate rare edge cases in long‑running MySQL deployments.