Categories

Error “Warning: mysql_pconnect(): MySQL server has gone away…”

If you are having a reoccurring problem where Mysql refuses connections with the following error message:

Warning: mysql_pconnect(): MySQL server has gone away in C:\WampDeveloper\Websites\domain.name\webroot\script\file.php on line X

Note that you are using persistent connections. Persistent connections do not automatically close when the script ends, and likely what is happening is either…

That same persistent connection, is correctly reused each time the script runs, but eventually it does reach its maximum run-time value, and is closed by MySQL. And only after your page is refreshed/reloaded and the script runs again, a new persistent connection is created and everything works again (until the next timeout).

Many persistent connections are created, and after MySQL reaches the maximum number of allowed connections, no new connections are allowed until some of the previous connections timeout. Sometimes this is due to a large number of users (100+) all accessing the script, other times this is due to issues and bugs in the script itself (and there can also be some complications when running PHP-FCGI).

To fix this, you have several different options:

Default to Regular Connections

Tell PHP to silently use non-persistent connections even when persistent connections are specified…

Edit php.ini (C:\WampDeveloper\Config\Php\) and update or add this into the [MySQL] section…

; Allow or prevent persistent links.
mysql.allow_persistent=Off

This will close each connection when the script ends, regardless if “mysql_pconnect” or “mysql_connect” is used.

Switch to Regular Connections

Don’t use the persistent versions of PHP functions and methods for MySQL connections. Persistent connections are never recommended, and should only be used in special circumstances (because they tend to just cause connection exhaustion while offering no real performance gains).

Instead of “mysql_pconnect” just use “mysql_connect“. Both functions use the same exact API (i.e., parameters), and you can safely search-and-replace all occurrences of “mysql_pconnect(” with “mysql_connect(” in your script files.

Increase Max Connections and Timeout Values

Edit my.ini (C:\WampDeveloper\Config\Mysql\) and add this into the [mysqld] section…

Increase the number of MySQL connections allowed:

max_connections = 151

* The above is the default value.

Depending on the situation, decrease the persistent connection timeout value (so those persistent connections are closed and recycled faster):

interactive_timeout = 28800
wait_timeout = 28800

* The above are the default values, in seconds (8 hours).

These settings might not exist in my.ini, you have to add them into the [mysqld] section of the file.

Note that:

1. These are the default values for each setting… If you put them “as-is”, they will have no effect… As those settings + values, even while they might not be present in my.ini, are already set by default by MySQL for itself on each start-up.

2. These values don’t technically fix anything, they just try to mitigate around some underlining problems with connection timeouts caused by issues in the script. They are temporary patchwork (in most cases).

3. Take care setting these values correctly. max_connections might need to be doubled, and/or the timeout value might need to be lowered to 300 (seconds). But if you increase or decrease these values too much, other issues could start happening.

4. Don’t use these settings if they do not fix anything.

5. You’ll need to restart Apache and MySQL after making changes to my.ini and php.ini.