Sometimes Magento indexes hang up unexpectedly. They are shown as running in Magento backend but looking at the process status (@see https://www.ask-sheldon.com/list-processes-after-cpu-usage/), you recognize that the respective process doesn’t do anything. Other common problems are, that there are payment transactions failures when price index is running (catalog_product_price @see http://magento.stackexchange.com/questions/260/price-re-index-causes-db-deadlocks-during-checkout) or that the search doesn’t work properly, if the catalogsearch_fulltext-index is running.  And there are a lot more situations, for that it is good to know, how to stop running indexes.
Get index statuses
To get a list of Magento‘s indexes and their statuses, you can use the indexer commandline tool of Magento (MAGENTOROOT/shell/indexer.php):
$> php mage/shell/indexer.php info # just a list of index idetifiers and names $> php mage/shell/indexer.php status # status list
The last command should give you something like that:
Category Flat Data: Pending Product Flat Data: Pending Stock Status: Pending Catalog product price: Pending Category URL Rewrites: Pending Product URL Rewrites: Pending URL Redirects: Pending Catalog Category/Product Index: Pending Catalog Search Index: Running Product Attributes: Require Reindex SEO URL Rewrites (MANAdev): Pending Tag Aggregation Data: Pending SEO Schemas (MANAdev): Pending Default Values (MANAdev): Pending
As you can see Catalog Search Index has the status Running, what caused a non working search (and me to write this article ;-)).
Another possibility is to just query the Magento database directly:
SELECT * FROM `index_process;
What should give you a result list like that:
| # | process_id | indexer_code | status | started_at | ended_at | mode |
|---|---|---|---|---|---|---|
| 1 | 1 | catalog_product_attribute | require_reindex | 2016-05-04 08:11:49 | 2016-05-04 08:14:43 | real_time |
| 2 | 2 | catalog_product_price | pending | 2016-05-04 08:10:41 | 2016-05-04 08:10:41 | real_time |
| 3 | 3 | catalog_url | pending | 2016-05-04 08:14:50 | 2016-05-04 08:14:50 | real_time |
| 4 | 4 | catalog_product_flat | pending | 2016-05-04 08:09:02 | 2016-05-04 08:09:02 | real_time |
| 5 | 5 | catalog_category_flat | pending | 2016-05-04 08:09:01 | 2016-05-04 08:09:01 | real_time |
| 6 | 6 | catalog_category_product | pending | 2016-05-04 08:11:34 | 2016-05-04 08:11:34 | real_time |
| 7 | 7 | catalogsearch_fulltext | running | 2016-05-03 05:10:19 | 2016-05-03 05:18:32 | real_time |
| 8 | 8 | cataloginventory_stock | pending | 2016-05-04 08:10:28 | 2016-05-04 08:10:28 | real_time |
| 9 | 9 | tag_summary | pending | 2016-05-04 08:15:20 | 2016-05-04 08:16:54 | real_time |
| 10 | 10 | url_redirect | pending | 2016-05-04 08:11:34 | 2016-05-04 08:11:34 | real_time |
| 11 | 11 | targetrule | pending | 2016-05-04 08:16:54 | 2016-05-04 08:16:54 | real_time |
| 12 | 12 | catalog_url_category | pending | 2016-05-04 08:11:16 | 2016-05-04 08:11:16 | real_time |
| 13 | 13 | catalog_url_product | pending | 2016-05-04 08:11:19 | 2016-05-04 08:11:19 | real_time |
| 14 | 14 | catalog_category_product_cat | pending | 2016-05-04 08:11:49 | 2016-05-04 08:11:49 | real_time |
| 15 | 15 | mana_db_replicator | pending | 2016-05-04 08:14:43 | 2016-05-04 08:14:50 | real_time |
| 16 | 16 | mana_db | pending | 2016-05-04 08:14:43 | 2016-05-04 08:14:43 | real_time |
| 17 | 17 | mana_seo_url | pending | 2016-05-04 08:16:52 | 2016-05-04 08:16:54 | real_time |
Stop running indexes
Now it is time to bring it to an end 😉
With the help of the following steps I stop running indexes when necessary:
- Set index mode to maual:
$> php mage/shell/indexer.php --mode-manual
This changes the index mode of all indexes to manual. You can check results by calling …
$> php mage/shell/indexer.php --mode
… to get a list of indexes with their respective index mode settings like that:
Category Flat Data: unknown Product Flat Data: unknown Stock Status: unknown Catalog product price: unknown Category URL Rewrites: unknown Product URL Rewrites: unknown URL Redirects: unknown Catalog Category/Product Index: unknown Catalog Search Index: unknown Product Attributes: Manual Update SEO URL Rewrites (MANAdev): Manual Update Tag Aggregation Data: Manual Update SEO Schemas (MANAdev): Manual Update Default Values (MANAdev): Manual Update
- Update status in the database
UPDATE `index_process` SET `status` = 'pending' WHERE `status` = 'working';
- Reset index mode:
$> php mage/shell/indexer.php --mode-realtime
This will reset all indexes to realtime mode again and can be checked again by:
$> php mage/shell/indexer.php mode
That should result in something like that:
Category Flat Data: unknown Product Flat Data: unknown Stock Status: unknown Catalog product price: unknown Category URL Rewrites: unknown Product URL Rewrites: unknown URL Redirects: unknown Catalog Category/Product Index: unknown Catalog Search Index: unknown Product Attributes: Update on Save SEO URL Rewrites (MANAdev): Update on Save Tag Aggregation Data: Update on Save SEO Schemas (MANAdev): Update on Save Default Values (MANAdev): Update on Save
And if you call …
$> php mage/shell/indexer.php status # status list
… again,  you should only see indexes with a status unlike running:
Category Flat Data: Pending Product Flat Data: Pending Stock Status: Pending Catalog product price: Pending Category URL Rewrites: Pending Product URL Rewrites: Pending URL Redirects: Pending Catalog Category/Product Index: Pending Catalog Search Index: Pending Product Attributes: Require Reindex SEO URL Rewrites (MANAdev): Pending Tag Aggregation Data: Pending SEO Schemas (MANAdev): Pending Default Values (MANAdev): Pending
Also when directly querying the database, the output of ….
SELECT * FROM `index_process;
.., should have changed to something like that:
# process_id indexer_code status started_at ended_at mode 1 1 catalog_product_attribute require_reindex 2016-05-04 08:11:49 2016-05-04 08:14:43 real_time 2 2 catalog_product_price pending 2016-05-04 08:10:41 2016-05-04 08:10:41 real_time 3 3 catalog_url pending 2016-05-04 08:14:50 2016-05-04 08:14:50 real_time 4 4 catalog_product_flat pending 2016-05-04 08:09:02 2016-05-04 08:09:02 real_time 5 5 catalog_category_flat pending 2016-05-04 08:09:01 2016-05-04 08:09:01 real_time 6 6 catalog_category_product pending 2016-05-04 08:11:34 2016-05-04 08:11:34 real_time 7 7 catalogsearch_fulltext pending 2016-05-03 05:10:19 2016-05-03 05:18:32 real_time 8 8 cataloginventory_stock pending 2016-05-04 08:10:28 2016-05-04 08:10:28 real_time 9 9 tag_summary pending 2016-05-04 08:15:20 2016-05-04 08:16:54 real_time 10 10 url_redirect pending 2016-05-04 08:11:34 2016-05-04 08:11:34 real_time 11 11 targetrule pending 2016-05-04 08:16:54 2016-05-04 08:16:54 real_time 12 12 catalog_url_category pending 2016-05-04 08:11:16 2016-05-04 08:11:16 real_time 13 13 catalog_url_product pending 2016-05-04 08:11:19 2016-05-04 08:11:19 real_time 14 14 catalog_category_product_cat pending 2016-05-04 08:11:49 2016-05-04 08:11:49 real_time 15 15 mana_db_replicator pending 2016-05-04 08:14:43 2016-05-04 08:14:50 real_time 16 16 mana_db pending 2016-05-04 08:14:43 2016-05-04 08:14:43 real_time 17 17 mana_seo_url pending 2016-05-04 08:16:52 2016-05-04 08:16:54 real_time
Only stop the chosen one
As you might have recognized, the steps above will stop all running indexes at once. But perhaps,for some reason, you only want to stop a single one.
Therefore the shell commands have an additional parameter. You can append the index identifier to each of the commands like that:
$> php mage/shell/indexer.php --mode-manual catalogsearch_fulltext $> # or $> php mage/shell/indexer.php --mode-realtime catalogsearch_fulltext
The value of that parameter (the index identifier) can be got by calling:
$> php mage/shell/indexer.php info
As already told you above this results in a list of index identifiers and names like that:
catalog_category_flat Category Flat Data catalog_product_flat Product Flat Data cataloginventory_stock Stock Status catalog_product_price Catalog product price catalog_url_category Category URL Rewrites catalog_url_product Product URL Rewrites url_redirect URL Redirects catalog_category_product Catalog Category/Product Index catalogsearch_fulltext Catalog Search Index catalog_product_attribute Product Attributes mana_seo_url SEO URL Rewrites (MANAdev) tag_summary Tag Aggregation Data mana_db SEO Schemas (MANAdev) mana_db_replicator Default Values (MANAdev)
The first column corresponds to the index identifier and hence the tailing parameter.
Also when using the already mentioned MySQL update query, you can only modify a chosen index:
UPDATE `index_process` SET `status` = 'pending' WHERE `status` = 'working' AND `indexer_code` LIKE 'catalogsearch_fulltext'
That’s it! Now you know how to stop running indexes of Magento.
Last escape to stop running indexes
If the methods mentioned above don’t work, you have should have a look to the MySQL process list (on shell):
$> mysqladmin -u USER -pPASSWORD -h HOST -i 2 processlist
That gives you the process list every 2 seconds (-i parameter).
Or if mysqladmin isn’t available, you could use mysql and watch:
$> watch -n 2 echo "SHOW PROCESSLIST" | mysql -u USER -pPASSWORD-h HOST DBNAME
Or you can simply login to MySQL console and execute “SHOW PROCESSLIST”:
$> mysql -u USER -pPASSWORD -h HOST $> SHOW PROCESSLIST:
Nevertheless you’ll get a list very equal to this one:
+----------+---------------+---------------------+------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+---------------+---------------------+------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 12636524 | ************* | ******************* | db234404_3 | Query | 150 | Waiting for table level lock | INSERT INTO `catalogsearch_fulltext` (`product_id`,`store_id`,`data_index`) VALUES ('300868', '4', ' |
| 12645345 | ************* | ******************* | db234404_3 | Query | 595 | Sending data | INSERT INTO `catalogsearch_result` SELECT 328 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) |
| 12645516 | ************* | ******************* | db234404_3 | Query | 354 | Sending data | INSERT INTO `catalogsearch_result` SELECT 92 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) A |
| 12645631 | ************* | ******************* | db234404_3 | Sleep | 93 | | NULL |
| 12645735 | ************* | ******************* | db234404_3 | Query | 0 | Sending data | SELECT `api_delivery`.* FROM `api_delivery` WHERE (`api_delivery`.`delive |
| 12645761 | ************* | ******************* | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
As you can see the runtime of the process with the id 12636524 is very long. And that’s our index again. In this case the catalogsearch index.
That’s the candidate we gonna kill:
$> mysql -u USER -pPASSWORD -h HOST $> KILL 12636524
Or just with a single line:
$> echo "KILL 12636524" | mysql -u USER -pPASSWORD-h HOST DBNAME
That’s it! Now the system should be stable again.