0

I migrated database from Magento 1.9 to 2.2

When re-indexing I get the following 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 MySQL server version for the right syntax to use near ')
     INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child' at line 2, query was: INSERT INTO `catalog_category_product_index_store2_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 2 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
     INNER JOIN `temp_catalog_category_tree_index_d6ff2037` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN ()
     INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_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 cpsd.attribute_id = 96
     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 = 2
     INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102
     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 = 2
     INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51
     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 = 2
     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 = 96
     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 = 2 WHERE (cpw.website_id = '2') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) AND (ccp.product_id IN (1589, 1590, 1591, 1593, 1594, 1595, 1596, 1597, 1598, 1599, 1600, 1601, 1604, 1605, 1606, 1607, 1608, 1609, 1611, 1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 1621, 1622, 1623, 1624, 1625, 1626, 1627, 1628, 1629, 1630, 1635, 1639, 1640, 1641, 1644, 1645, 1646, 1647, 1648, 1657, 1658, 1659, 1660, 1661, 1662, /*removed many ids here */ 2253, 2254, 2255, 2256, 2257, 2258, 2259, 2260, 2261, 2262, 2263, 2264, 2265, 2266, 2267, 2268, 2269, 2270, 2271, 2272, 2273, 2274, 2275, 2276, 2277, 2278, 2279, 2280, 2285, 2286, 2287, 2288, 2289, 2290, 2291, 2292, 2293, 2294, 2295, 884, 912, 913, 914, 915, 916, 917, 918, 920, 921, 988, 989, 990, 991, 992, 993, 994, 1005, 1006, 1007, 1008, 1009, 1010, 1045, 1046, 1062, 1063, 1064, 1065, 1066, 1070, 1071, 1072, 1073, 1074, 1075, 1076, 1077, 1078, 1079, 1080, 1081, 1082, 1083, 1089, 1090, 1091, 1092, 1093, 1094, 1095, 1096, 1097, 1193, 1194, 1195, 1196, 1272, 1274, 1275, 1276, 1481, 1482, 1483, 1649, 1651, 1652, 1653, 1655, 2159, 2162, 878, 889, 890, 891, 892, 893, 894, 895, 896, 897, 898, 899, 900, 907, 908, 909, 910, 911, 922, 940, 941, 942, 943, 944, 945, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979, 980, 981, 982, 983, 984, 985, 986, 987, 1086, 1123, 1312, 1313, 1314, 1315, 1316, 1317, 1318, 1319, 1320, 1321, 1322, 1323, 1324, 1325, 1326, 1327, 1328, 1329, 1330, 1331, 1332, 1333, 1334, 1335, 1336, 1337, 1338, 1339, 1340, 1341, 1342, 1363, 1411, 1412, 1413, 1414, 1415, 1416, 1417, 1418, 1419, 1420, 1421, 1422, 1423, 1424, 1433, 1434, 1435, 1436, 1501, 1502, 1503, 1504, 1505, 1506, 1507, 1508, 1781, 1833, 1834, 1835, 1836, 1837, 1838, 1839, 1840, 1841, 1842, 1843, 1845, 1846, 1847, 1848, 1849, 1850, 1851, 1852, 1853, 1854, 1855, 1856, 1857, 1858, 1859, 1860, 1861, 1862, 1863, 1864, 1866, 1867, 1868, 1869, 1870, 1871, 1872, 1873, 1874, 1875, 1876, 1877, 1878, 1879, 1880, 1881, 1882, 1883, 1884, 1885, 1886, 1887, 1888, 1889, 1890, 1891, 1892, 1902, 1903, 1904, 1905, 1909, 1975, 1979, 2377, 2378, 2380, 2381, 2382, 2460, 2461, 2462, 2463, 2464, 2465, 2466, 877, 946, 947, 949, 950, 951, 952, 953, 954, 955, 1085, 1464, 1730, 1731, 1732, 1733, 1734, 1736, 1737, 1738, 2160, 2161, 1011, 1012, 1013, 1476, 1477, 1542, 1543, 1544, 1545, 1546, 1547, 1548, 1549, 1550, 1551, 1552, 1553, 1554, 1555, 1556, 1557, 1558, 1559, 1573, 1789, 2482, 2606, 2435, 2436, 2437, 2438, 2439, 2443, 2444, 2456, 2457, 2458, 2563, 2564, 2565, 2566, 2567, 2568, 2569, 2570, 2571, 2572, 2573, 2574, 2575, 2576, 2577, 2578, 2579, 2580, 2581, 2582, 2583, 2584, 2585, 2586, 2587, 327, 329, 330, 331, 862, 863, 2507, 2508, 2509, 2510, 2511, 2512, 2513, 2514, 2515, 2516, 2517, 2518, 2519, 2520, 2521, 2522, 2523, 2524, 2525, 2526, 2527, 2528, 2529, 2530, 2531, 2532, 2533, 2534, 2535, 2536, 2537, 2538, 2539, 2540, 2541, 2542, 2543, 2544, 2545, 2546, 2547, 2548, 2549)) GROUP BY `cc`.`entity_id`,
        `ccp`.`product_id`,
        `visibility` 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`)
    Product Categories 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 MySQL server version for the right syntax to use near ')
     INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child' at line 2, query was: INSERT INTO `catalog_category_product_index_store2_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 2 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
     INNER JOIN `temp_catalog_category_tree_index_9349bfa6` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN ()
     INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_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 cpsd.attribute_id = 96
     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 = 2
     INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102
     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 = 2
     INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51
     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 = 2
     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 = 96
     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 = 2 WHERE (cpw.website_id = '2') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) AND (ccp.product_id IN (1589, /*removed many ids here*/ 2354, 2355, 2356, 2357, 2360, 2361, 2363, 2367, 2428, 2434, 2447, 2448, 2451, 2549)) GROUP BY `cc`.`entity_id`,
        `ccp`.`product_id`,
        `visibility` 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`)

(note that I have shortened the id lists)

What is wrong here and how do I correct this?

Update: changed Flat Catalog Category and Use Flat Catalog Product to 'No' ( at Configuration / Catalog / Catalog / Storefront) and the error went away. Changed back to 'Yes' and still no error. All good, but would still like to understand what the heck happened.

  • Did you start with a clear 2.2 database? Did you get any errors during your migration? I am assuming you did settings first? – brentwpeterson Jan 31 at 10:57
  • Yes, No, Correct. – Tero Lahtinen Jan 31 at 11:10
  • What version of MySql? – brentwpeterson Feb 3 at 5:24
  • mysql 14.14 Distrib 5.6.33 – Tero Lahtinen Feb 4 at 15:00
  • So your original and target are this version? please do select @@version; on original and target. – brentwpeterson Feb 5 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.