I recently upgraded Magento 2.07 to Magento 2.1 and then to 2.1.1 but now I get the MySQL error ERROR 1054: 1054: Unknown column 'entity_id' in 'NEW'
any time I try to import a product or update a product attribute.
I can't re-import my DB backup either because MySQL reports errors whilst importing the backup about NEW.entity_id
Best Answer
Solution
Just toggle your indexer mode from
schedule
torealtime
and then back toschedule
. You can use the Magento Admin (System->Index Management) or the command line tool thus:As described in the Magento2 documentation.
What's occurring?
The error is due to some tables eg
catalog_product_entity_int
having triggers on them so that the scheduled indexer knows what to index. The module CatalogStaging (or one of its dependencies) changes a column name in the table fromentity_id
torow_id
but the trigger does not get updated so it still refers toNEW.entity_id
when theentity_id
column no longer exists.The triggers are created when the index mode is set to
scheduled
so this situation may affect any DB of a Magento install that has been set to index on schedule before upgrading to 2.1.The fix is simple but not obvious : toggle the indexer to
realtime
(update on save) and then put it back toschedule
. This clears the triggers and then recreates them with the correct reference to the updated column name:NEW.row_id
in the trigger as now required by the Magento_CatalogStaging module