👋 🐛 199 – bye bye bug #199, MySQL auto_increment is fixed !

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 !

 

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

9 Comments

  1. 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.

  2. 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

  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.