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.

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.

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.