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

3
votes

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.

2
phpmysqlpdo
MySQL has per-connection timeouts. Since PDO and mysql_*() are two different environments, it's probable they have different timeouts configured. – Marc B
i guess Table Lock could be causing it. – Khurram Ijaz
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
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
@Cadrach - are you using socket for both connection? or via http port? – ajreal

2 Answers

1
votes

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.

-1
votes

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