Como arreglar problemas reindexando magento

Si nos encontramos con que el reindexado no funciona tras actualizar magento de versión, es posible que haya datos sin relacionar en las tablas con lo que tendremos que arreglar los problemas reindexando magento

Si al ejecutar el indexador desde la consola obtenemos algo así:

[~/dev/shell]# php indexer.php reindexall
Product Attributes index was rebuilt successfully in 00:00:00
Product Prices index was rebuilt successfully in 00:00:00
Catalog URL Rewrites index was rebuilt successfully in 00:00:02
Category Products index process unknown error: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mage19`.`mg_catalog_category_product_index`, CONSTRAINT `FK_MG_CAT_CTGR_PRD_IDX_CTGR_ID_MG_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `mg_catalog_category_entity` )' in /dev/lib/Zend/Db/Statement/Pdo.php:228

Podremos arreglarlo con estas consultas sql:
delete from `catalog_category_product` WHERE product_id not in(select entity_id from `catalog_product_entity`);

delete from `catalog_category_product` WHERE category_id not in(select entity_id from catalog_category_entity);

delete from `catalog_product_website` WHERE product_id not in(select entity_id from catalog_product_entity);

delete from `catalog_product_index_eav_idx` WHERE entity_id not in(select entity_id from catalog_product_entity);

delete from `catalog_product_index_eav` WHERE entity_id not in(select entity_id from catalog_product_entity);

delete from `catalog_product_link` WHERE product_id not in(select entity_id from catalog_product_entity);

delete from `catalog_product_relation` WHERE parent_id not in(select entity_id from catalog_product_entity);

delete from `sales_flat_quote_shipping_rate` WHERE `address_id` not in(select `address_id` from `sales_flat_quote_address`); 

Como siempre, mucho cuidado con ejecutar consultas SQL contra la base de datos de producción