I've got multiple computers with MySQL 5.6.21 installed. Some were upgraded from 5.5, others have only ever had 5.6 on them. Every one of them lock up every so often during routine SQL queries to where our PHP scripts can't run any further queries whatsoever. When the database locks up, there's two options to get it running again:
- restart the mysql server
- kill the process that's holding everything else up
All the MySQL servers are running InnoDB databases and seem most likely to freeze up when running queries in quick succession or when running non-finalized queries that aren't written correctly, have syntax errors, etc during the development process. I've noticed that the process that are causing the database to lock up always seem to be a 'Sleep' Command (at least that's what "show processlist" indicates).
It should be noted that when these computers were working with MySQL 5.5.x the databases never locked up. These databases aren't networked in any way. These are developer machines with databases setup on localhost.
This seems very similar to someone else I knew that tried MySQL 5.6.x. He was running some queries within a threaded web application. Under MySQL 5.5.x, the large load of queries ran just fine, but after he upgraded to MySQL 5.6.x, he noticed that the threads that ran a bunch of queries all at once seemed to crash/lock-up the server. So, he just reverted back to MySQL 5.5.x.
Has anyone else come across anything like this?
I've read through these two threads:
MySQL 5.6 deadlock for locking the same rows twice?
https://dba.stackexchange.com/questions/87016/only-innodb-databases-locking-up/87017#87017
The first thread mentions it's a insert select issue. But we're coding in PHP and so don't ever execute multiple queries at once. And our databases lock up regardless of if transactions are being used.
The second thread mentions a bunch of InnoDB settings in the my.cnf file. We don't have any InnoDB settings configured in the my.cnf file, and I'd be really surprised if InnoDB's out-of-the-box MySQL settings caused the database to totally lock up.
Finally, here's what the my.cnf file looks like:
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
open_files_limit=5000
long_query_time=1
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
[mysql.server]
user=mysql
basedir=/usr/local/mysql
[client]
socket=/tmp/mysql.sock
Thanks so much for your suggestions!
****** Update 08/04/2015 ******
After a few weeks of dealing with and looking into the issue, I've found that in fact MySQL doesn't lock up (because I can continue to use access the database via the terminal and other database tools such as Sequel Pro just fine). Nor does PHP lock up (because pages that are not dependant on the database load just fine).
So, something is causing PHP to not be able to connect to the MySQL server because PHP thinks the MySQL server has gone away or doesn't exist. That something seems related to one or multiple processes in MySQL's "show processlist" command. Are there any known compatibility issues with PHP Mysqli/PDO and Mysql 5.6? I've now seen this type of issue on OSX and Ubuntu servers running vastly different PHP code.
I turned logging on in MySQL to see if it's a certain type of query that causes the issue, but there's no one thing that causes it. Whenever the issue starts, the last PHP query is always a valid query that I can run in the terminal with no problem. Running the query again in PHP doesn't even guarantee that the issue will arise again.
Thanks for the help!
****** Update 11/16/2015 ******
As promised to Peter A, here's a screenshot of what the processlist looks like on one of our development machines last time a MySQL server locked up.
Interestingly, I couldn't create new MySQL connections, but could continue to query the same database via connections I already had open in SqlPro or the terminal. Killing process 109 didn't make any difference. Nothing would get any localhost webpage to load until the entire MySQL server was restarted.
The only common thread I can find when a MySQL 5.6 server locks up is that a PHP application caused it. It doesn't seem to matter what type of database connection the PHP application uses. We've seen PDO, Mysqli, and the deprecated Mysql extensions all cause the MySQL 5.6 server to lock up.
