What Happens When an Auto-Increment ID Hits Its Max Value: Dissecting the GitHub Outage

On May 5th, 2020, GitHub experienced an outage affecting critical services including GitHub Actions, Pages, and Dependabot. The root cause was deceptive in its simplicity: an internal MySQL table’s auto-increment primary key reached the maximum value allowed by a signed 32-bit integer ($2,147,483,647$).

When a column hits its maximum capacity, relational databases exhibit subtle and unexpected behaviors that can bypass conventional error-handling logic. This guide dissects the mechanics of MySQL auto-increment overflow, explains why the database throws duplicate key errors instead of overflow errors, and details the zero-downtime hot-swap migration pattern used by SRE teams to recover from exhausted primary keys.

---

1. The Incident: What Happened at GitHub?

During the incident, a shared database table reached the upper bound of MySQL’s standard INT data type. Once the auto-increment column hit this limit:

1. Incoming inserts failed because the storage engine could not allocate a larger integer.
2. The application layer (Ruby on Rails using ActiveRecord) raised an ActiveModel::RangeError when handling values exceeding the 32-bit integer range.
3. The failed inserts generated HTTP 5xx responses across API endpoints, blocking the issuance of installation tokens for GitHub Actions, Pages, and Dependabot.
4. The resulting disruption lasted 2 hours and 24 minutes while engineers remediated the underlying database schema.

[Client Request] 
       │
       ▼
[Rails / ORM Layer] ──── Attempts INSERT without ID ────► [MySQL Engine]
       ▲                                                         │
       │                                                         ▼
[HTTP 5xx Error] ◄─── ActiveModel::RangeError ◄─── Auto-Increment Exhausted

---

2. Deep Dive: MySQL 32-Bit Integer Limits

In MySQL, the standard INT type defaults to a signed 4-byte (32-bit) integer:

• Storage Width: 4 bytes ($32\text{ bits}$)
• Signed Range: $-2^{31}$ to $2^{31} - 1$ ($-2,147,483,648$ to $+2,147,483,647$)
• Unsigned Range: $0$ to $2^{32} - 1$ ($0$ to $+4,294,967,295$)
• Bigint Range: $-2^{63}$ to $2^{63} - 1$ (up to $9.22 \times 10^{18}$)

In a standard auto-increment column starting from 1, the practical capacity of a signed INT is strictly 2,147,483,647 rows.

---

3. Simulating the Failure: The Deceptive “Duplicate Entry” Error

To understand the storage engine’s behavior, consider reproducing this sequence in MySQL 8.0:

Schema Setup

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(8) NOT NULL
);

If you manually insert an out-of-order ID, MySQL resets its internal sequence generator to match the highest assigned ID:

INSERT INTO users (name) VALUES ('a');          -- id = 1
INSERT INTO users (name) VALUES ('b');          -- id = 2
INSERT INTO users (id, name) VALUES (100, 'c');  -- id = 100
INSERT INTO users (name) VALUES ('d');          -- id = 101

Forcing Integer Overflow

Insert the maximum allowable signed 32-bit integer:

INSERT INTO users (id, name) VALUES (2147483647, 'e');
-- Query OK, 1 row affected

Now attempt to insert another row without providing an ID:

INSERT INTO users (name) VALUES ('f');

The Unexpected Error Output

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'users.PRIMARY'

Why Does It Throw Duplicate entry Instead of Out of range?

In languages like C or Go, standard arithmetic overflows wrap around from the maximum positive value to the minimum negative value ($2^{31}-1 + 1 = -2^{31}$).

MySQL’s auto-increment counter does not wrap around. When the sequence generator reaches the column’s upper limit, it clamps at the maximum value:

$\text{Next\_ID} = \min(\text{Current\_ID} + 1, \text{MAX\_VALUE})$

$\text{Next\_ID} = \min(2147483647 + 1, 2147483647) = 2147483647$

The storage engine attempts to use $2,147,483,647$ again. Because that ID already exists in the primary key index, the engine aborts the operation with a duplicate key violation (ERROR 1062).

(Note: Application-level ORMs, such as Ruby on Rails’ ActiveRecord, inspect the target column type before executing or while parsing errors, often raising an internal ActiveModel::RangeError rather than propagating the raw database error).

