Problem with reindex catalog_product_prices

Question

Having a problem with reindexing catalog_product_prices. I get the following error message:

exception 'PDOException' with message '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 tad_price_type.store_id = 0 LEFT JOIN catalog_product_entity AS `tas_pric' at line 14' in /home/vampow/public_html/store/lib/Zend/Db/Statement/Pdo.php:228

Next exception 'Zend_Db_Statement_Exception' with message '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 tad_price_type.store_id = 0 LEFT JOIN catalog_product_entity AS `tas_pric' at line 14' in /home/vampow/public_html/store/lib/Zend/Db/Statement/Pdo.php:234

This is the SQL line that is causing the problem (14):

INNER JOIN catalog_product_entity AS tad_price_type ON tad_price_type.entity_id = e.entity_id AND tad_price_type.attribute_id = AND tad_price_type.store_id = 0

Ran Database repair tool. Result was database didn't require any changes.

Answer 1

As you can see on your error your query doesn't look correct: tad_price_type.attribute_id

INNER JOIN catalog_product_entity AS tad_price_type ON tad_price_type.entity_id = e.entity_id AND tad_price_type.attribute_id = AND tad_price_type.store_id = 0

The table catalog_product_entity doesn't have any attribute_id column.

Most of the times this happens after a new module is installed. You should try:

1. Check that all the new module files have the right ownership and permissions.
2. Flush all caches.
3. Try reindexing from command line: php shell/indexer.php reindexall

Good luck!