AUTO_INCREMENT overflow in MySQL and MariaDB

The error at the top of the range

Inserts into one table start failing, and the application log shows ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'. MySQL 8.0.19 and later name the key together with its table, as in 'order_items.PRIMARY'; MariaDB prints 'PRIMARY'. The row with id 2147483647 exists already. InnoDB caps the auto-increment counter at the column’s maximum, so each new INSERT receives that same value and collides with the primary key until you change the column type.

Searches also turn up ERROR 1467, Failed to read auto-increment value from storage engine. On InnoDB you meet that one at the top of BIGINT UNSIGNED, 18,446,744,073,709,551,615. An INT key that runs out gives you 1062.

The counter runs ahead of the row count. Rolled-back inserts leave gaps, INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE take a value even when no new row lands, and multi-primary clusters often set auto_increment_increment to the node count, so three nodes use up three values per row.

Type	Signed maximum	Unsigned maximum
TINYINT	127	255
SMALLINT	32,767	65,535
MEDIUMINT	8,388,607	16,777,215
INT	2,147,483,647	4,294,967,295
BIGINT	9,223,372,036,854,775,807	18,446,744,073,709,551,615

Checking the headroom by hand

information_schema.TABLES.AUTO_INCREMENT holds the next value each table hands out, and COLUMNS.COLUMN_TYPE gives the type and whether it is unsigned. On MySQL 8.0, TABLES serves cached statistics for up to information_schema_stats_expiry seconds, a day by default, so set that to 0 for your session first. MariaDB has no such cache.

SET SESSION information_schema_stats_expiry = 0;  -- MySQL 8.0 only

SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, t.AUTO_INCREMENT,
       ROUND(t.AUTO_INCREMENT / CASE LOWER(c.DATA_TYPE)
         WHEN 'tinyint'   THEN IF(c.COLUMN_TYPE LIKE '%unsigned%', 255, 127)
         WHEN 'smallint'  THEN IF(c.COLUMN_TYPE LIKE '%unsigned%', 65535, 32767)
         WHEN 'mediumint' THEN IF(c.COLUMN_TYPE LIKE '%unsigned%', 16777215, 8388607)
         WHEN 'int'       THEN IF(c.COLUMN_TYPE LIKE '%unsigned%', 4294967295, 2147483647)
         WHEN 'bigint'    THEN IF(c.COLUMN_TYPE LIKE '%unsigned%', 18446744073709551615, 9223372036854775807)
       END * 100, 1) AS pct_used
FROM information_schema.COLUMNS c
JOIN information_schema.TABLES t
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.EXTRA LIKE '%auto_increment%'
  AND c.TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY pct_used DESC
LIMIT 20;

MySQL also ships the same calculation as a view in the sys schema, sorted by the share used. It reads the same cached TABLES values, so the session setting above applies to it as well:

SELECT table_schema, table_name, column_name, column_type,
       auto_increment, max_value, auto_increment_ratio
FROM sys.schema_auto_increment_columns
LIMIT 10;

Deciding how to widen it

Measure the pace first. Run the query again a day later: the growth of AUTO_INCREMENT per day against the values left gives you the days before the first failed INSERT. With that number you choose between a scheduled migration and one you start tonight.

Changing INT to BIGINT rewrites the table. MySQL can’t change a column type in place: ALGORITHM=INPLACE fails with Cannot change column type INPLACE, and a plain ALTER TABLE runs as ALGORITHM=COPY. The copy blocks writes to the table until it finishes and needs free disk for a second copy. Replicas replay the same ALTER after the source commits it and fall behind for about as long as it took. MariaDB 11.2 and later accept ALGORITHM=COPY, LOCK=NONE, which keeps writes going during the copy.

On a big, busy table you want an online tool. gh-ost and pt-online-schema-change build the new table in chunks while the application keeps writing, then swap the two under a short metadata lock. If a long transaction holds the table at that moment, the swap waits and queries pile up behind it. gh-ost reads the binary log in row format and refuses tables with foreign keys. pt-online-schema-change works through triggers and handles child tables with --alter-foreign-keys-method.

Check foreign keys before you pick a tool. Child columns that reference the id hold the same values, so they need BIGINT too, and MySQL requires the same integer size and sign on both sides of a foreign key:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'shop'
  AND REFERENCED_TABLE_NAME = 'order_items'
  AND REFERENCED_COLUMN_NAME = 'id';

If inserts fail already, no server setting buys time. InnoDB resets an AUTO_INCREMENT value at or below the current maximum to MAX(id) + 1, so the widening is your incident fix.

Auto-increment headroom in RowDeck

The Auto-increment tile on Health turns amber at 75% of the range and red at 90%. Tap it to open Purge & Bloat. Auto-increment headroom puts the column closest to its end on top, in the screenshot order_items.id · INT · 2.00B of 2.15B at 93%, and lists the next columns with their types, signed or UNSIGNED.

From 90% a red card says When it reaches the end, every INSERT into order_items fails. It adds that widening the column to BIGINT rebuilds the table and needs a plan, shows the ALTER TABLE ... MODIFY ... BIGINT NOT NULL AUTO_INCREMENT statement and offers Copy ALTER statement. RowDeck doesn’t run it for you: you take it to your migration and choose the hour. MODIFY restates the whole column, so add back a COMMENT or any other attribute the column carries today.

Below the headroom, the same screen shows purge lag and the oldest transaction.

Free and Pro

Health, Purge & Bloat and the copied ALTER are part of Free for one saved connection, with no time limit. Pro is a one-time purchase that adds unlimited connections and the actions: kills, ANALYZE and OPTIMIZE TABLE, writes and the AI assistant. On both plans, Purge & Bloat copies the widening statement and leaves running it to you.

Keeping it from happening again

• Create new surrogate keys as BIGINT. It costs four more bytes per row in the primary key and in each secondary index entry, since InnoDB secondary indexes carry the primary key.
• Alert at 75% of the range in your monitoring, where RowDeck turns amber, and check again after bulk imports or a release that adds INSERT IGNORE or upserts.
• Widen child columns and application types in the same change. A 32-bit integer in application code or in an analytics table overflows at the same 2,147,483,647.