Fix MySQL AUTO_INCREMENT Exhaustion by Migrating to BIGINT (2026)

The AUTO_INCREMENT counter in your MySQL table has run out of room, and MySQL cannot generate new unique IDs for your records.

Common Causes and Fixes

Table is genuinely full:
- Diagnosis:
```
SELECT MAX(your_id_column) FROM your_table_name;
SHOW VARIABLES LIKE 'max_connections'; -- Check system limits
SHOW VARIABLES LIKE 'max_allowed_packet'; -- Check packet size limits
```
  Examine MAX(your_id_column) to see how close it is to the maximum value for the current data type. For INT, this is 2,147,483,647. For BIGINT, it’s 9,223,372,036,854,775,807.
- Fix: The only true fix is to increase the capacity of the AUTO_INCREMENT column. This involves migrating to a larger data type, typically BIGINT.
```
ALTER TABLE your_table_name MODIFY your_id_column BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
```
  This command changes the column type to BIGINT UNSIGNED, which has a much larger range. UNSIGNED is important if you only need positive IDs, doubling the positive range. NOT NULL and AUTO_INCREMENT are retained.
- Why it works: BIGINT provides a significantly larger range of values than INT, effectively eliminating the possibility of exhaustion for most practical applications.
Accidental deletion of records with high AUTO_INCREMENT values:
- Diagnosis:
```
SELECT MAX(your_id_column) FROM your_table_name;
SHOW TABLE STATUS LIKE 'your_table_name';
```
  If MAX(your_id_column) is very high, but TABLE_ROWS in SHOW TABLE STATUS is low, it suggests records were deleted. The AUTO_INCREMENT counter does not decrease when rows are deleted; it only moves forward.
- Fix: You need to reset the AUTO_INCREMENT counter to a value greater than the current maximum existing ID.
```
ALTER TABLE your_table_name AUTO_INCREMENT = <new_starting_value>;
```
  Replace <new_starting_value> with a number slightly larger than the current MAX(your_id_column). For example, if MAX(your_id_column) is 2,000,000,000, set it to 2000000001.
- Why it works: This command directly tells MySQL what the next value for AUTO_INCREMENT should be. It doesn’t affect existing data but ensures new records start with IDs above the highest currently used one.
Replication lag causing duplicate ID generation attempts:
- Diagnosis:
```
SHOW REPLICA STATUS\G # or SHOW SLAVE STATUS\G for older versions
```
  Look for Seconds_Behind_Master and any errors related to duplicate keys or AUTO_INCREMENT conflicts on the replica.
- Fix: If the issue is replication lag and not true exhaustion, ensure your replication is healthy. If a replica falls too far behind, it might try to insert a record with an ID that the master has already used or that the replica itself has already assigned. This often requires re-syncing the replica or ensuring it catches up. In extreme cases, you might need to manually adjust AUTO_INCREMENT on the replica to match the master’s next expected value after a re-sync.
```
-- On the replica, after ensuring master and replica are in sync
ALTER TABLE your_table_name AUTO_INCREMENT = <next_expected_id_from_master>;
```
- Why it works: Manually setting the AUTO_INCREMENT value on the replica ensures it aligns with the master’s sequence, preventing duplicate key errors that arise from divergence due to lag.
Improper use of INSERT IGNORE or REPLACE with AUTO_INCREMENT:
- Diagnosis: Check your application logic for INSERT IGNORE or REPLACE statements that might be attempting to insert rows with explicitly provided AUTO_INCREMENT values.
- Fix: Avoid explicitly setting the AUTO_INCREMENT column when using INSERT IGNORE or REPLACE unless you have a very specific, controlled reason. If you must insert a specific ID, use a regular INSERT statement and handle potential duplicate key errors in your application logic.
```
-- Instead of INSERT IGNORE ...
INSERT INTO your_table_name (your_id_column, other_column) VALUES (12345, 'some_value');
-- Handle potential duplicate key error in application code
```
- Why it works: INSERT IGNORE and REPLACE can sometimes advance the AUTO_INCREMENT counter even if no row is actually inserted or if a row is replaced, leading to wasted IDs. Explicitly inserting and handling errors gives you more control.
Large auto_increment_increment and auto_increment_offset on multi-master setups:
- Diagnosis:
```
SHOW VARIABLES LIKE 'auto_increment_increment';
SHOW VARIABLES LIKE 'auto_increment_offset';
```
  In a multi-master setup (e.g., Galera Cluster), these variables are often configured to ensure unique IDs across nodes. If auto_increment_increment is set to a large number (e.g., 2, or the number of nodes), and auto_increment_offset is set to the node’s ID, it can lead to faster exhaustion if not carefully managed.
- Fix: This is a configuration issue. For Galera Cluster, the recommended settings are typically auto_increment_increment = N and auto_increment_offset = node_id, where N is the number of nodes in the cluster. Ensure these are set correctly in your my.cnf or my.ini and that all nodes have consistent configurations.
- Why it works: By setting auto_increment_increment to the number of nodes, each node reserves a unique block of IDs for itself, preventing collisions. auto_increment_offset determines which block that node uses.
Running out of space in the underlying storage engine for the ID column:
- Diagnosis: While rare for AUTO_INCREMENT itself to exhaust storage directly, if the table is extremely large and the BIGINT column is part of a clustered index (like InnoDB’s primary key), the sheer size of the index can consume significant disk space. This can manifest as general storage errors rather than a specific AUTO_INCREMENT error.
- Fix: This is a broader storage management issue. Ensure you have sufficient disk space. For InnoDB, consider table partitioning if the table is exceptionally large, or optimizing the table structure.
- Why it works: Ensuring adequate physical storage capacity is fundamental for any database operation.

The next error you’ll likely encounter after fixing AUTO_INCREMENT exhaustion is a Table '...' is full error if you’re also experiencing disk space issues, or a Duplicate entry '...' for key '...' error if replication divergence wasn’t fully resolved.