[Warning]: PDO::__construct(): MySQL server has gone away - wait_timeout not the cause?

I am hoping that someone on here can help with an issue I am having on a webserver. Currently the server hosts a web application which we have purchased for helpdesking. Every 8 hours when someone navigates to the page they will be presented with this error:

[Warning]: PDO::__construct(): MySQL server has gone away (Database/class.SWIFT_Database.php:334)

I have tried setting the wait_timeout to the max value in the /etc/my.cnf file ie;

wait_timeout=31536000

Also from the mysql I have set the global wait_timeout to this value and set the session wait_time out to the same.

I have noticed some strange behaviour, when you restart the mysql service, the wait timeout resets to the 28800 default, despite it being set in the my.cnf. I can't find any other config files for mysql though happy to look if someone can poi开发者_JAVA百科nt me in the direction.

Also this morning when I logged into the server and ran the following command, the session wait_timeout value had reverted!

mysql> select @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|              31536000 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

I see that this is an issue for a lot of people online with various webapps running on mysql, but no one seems to have a fix. A lot of advice online points to the wait_timeout but it doesn't seem to be changing the bug that I am seeing. I have tried out the fixes on the mysql manual about this issue, but still no luck (link: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html)

Any advice would be greatly appreciated. Server details and product versions below:

Server: OpenSuse 11.4 MySql Version: 5.1.53

Many Thanks In Advance!

While not very elegant, the following snippet has helped me get rid of the timeouts and keep persistent connections. It will throw the exception if the connection fails $limit times in a row, though if the problem is timeouts you will only need 1 retry at most.

$db = null;
$limit = 10;
$counter = 0;
while (true) {
    try {
        $db = new PDO('mysql:host=' . db_host . ';dbname=' . db_name, db_user, db_pass);
        $db->exec( "SET CHARACTER SET utf8" );
        $db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC ); 
        $db->setAttribute( PDO::ATTR_PERSISTENT, true );
        break;
    }
    catch (Exception $e) {
        $db = null;
        $counter++;
        if ($counter == $limit)
            throw $e;
    }
}

This article on Drupal.org may be helpful in your situation:

MySQL comes with a default configuration of the resources it is going to use, specified in "my.cnf" (Linux) or "my.ini" (Windows) during the installation of MySQL.

In Linux this file is located at /etc/my.cnf to set global options, or /usr/local/var/mysql-data-dir/my.cnf to set server-specific options.

In Windows this file is located by default at C:\Program Files\MySQL\MySQL Server X.Y\my.ini.

Resources allowed by the default configuration are normally insufficient to run a resource-intensive application. You must modify the following resource specifications if they are available in your original configuration file, or add them to the configuration file if they are not already specified (because some are not present by default) :

Important: Remember to keep backup files before you do anything! You will also have to reload the MySQL service after making changes to these configuration files.

MyISAM specifications:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer = 384M
max_allowed_packet = 64M
table_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

InnoDB specifications:

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 180

Note: It is assumed here that you are using the InnoDB database tables, as Drupal is a resource intensive application. If you are not using the InnoDB database tables try to change this, in view of the fact that you are getting the Warning: MySQL server has gone away - apparently meaning that your setup is resource intensive.

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