PDO: MySQL server has gone away

views:

347

answers:

+1 Q:

PDO: MySQL server has gone away

I have a script that does a lot of legwork nightly.

It uses a PDO prepared statement that executes in a loop.

The first few are running fine, but then I get to a point where they all fail with the error: "MySQL server has gone away".

We run MySQL 5.0.77.

PHP Version 5.2.12

The rest of the site runs fine.

Have a look at the mysql logs (most probably in /var/log/mysql)

Dominik 2010-02-09 20:04:17

+4 A:

The B.5.2.9. MySQL server has gone away section of the MySQL manual has a list of possible causes for this error.

Maybe you are in one of those situations ? -- Especially considering you are running a long operation, the point about wait_timeout might be interesting...

Pascal MARTIN 2010-02-09 20:07:37

I think you are right. my wait timeout is 90 seconds and my code can sometime take a while before executing. Is there a way to change dynamically via PHP just for 1 script?

nute 2010-02-09 20:21:16

or a way to "ping" mysql via php PDO?

nute 2010-02-09 20:24:20

Judging from the doc, the simplest way seems to just re-connect ;; i.e. if you get that error, re-instanciating your PDO class might work

Pascal MARTIN 2010-02-09 20:42:17

but wouldn't that kill my PreparedStatement (as it is attached to a PDO instance)? I am currently testing a work-around: sending "SELECT 1;" once in a while.

nute 2010-02-09 20:44:15

If you are sending queries like your `select 1` from another PHP script, it will probably not change a thing : each process has a separate connection to the database ;; and you quite can't do any kind of multi-threading in PHP ;; so I don't really seen another option than re-connecting when the connection is gone (and, yes, you'll have to re-prepare your statements :-( )

Pascal MARTIN 2010-02-09 20:48:55

No I am sending "select 1" from the same script, the same PDO instance. So far it's wokring. I wanted mysql_ping, but it seems PDO doesn't have it. So I guess "select 1" is the poor man's mysql_ping.

nute 2010-02-09 20:51:21

Try using PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true) on your pod instance(s). Dont know that it will help but with no log data its all i got.

prodigitalson 2010-02-09 20:11:55

It's likely that either your connection has been killed (e.g. by wait_timeout or another thread issuing a KILL command), the server has crashed or you've violated the mysql protocol in some way.

The latter is likely to be a bug in PDO, which is extremely likely if you're using server-side prepared statements or multi-results (hint: Don't)

A server crash will need to be investigated; look at the server logs.

If you still don't know what's going on, use a network packet dumper (e.g. tcpdump) to dump out the contents of the connection.

You can also enable the general query log - but do it very carefully in production.

MarkR 2010-02-10 07:44:37

ansaurus

tags:

views:

answers:

PDO: MySQL server has gone away

related questions