Magento 2 How to unlock reindex process

Question

I'm making some tests in Magento reindex process and I locked the stock index process.

How can I unlock this process?

Design Config Grid index has been rebuilt successfully in 00:00:02
Customer Grid index has been rebuilt successfully in 00:00:03
Category Products index has been rebuilt successfully in 00:00:00
Product Categories index has been rebuilt successfully in 00:00:00
Product Price index has been rebuilt successfully in 00:00:00
Product EAV index has been rebuilt successfully in 00:00:00
Stock index is locked by another reindex process. Skipping.
Catalog Rule Product index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
Catalog Search index has been rebuilt successfully in 00:00:06

Answer 1

You can reset the indexer through command line with indexer:reset command.

This will give the list of indexes name:

php bin/magento indexer:info

Output:

design_config_grid                       Design Config Grid
customer_grid                            Customer Grid
catalog_category_product                 Category Products
catalog_product_category                 Product Categories
catalog_product_price                    Product Price
catalog_product_attribute                Product EAV
catalogsearch_fulltext                   Catalog Search
cataloginventory_stock                   Stock
catalogrule_rule                         Catalog Rule Product
catalogrule_product                      Catalog Product Rule

This will give the list of indexes status:

php bin/magento indexer:status

Output:

Design Config Grid:                                Ready
Customer Grid:                                     Ready
Category Products:                                 Ready
Product Categories:                                Ready
Product Price:                                     Ready
Product EAV:                                       Ready
Catalog Search:                                    Ready
Stock:                                             Processing
Catalog Rule Product:                              Ready
Catalog Product Rule:                              Ready

If you want to reset all the indexes, you can run the following command:

php bin/magento indexer:reset

If you want to reset particular index (e.g. cataloginventory_stock), you can run the following command:

php bin/magento indexer:reset cataloginventory_stock

Answer 2

When I faced this kind of situation I had to run the following SQL query directly into the database:

UPDATE indexer_state SET status = 'valid';

I was not able to to find any option to force reindexing when an index has failed previously.

Answer 3

On MySQL run:

SET SQL_SAFE_UPDATES = 0;
update indexer_state set status = 'invalid' where status != 'valid';

Then on your terminal run:

php bin/magento indexer:reindex

It normally happens when the memory limit is few, so increase on your .htaccess or NGINX config.

Answer 4

It is also possible that you run into a state where some tables are locked in MySQL. In this case you can issue unlock tables; sql statement to be able to proceed.

I ran into a problem like this:

Category Products indexer process unknown error:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO

Where I could not re-index until the table lock was removed.

Does resetting the index lose any data waiting to be indexed? – ol'bob dole

Indexing is a process of looking at the data set and storing some keys to pair with the proper data. You would lose your current index in the process and it re-creates it. Outside of side effects of index using up resources or your dataset being inaccurate, there should be no risk to re-indexing.

Answer 5

php bin/magento indexer:reset

Is really bad advice, if you have a store with millions of customers. The reason is it will reset all indexers, including the customer_grid, which will take to a minimum of 40 mins to reindex

Reset selectively please!

Answer 6

Just use the commands:

php bin/magento indexer:reset
php bin/magento indexer:reindex
php bin/magento cache:clean full_page block_html

Answer 7

Just run these commands:

php bin/magento indexer:reset;
php bin/magento indexer:reindex;

Answer 8

If index type is locked, when you reindex all the data by running this command:

php bin/magento indexer:info

After running the command above, you will receive the result like the following:

In this step, what you need to do is checking the status of all the index types by using this command:

php bin/magento indexer:status

After running the command, you will see the index type Processing status Stock indicates that it is Locked. For more details, see this output To solve this problem, there is a great solution which is reset the index types which are locked. This example is in the Processing state. Run this command to reset all index types:

php bin/magento indexer:reset

At this step, all you will have to do is check the indexer status one more time. In order to do that run the following command :

php bin/magento indexer:status

You can see in the output, the Stock index type has been unlocked. Besides, the status of it has been changed from Processing to Reindex required. Now, the Stock index type can be reindexed by using the following command:

php bin/magento indexer:reindex

I hope fix issue.

Answer 9

I solved this issue with the following steps:

1.- drop table customer_grid_flat

2.- Re-create table:

CREATE TABLE IF NOT EXISTS customer_grid_flat ( entity_id int UNSIGNED NOT NULL COMMENT 'Entity ID' , name text NULL COMMENT 'Name' , email varchar(255) NULL COMMENT 'Email' , group_id int NULL COMMENT 'Group_id' , created_at timestamp NULL default NULL COMMENT 'Created_at' , website_id int NULL COMMENT 'Website_id' , confirmation varchar(255) NULL COMMENT 'Confirmation' , created_in text NULL COMMENT 'Created_in' , dob date NULL COMMENT 'Dob' , gender int NULL COMMENT 'Gender' , taxvat varchar(255) NULL COMMENT 'Taxvat' , lock_expires timestamp NULL default NULL COMMENT 'Lock_expires' , shipping_full text NULL COMMENT 'Shipping_full' , billing_full text NULL COMMENT 'Billing_full' , billing_firstname varchar(255) NULL COMMENT 'Billing_firstname' , billing_lastname varchar(255) NULL COMMENT 'Billing_lastname' , billing_telephone varchar(255) NULL COMMENT 'Billing_telephone' , billing_postcode varchar(255) NULL COMMENT 'Billing_postcode' , billing_country_id varchar(255) NULL COMMENT 'Billing_country_id' , billing_region varchar(255) NULL COMMENT 'Billing_region' , billing_street varchar(255) NULL COMMENT 'Billing_street' , billing_city varchar(255) NULL COMMENT 'Billing_city' , billing_fax varchar(255) NULL COMMENT 'Billing_fax' , billing_vat_id varchar(255) NULL COMMENT 'Billing_vat_id' , billing_company varchar(255) NULL COMMENT 'Billing_company' , PRIMARY KEY (entity_id), INDEX CUSTOMER_GRID_FLAT_GROUP_ID (group_id), INDEX CUSTOMER_GRID_FLAT_CREATED_AT (created_at), INDEX CUSTOMER_GRID_FLAT_WEBSITE_ID (website_id), INDEX CUSTOMER_GRID_FLAT_CONFIRMATION (confirmation), INDEX CUSTOMER_GRID_FLAT_DOB (dob), INDEX CUSTOMER_GRID_FLAT_GENDER (gender), INDEX CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID (billing_country_id), FULLTEXT FTI_8746F705702DD5F6D45B8C7CE7FE9F2F (name, email, created_in, taxvat, shipping_full, billing_full, billing_firstname, billing_lastname, billing_telephone, billing_postcode, billing_region, billing_city, billing_fax, billing_company) ) COMMENT='customer_grid_flat' ENGINE=innodb charset=utf8 COLLATE=utf8_general_ci