Skip to main content

Elasticsuite Indexer problem

This blog post might be outdated!
This blog post was published more than one year ago and might be outdated!
· One min read
Stephan Hochdörfer
Stephan Hochdörfer
Head of IT Business Operations

While deploying an upgrade for one of our Magento projects, I run into a problem with Elasticsuite.

During the 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:

  1. Configure the indexers to "update on save"
  2. Run setup:upgrade
  3. 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.