The auto-increment feature, which generates unique values for primary key columns, is an integral part of the database’s design. With the release of MySQL 8, a notable enhancement was introduced to the auto-increment counter. Compared to MySQL 5.7, this enhancement ensures that the maximum auto-increment counter value persists between server restarts, providing enhanced consistency and reliability in data management. In this article, we will look into this enhancement, compare MySQL 5.7 with MySQL 8, and provide practical examples to demonstrate the differences.
Auto-increment in MySQL 5.7
In MySQL 5.7, the auto-increment counter works as follows: when a new row is inserted into a table with an auto-increment column, the counter increments by 1, and the generated value is used for the primary key of the inserted row. This counter value is stored in memory and is not persistent across server restarts. As a result, if the server crashes or is restarted, the counter might reset to a lower value.
Persistent auto-increment counter in MySQL 8
With the release of MySQL 8, a significant improvement was made to the auto-increment counter mechanism. In MySQL 8, the maximum auto-increment counter value is now persisted across server restarts. This means that even if the server is restarted, the auto-increment counter will resume from where it left off, ensuring that Auto-Increment primary key values remain sequential.
Key differences illustrated with an example
Let’s consider a simple example to illustrate the differences between MySQL 5.7 and MySQL 8 regarding the persistent auto-increment counter. We will create a table called “users” to store user information:
Table Creation in MySQL 5.7:
1 2 3 4 5 6 7 8 9 10 11 12 | 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) |
Now, let’s insert three records into the “users” table within MySQL 5.7:
1 2 3 4 5 6 | mysql> INSERT INTO users (username) VALUES ('user1'); INSERT INTO users (username) VALUES ('user2'); INSERT INTO users (username) VALUES ('user3'); Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) |
Following this, we can query three entries in the “users” table:
1 2 3 4 5 6 7 8 9 | mysql> select * from users; +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +----+----------+ 3 rows in set (0.00 sec) |
We proceed to delete one record and insert a new one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 removal of the record with ID 3 and the insertion of a new record, as expected, we observe the ID 4 for the new entry:
1 2 3 4 5 6 7 8 9 | mysql> select * from users; +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | | 4 | user4 | +----+----------+ 3 rows in set (0.00 sec) |
Now, let’s delete the last record (ID 4) from the “users” table, perform a restart, and inspect the table’s contents:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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 No connection. Trying to reconnect... Connection id: 2 Current database: db1 +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | +----+----------+ 2 rows in set (0.01 sec) |
Only two records remain in the table. To explore, let’s insert the fifth record and assess whether it adopts ID 5 or reverts to ID 3:
1 2 3 4 5 6 7 8 9 10 11 12 | 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) |
Consequently, in MySQL 5.7, a restart causes the auto-increment counter to reset to a lower value, leading to the insertion of the new record with ID 3.
MySQL 8’s solution
MySQL 8 addresses the auto-increment counter loss issue in the InnoDB storage engine upon server restart. This enhancement ensures that the auto-increment counter’s value persists across server restarts, guaranteeing consistency in generating primary keys.
Creation of the Table in MySQL 8:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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) |
Inserting three records in MySQL 8 proceeds as follows:
1 2 3 4 5 6 7 8 | 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) |
Upon inspection, the records in the “users” table are as follows:
1 2 3 4 5 6 7 8 9 | mysql> select * from users; +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +----+----------+ 3 rows in set (0.00 sec) |
Subsequently, we delete a record and insert a new one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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) |
Following the deletion of record ID 3 and the insertion of a new entry, the ID 4 is utilized:
1 2 3 4 5 6 7 8 9 | mysql> select * from users; +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | | 4 | user4 | +----+----------+ 3 rows in set (0.00 sec) |
Upon deletion of the last record (ID 4), restarting, and reviewing the table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 8 Current database: db1 +----+----------+ | id | username | +----+----------+ | 1 | user1 | | 2 | user2 | +----+----------+ 2 rows in set (0.02 sec) |
After the restart, the “users” table maintains just two records. Upon inserting a new entry, as anticipated in MySQL 8, the record is added with the ID 5.
1 2 3 4 5 6 7 8 9 10 11 12 | 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) |
Conclusion
The auto-increment counter issue reported in MySQL versions prior to MySQL 8’s InnoDB storage engine could cause confusion and possibly data inconsistency, especially during server restarts. The value of the counter could be lost, resulting in a mismatch in auto-generated primary key values. MySQL 8 has fixed this problem by guaranteeing that the auto-increment counter persists between server restarts.
By upgrading to MySQL 8, developers can use this functionality to create more robust applications that manage diverse failure scenarios without affecting data integrity.
Please read the following blogs to learn more about ways to make the major version upgrade easier:
- Two Extremely Useful Tools (pt-upgrade and checkForServerUpgrade) for MySQL Upgrade Testing
- Percona Utilities That Make Major MySQL Version Upgrades Easier
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
