mysql errors after upgrading php and mysql

Question

recently upgraded server to php 5.6.5 and mysql 5.6.22. running drupal 7.

after the upgrade we started having issues.

the server seems to run well for a week or so before starting to throw error pages with:

Notice: PDO::__construct(): send of 128 bytes failed with errno=32 Broken pipe in DatabaseConnection->__construct() (line 307 of /path/to/site/includes/database/database.inc).
Notice: PDO::__construct(): send of 128 bytes failed with errno=32 Broken pipe in DatabaseConnection->__construct() (line 307 of /path/to/site/includes/database/database.inc).
Notice: PDO::__construct(): send of 128 bytes failed with errno=32 Broken pipe in DatabaseConnection->__construct() (line 307 of /path/to/site/includes/database/database.inc).
Notice: PDO::__construct(): send of 128 bytes failed with errno=32 Broken pipe in DatabaseConnection->__construct() (line 307 of /path/to/site/includes/database/database.inc).
Warning: Cannot modify header information - headers already sent by (output started at /path/to/site/includes/database/database.inc:307) in drupal_send_headers() (line 1233 of /path/to/site/includes/bootstrap.inc).
PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in lock_may_be_available() (line 167 of /path/to/site/includes/lock.inc).
Notice: PDO::__construct(): send of 128 bytes failed with errno=32 Broken pipe in DatabaseConnection->__construct() (line 307 of /path/to/site/includes/database/database.inc).

when this happens, not every page shows the error, more like 25% or so.

so far i have had no luck tracking down the cause of the error. it seems to 'reset' if i stop/start mysql. such that if i stop/start i can get another week of no problems and then it begins again. this was making me think that it is a problem with a mysql setting related to connections or something, but i am no expert in this area and so am asking here.

after googling and seeing many posts suggesting to increase 'max_allowed_packets', we tried this but still the same issue occurs.

Answer 1

I added the following items to my.cnf which fixed my PDO broken pipe issues. I am using a homebrew installation of mysql on a macbook pro with 16GB RAM:

key_buffer_size = 384M max_allowed_packet = 100M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M query_cache_size = 128M

query_cache_size = 256M

Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8 innodb_buffer_pool_size=512M

Answer 2

The timestamps on those log entries would be helpful so we can see how much time went by before you received the "MySQL server has gone away..." message.

From the symptoms it seems MySQL is crashing, but it could be a connection issue between the client (Drupal/PHP/PDO) and the MySQL server; "send of 128 bytes failed" is an indication.

Log onto your MySQL server and tell us what's in your MySQL error log (typically in /var/lib/mysql but depends on your OS). Typically the error file is named mysql.err.