Corrupt Table?

[lucky760]

Everything has been working well with my database. Yesterday, without having made any changes to it, my index.php stopped loading. To be more specific, when attempting to browse to my page, it just sits until it times out. After many, many hours of trying to debug the page (that, again, I hadn't changed since it was working) I seem to have found the source. One of my tables seems to be corrupt or otherwise damaged because in phpmyadmin I cannot list the contents of the table. A listing of the processes displays "Waiting for table" but never changes (I tried to drop the table so I could recreate it last night before bed and woke up with no difference this morning).

I am guessing it's corrupt and that I need to drop the table and recreate it because it seems to be the only source of error, and it's the only table that I can't list in phpmyadmin.

Do you have any idea how I can fix this? I can't seem to do anything to the table because it just keeps sitting on "Waiting for table" and I know no manner in which I can circumvent it (I'm not familiar at all with the intricacies of MySQL).

[mitchind]

I would first try the Repair Table option from phpMyAdmin.

If that doesn't work, try restoring the table from system backups.

[lucky760]

Thank you for the suggestions mitchind. I've tried to "REPAIR TABLE `tablename`" but it just hangs indefinitely and the process listing shows:
ID | Command | Time | Status | SQL-query
14987877 | Query | 42 | Waiting for table | REPAIR TABLE `table_name`

I figured restoring the table would be the only thing I could do, but I am unsure how exactly to go about it. I've taken the backup and saved just the DROP/CREATE code to a separate file, and in phpmyadmin I upload the file, but then it too just hangs indefinitely on the first query in the backup file. The process listing shows:
ID | Command | Time | Status | SQL-query
14998225 | Query | 14 | Waiting for table | DROP TABLE IF EXISTS `table_name`

So you see, my problem is that I can't even restore the table because I'm always stuck "waiting" for some unknown reason. Do you know of any way around this? This is very frustrating because my site is and will remain down until I can fix this... Ugh.

[edit]
I've found that there are only a few commands that would have the process listing report "waiting for table" and I haven't used any of them :
FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

[mitchind]

It might be out your hands for now - sounds like a couple of MySQL Servers are having problems - I think I saw mysql07 and mysql06 mentioned this morning.

What server are you on?

[lucky760]

Really? Are other people having the same problems with locked tables or "waiting for table" messages? I read people mentioning problems with their mysql servers, but I haven't experienced those (except for the problem connecting through ops). I'm on mysql09. It's been going on since yesterday afternoon and just for a single table, so I suspect there is just something wrong with my database, but I don't think I'll be able to do anything about it myself. Do you know of any way to contact a powweb admin or someone who has the authority to run more powerful queries against my table?

Thanks again.

[mitchind]

You can try logging a trouble ticket to support@powweb.com but it's a holiday today so I'm not sure of their response.

There's also the online chat - but I've never had much success with that.

[coldhead]

This mysql command may work

"KILL QUERY 14998225;"

It wont happen immeditally but it will happen.

info http://dev.mysql.com/doc/mysql/en/kill.html

[lucky760]

My problem was a silly one. I just needed to kill a process, but I never saw any because I kept logging in with a different account than is accessed by my PHP scripts.