PDOException: SQLSTATE[HY000]: General error: 2006 MySQL

MySQL server has gone away.

But where did it go?

PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away:

This can be a tear your hair out kind of error, especially in Drupal. The solution, however, is often pretty simple: MySQL is out of connections. Or, more appropriately, max_allowed_packet is probably set too low in your application, or even might not be set at all!

If you’re running your own server, open my.cnf (CentOS puts it in /etc/my.cnf as default). Find the [mysqld] section or look through the entire file for max_allowed_packet. If you can’t find it add the following:

max_allowed_packet=8M

then close/save and restart

service mysqld restart (for CentOS 6.x)

I would also make sure you have enough drive space as this can help generate this error, as well.

Important! If MySQL 5.1 was the last version you installed, you should know that you must tune MySQL 5.5 and higher. There are significant performance improvements, but  those can only be used after you tune it.  In fact, just adding max_allowed_packet without adding other settings can hurt performance. Ok, so where do I start?

Tuning MySQL5.x

Here’s the very short answer. This should only be used for some very general guidelines. Your mileage will certainly vary based on your application, server resources, load, etc. Your MySQL server should be running for a minimum of 24 hours under some kind of load  before running this, but you can run it now if you just rebooted and then in another 24 hours.

SSH to the server

If you don’t have PERL installed, install it (if it passed security requirements)

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

then run it:

perl mysqltuner.pl

Sample output (trimmed to pertinent stuff- there’s a lot of it)

Variables to adjust:
 query_cache_size (>= 8M)
 tmp_table_size (> 16M)
 max_heap_table_size (> 16M)
 thread_cache_size (start at 4)
 table_open_cache (> 400)
 innodb_buffer_pool_size (>= 6G) if possible.

I’ll say it again: these should be considered starting points, not definitive configurations. Do not rely on this script to do the job of a DBA. There are more settings than these that are simply not output. It can get you out of a jam, however.

Important! Make sure to check the correct statement for your version of MySQL!

Quick example: turning on slow query log

Used in 5.5x

log_slow_queries = 1

Used in 5.6x

slow_query_log = 1

Good luck and feel free to leave questions/comments below!

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.