I am trying to run a sitemap generator job for Magento and here is what I get as Innodb status after the query has failed to complete even after several hours. I do not know much about DBA stuff and would appreciate if someone can give me some pointers on what to do next to resolve this.

160112 19:12:29
TRANSACTION 51E4647, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 3
MySQL thread id 496, OS thread handle 0x7fe32e89d700, query id 74695 localhost root update
INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES ('341014', '298405', '2016-01-12 19:12:03')
RECORD LOCKS space id 0 page no 39353 n bits 760 index `url_id` of table `shopez_demo1`.`log_url` trx id 51E4647 lock_mode X insert intention waiting
Record lock, heap no 1
TRANSACTION 51E12D2, ACTIVE 1643 sec fetching rows
mysql tables in use 1, locked 1
13212 lock struct(s), heap size 1571256, 3349297 row lock(s)
MySQL thread id 456, OS thread handle 0x7fe32e91f700, query id 74740 localhost root Sending data
SELECT `log_url`.* FROM `log_url` LIMIT 2048 OFFSET 339968
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 39353 n bits 760 index `url_id` of table `shopez_demo1`.`log_url` trx id 51E12D2 lock mode S
Record lock, heap no 1
Record lock, heap no 2
Record lock, heap no 3
Record lock, heap no 4
Record lock, heap no 5
Record lock, heap no 6
  • "failed to complete" sounds like it takes really long. But deadlocked queries are killed instantly, not waiting indefinitely. So unless one of the queries shown in that snippet is the one you are talking about, it's no deadlock. Use show (full) processlist; instead to see the query status etc.
    – jkavalik
    Jan 13 '16 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.