Summary of the persistence problem of MySQL 8.0 self-increasing variables

MySQL 8.0 Features - Persistence of Autoincrement Variables

Before MySQL 8.0, if the value of the autoincrement primary key AUTO_INCREMENT is greater than max(primary key)+1, after MySQL restart, AUTO_INCREMENT=max(primary key)+1 will be reset. In some cases, this phenomenon will lead to business primary key conflicts or other difficult-to-discover problems. The following is a case comparison of whether the autoincrement variables in different versions are persisted.

MySQL 5.7 Testing

In MySQL version 5.7, the test steps are as follows: The created data table contains the id field of the auto-increment primary key, and the statement is as follows:

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
-- insert4A null value，Execute as follows：
INSERT INTO test1
VALUES(0),(0),(0),(0);
-- Query the data tabletest1Data in，The results are as follows：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
-- deleteidfor4Records of，The statement is as follows：
DELETE FROM test1 WHERE id = 4;
-- 再次insert一A null value，The statement is as follows：
INSERT INTO test1 VALUES(0);
-- Query the data table at this timetest1Data in，The results are as follows：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)
-- From the results, we can see，虽然delete了idfor4Records of，但是再次insert空值时，并没有重用被delete的4，Instead, it was assigned5。
-- deleteidfor5Records of
DELETE FROM test1 where id=5;

Restart the database

service mysql stop
service mysql start

Continue to insert the null value, and then query the data in data table test1 again, and the result is as follows:

mysql> INSERT INTO test1 values(0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

From the results, we can see that the newly inserted 0 value is assigned 4. According to the operation logic before restart, 6 should be assigned here. The main reason for the above results is that the autoincrement primary key is not persisted. In MySQL 5.7 system, the allocation rules for auto-increment primary keys are determined by a counter inside the InnoDB data dictionary, which is only maintained in memory and will not be persisted to disk. When the database is restarted, the counter is initialized.

MySQL 8.0 Testing

The results of the last step of the above test step are as follows:

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)

From the results, it can be seen that the autoincrement variable has been persisted.

MySQL 8.0 persists the counter of the autoincrement primary key toRedo logmiddle. Every time the counter changes, it is written to the redo log. If the database is restarted, InnoDB initializes the counter's memory value based on the information in the redo log.

This is the end of this article about the persistence of MySQL 8.0 autoincrease variables. For more related contents of mysql autoincrease variables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!