While deploying an upgrade for one of our Magento projects, I run into a problem with Elasticsuite.
setup:upgrade routine the following SQL error was thrown and the upgrade procedure died:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento.smile_virtualcategory_catalog_category_product_position' doesn't exist, query was: CREATE TRIGGER trg_smile_virtualctgr_cat_ctgr_prd_position_after_insert AFTER INSERT ON smile_virtualcategory_catalog_category_product_position FOR EACH ROW BEGIN INSERT IGNORE INTO ``catalogsearch_fulltext_cl`` (``entity_id``) VALUES (NEW.``product_id``); END
At first, I was not sure what the problem was. Interestingly enough, everything worked fine in the staging environment but failed while deploying to the production environment. Luckily, I quickly came across this issue on GitHub.
The main problem seems to be Magento's Mview mechanism is trying to create triggers before the table is really created. The proposed solution is:
- Configure the indexers to "update on save"
- Set indexers back to "update on schedule"
That recommendation worked fine for me. In case you run into the same issue, execute the steps outlined above and everything should work fine.