Just a short post to document an issue I needed to troubleshoot the past few weeks on my web server in Vegas (Go Knights Go!). I’ve been running Homebrew versions of MySQL for some time now with no issues, but recently was receiving quite a few “Can’t connect to MySQL server” errors on all of my virtually hosted WordPress sites. It was particularly difficult to troubleshoot at first because it wasn’t an all or nothing situation – some database calls would go through, while others would not. All I knew was that the old “brew services restart mysql” command would clear things up for a few hours before the flakiness would return.

When I first started looking at the web server logs, I found a lot of error messages like this:

WordPress database error Table 'wp_termmeta' doesn't exist

There were a ton of these – guessing this was relating to the earlier database corruption issues I had related to InnoDB. Turns out that table really didn’t exist and I found this post that had the required SQL to properly recreate it. I wasn’t really sure if any of the themes or plugins that were looking for this table would actually re-populate the table with data (it doesn’t appear so), but the error messages in the logs have gone away (and everything seems a little faster now). Unfortunately, this did not have any impact on the MySQL connection issue.

Going back to the web server log, I found a number of mysqli errors:

PHP Warning: mysqli_connect(): MySQL server has gone away
PHP Warning: mysqli_connect(): Error while reading greeting packet.

These made me think it was on the database side and not an issue with Apache or PHP. I had tried messing a little with the my.cnf file, but it didn’t seem like any of the changes were having any impact. When I started looking for Homebrew MySQL tips, I first decided to reinstall MySQL while keeping the data directory in place (brew reinstall mysql). That seemed to work fine, keeping users and permissions in place while just recompiling the latest MySQL. All of the virtual sites came back up, but the can’t connect errors reappeared later in the day.

Next up, I was fully prepared to follow these instructions to blow away every sign of MySQL and reinstall from scratch (dumping all of the tables first using the awesome Sequel Pro). While looking at the /usr/local/var/mysql directory, I realized that Homebrew MySQL stores a local.err log in that same directory, which I hadn’t looked at (since Console doesn’t include it by default). This log was chock full of lines like this:

[Warning] File Descriptor 1024 exceeded FD_SETSIZE=1024

That lead me to this excellent post by Derek Jones that had me change a number of lines in the my.cnf file (specifically interactive_timeout = 300 and wait_timeout = 300). Some of the comments in this thread were also useful. I restarted the database server yet again and so far it’s been up and running for 24 hours straight (fingers crossed).

So to summarize troubleshooting 101: read all your logs, Google the errors and be thankful for those that blazed a path before you (and took the time to document it online).