Product Edit SQL Error

Question

It's been a few days that this error show up when editors editing a product:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ) AND (p.entity_id = '4623') LIMIT 1' at line 3, query was:SELECT DISTINCT p.entity_id FROM catalog_product_entity AS p INNER JOIN catalog_product_flat_4 AS cpf ON cpf.entity_id = p.entity_id LEFT JOIN catalog_category_product AS ccp ON ccp.product_id = p.entity_id WHERE ( () ) AND (p.entity_id = '4623') LIMIT 1

I suppose it's due to empty WHERE section of sql statement, i'm not sure if editors changed/delete record or something? I roll back database to few days back and check the equal sql statement that works fine and here it is:

SELECT DISTINCT p.entity_id FROM `catalog_product_entity` AS `p` INNER JOIN `catalog_product_flat_4` AS `cpf` ON cpf.entity_id = p.entity_id LEFT JOIN `catalog_category_product` AS `ccp` ON ccp.product_id = p.entity_id WHERE ( (`ccp`.`category_id` IN ('68')) ) AND (p.entity_id = '3671') LIMIT 1

As you see the part missing from first statement is ccp.category_id IN ('68') part. I checked the category with id=68 and it still exist. Any idea what may caused this error? Also what is interest me is that ccp.category_id IN ('68')) section is on every product i tried to edit. Although many products not belongs to this category (68). Why magento check that?

Answer 1

Open your phpmyadmin and run below query

TRUNCATE TABLE `catalog_product_flat_4`

Hope this will solve your problem

Answer 2

Finally i figured it out, for future references: There was this active "Catalog Price Rules" with empty Category rule (Category is ...), and that caused the error. Thanks to this post from michael thessel who helped me track down the issue, very useful post for track which Magento file running the queries.