PDO beginTransaction failure

Question

I am getting following error in my php error_log

PHP Warning: Error while sending QUERY packet. PID=29770 in /home/test/test.php on line 87

And on line 87 in my php script the code id

$DBH->beginTransaction();

And after that when i commit i get the following error

exception 'PDOException' with message 'There is no active transaction' in /home/test/test.php:98

I have already set the max_allowed_packet=512M and my table is InnoDB

What is wrong here?

EDIT: My code is running in a loop to insert total 100,000+ records into the table i am doing 5000 multiple inserts in each transaction. This is my code structure

beginTransaction
 Multiple insert query for 5000 records
commit

Running in a loop to insert 100,000+ records. Sometimes the script runs successfully sometimes it fails.

I get this error in the middle of 6-7 loops.(after inserting 30k to 40k records)

Answer 1

I did some web searching for the phrase "Error while sending QUERY packet." It was able to turn up this link: http://www.willhallonline.co.uk/blog/max-packet-size-php-error

The author of that post had a similar issue and also tried the "increase max_allowed_packet size" and increased timeouts as well but that didn't work. But what appears to have worked was splitting the job up into smaller chunks.

Our new smaller csv files updated successfully inside MySQL, though we're still none the wiser as to what was causing the original error.

It's not clear to me whether they processed the many chunks within the lifespan of one PHP process or if they ran the code multiple times, but the idea seems sound; maybe you can give it a try.

Answer 2

An other posibility is to write the data in a CSV file and use the MySQL LOAD DATA INFILE to load it to your database (here is the link to MySQL docs). I tryed this with CSV files sized up to 250MB and it worked very well.