Do you know the impact of the MySQL innodb self-increment ID BUG?

All these years of MySQL have been used in vain. . . Do you know that the MySQL innodb auto-increment ID BUG affects 99% of existing systems. . .

First let’s reproduce this magical problem:

Create a test table with an auto-increment ID, and then Insert 3 pieces of data and delete the one with id = 3.

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
 
insert into test values ();
select LAST_INSERT_ID();
insert into test values ();
select LAST_INSERT_ID();
insert into test values ();
select LAST_INSERT_ID();
delete from test where id = 3;

Then, let’s restart the MySQL service.

Insert a record again and take a look at the last inserted ID. . .

insert into test values ();
select LAST_INSERT_ID();
select * from test;

The result is that after restarting and inserting the record again, the ID is still 3! ! !

The original innodb auto-increment ID will be automatically set to the maximum ID 1 in the record after the service is restarted.

This problem can be reproduced 100% of the time in a system that is physically deleted.

Assume that the auto-increment ID of a certain table will also be associated with other records.

In extreme cases, the record with the largest ID is deleted before restarting the service, and the record is inserted and then associated after the service is restored. . .

I can’t imagine the problem of data chaos!

Fortunately, this problem has been fixed in MySQL 8.0!

If you are a MySQL 5.7 or older version user, don’t worry. Various solutions are as follows:

* Change all physical deletions in the system to soft deletions. Generally, frameworks have this function built-in, and it is very convenient to modify and reconstruct.

* Enable innodb_autoinc_persistent setting, there is a 1% performance loss, which can be ignored.

innodb_autoinc_persistent=on
innodb_autoinc_persistent_interval=1

Recommended study: "MySQL Video Tutorial"

The above is the detailed content of Do you know the impact of the MySQL innodb self-increment ID BUG?. For more information, please follow other related articles on the PHP Chinese website!