Concern regarding auto_increment in MySQL InnoDB

Question

I'm developing and implementing CFR Part 11 compliant system. In each table I create a primary key that is used for audit trail. I implement triggers fired on create, update and delete operations.

I use MySQL 5.7 with InnoDB engine on JawsDB DaaS.

A few days ago I came across the following article: https://desmart.com/blog/be-careful-with-mysql-s-auto_increment-how-we-ended-up-losing-data

The author shared his concern regarding auto_increment mechanism in MySQL. Here is the issue in a few words:

1. You create 10 employee records with auto_increment PK (let's call it "recordID"). The system assigns recordID numbers from 1 to 10.
2. Employee "9" does something in the system and his actions are recorded in audit trail table with his recordID.
3. Employee 9 and 10 are deleted . You delete 2 last records (9 and 10)
4. You restart MySQL server
5. You insert new record and the DB system assigns the newly inserted record's recordID value "9".

The problem is that we already have audit trail on the recordID "9" in the audit trail table.

Here are my questions:

1. Have you ever came across the situation?
2. Is MySQL 5.7 affected by the bug? Shall I upgrade to 8.0 since it is stated here that the bug was fixed in 8.0?
3. What would you suggest to mitigate the risk? Shall I pick another DB engine? MyISAM?
4. Is there any way to ensure that auto_increment does not increment sequentially if last record was deleted?

Answer 1

Don't delete records for which there is a logical collation that you want to keep. Rather than marking deleting employees, mark them as terminated, keeping the row there. Otherwise you'll have an audit record without knowing who it corresponds too.

Option b), for an Audit record, denormalize it, if necessary create a shadow table for the employee. The audit should reflect the employee record at the time it occurred.

No, you shouldn't use MyISAM.

MySQL-8.0 changes behaviour as its manual states (the primary source you should consider for important decisions). If in doubt, test it.