My innodb new rows disappear mysteriously

Question

I used percona server 5.7 as my database on AWS. The problem is there's sometimes rows disappear mysteriously without a clue. My Backend using Node.JS already got inserted id from auto increment but after that, that rows is disappear without a trace. This happened only sometimes but happened everyday. What should I do to start diagnosis the problems?

More info: There's sometimes my auto increment is skipped like from 1000 to 1004 without anyone delete it from phpmyadmin or in my script since it's "user" table. At time registered time between 1000 to 1004, there's no error log at all from mysql and nodejs. However at this time MySQL experience some table Locked issues for unknown reason and sometimes make insert statement to be queued event I do not make alter table or any table level change at all.

Look for SQL "delete"s in your code. – tater Aug 05 '20 at 14:42 — tater, Aug 05 '20 at 14:42
Do you check for errors after each `INSERT`? – Rick James Aug 05 '20 at 15:56 — Rick James, Aug 05 '20 at 15:56
Edited for more info for both question. – Paiboon Panusbordee Aug 06 '20 at 15:53 — Paiboon Panusbordee, Aug 06 '20 at 15:53

score 1 · Answer 1 · answered Aug 06 '20 at 17:10

Did you use MySQL transaction, AUTO_INCREMENT, and InnoDB/XtraDB?

Did your Node.js MySQL driver uses implicit transaction?

If so, you probably got hit by InnoDB AUTO_INCREMENT sequence gap. You can see the docs here, but in short,

In all lock modes (innodb_autoinc_lock_mode = 0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are "lost". Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the "INSERT-like" statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

If you're that paranoid about deleted rows, use general_log MySQL config parameter to generate query logs, and then check your query log against your missing rows. Note that query logs could be huge, and there will be performance impact.

My innodb new rows disappear mysteriously

1 Answers1