What will happen if the AUTO_INCREMENT ID column reaches its maximum value? (Github Outage Issue)

By | March 16, 2024

On May 5, 2020, the auto-incrementing ID column in a shared database table on Github grew larger than what the MySQL integer type could handle. The github-blog page has all of the incident’s information. We will simulate the situation in which we force the value of our auto-increment ID column in the MySQL database to reach its maximum value in our local environment.

Auto-increment ID column reaches its maximum value

We will be using MySQL Workbench with MySQL 8.0.28 in this example.

Fig 1- MySQL Version

Step 1- Create DB schema

We start by building a database schema. To find out, please visit MySQL Workbench – How to make database schema in MySQL Workbench?.

Step 2- Create Table in MySQL

We make a table called tbl_user with an ID column and a name. Our primary key will be the ID column, which will automatically increment.

CREATE TABLE `github-outage`.tbl_user(
    name VARCHAR(255) NOT NULL

Step 3- Integer Maximum Value

Below, we’ll see the highest integer value that MySQL 8.0 supports.

TypeStorage (Bytes)Minimum Value SignedMinimum Value UnsignedMaximum Value SignedMaximum Value Unsigned

Above, we can find that the integer value starts from -2147483648 ( 231) to 2147483647 (231-1).

Step 4- Add data to the table

We will now update the table with data. We’ll use the following query for this.

INSERT INTO `github-outage`.tbl_user(name) VALUES ('Priya');
INSERT INTO `github-outage`.tbl_user(name) VALUES ('Rohan');

The above command will add two rows with the name and auto_increment ID column to tbl_user.

Fig 2- Insert data to tbl_user

We now explore a little bit with the data ingestion section. Assume that the ID column is explicitly specified in the INSERT statement.

INSERT INTO `github-outage`.tbl_user VALUES (1000, 'Ankit');

As we have specified the ID column explicitly, it will insert the data with the specified ID.

Fig 3- Insert data to tbl_user

If we attempt to insert again without naming the ID column, auto_increment will cause data to be inserted into tbl_user with the ID set to 1001, increasing the value of the previous ID column.

Fig 4- Insert data into tbl_user

It indicates that the maximum value supplied is used to replace the internal auto_increment offset.

Step 5- Add maximum possible int value

We will attempt to input the largest integer value that MySQL can support, that’s 2147483647.

Fig 5- Insert maximum possible integer value

Now, we will insert one more statement, and it will be auto-incremented, pass over the maximum value, which is supported by an integer, and observe the behavior of MySQL. We will be using the following query:

INSERT INTO `github-outage`.tbl_user(name) VALUES ('Bhoomika');

MySQL is displaying an error message indicating a duplicate item below. Instead of a duplicate entry, which is obviously not the case, it should be more generic and provide context for the error.

AUTO_INCREMENT ID column reaches its maximum value
Fig 6- Insert more than maximum value supported by integer field

Leave a Reply

Your email address will not be published. Required fields are marked *