I'm getting the following error when running the catalog_product_price
indexer from the CLI tool (bin/magento indexer:reindex catalog_product_price
):
SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT
would examine more than MAX_JOIN_SIZE rows; check your WHERE and use
SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay,
query was:DELETE `index_price` FROM `catalog_product_index_price` AS `index_price` LEFT JOIN `catalog_product_index_price_idx` AS `ip_tmp` ON index_price.entity_id = ip_tmp.entity_id AND index_price.website_id = ip_tmp.website_id WHERE (ip_tmp.entity_id IS NULL)
The website has 8,419 products, and 4 websites.
Truncating the price index tables works for a while, however the error does come back after a few days.
Has anyone seen this issue before? Is it simply a case of tweaking the MySQL parameters? Or is this indicative of a larger problem?
Best Answer
Indeed you can tweak Mysql parameters.
For example, with a default AWS Maria DB, I have these values :