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.

  • 1
    Look for SQL "delete"s in your code. – tater Aug 5 at 14:42
  • Do you check for errors after each INSERT? – Rick James Aug 5 at 15:56
  • Edited for more info for both question. – Paiboon Panusbordee Aug 6 at 15:53

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.