Magento EE 1.13.0.2 partial reindex

Question

I'm having a problem with magento partial reindexing in the EE. When the indexers are set to on save (in system->configuration->(Advanced)Index management) the url_rewrites don't update.

If I save a product in the backend, then look in the table enterprise_catalog_product_rewrite there is no entry for this product and I guess because of this there is no entry in enterprise_url_rewrite which means it doesn't work.

I can see the product in the listing but the url is not SEO friendly and if the url key is entered in the browser it will not display the product.

I've been searching information on how this partial indexing works but there seems to be nothing except how better it is.

I've tried truncating url rewrite related tables manually (like this) but it just messed everything up, so I reverted the database.

Answer 1

The new UNIQUE index of Magento CE 1.8 changes the URL keys in the URL rewrite table. If you have multiple store views and import your products, (so that for each store view the URL key is saved) you automatically have duplicated keys. The update script is smart enough to not throw an error, but to rename all the keys so you’ll end up with something like:

 - http://de.example.com/someproduct.html
 - http://en.example.com/someproduct1.html
 - http://nl.example.com/someproduct2.html

To avoid that, the URL rewrites have to be fixed before the update so that there is one rewrite per product and URL key, and the store views use “Use default”. You can manage that with a single SQL query before the update:

DELETE nondefault
FROM catalog_product_entity_varchar AS nondefault
INNER JOIN catalog_product_entity_varchar AS def ON def.value = nondefault.value
AND def.entity_id = nondefault.entity_id
WHERE def.attribute_id =86
AND nondefault.attribute_id =86
AND nondefault.store_id <>0
AND def.store_id =0

Note that 86 is the ID of the URL key attribute. If you already have updated your system without running this query first, you have to remove the wrongly created URL keys first. This can be done with the following query:

DELETE url_table
FROM catalog_product_entity_url_key url_table
INNER JOIN catalog_product_entity_varchar old_url_table ON url_table.store_id = old_url_table.store_id
AND url_table.store_id <>0
AND old_url_table.store_id <>0
AND url_table.attribute_id = old_url_table.attribute_id
AND url_table.entity_id = old_url_table.entity_id;

I hope this helps! The following link might help you if you have further questions: http://www.code4business.de/update-magento-enterprise-edition-1-13-0-2/

Answer 2

I had the same problem where catalog_product_entity_url_key would contain the correct data for each store, and enterprise_url_rewrite would contain the correct request_path > target_path pairs. But no matches would happen because enterprise_catalog_product_rewrite was missing an entry for the given product, or had an entry which was pointing to a url_rewrite_id which didn't exist anymore in enterprise_url_rewrite.

My solution was to rebuild the enterprise_catalog_product_rewrite table with this query:

REPLACE INTO enterprise_catalog_product_rewrite
(`product_id`, `store_id`, `url_rewrite_id`)
(SELECT cpeuk.entity_id as product_id, cpeuk.store_id, eur.url_rewrite_id
FROM catalog_product_entity_url_key cpeuk
INNER JOIN enterprise_url_rewrite eur
ON cpeuk.value = eur.identifier)