After 5 years, several rows disappeared from mySQL innoDB table

Question

I have several DDBB on InnoDB (MySQL 5.5.7. -FreeBSD). For 5 years I have not any problems. I realize periodically check tables, optimization, ...

Mysteriously, a table of one of the DB has lost 20 rows from 70 (DELETE!). These rows were inserted several years ago. No relationship between them (random ID's). It's a very small table.

I have not found the cause after several hours of research (and Google). I have recovered the information through the last backup.

I checked:

WEB APP:

1) The application has no DELETE statement, only SELECT or UPDATE.

2) No DELETE ON CASCADE, no foreign key.

2) Protected SQL INJECT.

3) There is no application management tables (as phpMysqlAdmin).

4) My app log no shows attempt to attack or access during those hours.

MYSQL:

1) All verifications of rows have been made directly in the mysql console without using the APP.

2) mysqlcheck: no error in affected table.

3) Mysqldump: no dump the disappear rows, only remaining rows.

4) Error log: no registered error.

5) The table.idb file no contain the lost records, only remaining rows.

6) mysql user is only accessible locally (by IP).

SERVER:

1) Anyone has accessed the server.

2) No errors have occurred on HDD, or on the controller.

3) I do not see incidents in the system logs.

Apparently everything is correct. I do not know what happened.

I think of two options:

1) A bug in MySQL 5.5.7 ???

2) In the hours that the records were lost, I was doing an import (on different database) of million of INSERT and DELETE. I do not think this intense process has damaged (without a trace) another table in another database.

I'm worried about if it happens again!

Thanks!

UPDATE 1 @pala_ has recommended me to consult the bin-log (I had not looked!).

There in the bin-log 20 queries with the famous DELETE!

I paste the bin-log:

(...)
BEGIN
/*!*/;
# at 83069675
# at 83069772
# at 83070746
# at 83071672
# at 83072677
#150505 12:29:18 server id 168291  end_log_pos 83069772         Table_map: `affected_database`.`affected_table` mapped to number 583255
#150505 12:29:18 server id 168291  end_log_pos 83070746         Delete_rows: table id 583255
#150505 12:29:18 server id 168291  end_log_pos 83071672         Delete_rows: table id 583255
#150505 12:29:18 server id 168291  end_log_pos 83072677         Delete_rows: table id 583255
#150505 12:29:18 server id 168291  end_log_pos 83073123         Delete_rows: table id 583255 flags: STMT_END_F
### DELETE FROM affected_database.affected_table
### WHERE
###   @1=xxxxxxx
###   @2=xxxxxxxx
###   @3=xxxxxxxxxx
###   @4=xxxxxxxxx
###   @5=xxxxxxxxx
###   @6=xxxxxxxxx
###   @7=xxxxxxxxxx
###   @8=xxxxxxxxx
###   @9=xxxxxxxxxx
###   @10=xxxxxxxxxxxx
###   @11=xxxxxxxxxxx
###   @12=xxxxxxxxxxx
###   @13=xxxxxxxxxxx
###   @14=xxxxxxxxxxx
###   @15=xxxxxxxxxxx
###   @16=xxxxxxxxxxx
###   @17=xxxxxxxxxxx
###   @18=xxxxxxxxxxx
###   @19=xxxxxxxxxxx
###   @20=xxxxxxxxxxx
###   @21=xxxxxxxxxxx
###   @22=xxxxxxxxxxx
###   @23=xxxxxxxxxxx
###   @24=xxxxxxxxxxx
### DELETE FROM affected_database.affected_table
### WHERE
(...) x20

How it was run?

Thanks

Answer 1

The DELETE seem to have actually been run according to you bin-log. The more coincidental options (bug in MySQL, issue during an unrelated import (if it is indeed unrelated) ) seem therefore unlikely.

We are left with 2 choices: 1. "authorized" but unknown, e.g. there is a delete methode in your codebase but you just didn't find it or someone with a valid login ran a command (you don't need an application for this per se, but you do need access to a server that allows logging in to your database) 2. unauthorized: Someone gained access to your system (and cleaned up), or someone found an sql-injection

It's really hard to say which is the most likely. From the checks you did it is hard to say how thorough you were, but both the access to the system (no incidents) as the issues with the code (protection from injection) are tricky to find out completely. On the other hand, if there are a lot of people with possible access to the system it's really hard to rule that out.

If you really feel this might happen again you should probably enable the general query log. That would give you a hint of the source. You could look it over every day and rotate it so it doesn't get too large.

If there's paranoia-level issues with your server, you could try to save your logs externally, in a place only you have access to: this would eliminate any tampering-of-evidence. I wouldn't go that far yet myself.