Records missing after insert
Posted by: Paul Nair
Date: April 18, 2013 04:32PM
I have a problem with data not inserting into my database. The application uses PHP's PDO to connect to a MySQL version 5.5.27 server hosted by AWS.
One of my scripts does some queries, including 3 inserts. The first insert is fine, but for the second two inserts, no data appears in my table. These queries are both within functions that are called from various other scripts with no problems. I have my PDO object set to throw exceptions but no exceptions are thrown.
The tables I am inserting into both have auto-increment fields, and I am using PDO's lastInsertId method to get the inserted value from these fields, and it does give me a value. When I check the tables, the auto-increment value of those tables has increased by one, but there is no record under the auto-increment value given to me.
There is nothing in the MySQL error log, but when I look in the general log, I can see the insert statements. When I copy and paste them into the MySQL on the console, they run fine and the data is inserted. I've double checked that my application hasn't started a transaction by called the inTransaction method of the PDO object just before one of the inserts and it came back false. I've set the max_prepared_stmt_count to 1000 in case I was hitting a limit there but that hasn't helped.
After trying lots of things, the only way I've managed to get it to work is before running these two queries, I destroy my PDO object then recreate it. So I guess it's something to do with the code that runs before it doing something to my PDO object, but what I don't understand is how MySQL can take two inserts, not produce any errors, give back an auto increment id and increase the auto-increment value of the table, but not actually insert any data!
All tables are InnoDB and the php is version 5.3.10 running on Ubuntu. Any help on this would be greatly appreciated as I've run out of things to try to solve this now.