Latest news

How To Fix Deadlock Error When Reindexing All In Magento 2?

No comments

PROBLEM:

Magento Version: 2.1.9
Mode: Production
Indexing Mode: Update on save

Total Products: 18k
Total Customers: 54k
Total Orders: 18k

After the developer tried to re-indexing, the exception.log file was generated including the following error in this file. Meanwhile, the server was stuck and needed restarting. Then, the site can run as usual.

“PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction”

So, how to fix this deadlock error and what is the best way to re-index manually?

SOLUTIONS:

There are 3 simple steps to handle this Magento 2 deadlock error:

    • Step 1: Open your database on local then run SQL:

 

DROP TABLE `catalog_category_product_index`;
DROP TABLE `catalog_category_product_index_tmp`;


CREATE TABLE `catalog_category_product_index` (
  `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Category ID',
  `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) DEFAULT NULL COMMENT 'Position',
  `is_parent` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `visibility` smallint(5) UNSIGNED NOT NULL COMMENT 'Visibility'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Index';
ALTER TABLE `catalog_category_product_index`
  ADD PRIMARY KEY (`category_id`,`product_id`,`store_id`),
  ADD KEY `CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY` (`product_id`,`store_id`,`category_id`,`visibility`),
  ADD KEY `CAT_CTGR_PRD_IDX_STORE_ID_CTGR_ID_VISIBILITY_IS_PARENT_POSITION` (`store_id`,`category_id`,`visibility`,`is_parent`,`position`);
CREATE TABLE `catalog_category_product_index_tmp` (
  `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Category ID',
  `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) NOT NULL DEFAULT '0' COMMENT 'Position',
  `is_parent` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `visibility` smallint(5) UNSIGNED NOT NULL COMMENT 'Visibility'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Indexer Temp Table';
ALTER TABLE `catalog_category_product_index_tmp`
  ADD KEY `CAT_CTGR_PRD_IDX_TMP_PRD_ID_CTGR_ID_STORE_ID` (`product_id`,`category_id`,`store_id`);

 

    • Step 2: Try to re-index on local

    • Step 3: If you can re-index on the local then re-index on the server

 

We have shown you 3 steps to fix the deadlock error that occurs when re-indexing in Magento CE 2.1.9. If you have any problems when following the instructions, please leave a comment below. See you in the next Magento 2 tutorials.

See More:

100+ FREE Magento Ecommerce Responsive Themes 2018

What Should We Prepare For Magento 2 Upgrade/ Magento Ecommerce Websites Re-Build?

getAllVisibleItems: How To Show Only Configurable Products & Hide Parent Products?

20 Magento Ecommerce Design Trends (That’ll Dominate In 2018)

How To Protect Original Product Images In Magento 2?

1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.78 out of 5)
Loading...
CharlieHow To Fix Deadlock Error When Reindexing All In Magento 2?

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *