Auto-Increment Counter Persistence in MySQL 8: Comparing the Evolution from MySQL 5.7

1 Auto-Increment

The auto‑increment feature generates unique values for primary‑key columns, a common database design pattern. Compared with MySQL 5.7, MySQL 8 adds an important upgrade: the maximum value of the auto‑increment counter is persisted after a server restart, providing better data consistency and reliability.

2 MySQL 5.7 Auto-Increment

In MySQL 5.7, the auto‑increment counter works as follows: when a new row is inserted into a table that contains an auto‑increment column, the counter is increased by 1 and the generated value is used as the primary key. The counter value is stored only in memory and is not persisted; therefore, after a server crash or restart the counter may reset to a lower value.

3 MySQL 8 Auto-Increment Persistence

With the release of MySQL 8, the auto‑increment mechanism received a significant improvement. The maximum value of the auto‑increment counter is now persisted across server restarts, meaning the counter resumes from the last value after a restart, ensuring continuous primary‑key values.

4 Example Comparison

Below is a simple example that demonstrates the difference between MySQL 5.7 and MySQL 8 regarding persistent auto‑increment counters. We create a table named users to store user information.

Creating the table in MySQL 5.7:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42-46 |
+-----------+

mysql> CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)

Insert three rows and view the data:

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user3    |
+----+----------+
3 rows in set (0.00 sec)

Delete a record and insert a new one:

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

After the deletion of ID 3 and insertion of a new record, the new record receives ID 4 as expected.

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

Now delete the last record (ID 4), restart the server, and check the table:

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)

service mysql restart

mysql> select * from users;
ERROR 2006 (HY000): MySQL server has gone away
... (reconnect output) ...
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.01 sec)

Only two records remain. Insert a fifth record and observe whether it receives ID 5 or falls back to ID 3.

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user5    |
+----+----------+
3 rows in set (0.00 sec)

Thus, in MySQL 5.7 a server restart can cause the auto‑increment counter to reset to a lower value, leading new inserts to reuse IDs such as 3.

5 MySQL 8 Solution

MySQL 8 resolves the loss of the auto‑increment counter in the InnoDB storage engine after a server restart. This enhancement ensures the counter value is persisted, guaranteeing consistent primary‑key generation.

Creating the table in MySQL 8:

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 8.0.33-0ubuntu0.22.04.2  |
+---------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
Query OK, 0 rows affected (0.04 sec)

Insert three rows and view them:

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user3    |
+----+----------+
3 rows in set (0.00 sec)

Delete a row (ID 3) and insert a new one:

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

Delete the last record (ID 4), restart the server, and check the table:

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)

service mysql restart

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.02 sec)

After the restart, the users table still contains only the two original rows. When a new record is inserted, MySQL 8 correctly assigns ID 5 as expected.

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  5 | user5    |
+----+----------+
3 rows in set (0.00 sec)

6 Summary

In versions prior to MySQL 8, the InnoDB storage engine could lose the auto‑increment counter during a server restart, leading to confusion and data inconsistency because generated primary‑key values might not be continuous. MySQL 8 solves this problem by persisting the counter across restarts.

Upgrading to MySQL 8 allows developers to rely on stable auto‑increment behavior, making applications more robust against failures without compromising data integrity.