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)

What code occurs before the `beginTransaction()` line? Please edit to show more context there. In particular, were there previous queries which may not have had all rows fetched, or other situations which may interfere with the connected state of `$DBH`. — Michael Berkowski, Dec 02 '15 at 14:37
what is the query you are tring to run like? give us an example — Elentriel, Dec 02 '15 at 14:38
please give an example, or tell the size of operation you are doing — Amir, Dec 02 '15 at 14:41
Also, what happens if you test without a transaction, executing plain `query()` or `prepare()/execute()` sequences instead? Same error, different error, or successful queries? — Michael Berkowski, Dec 02 '15 at 14:48
I meant to post real code. The state of the object `$DBH` is important. The queries themselves may be important. It sounds like you are losing the client connection to mysql, possibly because of a timeout, but if it fails inside the insert loop after 6-7 iterations, then it does not make sense that the error is reported at beginTransaction(). — Michael Berkowski, Dec 02 '15 at 16:20
What does [`$DBH->inTransaction()`](http://php.net/manual/en/pdo.intransaction.php) return after each insert? — Damian Yerrick, Dec 02 '15 at 16:31
PS, because MySQL is retarded, if you truncate, alter, or create a new table, inside a transaction, your transaction will be committed automatically and transaction ended, without warning, IIRC — hanshenrik, Dec 02 '15 at 18:41
Doesn't this approach work for you? http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query — ovi_mihai, Dec 08 '15 at 18:10

score 1 · Answer 1 · answered Jan 25 '16 at 06:12

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.

score 1 · Answer 2 · answered Mar 21 '16 at 08:34

1

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.

answered Mar 21 '16 at 08:34

Zoli

1,081
1
8
28

score 0 · Answer 3 · answered Dec 09 '15 at 13:27

There is a possibility that the connection to the database is not persistent. This might cause the transactions to wipe out before commiting and hence the error.

I would have rather commented as this is not an "answer" per se , but unfortunately I dont have the required reputation.

PDO beginTransaction failure

3 Answers3