0

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?

  • Can you share the Mysql parameter value for max_join_size – Franck Garnier Mar 27 '17 at 11:56
2

Indeed you can tweak Mysql parameters.

For example, with a default AWS Maria DB, I have these values :

show variables where Variable_name = 'max_join_size';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+

MariaDB [(none)]> show variables where Variable_name = 'SQL_BIG_SELECTS';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
+-----------------+-------+
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.