This question already has an answer here:

I am trying to rebuild catalogsearch_fulltext index but the process always get stucked in the middle. Normally it was running 3-4 hours but now it did not finish after 19 hours. I already killed the process and started it again but same problem.

web-user 17756 52.7  0.9 646208 166816 ?       S    09:53  90:20 php indexer.php --reindex catalogsearch_fulltext

Looks like the database table is blocked. I can't run SELECT COUNT(*) as table_size FROM catalogsearch_fulltext anymore. It just runs for ever. ok, now this query works again but indexer is stucked at 92381 of 152362 rows.

Why does is the table blocked and how can I stop this from happening?


I read about innodb deadlocks and SHOW ENGINE INNODB STATUS is showing me one transaction with INSERT INTO catalogsearch_fulltext ... that is running for every ACTIVE 11636 sec:

---TRANSACTION 0 360093642, ACTIVE 11636 sec, process no 14899, OS thread id 139663553517312
47 lock struct(s), heap size 6752, 12055 row lock(s), undo log entries 6018
MySQL thread id 15706, query id 2449417 localhost db-user-1 Table lock
INSERT INTO `catalogsearch_fulltext` (`product_id`,`store_id`,`data_index`) VALUES ('

also it shows ~10 transactions with that looks like they are coming from user search queries :

INSERT INTO `catalogsearch_result` SELECT 763 AS `query_id`, `s`.`product_id`,
  MATCH (s.data_index) AGAINST (' \"coldplay\" \"viva\" \"la\" \"vida\"' IN 
  BOOLEAN MODE) AS `relevance` FROM `catalogsearch_fulltext` AS `s` ...

Is it possible that user queries create this deadlock?

marked as duplicate by David Manners, Fabian Blechschmidt, Keyul Shah, Mufaddal, Marius May 21 '14 at 5:24

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • You can just truncate the table and run your indexes again, does that solve anything? – Paul Hachmang Oct 22 '13 at 11:01
  • @PaulHachmang I did truncate the table every time. – PiTheNumber Oct 22 '13 at 11:03
  • TRY TO RESTART MYSQL – mageUz Oct 22 '13 at 11:08
  • @mageUz Beside the point that it is a cluster server with other systems and a restart would cause data lose for all applications. I don't have root access. Anyway this will not solve the problem. – PiTheNumber Oct 22 '13 at 11:11

This similar question "MySQL keeps hanging (queries stuck on sending data)" has the solution to build the index in a temporary table so it does not get any conflicts. Haven't tried it yet but it looks promising.

  • did you ever try this? – David Manners May 19 '14 at 20:16
  • @DavidManners Yes, I installed this extension and it solved the problem. I am still using it today. – PiTheNumber May 20 '14 at 11:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.