Column not found: 1054 Unknown column 'main.entity_id' in 'field list' after successfully migrating data from EE 1.12.0.2 to EE 2.1.1

[RakeshJesadiya]

Hello,
I have migrated data from Magento EE 1.12.0.2 to EE 2.1.1.

I have multi store website English and Arabic Language.

Migration are successfully for data of old magento 1 to latest magento 2 version.

In admin panel Its display all store view with different language translation and all customer, Proudct,Order data.

But when i have checked in frontend its not working and display error like below,

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main.entity_id' in 'field list', query was: SELECT main.entity_idFROMcatalog_product_entity_intASmainINNER JOINstoreON main.store_id=store.store_id WHERE (main.attribute_id = '175') AND (store.website_id IN(0, '1')) AND (main.value = '85') AND (main.entity_id IN('2383', '1126', '1172', '2872', '1126', '1172', '2845', '1126', '1172', '2258', '1126', '1172', '2799', '1126', '1172', '2741', '1126', '1172', '4185', '1126', '1172', '3736', '1126', '1172', '2741', '1126'))

I am not sure why this issue are display in frontend page.

Any hint for resolve this issue after migration completed.

Thanks.

[RakeshJesadiya]

When check report log at that time display below error,

#1 /var/www/html/magento-ee/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array) #2 /var/www/html/magento-ee/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) #3 /var/www/html/magento-ee/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT main....', Array)
#4 /var/www/html/magento-ee/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(452): Zend_Db_Adapter_Pdo_Abstract->query('SELECT main....', Array) #5 /var/www/html/magento-ee/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(507): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT main....', Array)
#6 /var/www/html/magento-ee/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(794): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#7 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Segment/Condition/Product/Attributes.php(238): Zend_Db_Adapter_Abstract->fetchCol(Object(Magento\Framework\DB\Select))
#8 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Segment/Condition/Product/Combine/History.php(347): Magento\CustomerSegment\Model\Segment\Condition\Product\Attributes->getSubfilterSql('item.product_id', true, '1')
#9 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Condition/Combine/AbstractCombine.php(206): Magento\CustomerSegment\Model\Segment\Condition\Product\Combine\History->processCombineSubFilters('1', true, Array)
#10 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Segment/Condition/Product/Combine/History.php(264): Magento\CustomerSegment\Model\Condition\Combine\AbstractCombine->getConditionsSql(NULL, '1')
#11 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Condition/Combine/AbstractCombine.php(227): Magento\CustomerSegment\Model\Segment\Condition\Product\Combine\History->isSatisfiedBy(NULL, '1', Array)
#12 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Segment.php(316): Magento\CustomerSegment\Model\Condition\Combine\AbstractCombine->isSatisfiedBy(NULL, '1', Array)
#13 /var/www/html/magento-ee/var/generation/Magento/CustomerSegment/Model/Segment/Interceptor.php(102): Magento\CustomerSegment\Model\Segment->validateCustomer(Object(Magento\Customer\Model\Customer\Interceptor), Object(Magento\Store\Model\Website\Interceptor))
#14 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Customer.php(223): Magento\CustomerSegment\Model\Segment\Interceptor->validateCustomer(Object(Magento\Customer\Model\Customer\Interceptor), Object(Magento\Store\Model\Website\Interceptor))
#15 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Model/Customer.php(165): Magento\CustomerSegment\Model\Customer->_processSegmentsValidation(Object(Magento\Customer\Model\Customer\Interceptor), Object(Magento\Store\Model\Website\Interceptor), Object(Magento\CustomerSegment\Model\ResourceModel\Segment\Collection))
#16 /var/www/html/magento-ee/vendor/magento/module-customer-segment/Observer/ProcessEventObserver.php(73): Magento\CustomerSegment\Model\Customer->processEvent('visitor_init', Object(Magento\Customer\Model\Customer\Interceptor), Object(Magento\Store\Model\Website\Interceptor))
#17 /var/www/html/magento-ee/vendor/magento/framework/Event/Invoker/InvokerDefault.php(73): Magento\CustomerSegment\Observer\ProcessEventObserver->execute(Object(Magento\Framework\Event\Observer))`

