Stop running indexes of Magento

Stop running indexes of MagentoSometimes 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:

  1. 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
  2. Update status in the database
    UPDATE `index_process` SET `status` = 'pending' WHERE `status` = 'working';
  3. 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.

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.