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

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)
  • Thanks for the response, I'm not writing a custom module. This just happens when I run php bin/magento indexer:reindex – Daniel Harper May 23 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 May 24 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. – Daniel Harper May 28 at 17:30

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.