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:
- You create 10 employee records with auto_increment PK (let's call it "recordID"). The system assigns recordID numbers from 1 to 10.
- Employee "9" does something in the system and his actions are recorded in audit trail table with his recordID.
- Employee 9 and 10 are deleted . You delete 2 last records (9 and 10)
- You restart MySQL server
- 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:
- Have you ever came across the situation?
- 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?
- What would you suggest to mitigate the risk? Shall I pick another DB engine? MyISAM?
- Is there any way to ensure that auto_increment does not increment sequentially if last record was deleted?