search

magento / data-migration-tool

Magento Data Migration Tool
https://devdocs.magento.com/guides/v2.3/migration/bk-migration-guide.html
Open Software License 3.0
334 stars 200 forks source link

Catalog Search index process unknown error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.store_id' in 'field list', query was: SELECT `u`.* FROM ( (SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `customer_entity_varchar` AS `t`.... #842

Open ghost opened 3 years ago

ghost commented 3 years ago

Preconditions

  1. Migrating from Magento 1.9.4.5
  2. Migrating to Magento 2.4.0

Steps to reproduce

  1. Setup clean installation of Magento 2.4.0
  2. Run Settings migration as usual
  3. Run Data migration as usual
  4. Perform reindexing

Expected result

  1. Successfully rebuilt Catalog Search index.

Actual result

Catalog Search index process unknown error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.store_id' in 'field list', query was: SELECTu.* FROM ( (SELECTt.value,t.attribute_id,t.store_idFROMcustomer_entity_varcharAStWHERE (entity_id = '3') AND (attribute_id IN ('111', '479', '113', '114', '118', '563', '533', '514', '927', '982')) AND (store_idIN ('2', 0)))UNION ALL(SELECTt.value,t.attribute_id,t.store_idFROMcustomer_entity_intAStWHERE (entity_id = '3') AND (attribute_id IN ('119', '940', '941', '937', '117', '120')) AND (store_idIN ('2', 0)))UNION ALL(SELECTt.value,t.attribute_id,t.store_idFROMcustomer_entity_textAStWHERE (entity_id = '3') AND (attribute_id IN ('112', '115', '116', '121', '122', '123', '926', '530')) AND (store_idIN ('2', 0)))UNION ALL(SELECTt.value,t.attribute_id,t.store_idFROMcatalog_category_entity_varcharAStWHERE (entity_id = '3') AND (attribute_id IN ('965')) AND (store_idIN ('2', 0)))UNION ALL(SELECTt.value,t.attribute_id,t.store_idFROMcustomer_entity_datetimeAStWHERE (entity_id = '3') AND (attribute_id IN ('565', '566')) AND (store_idIN ('2', 0)))UNION ALL(SELECTt.value,t.attribute_id,t.store_idFROMcustomer_entity_decimalAStWHERE (entity_id = '3') AND (attribute_id IN ('942')) AND (store_idIN ('2', 0))) ) ASuORDER BYstore_idASC

Additional notes

I managed to migrate both Settings and data successfully to a freshly installed magento 2.4.0 project. My orders, products, settings etc .. where successfully migrated ( i am able to see them in backend ) But when i run the command php bin/magento indexer:reindex after successful migration, it shows this sql error.

This same sql error is shown when i take the category menu in backend admin panel.

I checked in my old magento 1.9.4.5 site, there also store_id column is not present in customer_entity_varchar table, but everything is working perfectly there.

Tried migrating data again in a freshly installed magento 2.4.0 agian, but no luck.

m2-assistant[bot] commented 3 years ago

Hi @iamprasanthmp. Thank you for your report. To help us process this issue please make sure that you provided sufficient information.

Please, add a comment to assign the issue: @magento I am working on this

m2-assistant[bot] commented 3 years ago

Hi @iamprasanthmp! :wave: Thank you for collaboration. Only members of Community Contributors Team are allowed to be assigned to the issue. Please use @magento add to contributors team command to join Contributors team.

m2-assistant[bot] commented 3 years ago

Hi @iamprasanthmp! :wave: Thank you for joining. Please accept team invitation :point_right: here :point_left: and add your comment one more time.

thedarkboo commented 3 years ago

@iamprasanthmp Hi, i'm having the same problem.. so far from what i can tell, something in magento is trying to use customer address tables for the category information.

i got the admin working by doing this:

alter table customer_address_entity_decimal add `store_id` smallint(5) NOT NULL DEFAULT 0 COMMENT 'Store Id';
alter table customer_address_entity_datetime add `store_id` smallint(5) NOT NULL DEFAULT 0 COMMENT 'Store Id';
alter table customer_address_entity_text add `store_id` smallint(5) NOT NULL DEFAULT 0 COMMENT 'Store Id';
alter table customer_address_entity_int add `store_id` smallint(5) NOT NULL DEFAULT 0 COMMENT 'Store Id';
alter table customer_address_entity_varchar add `store_id` smallint(5) NOT NULL DEFAULT 0 COMMENT 'Store Id';

by adding the missing store_id field to those tables, indexing will work, and you should be able to gain access to the CATALOG -> CATEGORIES page, but you'll see that none of the categories have any text.. just their id's, and number of products in parentheses.

i started messing around by adding a catagory name and grepping to see where it changed, and if your actually able to save without it erroring.. you'll notice that it's populating the customer_address_entity_varchar table.

i'm pretty sure it's supposed to be using catalog_category_entity_varchar so somewhere it got messed up during migration.

so in my case, i have a lot of sites i've been converting, and the ones that were true 1.9.0.1 convert no problem, but sites that used to be 1.3.2.4 and got migrated over to 1.9.0.1 then migrated again to 2.4.0 are the ones that are failing for me.

ghost commented 3 years ago

Hello @thedarkboo, The issue still exist when trying to migrate straight from 1.9.4.5 to 2.4.0. The work around i figure out was to migrate to 2.3.4 and from there upgrading to 2.4.0. Hope it solves the issue for you to.

thedarkboo commented 3 years ago

Hello, i just wanted to follow up.

When i did a select * from catalog_category_entity the attribute_set_id was set to 5 for all my entity_ids other than 1 (which was set to 0) so i did update catalog_category_entity set attribute_set_id = 3 where attribute_set_id = 5; and reindexed and flushed, and everything seems to be working now.

i might be wrong, but i think this might have to do with the way things were inserted in the original db. The order of all the inserts seem to be odd numbers.. so the sequence of ids are inserted by odds like.. 1, 3, 5, 7, 9.

i noticed that in my later 1.9 db's (that work without any problems), they inserts are ordered sequentially 1, 2, 3, 4, 5...

romastepa commented 3 years ago

@thedarkboo @iamprasanthmp you don't need update customer tables!

UPDATE eav_entity_attribute 
SET  entity_type_id = {entity_type_id_you_need}
WHERE entity_attribute_id IN (SELECT entity_attribute_id FROM (
    SELECT eea.*
    FROM `eav_entity_attribute` AS eea
    JOIN `eav_attribute` AS ea ON eea.`attribute_id` = ea.`attribute_id` 
    WHERE ea.`entity_type_id` =  {entity_type_id_you_need}
) as x);

{entity_type_id_you_need} you can check in eav_entity_type (entity_type_code = 'catalog_category')

thedarkboo commented 3 years ago

@romastepa In the end, none of these solutions worked for me.. because when you set the entity_type_id to something else, it causes problems elsewhere. So in the end, i wrote a script to go through and reindex the db tables to be sequential and it seems to be working fine now.

The issue i had after setting the entity_type_id to 5 was the customer addresses where not showing up in the orders or their overview page.. related to: https://github.com/magento/magento2/issues/5873