There is a specific error message that means your night is over: Duplicate entry '2147483647' for key 'PRIMARY'. Not one occurrence — thousands per second, from every writer, on one table. The table has not changed in years. Nobody deployed. The database is healthy by every resource metric: CPU fine, disk fine, replication current. And yet a core table has just become read-only, permanently, until a schema change ships.
That is AUTO_INCREMENT exhaustion. When the counter reaches the maximum value of the column's type, MySQL does not wrap around and does not raise a friendly out-of-range error. The counter parks at the ceiling, every subsequent insert tries to use the same maximum value, and each one fails with a duplicate-key error. Reads keep working, which makes the first ten minutes of the incident deeply confusing — dashboards show a healthy database while the application logs fill with what looks like an application bug.
Every MySQL fleet older than a few years has at least one table quietly counting toward this cliff. These notes cover why tables get there sooner than their row counts suggest, the query that finds every at-risk table, how to think about headroom, and the online migration that fixes it for good.
Why the ceiling arrives early: the counter outruns the rows
The intuition that a 2.1 billion ceiling is safe because the table only has 400 million rows is the mistake that schedules the incident. AUTO_INCREMENT values are allocated, not committed. Every rolled-back insert burns its value. INSERT ... ON DUPLICATE KEY UPDATE can burn a value per attempt even when it updates. INSERT IGNORE burns values for ignored rows. REPLACE burns a value each time. Bulk inserts reserve ranges that round up to powers of two under the default innodb_autoinc_lock_mode of 2, leaving gaps at the end of each batch.
Upsert-heavy workloads are the classic accelerant: I have seen a deduplication table with 40 million live rows and an AUTO_INCREMENT counter past 1.9 billion, because every incoming event attempted an insert and most collided. The row count grew linearly; the counter grew with traffic. One more detail for 8.0 operators: since 8.0 the counter is persisted across restarts, so the old 5.7 behavior where a restart rewound the counter to MAX(id)+1 — occasionally hiding burned range — is gone. The counter only goes up.
The detection query: check the whole fleet, not the table you suspect
The check is a join between information_schema.tables, which knows the current AUTO_INCREMENT value, and information_schema.columns, which knows the column type and signedness. Computed as a percentage of the type's ceiling, sorted worst first:
SELECT t.table_schema,
t.table_name,
c.column_name,
c.column_type,
t.auto_increment,
ROUND(100 * t.auto_increment / (
CASE
WHEN c.column_type LIKE 'tinyint%unsigned' THEN 255
WHEN c.column_type LIKE 'tinyint%' THEN 127
WHEN c.column_type LIKE 'smallint%unsigned' THEN 65535
WHEN c.column_type LIKE 'smallint%' THEN 32767
WHEN c.column_type LIKE 'mediumint%unsigned' THEN 16777215
WHEN c.column_type LIKE 'mediumint%' THEN 8388607
WHEN c.column_type LIKE 'int%unsigned' THEN 4294967295
WHEN c.column_type LIKE 'int%' THEN 2147483647
WHEN c.column_type LIKE 'bigint%unsigned' THEN 18446744073709551615
ELSE 9223372036854775807
END), 2) AS pct_used
FROM information_schema.tables AS t
JOIN information_schema.columns AS c
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND c.extra LIKE '%auto_increment%'
WHERE t.auto_increment IS NOT NULL
AND t.table_schema NOT IN
('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY pct_used DESC
LIMIT 25;One 8.0-specific trap will make this query lie to you: dictionary statistics are cached, and information_schema_stats_expiry defaults to 86400 seconds. The AUTO_INCREMENT value you read can be a day old, which matters a great deal on a table burning millions of values an hour. For a monitoring check, set the variable to 0 in the session before querying, or run ANALYZE TABLE on the suspects first.
SET SESSION information_schema_stats_expiry = 0;
-- then run the detection query for live counter values
-- And before planning any fix, find every column that must move together:
SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'orders'
AND referenced_column_name = 'id'
AND table_schema = 'app';Anything over 50% goes on a migration schedule. Over 75%, the migration gets a date this quarter. Over 90%, it is this week's work — the last ten percent always burns faster than the first ninety, because traffic grew while you were not looking.
Signed, unsigned, and how much headroom each buys
A signed INT tops out at 2,147,483,647; unsigned doubles that to 4,294,967,295. Almost no schema uses negative ids, so a signed INT primary key is donating half its range to values that will never exist — the default in most ORMs and hand-written DDL for years. That makes UNSIGNED look like an attractive quick fix, and it is a trap: changing signedness is the same full table rebuild as changing the type, for a single doubling. If you are paying for the rebuild anyway, go to BIGINT UNSIGNED and retire the problem — 18.4 quintillion values, a ceiling that outlives the company at any plausible insert rate. The storage delta is four bytes per row plus four bytes per secondary index entry, since InnoDB secondary indexes carry the primary key; on most tables that is noise next to the cost of ever doing this migration twice.
The genuinely cheap trick to know about is for mid-incident triage only: if the exhausted column is signed and the application tolerates negative ids — most do not, but some ingestion tables do — you can restart the sequence in the negative range and buy the full lower half of the type while the real migration runs. File it under things you are glad to know and hope never to type.
The online migration to BIGINT
A plain ALTER TABLE ... MODIFY id BIGINT UNSIGNED is a COPY-algorithm rebuild that blocks writes for the duration — on a billion-row table, hours of write downtime, which is the exact outage you are trying to avoid. So the production path is an online schema change tool: build a shadow table with the new type, backfill, keep it current, then swap. gh-ost tails the binlog to apply ongoing changes while pt-online-schema-change uses triggers; the operational differences are in my gh-ost vs pt-osc field notes, and if you are choosing, note that a binlog-based tool needs row-format binlogs — background on that stream in the binlog vs WAL notes.
The schema change is the easy half. The migration checklist that has actually bitten me:
- Foreign keys and shadow foreign keys. Every column referencing the exhausted id — declared FK or application-level convention — must become BIGINT too, ideally first. A child table still on INT turns ids past 2.1 billion into out-of-range errors one hop downstream. Both OSC tools have limited or fiddly FK support, so FK-heavy schemas need explicit sequencing.
- Application integer types. A Java int, a Rust i32, a protobuf int32, an ORM column type — each one truncates or throws when ids cross 2^31. And JavaScript loses integer precision past 2^53, which argues for serializing ids as strings in JSON APIs while you are in there.
- Replicas and mixed types. Run the change consistently through the topology; replicating between INT and BIGINT versions of a column works only within documented limits and is not a state to sit in longer than necessary.
If it fires before you fixed it
Mid-incident, the options are ranked by ugliness. Best: if the table is append-mostly event data, archive-and-truncate may be legitimate — truncation resets the counter. Next: the negative-range restart above, if signedness and application semantics allow. Next: an emergency OSC run at maximum aggressiveness, accepting the replication lag. Last and worst: stopping writers and running the blocking ALTER, which at least has a predictable duration you can measure on a replica first. What does not work is anything involving the counter alone — ALTER TABLE ... AUTO_INCREMENT = 1 cannot set the counter below the maximum existing value, so there is no way to reuse gaps without rewriting rows.
Watching the runway — and where MonPG fits
This is the single most forecastable database incident that exists. The counter is monotonic, the ceiling is a constant, and consumption rate is measurable — a weekly check plus a trend line gives you months of warning, which is why it is so galling that teams still hit it cold. The detection query above belongs in scheduled monitoring, not in an incident retro.
Where MonPG stands, honestly: MonPG is a PostgreSQL monitoring platform, and MySQL support is in development — we do not monitor MySQL today. Fleet-wide AUTO_INCREMENT runway tracking with burn-rate forecasting is on the MySQL roadmap, and the MySQL monitoring (coming soon) page is where to follow it or tell us which checks your fleet needs first. If you also operate PostgreSQL, sequence exhaustion works differently but no less dangerously there, and MonPG covers it today — you can start there while the MySQL side lands.