I have a php application which is inserting rows to table A, very simple query. It runs every 10-15 minutes. Until last week there weren't any problems with it but after a disk failure I moved db to another server and this problem started happening.
There is no error returning, nothing on logs but my INSERT's are missing.
There is a loop which inserts a new row for every cycle.
After each insert, I check for mysqli_insert_id then log it to a file. Then I run a SELECT query to check if that primary field is in fact on the table, yes it is. number of affected rows is 1. But when I run a SELECT query from a different mysql connection, before PHP stops executing (so it's connection is still up), nothing shows up.
It looks like a auto-rollback but I haven't found any deadlocks, used pt-deadlock-logger; http://www.percona.com/doc/percona-toolkit/2.1/pt-deadlock-logger.html
I don't know if there is anything else that causes auto-rollback.
BTW, I have also found that, there are other INSERT queries in that PHP loop and they haven't inserted either.
MySQL: 5.6.21 Engine: InnoDB