Magento – Index error after upgrade to EE 1.14.2.0: table catalog_product_entity_tmp_indexer doesn’t exist

as stated correctly by Sander, deleted products still present in the product flat table cause this error. Anyway, truncating the whole table will not be necessary. You can find these products by this SQL-query:

SELECT pf1.entity_id
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id ) 

You will then have to delete these items, which can be done using this SQL-query:

DELETE pf1.*
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id ) 

Taken from here (German): http://www.avs-webentwicklung.de/nc/blog/artikel/magento-fehler-beim-index-aufbau-sofortmassnahmen.html

Ask me if u need more advise.

It took some digging, but I eventually found this article. Problem solved. :) http://support.createhosting.co.nz/knowledgebase.php?action=displayarticle&id=70

EDIT:

Mark as answered, details from link below for future reference:

This error occurs when trying to run the Category Products index, and is due to the indexing process attempting to insert a row into the catalog_category_product_index table, where either the product_id or the category_id don't exist. This causes the "Intergrity Constraint Violation".

This can sometimes happen when a database gets corrupt, often from 3rd party desktop type software or a badly written extension.

To fix this, first check that this is indeed the issue. Backup your database and then run the following SQL to see if any product and/or category associations are incorrect:

# Determine incorrect product associations
SELECT * FROM `catalog_category_product` WHERE
product_id not in (select entity_id from catalog_product_entity);

# Determine incorrect categories
SELECT * FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

If that returns a record, you'll know exactly which product is in a non-existent category or which category has a non-existent product. Run the following SQL to then remove those invalid records:

# Delete incorrect product associations
DELETE FROM `catalog_category_product` WHERE 
product_id not in (select entity_id from catalog_product_entity);

# Delete incorrect categories
DELETE FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

Now log back into Magento Admin and reindex the Category Products index.