0

When I run indexer:reindex I get the following SQL error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right syntax to use 
near 'condition IS NOT NULL)' at line 5, query was: 

UPDATE `catalog_product_flat_1_tmp_indexer` AS `et`
 LEFT JOIN `eav_attribute_option_value` AS `t0` ON t0.option_id = et.condition AND t0.store_id = 0
 LEFT JOIN `eav_attribute_option_value` AS `ts` ON ts.option_id = et.condition AND ts.store_id = 1
SET `et`.`condition_value` = IFNULL(ts.value, t0.value)
WHERE (condition IS NOT NULL)

I can't work out what attributes are cuasing it, this happened updating between 2.2.7 -> 2.2.8

1 Answer 1

0

You should not use condition as a variable as it is declared in mysql :
https://dev.mysql.com/doc/refman/5.6/en/declare-condition.html

However, now that you have used, you should always add escape with ` like you can see for other places. The last where clause seems to be a custom from your module which you need to modify. So, after modification it should look like this:

UPDATE `catalog_product_flat_1_tmp_indexer` AS `et`
 LEFT JOIN `eav_attribute_option_value` AS `t0` ON t0.option_id = et.condition AND t0.store_id = 0
 LEFT JOIN `eav_attribute_option_value` AS `ts` ON ts.option_id = et.condition AND ts.store_id = 1
SET `et`.`condition_value` = IFNULL(ts.value, t0.value)
WHERE (`condition` IS NOT NULL)
3
  • Thanks for the response, I'm not writing a custom module. This just happens when I run php bin/magento indexer:reindex Commented May 23, 2019 at 10:55
  • I got that. Actually what I am trying to highlight is in one of the custom modules you might have done something like this: $this->dbCollectionModel->getSelect()->where('condition IS NOT NULL'); and if it is then you should change it to something like this: $this->dbCollectionModel->getSelect()->where('**condition** IS NOT NULL'); Unable to add ` character for condition
    – Arif Ahmad
    Commented May 24, 2019 at 3:15
  • I'll search the codebase for that but I have disabled all custom modules I'm using and I still get the error. Commented May 28, 2019 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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