Category Products indexer process unknown error:

[vipulmishra341]

Preconditions

1. Enter your credentials in ssh.
2. Login to the site using ssh.

Steps to reproduce

1. Run this command after opening ssh
2. php bin/magento indexer:reindex

Expected result

It should display the success message of reindexing every indexes required.

Actual result

After migrate Magento 1.6.2 database to Magento2.1.1 and trying to re-index the all indexer I am getting below error

Category Products indexer process unknown error:SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JOIN catalog_product_entity_int AS cpss ON
cpss.entity_id = cp.entity_id AND' at line 4, query was: 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.pr
oduct_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 = 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 (IFNUL
L(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (cp.entity_id >= '1') AND (cp.entity_id < 1000001) GROUP BY cp.entity_id ON DUPLICATE KEY UPDATE catego ry_id = VALUES(category_id), product_id = VALUES(product_id), position= VALUES(position), is_parent = VALUES(is_parent), store_id = VALUES(st ore_id), visibility = VALUES(visibility)

Please help me to resolve it. settings and Data migration already successfully done and when i checked the magento2 admin panel no product show so i tried to re-index it and got the above issue.

[victor-v-rad]

Hi @vipulmishra341
At first glance is not clear whether the Data Migration Tool or Magento 2 cause it. The easy way to figure it out would be if you send your DB (clean from passwords) to me (in my profile https://github.com/victor-v-rad you can find my email) and we will try to migrate and reproduce it.

[vipulmishra341]

Issue resolve after doing migration at fresh Magento2 database