---

4. Remediation Strategies

Once an auto-increment column is exhausted in a live production environment, quick and safe remediation is critical.

Approach 1: In-Place Schema Migration (ALTER TABLE)

The most direct approach is widening the data type to INT UNSIGNED ($4.29\times 10^9$) or BIGINT ($1.84\times 10^{19}$):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET foreign_key_checks = 0;

ALTER TABLE users 
    CHANGE id id BIGINT NOT NULL AUTO_INCREMENT,
    ALGORITHM=COPY, 
    LOCK=SHARED;

Operational Constraints of Approach 1:

• Copy Overhead: Widening a primary key on a table containing 2.1 billion rows requires rewriting every single data page and secondary index.
• Downtime / Replication Lag: Running this migration on a 2-billion-row table will lock writes or stall replication for hours or days, rendering it unusable during an active, high-severity outage.

---

Approach 2: Zero-Downtime Table Swap (SRE Best Practice)

When a table is receiving high write traffic and cannot withstand prolonged locks, SRE teams use an atomic table-swap strategy to restore write availability immediately, deferring historical data backfills to an asynchronous background task.

Step 1: Create identical table users_2
Step 2: Set users_2 auto-increment to 2,147,483,648 (BIGINT)
Step 3: Atomic Rename:
        users     ──► users_old
        users_2   ──► users
Step 4: Writes resume immediately on users (empty, BIGINT)
Step 5: Backfill historical rows from users_old to users asynchronously

Step-by-Step Implementation

Step 1: Configure permissive session settings Avoid blocking on isolation locks or foreign key validation:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET foreign_key_checks = 0;

Step 2: Create a clone table with widened types

-- Create identical schema
CREATE TABLE users_2 LIKE users;

-- Expand ID column to BIGINT
ALTER TABLE users_2 
    MODIFY id BIGINT NOT NULL AUTO_INCREMENT;

-- Set counter directly above the exhausted 32-bit signed threshold
ALTER TABLE users_2 
    AUTO_INCREMENT = 2147483648;

Step 3: Atomically swap the tables MySQL allows multiple table renames in a single atomic statement:

RENAME TABLE 
    users TO users_old, 
    users_2 TO users;

Impact: The application can now immediately resume inserting records into users starting at ID $2,147,483,648$. Service availability is restored within seconds.

Step 4: Asynchronously backfill historical data Once immediate availability is restored, backfill the older records in throttled batches to prevent replication lag or I/O starvation:

INSERT INTO users (id, name) 
SELECT id, name FROM users_old 
WHERE id BETWEEN 1 AND 50000;
-- Repeat incrementally in chunks until complete

---

5. Architectural Prevention & Best Practices

Prevention Mechanism	Description	Best Suited For
Capacity Monitoring Alerts	Prometheus/Datadog alerts triggered when any auto-increment reaches 70%–80% of its type ceiling.	All relational databases
Default to BIGINT	Defining all auto-increment surrogate keys as BIGINT by default from day zero.	High-ingestion tables
Distributed ID Generators	Decoupling primary key generation using Twitter Snowflake, ULIDs, or UUIDv7.	Microservices & distributed shards

Capacity Monitoring SQL Pattern

You can monitor primary key consumption across all tables in a MySQL instance using information_schema:

SELECT 
    table_schema, 
    table_name, 
    auto_increment,
    ROUND((auto_increment / 2147483647) * 100, 2) AS pct_consumed
FROM information_schema.tables
WHERE auto_increment IS NOT NULL
  AND data_type = 'int' -- or check column definition
ORDER BY pct_consumed DESC;

Summary

1. Integer Cap: Standard signed INT columns cap out at $2,147,483,647$ ($2^{31} - 1$).
2. Clamping Mechanism: MySQL’s auto-increment counter does not roll over to negative values; it saturates at the maximum limit, generating repeated IDs that fail with Duplicate entry violations.
3. Mitigation Trade-Offs: Direct ALTER TABLE operations on multi-billion-row datasets block systems for hours or days. The table-swap pattern restores write availability within seconds by decoupling ingestion recovery from historical backfills.
4. Proactive Guardrails: Production database clusters require automated alerts at 70% sequence consumption to permit planned zero-downtime migrations well before catastrophic failure.