[victor-v-rad]

Hi @RJesadiya
Try using the latest version of the tool 2.1.3 It has fixes which may help.

[RakeshJesadiya]

Hi, @victor-v-rad
I have try using Magento EE 2.1.3,

[2016-12-23 09:02:50][ERROR]: Foreign key (FK_CAT_EAV_ATTR_ATTR_ID_EAV_ATTR_ATTR_ID) constraint fails. Orphan records id: 163,164 from `catalog_eav_attribute`.`attribute_id` has no referenced records in `eav_attribute`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_CSTR_ENTT_DTIME_ENTT_ID_CSTR_ENTT_ENTT_ID) constraint fails. Orphan records id: 3357,4408 from `customer_entity_datetime`.`entity_id` has no referenced records in `customer_entity`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_CUSTOMER_ENTITY_INT_ENTITY_ID_CUSTOMER_ENTITY_ENTITY_ID) constraint fails. Orphan records id: 3357,4408 from `customer_entity_int`.`entity_id` has no referenced records in `customer_entity`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_CSTR_ENTT_VCHR_ENTT_ID_CSTR_ENTT_ENTT_ID) constraint fails. Orphan records id: 3357,4408 from `customer_entity_varchar`.`entity_id` has no referenced records in `customer_entity`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_EAV_ENTT_ATTR_ATTR_ID_EAV_ATTR_ATTR_ID) constraint fails. Orphan records id: 163,164 from `eav_entity_attribute`.`attribute_id` has no referenced records in `eav_attribute`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_ENT_CSTRSEGMENT_CSTR_CSTR_ID_CSTR_ENTT_ENTT_ID) constraint fails. Orphan records id: 3357,4408 from `enterprise_customersegment_customer`.`customer_id` has no referenced records in `customer_entity`
[2016-12-23 09:02:50][ERROR]: Foreign key (FK_ENTERPRISE_REWARD_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID) constraint fails. Orphan records id: 3357,4408 from `enterprise_reward`.`customer_id` has no referenced records in `customer_entity`

[victor-v-rad]

Look at the similar issue #208

[sanganinamrata]

@RakeshJesadiya : This will help you:

• https://devdocs.magento.com/guides/v2.1/migration/migration-troubleshooting.html

You have orphaned records in your database. This can occur when records have sometime in the past been deleted while Foreign Key checks are disabled. When this is done improperly, it will potentially leave unwanted left over data in the database.

These queries will remove this orphaned data from your DB:

DELETE FROM catalog_compare_item WHERE customer_id = 0;
DELETE FROM catalog_eav_attribute WHERE attribute_id = 464;
DELETE FROM catalog_product_entity_int WHERE attribute_id = 464;
DELETE FROM eav_entity_attribute WHERE attribute_id IN (121,139,157,464,501);
DELETE FROM newsletter_queue WHERE template_id = 1;
DELETE FROM report_compared_product_index WHERE product_id = 180;
DELETE FROM report_viewed_product_index WHERE customer_id = 332;
DELETE FROM report_viewed_product_index WHERE product_id IN (158,159,160,161,162,163,166,167,168,169,170,172,173,174,176,177,178,179,180,190,192,193,200,207,208,209,244,245,248,322,324,325,326,327,328,329,330,332,334,344,346,347,348,349,350,351,352,353,357,358,359,360,361,362,366,367,368,373,376,377,378,379,382,384,385,386,388,389,390,393,394,395,396,397,399,400,401,404,406,407,675,677,678,679,680,708,710,714,715,716,717,718,720,721,725,726,729);

The very last line of your error report above is cut off. So I was unable to provide a query for that error.
As always, I recommend backing up beforehand.

Let me know if you want further help.

Thank you.