What Happens When MySQL Auto‑Increment IDs Exhaust? Practical Solutions

Introduction

In interviews you may encounter the question: “Do you use MySQL? Do you use auto‑increment primary keys or UUIDs?” and then, “What if the auto‑increment key reaches its maximum value?” This article discusses what to do when an auto‑increment key is exhausted.

Interviewer: “Do you use MySQL? Do you use auto‑increment primary keys or UUIDs?” Candidate: “We use auto‑increment primary keys.” Interviewer: “Why auto‑increment?” Candidate: “Because data is stored sequentially, giving the best performance…” Interviewer: “What if the auto‑increment key reaches its maximum?” Candidate: “What? I haven’t studied that!”

Simple Version

In MySQL, an unsigned INT ranges from 0 to 4,294,967,295 (about 4.3 billion). When the auto‑increment ID reaches this limit, further inserts cause a primary‑key‑conflict error.

Solution: change the column type from INT to BIGINT. BIGINT’s range is far larger (‑9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), so even inserting 10,000 rows per second for 100 years would still be far from the limit.

Candidate: “Just change the auto‑increment column to BIGINT!”

How to Change Column Types Online

There are three common ways to modify a column type without blocking writes:

MySQL native online DDL (5.6+) : Use ALTER statements that allow most operations while the table remains readable and writable.

Third‑party tools : pt‑online‑schema‑change (pt‑osc) and gh‑ost perform the change by creating a new table, copying data, and swapping tables, avoiding downtime.

Modify on a replica and then promote : Change the schema on a read‑only replica, then switch the master‑slave roles. This method is complex and may risk data loss during the switchover.

For pt‑osc, the process is:

Create a new table with the desired structure.

Create triggers to capture changes on the original table during the copy.

Copy data from the original table to the new table.

Rename the original table to a backup name, rename the new table to the original name, and drop the backup.

Drop the triggers.

These tools cannot be used if the table contains triggers or foreign keys; in that case, the replica‑swap method is required.

Advanced Considerations

If the auto‑increment column is signed INT, its range is –2,147,483,648 to 2,147,483,647. Real data IDs are often non‑continuous due to deletions, so the effective usable IDs are fewer. In large‑scale systems, tables may reach billions of rows, prompting sharding or database partitioning, which then requires a globally unique ID strategy rather than relying on auto‑increment.

Interviewer: “What if the auto‑increment key reaches its maximum?” Candidate: “We never encounter that; we use INT and shard the data, so the issue doesn’t arise.”