PHP PDO query waiting for a request on a different MySQL connection, returns "Mysql Server Has Gone Away"

Question

I am having a problem with a website, connecting to a MySQL database using two types of connection on different parts: some PDO, some mysql_connect().

The first part of the website is requesting MySQL using the very classic 'mysql_query()' PHP function. This part makes some heavy queries on geographical data. Some of these requests (already optimized) take a long time.

Another part of the site is more recent, and made using Doctrine via a PDO connection.

The problem is, when one of the big processes is being ran in one browser page (can take around 1minute to process and return the page), if a user opens another page the PDO connection is in sleep mode, and holds the whole page from loading. After 60s (wait_timeout of mysql) the connection is killed, and the PDO gets an exception "The MySQL Server has gone away".

What is strange is that other pages with only classical mysql_connect() and mysql_query() can be run without a problem in parallel, only PDO queries are holding back and eventually dying.

Any input would be really appreciated.

MySQL has per-connection timeouts. Since PDO and mysql_*() are two different environments, it's probable they have different timeouts configured. — Marc B, Sep 13 '11 at 15:24
I am doing reading only with these requests (only SELECT), and the PDO is working on another database entirely. Also, the timeout is not the problem, the problem is that PDO cannot run at all in parallel of the other queries. — Cadrach, Sep 13 '11 at 15:38
There can be various reasons. It would be better if you can log the queries. Also, try "show processlist" in mysql while PDO is trying to run the query.Also, can you try running the PDO query directly from mysql client? It will tell you if the query is actually taking long time. — Vikash, Sep 13 '11 at 15:45
@Cadrach - are you using socket for both connection? or via http port? — ajreal, Sep 13 '11 at 15:47
Show processlist shows the connection from mysql_connect, with the query running, and the connection from PDO as sleeping. — Cadrach, Sep 13 '11 at 15:48
@Ajreal: I am using sockets, and PHP and MySQL are on the same machine. — Cadrach, Sep 13 '11 at 15:50
Closing this question, it was in fact related to the php session being held up on write, preventing the other process from running. session_write_close() resolved it. — Cadrach, Sep 14 '11 at 14:10

score 1 · Accepted Answer · answered Sep 14 '11 at 14:13

1

Closing this question, it was in fact related to the php session being held up on write, preventing the other process from running. session_write_close() resolved it.

answered Sep 14 '11 at 14:13

Cadrach

41
5

1

please remember to come back and accept this answer when the time limit allows – ghostJago Sep 15 '11 at 17:24

score -1 · Answer 2 · edited May 23 '17 at 12:24

There are various reasons that a connection gets closed.

Reference: https://dev.mysql.com/doc/refman/5.0/en/gone-away.html

I too faced the similar problem on using PDO where the hosting administrator kills the connection if it sleeps more than a minute. Hence I came up with my own class which will wrap the PDO class. This will detect whether a connection is closed and will try to reconnect on query execution.

Answer Below

PDO: MySQL server has gone away

PHP PDO query waiting for a request on a different MySQL connection, returns "Mysql Server Has Gone Away"

2 Answers2