SmartCategory Indexer Issue

[pwslakshmi]

Hi @karliuka all

Please help. Urgent Fix needed on Production. Magento version:2.1.2 PHP 5.6 Have multiple stores. When installed it is fine. It has been 2 months we have installed this module. I suspect when added staging rule(content->staging->dashboard), this indexer is getting failed. I assume there is some conflict arised between other features or modules. Appreciate if any one can respond immediately. FYI, this error is also displayed after 1 hour

Error:
-bash-4.2$ bin/magento indexer:reindex smartcategory_product
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (indGdbPrd.in_catalog_category_product, CONSTRAINT CAT_CTGR_PRD_CTGR_ID_IN_SEQUENCE_CAT_CTGR_SEQUENCE_VAL FOREIGN KEY (category_id) REFERENCES in_sequence_catalog_category (sequence_), query was: INSERT INTO in_catalog_category_product (category_id,product_id,position`) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)

etc/module.xml of the module

[pwslakshmi]

@karliuka @YachYaroslav @faonni

Please help providing a fix/suggestion on the below issue
#35 (comment)

[earthlinkdesign]

Has this been fixed? I have the exact same issue. Thanks

[pwslakshmi]

@earthlinkdesign

No. Not fixed. If you got any solution, please share. Thanks in advance.

[nobodyfamouz]

Hi guys,

I was able to figure it out. It seems that when a Magento category gets deleted the rule for that category stays in the table faonni_smartcategory_rule. So a full index tries to insert products for category that has been deleted.

Quick fix is to delete rules for missing categories.

[earthlinkdesign]

Hi guys,

I was able to figure it out. It seems that when a Magento category gets deleted the rule for that category stays in the table faonni_smartcategory_rule. So a full index tries to insert products for category that has been deleted.

Quick fix is to delete rules for missing categories.

Can confirm this works. Thanks