Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
up vote 0 down vote accepted

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

share|improve this answer
also: 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


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