cancel
Showing results for 
Search instead for 
Did you mean: 

Magento 2.2.1 Indexer Fails with invalid SQL Query after migration

Regular Visitor

Magento 2.2.1 Indexer Fails with invalid SQL Query after migration

I am migrating a Magento 1.9.1.0 site to 2.2.1 using the steps in the migration guide. The migration works, however, when I run the Category Products indexer, it fails.  The relevant line from the failing query is:
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cp.entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id =
Notice the missing value after the last "=".

 

The complete query exceeds the maximum characters I can post here.  But here is the abridged version:

 

INSERT INTO `catalog_category_product_index_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT 3 AS `category_id`, `cp`.`entity_id` AS `product_id`, IF(ccp.product_id IS NOT NULL, ccp.position, 0) AS `position`, IF(ccp.product_id IS NOT NULL, 1, 0) AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_product_entity` AS `cp`
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = cp.entity_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cp.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 215
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cp.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cp.entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 
 LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cp.entity_id AND cpvs.attribute_id = cpvd.attribute_id  AND cpvs.store_id = 1
 LEFT JOIN `catalog_category_product` AS `ccp` ON ccp.product_id = cp.entity_id WHERE (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (cp.entity_id IN (.........lots of ids here......)) GROUP BY `cp`.`entity_id` ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)



Stack trace:
PHP Fatal error:  Uncaught Error: Call to a member function getBackend() on boolean in /html/vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php:295
Stack trace:
#0 /html/vendor/magento/module-catalog/Model/Indexer/Product/Price/Action/Full.php(141): Magento\Catalog\Model\Indexer\Product\Price\AbstractAction->_prepareTierPriceIndex(Array)
#1 /html/vendor/magento/module-catalog/Model/Indexer/Product/Price.php(66): Magento\Catalog\Model\Indexer\Product\Price\Action\Full->execute()
#2 /html/vendor/magento/module-indexer/Model/Indexer.php(412): Magento\Catalog\Model\Indexer\Product\Price->executeFull()
#3 /html/vendor/magento/module-indexer/Model/Indexer/DependencyDecorator.php(248): Magento\Indexer\Model\Indexer->reindexAll()
#4 /html/vendor/magento/module-indexer/Console/Com in /html/vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php on line 295

 

Any guidance on fixing this would be great.  If this is better posted as a bug report, please let me know and I can create an issue on Git Hub.


Notes
I did remove some orphaned records from the magento 1 database before migrating.  This included some records from the catalog_product_entity_int table.

 

1 REPLY
Highlighted
M2 Certified
Magento
E-Commerce

Re: Magento 2.2.1 Indexer Fails with invalid SQL Query after migration

@dmahaffy

Hi,

 

Did you figure out the issue? If you can share your finding may help someone else.

 

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

 

Thanks,
Tarandeep
Magento 2 Certified Solution Specialist (x2). Follow me on Twitter