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.

Dress

Can you show some code? What are your command timeouts set to? – Emissary

the problem isin't related to timeouts or memory limit because i've disabled the first and set to a very very very high value the second – Dress

Where I work we develop using a local Apache 2.2/PHP 5.3.3 on a Windows XP Pro SP3 too, and a shared test database server, and we get this error all the time. HOWEVER, the shared Apache/Linux setup, whose versions I'm not aware of, do not give this error, so this might suggest the problem might be caused by Apache version or something like that instead of database configuration or usage! – Mozellamozelle

@DanieleSalvatoreAlbano i have the exact problem. i tired many thing. did you finally solved this? – Distorted

The error message is fairly generic and the problem itself is triggered by the died network connection, that may happen for many different reason. Are you using unbuffered queries? Does the query take a long time to execute if you use a mysql client to test it? It's very likely that you will need to tune the timeouts (globally in mysql or per session if yiu can't manage the mysql instance you are using) – Dress

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.

Quintet

Initially table type was myisam, later i switched to innodb to do a test but the problem remained. However actually i didn't get it anymore, i must hyper-stress the server to get it – Dress

This didn't work for me, even when the table is copied and I am 100% sure nobody was writing to it. – Nucleolus

Note that internal temporary tables (created by MySQL for some queries, see EXPLAIN) will use MyISAM in MySQL 5.6: On-disk temporary tables are managed by the MyISAM storage engine Hence you might end up with this error while query seems to only involve InnoDB tables (but actually requires a temporary table that MySQL 5.6 will create using MyISAM) – Marchelle

Answer 2

The actual issue is that the connection between PHP and MySQL was broken (=stopped before all data were received by PHP).

When PHP (PDO) does a MySQL Query, it sends the query on the opened connection, then waits for the response. The response consists of a set of headers, and a body, somehow like an HTTP request.

In case the connection is broken while PDO hasn't received all headers, then you will get a Warning "Error reading result set's header", meaning that PDO cannot interpret the response, since it's partial (headers) only.

If the connection is broken while the body is being parsed, then, PDO will yield a "Empty row packet body", corresponding to your error. See this Github PR for additionnal infos

So the fix is to find why the connection was killed:

• Was it because connection timed out? Then try fixing the configuration as suggeested
• Was it because the connection got manually killed by `KILLè command? Then ask the killer to stop doing that
• Was it because Mysql memory got full and your instance got killed by your web hosting? Then reduce the resultset size/get a bigger MySQL server/ask for more RAM
• Was it because a "deadlock" occured, so Mysql arbitrarily killed a connection (more likely to happen with MyISAM tables, including internal temporary ones)? Then try using InnoDB
• Was it because the hardward connection got interrupted, like a bad wire/wifi reception between PHP and MySQL? Then fix the hardware.
• Was it because a dump program asked to kill all connections for processing the dump? Then wait for the existing connections to finish before running the dump

Marchelle

thanks for taking the time to answer but the thread is fairly old, in 9 years quite a few things can change even if your analysis / questions are absolutely valid :) – Dress

@DanieleSalvatoreAlbano It's actually still a today issue, as it's not MyISAM related (it will occur on InnoDB too) so yes, it probably won't help the OP, but knowing that the cause is the connection being killed/closed while sending the body data will help other people landing here from Duckduckgo (or an evil search engine : ) ) – Marchelle

absolutely but that's exactly why posting in a very old thread is not worth. People that will land in this page will most likely just move on because of if, the title mention PHP 5.3.3 and the post the usage of Windows XP and Apache 2.2 and thankfully why the latter may still be in use somewhere the first two aren't anymore. – Dress

It was useful for me. I was troubleshooting some query and killed a process to not wait for it to end the long query. I later saw this notice in the error log and couldn't place it at that time. When reading this explanation, the penny dropped. – Wagoner

Answer 3

Having this error a while a ago, I've fixed it by increasing the value of

net_read_timeout = 360
net_write_timeout = 360

While a connection is open on write, waiting for another query to end to continue inserting, this times out, giving an empty row packet. I'm working on very large dataset, used value are over 360. Your value will depend on your use case.

Ratal

Where to put these lines, in which block? – Occasion

In your mysql configuration, usually called my.conf, in the block mysqld – Ratal

Perfect, I wasn't sure about a block. Thanks! – Occasion

Answer 4

user589182's answer is spot on. I am doing essentially the same thing: Read a large result set unbuffered, and update some of the rows in the same table, from the same PHP script. I got the exact same error message after approx. 2500 UPDATEs. Problem solved after switching from MyISAM to InnoDB.

Suh

well, i'm on innodb but if i hyper-stress mysql, i get the error. – Dress

@DanieleSalvatoreAlbano i have the exact problem. i tired many thing. did you finally solved this? – Distorted

Answer 5

I got the same error, and I was also reading a large result set unbuffered while updating the rows of the same table.

Instead of switching to InnoDB, a better solution might be to create a temporary table containing only the primary key of the original table. Then, loop through that temporary table while selecting and updating one row at a time from the original table. You will have to use two separate MySQL connections in order to do this, otherwise you will get a "Commands out of sync" error.

You might need to lock the original table to prevent anyone else from reading/writing to it while this is happening.

Banister

Answer 6

I was on InnoDB and never had this type of problem until I switched coding environment(literally, like the location). So, if you changed your wireless connection, that may be the problem especially if its a public place.

Pollywog

Answer 7

I'm having the same problem now, reading huge unbuffered query. All the tables are InnoDB.

And it stops when another process starts mysqldump. So that might be the reason as well.

Gromyko

Answer 8

My "Empty row packet in body" and a few other related random errors was caused by a network router.

By bypassing the router and connecting directly out to the internet I had no more strange errors like this.

• Run the query directly in PHPMYADMIN and see if you experience an error, if not then check the local environment like networking for issues.

Ephesian