error reindexing

after I have cleared data and reimported I get this error when trying to a do a reindex magento?
please advise?
 
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual t hat corresponds to your MariaDB server version for the right syntax to use near ‘)
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.ch…’ at line 2, query was: INSERT INT O `catalog_category_product_index_store1_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, IFNULL(ccp2.position, ccp.position + 10000) AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM ` catalog_category_entity` AS `cc`
INNER JOIN `temp_catalog_category_tree_index_71f654a4` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_i d NOT IN ()
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
LEFT JOIN `catalog_category_product` AS `ccp2` ON ccp2.category_id = cc2.parent_id AND ccp.product_id = ccp2.pr oduct_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND c psd.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.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 = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd .attribute_id AND cpvs.store_id = 1
INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND c cad.attribute_id = 54
LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad .attribute_id AND ccas.store_id = 1
LEFT JOIN `catalog_product_relation` AS `relation` ON cpe.entity_id = relation.parent_id
LEFT JOIN `catalog_product_entity` AS `relation_product_entity` ON relation.child_id = relation_product_entity. entity_id
LEFT JOIN `catalog_product_entity_int` AS `child_cpsd` ON child_cpsd.entity_id = relation_product_entity.entity _id AND child_cpsd.store_id = 0 AND child_cpsd.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `child_cpss` ON child_cpss.entity_id = relation_product_entity.entity _id AND child_cpss.attribute_id = child_cpsd.attribute_id AND child_cpss.store_id = 1 WHERE (cpw.website_id = ‘1 ‘) AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.v alue, ccad.value) = 1) AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) AND (cc p.product_id IN (’17’, ’19’, ’20’, ’25’, ’26’, ’28’, ’30’, ’31’, ’32’, ’33’, ’34’, ’35’, ’36’, ’37’, ’39’, ’40’, ’41’, ’42’, ’43’, ’44’, ’45’, ’46’, ’47’, ’48’, ’49’, ’50’, ’51’, ’52’, ’53’, ’54’, ’55’, ’56’, ’57’, ’58’, ’59 ‘, ’60’, ’61’, ’62’, ’63’, ’64’, ’65’, ’66’, ’67’, ’68’, ’69’, ’70’, ’71’, ’72’, ’73’, ’74’, ’75’, ’76’, ’77’, ‘ 78′, ’79’, ’80’, ’81’, ’82’, ’83’, ’84’, ’85’, ’86’, ’87’, ’88’, ’89’, ’90’, ’91’, ’92’, ’93’, ’94’, ’95’, ’96’, ’97’, ’98’, ’99’, ‘100’, ‘101’, ‘102’, ‘103’, ‘104’, ‘105’, ‘106’, ‘107’, ‘108’, ‘109’, ‘110’, ‘111’, ‘112’, ‘1 13’, ‘114’, ‘115’, ‘116’, ‘117’, ‘118’, ‘120’, ‘121’, ‘122’, ‘123’, ‘124’, ‘125’, ‘126’, ‘127’, ‘128’, ‘129’, ‘1 30’, ‘131’, ‘132’, ‘133’, ‘134’, ‘135’, ‘136’, ‘137’, ‘138’, ‘139’, ‘140’, ‘141’, ‘142’, ‘143’, ‘144’, ‘145’, ‘1 46’, ‘147’, ‘148’, ‘149’, ‘150’, ‘151’, ‘157’, ‘158’, ‘159’, ‘160’, ‘161’, ‘165’, ‘166’, ‘167’, ‘168’, ‘169’, ‘1 70’, ‘172’, ‘173’, ‘174’, ‘175’, ‘176’, ‘177’, ‘178’, ‘179’, ‘180’, ‘181’, ‘182’, ‘183’, ‘184’, ‘185’, ‘186’, ‘1 87’, ‘188’, ‘189’, ‘190’, ‘191’, ‘192’, ‘193’, ‘194’, ‘195’, ‘216’, ‘217’, ‘218’, ‘219’, ‘220’, ‘221’, ‘222’, ‘2 23’, ‘224’, ‘225’, ‘226’, ‘227’, ‘228’, ‘229’, ‘230’, ‘231’, ‘232’, ‘233’, ‘234’, ‘235’, ‘236’, ‘237’, ‘238’, ‘2 40’, ‘241’, ‘242’, ‘243’, ‘244’, ‘245’, ‘246’, ‘247’, ‘248’, ‘249’, ‘250’, ‘251’, ‘252’, ‘253’, ‘254’, ‘255’, ‘2 56’, ‘257’, ‘258’, ‘259’, ‘260’, ‘261’, ‘262’, ‘263’, ‘264’, ‘265’, ‘266’, ‘271’, ‘273’, ‘274’, ‘275’, ‘276’, ‘2 77’, ‘278’, ‘279’, ‘280’, ‘283’, ‘284’, ‘285’, ‘288’, ‘290’, ‘291’, ‘292’, ‘294’, ‘295’, ‘296’, ‘297’, ‘298’, ‘2 99’, ‘300’, ‘301’, ‘302’, ‘303’, ‘304’, ‘305’, ‘306’, ‘318’, ‘332’, ‘333’, ‘334’, ‘335’, ‘336’, ‘337’, ‘338’, ‘3

4 answers

Profile photo of cojiyun11585 20.00 $tone January 7, 2022
Public

and ad the end of it is
 
GROUP BY `cc`.`entity_id`,
`ccp`.`product_id`,
`visibility` ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`produ ct_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `v isibility` = VALUES(`visibility`)
Catalog Rule Product index has been rebuilt successfully in 00:00:00

#1
Profile photo of Mall Staff 180840.00 $tone January 10, 2022
Public

Hi there,

The issue with the indexers of Magento that you mentioned is strange. Did you use another migration tool before using our migration tool for data migration?
Can you let me know of the version of your Magento2 instance? And the version of our migration tool in your instance?

Please try resetting the Root Category of the current default store of the default website on your site.
And then, you can reindex the data again by running the CLI commands:
php -f bin/magento indexer:reset
php -d memory_limit = -1 -f bin/magento indexer:reindex

Let me know how it goes.
 

Regards,
Mall.
 
 

#2
Profile photo of Mall Staff 180840.00 $tone January 11, 2022
Public

Hi there,

I always have this problem when resetting data from the plugin

When trying to save the root folder it hangs for ages then gives error
There has been an error processing your request
Exception printing is disabled by default for security reason

It seems that you did delete the initial data objects (default root Category, default Website, default Store…) in M2 after doing data migration with our migration tool.
In this case, after reset you must re-correct data records in table `store_group` and make sure all values of the `root_category_id` field are existing in the table `catalog_category_entity` in your M2 database. For example http://prntscr.com/26c9nv9 

If you want more help then please provide us with your Admin credentials and URL to the backend of your site.
 

Regards,
Mall.

#4

Please login or Register to Submit Answer

Written By

Comments