Strange MySQL error "Empty row packet body" when using mysql_fetch_object (PHP 5.3.3)

Question

I get a really strange, pointless and totally random error when I fetch rows from a resource (query) using PHP.

My development machine is a Windows XP SP3 with Apache 2.2 while MySQL runs on a virtual machine, using ubuntu 10.04, with 768mb of ram, 100GB of HDD and 4 logic cores (Intel q6600). However this problem is not related to PHP on windows because I get the same error when I run the code on the database machine.

I'm using mysql extension (not mysqli or mysqlnd), but looking around I founded a patch regarding this error related to mysqlnd extension, so, probably, I should try.

The main problem is that when I execute this query (a really big query with a couple of derived table and more than 20 joins) and process results fast and all goes well, but when my code spent around 15/20 seconds to process a block of rows (I need build an object from a block of rows linked in a really particular way between them, I can't change this, database isn't mine, and make some PDF from this object) after a while (random time) I get this error "Empty row packet body".

I use unbuffered queries to reduce memory consumption (if I enable buffering I get around 260MB of used memory) but this shouldn't be the problem.

Answer 1

I ran into the same error. I was using PDO, but it should basically be the same thing.

Are you operating on a MyISAM table? If so, the problem is probably related to the locking model this Engine uses: it locks the whole table, for reading with a shared lock, for writing with an exclusive lock.

This is what I was trying to do: Read a large result set unbuffered, and update some of the rows in the same table. Since you can't issue a statement on the same connection while it holds an unbuffered result set, I tried using another connection for the updates. Reading went well until the first update, at which point the script stalled for about a minute, then I got the "Empty row packet body" error.

You see, when reading unbuffered, the shared lock is kept until the whole result set has been read or the cursor is being closed. During that time, the table is locked with a shared lock, so other connections can obtain shared locks on the table (in other words, read from it), but exclusive locks (for writing) will have to wait. If this happens within the same script, it will deadlock.

Now, to prevent endless deadlocking, MySQL will forcibly release your shared lock after a while (IIRC this is affected by the value of table_lock_wait_timeout), dumping your result set and allowing the writing statement with the waiting exclusive lock to get its turn.

So, while in my case it was the same script that did this and therefore stalled until the timeout expired, it might also be that some other script is attempting a write operation on the table with the same effect, which is probably what happened in your case.

What solved the problem for me was changing the table type to InnoDB, since that Engine uses row-level instead of table-level locks. However, since you say the database is not yours, this might not be possible for you.