3

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 |
Thomas Lomas
  • 131
  • 1
  • 1
  • 5
  • 1
    Check your MySQL logfile for any error messages. – etagenklo Dec 07 '13 at 22:04
  • Tried rebooting/restarting MySQL? Indeed also check MySQL error logs, usually somewhere in /var/log or /var/lib/mysql file is usually called [hostname].err. If you use Linux you can try to find it run: `find / -name *.err` – edwardmp Dec 07 '13 at 23:46

2 Answers2

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.

ceejayoz
  • 32,469
  • 7
  • 81
  • 105
Dennis Kaarsemaker
  • 18,793
  • 2
  • 43
  • 69
  • And a relevant older thread ... http://stackoverflow.com/questions/10904044/need-to-clear-mysql-innodb-lock-without-restarting-database – Nikolaidis Fotis Dec 09 '13 at 09:36
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;

Nikolaidis Fotis
  • 1,994
  • 11
  • 13
  • 1
    Tried a restart and it didn't work. Also, the FULL PROCESSLIST only shows 1 query and that is the PROCESSLIST query. – Thomas Lomas Dec 08 '13 at 12:44