MySQL SELECT query hangs

Question

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even doing a:

mysql> use INFORMATION_SCHEMA;
mysql> SELECT * FROM TABLES;

hangs on me. The working databases seem fine (at least the websites are online). When I tried to access the bad database websites, it will just keep spinning.

If I do a:

mysql> use rbb;
mysql> SELECT * FROM wp_posts;

It will just hang.

My server is running CentOS 6.2 and up to now i've had no issues.

Any pointers would be much appreciated. I have no idea what to do next.

Even opening a Sequel Pro to the server hangs the application.

If I try a query and then check the process list in another connection, it shows:

+----+------+-----------+-----+---------+------+----------------+------------------------+
| Id | User | Host      | db  | Command | Time | State          | Info                   |
+----+------+-----------+-----+---------+------+----------------+------------------------+
| 10 | root | localhost | rbb | Query   | 45   | Opening tables | SELECT * FROM wp_users |

score 3 · Answer 1 · edited Dec 08 '13 at 17:23

3

According to the mysql documentation, the 'opening tables' state shouldn't be seen unless there are locks getting in the way, or when your table_open_cache is too small.

Check mysql's errorlog to find out what happened, check the show engine innodb status output for innodb info.

edited Dec 08 '13 at 17:23

ceejayoz

33,432

answered Dec 08 '13 at 16:26

Dennis Kaarsemaker

19,819

score 0 · Answer 2 · answered Dec 08 '13 at 03:40

0

Maybe a mutex / lock went wild ?

Probably a restart will fix. Otherwise you can check if there are any locks and release them by hand.

Try with SHOW FULL PROCESSLIST;

answered Dec 08 '13 at 03:40

Nikolaidis Fotis

2,052

MySQL SELECT query hangs

2 Answers2