What happens when MySQL auto-incrementing ID hits its limit?

On the 5th of May, 2020, GitHub experienced an outage because of this very reason. One of their shared table having an auto-incrementing ID column hits its max limit. Let's see what could have been done in such a situation.

What's the next value after MAX int?

GitHub used 4 bytes signed integer as their ID column, which means the value can go from -2147483648 to 2147483647. So, now when the ID column hits 2147483647 and tries to get the next value, it gets the same value again, i.e., 2147483647.

For MySQL, 2147483647 + 1 = 2147483647

So, when it tries to insert the row with ID 2147483647, it gets the Duplicate Key Error given that a row already exists with the same ID.

How to mitigate the issue?

A situation like this is extremely critical given that the database is not allowing us to insert any row in the table. This typically results in a major downtime of a few hours, and it depends on the amount of data in the table. There are a couple of ways to mitigate the issue.

Approach 1: Alter the table and increase the width of the column

Quickly fire the ALTER table and change the data type of the ID column to UNSIGNED INT or BIGINT. Depending on the data size, an ALTER query like this will take a few hours to a few days to execute. Hence this approach is suitable only when the table size is small.

Approach 2: Swap the table

The idea here is to create an empty table with the same schema but a larger ID range that starts from 2147483648. Then rename this new table to the old one and start accepting writes. Then slowly migrate the data from the old table to this new one. This approach can be used when you can live without the data for a few days.

Get warned before the storm

Although mitigation is great, it is better to place a monitoring system that raises an alert when the ID reaches 70% of its range. So, write a simple DB monitoring service that periodically checks this by firing a query on the database.

Here's the video of my explaining this in-depth ?? do check it out

GitHub experience an outage on 5th May 2020 on a few of their internal services and it happened because a table had an auto-incrementing integer ID and the column reached its maximum value possible 2147483647. In this video, we dissect what happened, mimic the situation locally and see what could have happened, and look at possible ways to mitigate and prevent a situation like this.

Outline:

• 00:00 Outage walkthrough
• 02:48 Mimicking the situation locally
• 10:13 MySQL AUTO_INCREMENT behavior
• 12:37 Preventive measures
• 14:25 Approach 1 for mitigating the issue
• 18:40 Approach 2 for mitigating the issue

References:

• https://github.com/arpitbbhayani/mysql-maxint
• https://github.blog/2020-07-08-introducing-the-github-availability-report/
• https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
• https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
• https://www.dhirubhai.net/pulse/so-you-hit-2147483647-heath-dutton-/

You can also

• Subscribe to the YT Channel Asli Engineering
• Download the notes
• Listen to this on the go on Spotify

Thank you so much for reading ?? If you found this helpful, do spread the word about it on social media; it would mean the world to me.

You can also follow me on your favourite social media LinkedIn, and Twitter.

Yours truly,

Arpit

arpitbhayani.me

Until next time, stay awesome :)

I teach a course on System Design where you'll learn how to intuitively design scalable systems. The course will help you

• become a better engineer
• ace your technical discussions
• get you acquainted with a massive spectrum of topics ranging from Storage Engines, High-throughput systems, to super-clever algorithms behind them.

I have compressed my ~10 years of work experience into this course, and aim to accelerate your engineering growth 100x. To date, the course is trusted by 500+ engineers from 9 different countries and here you can find what they say about the course.

Together, we will build some of the most amazing systems and dissect them to understand the intricate details. You can find the week-by-week curriculum and topics, benefits, testimonials, and other information here https://arpitbhayani.me/masterclass.