PHP activerecord mysql server has gone away

Question

I'm using php-activerecord for a short while now and i absolutely love it. Php-activerecord is an open source ORM library based on the ActiveRecord pattern. However, i recently tried to use it in combination with a websocket application based on Wrench.

This works perfectly but to start the script the application has to run as a daemon on linux in order to make the websockets always availeble. After a short while of not using the application and then trying to use it again it throws some database exceptions:

At first it gives a warning:

PHP Warning: Error while sending QUERY packet. PID=XXXXX in /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Connection.php on line 322

Then it throws a fatal error:

PHP Fatal error: Uncaught exception 'ActiveRecord\DatabaseException' with message 'exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Connection.php:322

Stack trace:

#0 /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Connection.php(322): PDOStatement->execute(Array)

#1 /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Table.php(218): ActiveRecord\Connection->query('SELECT * FROM ...', Array)

#2 /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Table.php(209): ActiveRecord\Table->find_by_sql('SELECT * FROM `...', Array, false, NULL)

#3 /home/user/domains/example.com/public_html/vendor/php-activerecord/php-activerecord/lib/Model.php(1567): ActiveRecord\Table->find(Array)

#4 in /home/user/domains/example.com/public_html/vendor/php-activerecord/lib/Connection.php on line 325

It seems like php-activerecord is keeping the mysql connection open all the time that the websocket server is running, this ofcourse should not be a problem if it then automatically tried to reconnect and run the query again. But it doens't.

I've read something about setting MYSQL_OPT_RECONNECT. But i'm not sure if that works or how to set that option using php-activerecord. Does anybody here have some experience in this area?

Edit: Here are my global timeout config variables

VARIABLE_NAME                   VARIABLE_VALUE  
DELAYED_INSERT_TIMEOUT          300
WAIT_TIMEOUT                    28800
CONNECT_TIMEOUT                 10
LOCK_WAIT_TIMEOUT               31536000
INNODB_ROLLBACK_ON_TIMEOUT      OFF
THREAD_POOL_IDLE_TIMEOUT        60
NET_WRITE_TIMEOUT               60
INNODB_LOCK_WAIT_TIMEOUT        50
INTERACTIVE_TIMEOUT             28800
DEADLOCK_TIMEOUT_LONG           50000000
SLAVE_NET_TIMEOUT               3600
DEADLOCK_TIMEOUT_SHORT          10000
NET_READ_TIMEOUT                30

Answer 1

PHP ActiveRecord uses PDO. There is absolutely no way to close a PDO connection, it is the wrong DB layer for long running background tasks.

You can try to influence the disconnection of a PDO connection with the following snippet.

//if not using ZF2 libraries, disconnect in some other way
$db->getDriver()->getConnection()->disconnect()
$db = NULL;
gc_collect_cycles();

Disconnect, set your reference to null, then run the garbage collector. The hope is that that will call the PDO's internal __destruct method to actually close the connection.

You must manage your DB connections in your own long running script. You must disconnect if your worker hasn't had to process work in a while, and you must reconnect when you have work.

The real solution is to not use PDO and disconnect and reconnect normally.

If you simply set both server and client library timeouts to be infinite, you'll run into problems with out of control scripts that never die, forcing you to restart the entire server (not a good idea to mess with timeouts).

EDIT: I actually had this exact problem and used this exact solution at work last year. This solved 99% of my problems. But still, every once in a while there wass a stray connection exception that I could not catch and try to reconnect. I simply restart the processes once a day to rid myself of those stray connection errors. That's why my answer is, don't use PDO. Switch now and get real control over disconnects and reconnects.

Answer 2

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection.

Try doing the following change.

max_allowed_packet=64M

If you have a lot of request set this and don't set it to bigger because its related with your environment.

max_connections=1000

Adding this line into my.cnf file might solves your problem. Restart the MySQL service once you are done with the change.

Read more on MySQL server has gone away

If it does not work try this auto-reconnect function as well.

Answer 3

As said, MySQL in PHP scripts times out when there is no communication between the two for some time. That is a good thing, since idle connections would eat up your server resources.

"Server has gone away" error mostly happens when a relatively lenghty computation happens between two queries.

In order to prevent that, you can

• Periodically execute a SELECT 1 query during your execution
• Create a wrapper around your queries which checks if connection is valid before executing
• Use answer from this post

However, I believe that reconfiguring MySQL to keep connection open for longer encourages careless programming and would advice against it.

Answer 4

It could be also the size of the query, as sometimes ORMs combine the queries to improve performance.

Try setting max_allowed_packet=128M, at least should be useful as a diagnose.

Answer 5

If your DB is not handling multiple, concurrent connections and queries You could set "infinite" timeouts. This won't affect DB resources significantly. Best approach is to send ping packets (SELECT 1) to renew timeout and make connection kept-alive.

Answer 6

In order to solve such problem, I suggest you to:

1. Distribute your processes using Gearman job server (http://gearman.org/)
2. Manage those processes easily using Supervisor (http://supervisord.org/)

Here is how.

Run your web socket application as a daemon, just like you already did now (perhaps using cron). Or even better, manage it using Supervisor. Configure it so that Supervisor start it when Supervisor starts and autorestart the daemon if it dies.

Example configuration:

[program:my-daemon]
command=/usr/bin/php /path/to/your/daemon/script
autostart=true
autorestart=true

Next, instead of running the query processing inside the application daemon, create a Gearman Worker to handle it. Once registered, the Worker will be waiting to be run/called. You must call the Worker from your websocket application, together with the necessary workload parameter if necessary (refer to Gearman website for this workload term explanation).

In Worker, set it to stop/exit when it already finish the job requested by the daemon. With this, you won't have the "mysql server has gone away problem" because the connection is immediately closed.

Finally, we have to make the Worker available all time just like the daemon. So, similar to the daemon, configure Supervisor to autostart and autorestart it when the Worker dies/stops, like that:

[program:my-worker]
command=/usr/bin/php /path/to/your/worker/script
autostart=true
autorestart=true

Another interesting thing is that you can add as many Workers as you like to be alive waiting. Just add the following configuration:

numprocs=9 #change it to any number
process_name=%(program_name)s_%(process_num)02d #to identity worker number

Since we told Supervisor to autorestart each processes, we always have constant Workers running in background.

Here is another explanation about this strategy: http://www.masnun.com/2011/11/02/gearman-php-and-supervisor-processing-background-jobs-with-sanity.html

Hope that helps!