2

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)

3 Answers3

0

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

pQd
  • 29,561
  • 5
  • 64
  • 106
  • 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? –  May 15 '10 at 18:30
0

Have you tried without the limit 1?

jneves
  • 1,043
  • 6
  • 15
0

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

cpbills
  • 2,692
  • 17
  • 12
  • also: http://www.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 –  May 16 '10 at 04:40