Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a very simple mysql script that is running. But it hangs, and runs forever without finishing. What are possible reasons, and how can I resolve them?

delete from guid_target_infos where type_id = 1 and updated_at < '2010-03-14' limit 1

UPDATE: show processlist

|  93 | mysql | localhost             | TwAnalyzer_dev | Query   |  257 | updating | delete  from guid_target_infos where type_id = 1 and updated_at < '2010-03-14' limit 1 | 

(Everything else - Command is SLEEP, and info is NULL)

share|improve this question
    
the script only has this query? If it has other queries, are you sure that the script hangs in this specific one? –  Marco Ramos May 15 '10 at 18:08
    
Yup! Innotop says this query is running has been running forever. I suspect something is locking up the particular table –  mingyeow May 15 '10 at 18:32
    
and when you do a "show processlist" when the delete is running, what does it show? –  Marco Ramos May 15 '10 at 18:46
    
what's the engine? myisam? innodb? another? –  Marco Ramos May 15 '10 at 18:50
1  
just to be pedantic and be certain this isn't true; are you running this from a shell, and did you leave off the ';' on the end? –  cpbills May 15 '10 at 19:11

3 Answers 3

up vote 0 down vote accepted

you could try show table status <tablename>; and see if that shows anything unusual.

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

share|improve this answer
    
also: xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks a guide on analyzing innodb table locks... –  cpbills May 15 '10 at 20:19
    
Thanks! I tried the steps he mentioned. but there seems to be the ONLY transaction - no other transaction is running! And i have even rebooted the server (not just restart the mysql), so i am very confused –  mingyeow May 16 '10 at 4:40

if your storage engine is innodb try increasing innodb_lock_wait_timeout in my.cnf

share|improve this answer
    
It is not increasing timeout. I suspect something is locking it up. How can I see what are the possible things that are locking it up? –  mingyeow May 15 '10 at 18:30

Have you tried without the limit 1?

share|improve this answer
    
hmm, what for? i would not be able to track the progress –  mingyeow May 15 '10 at 18:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.