1

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

melih
  • 370
  • 1
  • 4
  • 15
  • Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). Are you sure PHP hasn't been upgraded on the new server? – Jay Blanchard Nov 24 '14 at 16:02
  • I am not actually using mysql_insert_id function, I just checked the library we use, it is calling mysqli_insert_id. Thanks for the heads up. We just changed the DB server, PHP is running on the same machine. – melih Nov 24 '14 at 16:04
  • What kind of error checking are you doing on your queries and connection? – Jay Blanchard Nov 24 '14 at 16:19

1 Answers1

0

I have found the problem, somewhere deep down in our application code I have found this query being executed sometimes;

SET autocommit = 0

And it never being set back to 1.

melih
  • 370
  • 1
  • 4
  • 15