Magento 2 Reindex Fail `Unknown column 'entity_value.value'` in 'field list'

Question

I am having this error when running php bin/magento indexer:reindex also failing when loading the product:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'entity_value.value' in 'field list', query was: SELECT `entity`.`sku`, 
`product_entity`.`entity_id` AS `product_id`, 
`attribute`.`attribute_code`, `entity_value`.`value` AS `value_index`, 
`attribute_label`.`value` AS `super_attribute_label`, 
IFNULL(option_value.value, default_option_value.value) AS 
`option_title`, `default_option_value`.`value` AS `default_title` FROM 
`catalog_product_super_attribute` AS `super_attribute`
INNER JOIN `catalog_product_entity` AS `product_entity` ON 
product_entity.entity_id = super_attribute.product_id
INNER JOIN `catalog_product_super_link` AS `product_link` ON 
product_link.parent_id = super_attribute.product_id
INNER JOIN `eav_attribute` AS `attribute` ON attribute.attribute_id = 
super_attribute.attribute_id
INNER JOIN `catalog_product_entity` AS `entity` ON entity.entity_id = 
product_link.product_id
INNER JOIN `catalog_product_entity` AS `entity_value` ON 
entity_value.attribute_id = super_attribute.attribute_id AND 
entity_value.store_id = 0 AND entity_value.entity_id = entity.entity_id
LEFT JOIN `catalog_product_super_attribute_label` AS `attribute_label` 
ON super_attribute.product_super_attribute_id = 
attribute_label.product_super_attribute_id AND attribute_label.store_id 
= 0
LEFT JOIN `eav_attribute_option_value` AS `option_value` ON 
option_value.option_id = entity_value.value AND option_value.store_id = 
1
LEFT JOIN `eav_attribute_option_value` AS `default_option_value` ON 
default_option_value.option_id = entity_value.value AND 
default_option_value.store_id = 0 WHERE (super_attribute.product_id = 
1721) AND (attribute.attribute_id = '')

In backend I get error:

I have disabled it because categories where having the exception also.

I am not sure but this product is missing something is some table any ideas where to start cause I can't even deleted unless got direct in mysql but again how may table entry to delete a product !

Any ideas any help apreciated. thanks

Answer 1

I've found the problem:

Table: catalog_product_super_attribute was inserted a row with attribute_id that didn't exist e.g I have size and colour

in table eav_attribute I have size id=2 and colour id=1

in table catalog_product_super_attribute the was row in column attribute_id referencing size id=3 and colour id=4 once I updated it to the correct id everything went back to normal.

Thanks.

Answer 2

Thanks to the answer above I managed to know what's causing the issue (I spent hours trying to figure it out lol)

I ran this SQL query and that fixed it for me

DELETE FROM catalog_product_super_attribute
WHERE NOT EXISTS (
    SELECT *
    FROM eav_attribute
    WHERE attribute_id=catalog_product_super_attribute.attribute_id
)

Answer 3

Can someone help me how to do this?