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 !
It’s been way too long since I’ve said “fixed in Drizzle”, but I’m pretty sure I did do that…. Or at least intended to. Either way, MySQL 8 fixing it is fantastic.
“And add a column”* should be changed to “And add a row”
Thank you I edited it 😉
I implemented persistent AUTO_INCREMENT for MariaDB 10.2.4 under https://jira.mariadb.org/browse/MDEV-6076 using a simpler approach than the https://dev.mysql.com/worklog/task/?id=6204 in MySQL 8.0.0.
MariaDB 10.2.7 was released as GA in July 12, 2017.
https://mariadb.com/kb/en/library/mariadb-1027-release-notes/
[…] bye bye bug #199, MySQL auto_increment is fixed ! […]
Would a better workaround not just be to stop using auto-incrementing ID’s? Personally, I hate them. I’ve found it’s better for applications to fully define stored records at the time of insert. If such problems persist, then it’s always going to be better and easier to distribute an application if auto-incrementing ID’s are avoided.
Last-year I helped a client to migrate a tiny e-commerce system that basically had so many problems it was cheaper to ditch (it was off the shelf not working, not bespoke and we moved to off-the-shelf that had less moving pieces). Because the system used auto-incrementing ID’s there was so much extra work avoiding collisions that would not have been the case had a system like UUID been used, or a composite PK.
Hi Lewis,
Usually most of MySQL DBAs caring about InnoDB really like auto_increments. UUID can be very bad for performance as it might require a rebalancing of the cluster index. Also each secondary index contains the PK hidden. So you will waste a lot of diskspace too.
To workaround this, in MySQL 8’s shell we are changing the ew uuid fonction that creates sequential UUIDs to reduce the impact on InnoDB (with something similar to select replace(reverse(uuid()),’-‘,”);). Also I recommend to not store them as CHAR or VARCHAR but as VARBINARY.
Check http://mysqlserverteam.com/mysql-8-0-uuid-support/ and
[…] ที่มา – MySQL, Le Fred […]
CREATE SEQUENCE would seem to be the standard way of assigning unique identifiers. In MariaDB 10.3, a SEQUENCE is a table with no transactional logging. For InnoDB, it is similar to how persistent AUTO_INCREMENT is handled in MySQL 8.0.0 and MariaDB 10.2.4.
Persistent AUTO_INCREMENT adds one source of confusion that I did not think of: If an operation is rolled back in full or in part, neither LAST_INSERT_ID nor SHOW CREATE TABLE will reveal the current persistent AUTO_INCREMENT value. If the server is restarted after such a failure, there would be a larger ‘gap’ in the AUTO_INCREMENT values than there would if there was no restart. For the MariaDB 10.3 SEQUENCE this confusion does not exist, because all the state is exposed on the SQL layer.