magento2: SQL error on indexer:reindex 2.2.8

Summary

Catalog indexer is broken when flat mode is turned on.

Information on your environment

Local environment (containerised NGINX with lando) and hosting provided by platform sh

Preconditions (*)

Magento version 2.2.8
Flat category and flat product catalogue enabled (Error doesn’t appear with them off)

Steps to reproduce (*)

Updated to 2.2.8 from 2.2.6
Run php bin/magento indexer:reindex

Expected result (*)

Indexer completes successfully

Actual result (*)

Indexer produces 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)

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 16 (8 by maintainers)

Most upvoted comments

@engcom-backlog-nazar I can still replicate this issue on 2.2.9

danielpfarmer on Jul 24, 2019

@danharper83 condition is a reserved word in MySQL/MariaDB. Backticks (`) around the keyword would escape it and it would not be interpreted in a special manner by the parser; I think that’s the correct change to make that I referenced above

danmooney2 on May 28, 2019

Yeah, try this in app/code/Magento/Catalog/Model/Indexer/Product/Flat/FlatTableBuilder.php line 368:

)->where("`$attributeCode` IS NOT NULL");

danmooney2 on May 28, 2019