Multi select attribute not indexing CE 2.6.1

Question

I migrated my site from 1.9x to 2.6.1. I have at least 2 multi select attributes that are not being indexed to catalog_product_index_eav. In researching this issue, I found that if the eav_attribute.backend_type is set to varchar, I should see it in catalog_product_entity_varchar. Its not there, but I have found it in catalog_product_entity_text.

I found this issue by doing an advanced search and realizing that when I selected one of the multi select attributes to search on, no results were found. They do work on my 1.9x site. I have verified all the attribute settings. I've even created a new multiselect attribute as a test and that works fine. I even manually added the non working attribute to the catalog_product_index_eav table and then the search could find it.

So, I'm looking for more ways to debug or other things to check to figure out why its not indexing to catalog_product_index_eav.

Thank you

Answer 1

I was able to modify these scripts to fix my issue: https://dev98.de/2017/01/19/fixing-issues-after-changing-product-attribute-type-from-varchar-to-text/

First thing I did was change my attributes from text to varchar in the eav_attribute table. Then I ran the below sql

UPDATE catalog_product_entity_varchar, catalog_product_entity_text 
SET catalog_product_entity_varchar.value = catalog_product_entity_text.value
WHERE catalog_product_entity_varchar.attribute_id = 
catalog_product_entity_text.attribute_id 
AND catalog_product_entity_varchar.entity_id = 
catalog_product_entity_text.entity_id 
AND catalog_product_entity_text.store_id = 
catalog_product_entity_varchar.store_id 
AND catalog_product_entity_varchar.value is null
AND catalog_product_entity_text.attribute_id = {attribute_id};


INSERT IGNORE 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 = {attribute_id};
and catalog_product_entity_text.value is not null;

DELETE FROM catalog_product_entity_text where attribute_id = {attribute_id};

After that, I reindexed and all was fixed.

Answer 2

Here is my similar sql solution, which may be a bit more clear and easier to modify for your needs:

-- View the attributes to verify what we are changing
SELECT * FROM shm2_eav_attribute WHERE frontend_input = 'multiselect' AND entity_type_id = 4

-- Update them to varchar
UPDATE shm2_eav_attribute SET backend_type = 'varchar' WHERE frontend_input = 'multiselect' AND entity_type_id = 4

-- Copy the values from the text table to the varchar table 
INSERT IGNORE INTO shm2_catalog_product_entity_varchar 
SELECT NULL, attribute_id, store_id, VALUE,row_id 
FROM shm2_catalog_product_entity_text
WHERE attribute_id 
IN (SELECT attribute_id FROM shm2_eav_attribute WHERE frontend_input = 'multiselect' AND entity_type_id = 4) 

-- Remove them from the text table
DELETE FROM shm2_catalog_product_entity_text 
WHERE attribute_id IN (SELECT attribute_id FROM shm2_eav_attribute WHERE frontend_input = 'multiselect' AND entity_type_id = 4)