Attribute not shown in layer navigation after Migration

In my case migrated multi-select attribute has invalid backend_type value as text, it should be varchar for multiple select

After changing backend_type from text to varchar, it's working.

The steps to resolve this issue after migrating is first to open the attribute and save it again, this will set the type from text to varchar. After this run the SQL below to copy all data from the text to the varchar table.

INSERT INTO catalog_product_entity_varchar
(store_id, attribute_id, entity_id, value)
    SELECT store_id, attribute_id, entity_id, value
    FROM catalog_product_entity_text
    WHERE catalog_product_entity_text.attribute_id = {your attribute id}
    AND catalog_product_entity_text.value is not null;

Now delete the data from the text table

DELETE FROM `catalog_product_entity_text`
WHERE `attribute_id` = {your attribute id}

Run in terminal

• php magento2/bin/magento indexer:reindex
• php magento2/bin/magento cache:flush

If the reindex is giving a duplication error, search for the entity_id in the catalog_product_entity_varchar table and check if there is a duplicate value for the attribute id in the field.

Thank you to the other answers. I've put together a full fix to migrate the affected attributes.

This issue can be caused by a product migration. The multi-select attribute has the backend_type value as text when it should be varchar.

See if this issue affects the website (you will get no records returned if you are okay):

SELECT * from eav_attribute WHERE entity_type_id=4 AND frontend_input="multiselect" AND backend_type="text";

Copy data values from text to varchar table:

INSERT INTO catalog_product_entity_varchar (store_id, attribute_id, entity_id, value) SELECT store_id, attribute_id, entity_id, value FROM catalog_product_entity_text WHERE catalog_product_entity_text.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE frontend_input="multiselect" AND backend_type="text") AND catalog_product_entity_text.value is not null;

Delete text values:

DELETE FROM catalog_product_entity_text WHERE attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE frontend_input="multiselect" AND backend_type="text");

Change attribute type:

UPDATE eav_attribute SET backend_type="varchar" WHERE frontend_input="multiselect" AND backend_type="text" AND entity_type_id=4;

After this reindex the website, clear the cache, and the attributes should appear in the layered navigation.