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?