Hope someone can help me debug this issue.
There is a MySQL server 5.6.22 running on OS X 10.2. The load isn't too heavy. All databases are stored in InnoDB. From time to time it stop accepting connections from PHP applications and said applications return with a 2006 "gone away" error. Both PHP and MySQL are on the same machine. External connections are not allowed.
It works for a while after restart and then some arbitrary amount of hours later it starts denying some connections. If I keep it running like that some time later the ratio of denied vs accepted connections grows to the point of the server becoming unusable.
Connecting via the command line works. Nothing in the error logs (warning mode turned on). Restarting the server helps. "FLUSH TABLES" helps equally good. What's strange is if I issue "FLUSH TABLES tb_name" it suddenly starts denying all connections via PHP (not just some of them) until I issue "FLUSH TABLES" without a db name. I tried flushing every table sequentially by name but it doesn't help.
None of the suggestions from MySQL documentation or tinkering with various related variables helped.
This started happening when the OS was 10.9 and I think MySQL server got upgraded to a newer version at some point, although I am not 100% certain if that was the thing that introduced the unstable behavior.
I tried looking at global status, current connections et cetera – nothing out of the ordinary. I.e. the server doesn't get flooded with connections that don't get closed for example and I set a very short timeout for it anyway. It also doesn't have too many open files. The system is also mostly idling, lots of available CPU time and memory.
Any suggestions on where to look to get the root of the problem? It's not nice that the logs don't show anything and basically the only clue I have at the moment is that "FLUSH TABLES" works its magic, for whatever reason. If only I could break that command down to bits and execute them separately to see what part alleviates the problem…
Any suggestions on where to start looking?
Best Answer
So the answer is that really you should only use MariaDB on OSX. Neither Percona or official MySQL distribution will handle the gone away error which arises due to a different mechanism of file locking. All detailed here: https://bugs.mysql.com/bug.php?id=79125
MySQL guys are reluctant to issue a fix but apparently MariaDB had this fixed for a long time. I use this in production and never had a problem since. And just in case, I didn't use ports or brew to install it, simply compiled from source after installing cmake. Hope this helps someone.