Fun MySQL fact of the day: auto_increment is mostly monotonic

On Friday, I left you with a cliff-hanger suggesting that foregoing the use of foreign key constraints can result in undetectable account/identity takeovers, data leakage, data loss, corruption, and so on, and so on. And it can. And not only by accident, but also by a really fun MySQL quirk that exists in all versions of MySQL prior to 8.0.0. It's bug #199. And, well, is it really a bug if it's been documented behaviour since the beginning of time?

The quirk involves auto_increment, which is the mechanism in MySQL and InnoDB that provides monotonic, sequential numeric values that may be used to generate the value for one column in a table. Each table may have a single numeric column marked as auto_increment, and that column will, by default, atomically receive the next number in sequence on INSERT. This is extremely convenient (and often beneficial) for use on the primary key field and is used in such a fashion almost universally. If you aren't using auto_increment as such, you probably should at least consider it, but that's a Fun MySQL Fact for another day.

So, what's the dangerous quirk? Well, until MySQL 8.0.0, InnoDB stored the auto_increment value in memory. Only in memory. When the server crashes or stops, upon restart, the auto_increment value is reset to the result of SELECT MAX(id) FROM account FOR UPDATE.

Uh oh.... Uh oh.

Looking back to yesterday's example, let's see what will happen when we forego foreign keys:

ALTER TABLE user DROP FOREIGN KEY user_ibfk_1;

BEGIN; DELETE FROM account WHERE id = 3; COMMIT; ## -- No error :(

SELECT COUNT(*) FROM users WHERE account_id = 3\G
***************************[ 1. row ]***************************
COUNT(*): 2

SELECT auto_increment FROM information_schema.tables WHERE table_name = 'account'\G
***************************[ 1. row ]***************************
AUTO_INCREMENT: 4

%*#$^@ the server crashed!!
reconnecting...

SELECT auto_increment FROM information_schema.tables WHERE table_name = 'account'\G
***************************[ 1. row ]***************************
AUTO_INCREMENT: 3

Can you guess what happens next? The next account to be created will get id=3, and all the old orphaned user records will have a new owner. Suuure, reeeally careful ordering of DML (Data Manipulation Language) statements can save the day here, but will every developer always be on their game every day in every code path that touches the database? Even when you go on vacation? In my experience, things are going to slip through no matter how careful you are, so, why don't you just keep the foreign keys and spend your brain power solving problems that aren't already solved?

And don't worry, even though this quirk is fixed in 8.0.0, there are still a couple more to talk about in another Fun MySQL Fact Of The Day.