It was expected for a long time…. here is the fix for bug #199 !!

The bug #199 submitted by PeterZ has been fixed in 8.0. Thank you to Zhang Simon for his contribution that inspired us to implement the fix.

Let’s have a look at the test case described in the bug report, I will reproduce it on MySQL 5.7.19 & MySQL 8.0.3.

The initial steps are exactly the same on both versions:

mysql> create table a(id int unsigned not null primary key auto_increment); Query OK, 0 rows affected (0.17 sec) mysql> show create table a\G *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.04 sec) mysql> insert into a values(NULL); Query OK, 1 row affected (0.03 sec) mysql> insert into a values(NULL); Query OK, 1 row affected (0.05 sec) mysql> insert into a values(NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from a; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) mysql> delete from a where id=3; Query OK, 1 row affected (0.18 sec) mysql> insert into a values(NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from a; +----+ | id | +----+ | 1 | | 2 | | 4 | +----+ 3 rows in set (0.00 sec) mysql> delete from a where id=4; Query OK, 1 row affected (0.00 sec) mysql> select * from a; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.01 sec)

OK now, let’s restart mysqld:

systemctl restart mysqld

And add a row:

mysql> insert into a values(NULL); Query OK, 1 row affected (0.01 sec)

We can see that the result of the SELECT is different as now the server keeps the value of the auto_increment even after a restart:

5.7.19 8.0.3 mysql> select * from a; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) mysql> select * from a; +----+ | id | +----+ | 1 | | 2 | | 5 | +----+ 3 rows in set (0.00 sec)

As you can see, now the new row as an identifier that is unique and that was never been used by another deleted row.

This new behavior is mandatory for people implementing logical foreign keys in their application or for people archiving their data but still requires a unique identifier to their records.

So this is not a problem anymore, thank you for your patience and bye bye bug #199 !

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

To find out more, including how to control cookies, see here: Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.To find out more, including how to control cookies, see here: Cookie Policy