MySQL trouble shooting help, can't do anything on single table

Question

Apologize but I really don't have much information for the question.

I have a single MySQL MySIAM engine table that's holding around 80K records (continually increase). Today it's suddenly stop responding.

I can't even do a single query (e.g. SELECT * FROM table LIMIT 1), the server just spend time executing and look like will never stop.

I can't dump table to make backup.

However, another tables in the same database, same engine (MySIAM) are working just fine.

I'm not sure where to go from here. Not sure if it's DEADLOCK or anything.

All data in that table is really important. You direction pointing to help me identify the problem would be really appreciated. For example, are there any command to check table if it's corrupt by what reasons, etc.

---

UPDATE::::: I can't use CHECK TABLE neither, it also take forever execution time.

UPDATE :::: I did research and come up with something about REPAIR TABLE. However, it's suggested that I should do the backup first. As I can't do the back for this table, would it be OK to use the REPAIR command anyway?

---

::::::::::::: SOLVED :::::::::::

Follow Cristian's help, use SHOW PROCESSLIST; command. I see that there is a process with state 'Copying to tmp table' that hold another process. So I use KILL <process id> to kill that process and everything released to normal.

Cheers Chanon

Answer 1

Sorry but I can't comment your question... :)

Exactly which version of MySQL you run, 5.1.xx?

Can you post you SHOW PROCESSLIST; status?

UPDATE: Chanon, after this event, and to prevent this problem, you have to review and optimize the query that send MySQL in "Copying to tmp table" state, in order to avoid slowness and a risk of "disk full" for your temporary partition.