27

After several messed up imports, I've been left with a load of URL rewrites that I need to remove.

I'm running Enterprise 1.13

When I've had this problem in community, I've simply truncated core_url_rewrite, and the reindexed.

However, in Enterprise, I notice that there a number of different tables that control rewrites.

  • enterprise_url_rewrite
  • enterprise_url_rewrite_category_cl
  • enterprise_url_rewrite_product_cl
  • enterprise_url_rewrite_redirect
  • enterprise_url_rewrite_redirect_cl
  • enterprise_url_rewrite_redirect_rewrite

Am I safe to truncate all of them?

I fully anticipate for someone to tell me that I should never truncate these tables, so apologies for the naivety in advance.

  • What do you mean by 'a number of different tables that control rewrites'? On EE I usually did the same thing as on CE. Truncate core_url_rewrite and it worked. – Marius May 22 '13 at 11:18
  • Hi Marius. These are the tables that look to control rewrites. I had truncated core_url_rewrites, but it made no effect to those listed in admin. enterprise_url_rewrite enterprise_url_rewrite_category_cl enterprise_url_rewrite_product_cl enterprise_url_rewrite_redirect enterprise_url_rewrite_redirect_cl enterprise_url_rewrite_redirect_rewrite Thanks – JamesAllwood May 22 '13 at 11:24
  • Oh...sorry. My bad. I missed this line "I'm running Enterprise 1.13". I have no experience (yet) with EE 1.13. Ignore me for now. – Marius May 22 '13 at 11:35
  • 1
    Something to consider: gist.github.com/Vinai/5451584 – B00MER Sep 12 '13 at 18:27
  • 1
    We recently updated Magento EE 1.12 to EE 1.13 for one of our shops and we wrote a post on our website about changes and problems that might come up: code4business.de/update-magento-enterprise-edition-1-13-0-2/… The post has an English translation in the bottom of the page. – user2830524 Sep 30 '13 at 16:21
30

We we're in the similar situation as you James. After a lot of digging this is what I came up with:

The core_url_rewrite table is now deprecated, instead Magento EE 1.13 now stores the rewrites in enterprise_url_rewrite.

Tables: enterprise_*_category_rewrite use catalog_*_entity_url_key tables to rebuild the two rewrite tables when you run php indexer.php --reindex catalog_url_*

When you add a 'URL Redirect' in admin Catalog->URL Redirects for a custom URL it gets added to the enterprise_url_rewrite_redirect table and the flag for Magento that the index is now outdated is entered into enterprise_url_rewrite_redirect_cl table which when running php indexer.php --reindex url_redirect rebuilds the enterprise_url_rewrite_redirect_rewrite table.

Quick note, any table ending in _cl is safe to truncate, the 'CL' stands for Change Log and is used by Magento to check if re-indexing is required.

As far as the URL Key tables go, I'm still a bit clueless as to why there two URL Key entries one in catalog_*_entity_url_key and one in catalog_*_entity_varchar (attribute id 90), but I assume this is what happens:

When you create a new product/category Magento uses the name to generate a url_key which is placed in catalog_*_entity_url_key AND in the catalog_*_entity_varchar, but the primary table used by Magento is the catalog_*_entity_url_key because if you truncate it and run php indexer.php --reindex catalog_url_* your enterprise_*_category_rewrite tables will be empty and products/categories in the frontend will display ugly urls i.e. http://example.com/catalog/product/view/id/123/etc/etc (not SOE friendly) I believe the two tables are related and are used to build the enterprise_url_rewrite table because this table stores a 'request_path' most likely the url_key inside the catalog_*_entity_varchar table and an 'identifier' which is the primary URL Key from the catalog_*_entity_url_key table. I could be completely wrong about url_key and varchar tables, so I'm just thinking out loud.

Anyway to successfully truncate and rebuilt all rewrite tables you can execute:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `core_url_rewrite`;
TRUNCATE TABLE `enterprise_catalog_category_rewrite`;
TRUNCATE TABLE `enterprise_catalog_product_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite_category_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_product_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_rewrite`;
SET FOREIGN_KEY_CHECKS = 1;

and then run:

sudo php indexer.php --reindex catalog_url_product
sudo php indexer.php --reindex catalog_url_category
sudo php indexer.php --reindex url_redirect

If you also truncate enterprise_url_rewrite_redirect then you will loose all of your custom redirects that you see in your admin panel, perhaps this is your goal since you were left with a ton of useless URLs. As long as you DO NOT truncate the '*_entity_url_key' tables you'll be fine.

Our story was a little different, because we had duplicate URL Keys and major problems with product names from excel imports after upgrading to 1.13 from 1.11 so I wrote this quick script to reset the catalog_product_entity_url_key table and the URL keys and URL paths in the catalog_product_entity_varchar table using product names. I attached the code below, but if you use it, use it at your own risk.

<?php
include_once('app/Mage.php');
Mage::app();

$dbHandle          = Mage::getSingleton('core/resource')->getConnection('core_write');
$productCounter    = 0;
$nameFixCounter    = 0;
$vUrlKeyFixCounter = 0;
$urlPathCounter    = 0;
$urlKeyCounter     = 0;
$productCollection = $dbHandle->query("SELECT entity_id, sku FROM catalog_product_entity");

while($product = $productCollection->fetch()) {    
  $dataString       = null;

  $oldProductName   = $dbHandle->query("SELECT value FROM catalog_product_entity_varchar WHERE entity_id = '".$product['entity_id']."' AND attribute_id = 65")->fetch();
  $oldVarcharUrlKey = $dbHandle->query("SELECT value FROM catalog_product_entity_varchar WHERE entity_id = '".$product['entity_id']."' AND attribute_id = 90")->fetch();
  $oldUrlPath       = $dbHandle->query("SELECT value FROM catalog_product_entity_varchar WHERE entity_id = '".$product['entity_id']."' AND store_id = 0 AND attribute_id = 91")->fetch();
  $oldUrlKey        = $dbHandle->query("SELECT value FROM catalog_product_entity_url_key WHERE entity_id = '".$product['entity_id']."'")->fetch();

  $newProductName   = preg_replace('/\s+/', ' ', trim(preg_replace('/[^\x20-\x21\x23-\x2B\x2D-\xE7]/', ' ', $oldProductName['value'])));
  $newUrlKey        = preg_replace('/\s+/', '-', trim(preg_replace('/[^\x30-\x39\x61-\x7A]/', ' ', strtolower($newProductName))));

  if (strcmp($oldProductName['value'], $newProductName)) {
    echo "-[".$oldProductName['value']."]\n";
    echo "+[".$newProductName."]\n";
    $dbHandle->query('UPDATE catalog_product_entity_varchar SET value = "'.$newProductName.'" WHERE entity_id = "'.$product['entity_id'].'" AND attribute_id = 65');
    ++$nameFixCounter;
  }

  if (strcmp($oldVarcharUrlKey['value'], $newUrlKey)) {
    echo "-[".$oldVarcharUrlKey['value']."]\n";
    echo "+[".$newUrlKey."]\n";
    if ($oldVarcharUrlKey['value'] === null) {
      $dbHandle->query("INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) VALUES ('4', '90', '0', '".$product['entity_id']."', '".$newUrlKey."')");
    } else {
      $dbHandle->query("UPDATE catalog_product_entity_varchar SET value = '".$newUrlKey."' WHERE entity_id = '".$product['entity_id']."' AND attribute_id = 90");
    }
    ++$vUrlKeyFixCounter;
  }

  if (strcmp($oldUrlPath['value'], $newUrlKey.'.html')) {
    echo "-[".$oldUrlPath['value']."]\n";
    echo "+[".$newUrlKey.".html]\n";
    if ($oldUrlPath['value'] === null) {
      $dbHandle->query("INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) VALUES ('4', '91', '0', '".$product['entity_id']."', '".$newUrlKey.".html')");
    } else {
      $dbHandle->query("UPDATE catalog_product_entity_varchar SET value = '".$newUrlKey.".html' WHERE entity_id = '".$product['entity_id']."' AND store_id = 0 AND attribute_id = 91");
    }
    ++$urlPathCounter;
  }

  if (strcmp($oldUrlKey['value'], $newUrlKey)) {
    echo "-[".$oldUrlKey['value']."]\n";
    echo "+[".$newUrlKey."]\n";
    if ($oldUrlKey['value'] === null) {
      $dbHandle->query("INSERT INTO catalog_product_entity_url_key (entity_type_id, attribute_id, store_id, entity_id, value) VALUES ('4', '90', '0', '".$product['entity_id']."', '".$newUrlKey."')");
    } else {
      $dbHandle->query("UPDATE catalog_product_entity_url_key SET value = '".$newUrlKey."' WHERE entity_id = '".$product['entity_id']."'");
    }
    ++$urlKeyCounter;
  }

  $report  = "[".++$productCounter."] ";
  $report .= "NAME: [".(strcmp($oldProductName['value'], $newProductName)?'!=':'==')."] ";
  $report .= "V_KEY: [".(strcmp($oldVarcharUrlKey['value'], $newUrlKey)?'!=':'==')."] ";
  $report .= "PATH: [".(strcmp($oldUrlPath['value'], $newUrlKey.'.html')?'!=':'==')."] ";
  $report .= "KEY: [".(strcmp($oldUrlKey['value'], $newUrlKey)?'!=':'==')."]\n";
  echo $report;

}
echo 'Total Products: ['.$productCounter.'] Names: ['.$nameFixCounter.'] V_Keys: ['.$vUrlKeyFixCounter.'] Paths: ['.$urlPathCounter.'] Keys: ['.$urlKeyCounter.']';

The code can be tweaked to use Magentos formatKey method here: http://www.magentocommerce.com/wiki/3_-_store_setup_and_management/seo/url_key_characters_conversion unfortunately I came across the wiki after I updated all the keys so I didn't bother reupdating everything again.

Hope that helps :)!

  • sudo php indexer.php --reindex catalog_url_catalog should be sudo php indexer.php --reindex catalog_url_category. – Matthias Zeis Oct 10 '13 at 8:36
  • I'm trying to do the same right now. After truncating all tables though, only direct category and product URLs are reindexed. I couldn't find any entry for products in categories like catalog/product/view/id/XXX/category/YYY. Can you please confirm that this is the same for you? I'm kinda clueless about this... Is it a bug, or am I doing something wrong? I tried to do the same on a fresh install of 1.13.0.2, the same thing happened. Rewrites works all right in frontend, but no category is set. – fmrng Oct 10 '13 at 10:47
9

Based on what I've seen messing around with EE 1.13 in a test environment and a quick little tests I just did, you should be able to simple truncate those tables and then manually rebuild all the URL indexes from the CLI.

The *_cl tables are used in the TRIGGERS found on the catalog_product_entity_url_key table. The records they insert into these *_cl table are what, I think, is used to indicate what needs to be re-indexed after the saves.

Here is what I did. After using the CLI tool to re-build the indexes, everything appeared to be good to go. MySql truncation…

TRUNCATE TABLE `enterprise_url_rewrite_redirect_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect`;
TRUNCATE TABLE `enterprise_url_rewrite_product_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_category_cl`;
TRUNCATE TABLE `enterprise_url_rewrite`;
TRUNCATE TABLE `enterprise_catalog_product_rewrite`;
TRUNCATE TABLE `enterprise_catalog_category_rewrite`;
TRUNCATE TABLE `core_url_rewrite`;

Then on the CLI…

php shell/indexer.php --reindex catalog_url_product
php shell/indexer.php --reindex catalog_url_category
php shell/indexer.php --reindex url_redirect

Let us know your results… like Marius, I've not yet built an EE 1.13 site and have only the experience from messing around with it since Imagine. :)

  • 1
    Hi David, Thanks for your detailed response. I tried your instructions, but no luck unfortunately. It did clear all of the rewrites, but running indexer.php didn't regenerate any. Overnight, Magento support have actually got back to me, and their advice was that URL rewrites are now saved in: - catalog_product_entity_url_key for products - catalog_category_entity_url_key for categories I've tried clearing these too, although there was actually only 2 entries in them, but again now luck. I've asked them for some further clarification, so I'll let you know as soon as they get back to me. – JamesAllwood May 24 '13 at 7:48
  • One thing I noticed when looking at this was that the URL rewrites are stored in enterprise_url_rewrite vs core_url_rewrite as they were before. The catalog_*_entity_url_key tables seem to be a replicated table with the url-keys for use by the indexer, and they are also the tables with the triggers related to the URL rewrites. – davidalger May 24 '13 at 13:19
  • @Francesco, had you previously run that script after upgrading from 1.12? If not, then it would be expected that you need to run it, and I wouldn't call that buggy since it's part of the documented upgrade process going from 1.12 to 1.13. – davidalger Jan 10 '14 at 14:38
  • @davidalger: you are right the script works almost well (it creates some weird Urls but only fews) However the url rewrite functionality is quite weak in this EE release ( for ex. changing url-key for a product and save it, doesn't work as expected ) – Fra Jan 11 '14 at 3:18
  • This answer should be accepted. I can confirm this works on EE 1.13. – musicliftsme Feb 6 '14 at 14:55
4

A note regarding using TRUNCATE:

TRUNCATE TABLE `enterprise_url_rewrite`;

gives an error because of foreign key references:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`customerx_dev`.`enterprise_catalog_category_rewrite`, CONSTRAINT `FK_415B32DA3DF924D5C803CF24EB3AC1D9` FOREIGN KEY (`url_rewrite_id`) REFERENCES `customerx_dev`.`enterprise_url_rewrite` (`url_rewrite_i)

Running truncate/delete commands like this would work:

TRUNCATE TABLE `enterprise_url_rewrite_redirect_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect`;
TRUNCATE TABLE `enterprise_url_rewrite_product_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_category_cl`;
TRUNCATE TABLE `enterprise_catalog_product_rewrite`;
TRUNCATE TABLE `enterprise_catalog_category_rewrite`;
TRUNCATE TABLE `core_url_rewrite`;
DELETE FROM `enterprise_url_rewrite`;
  • Use SET FOREIGN_KEY_CHECKS = 0; before your TRUNCATE ... and SET FOREIGN_KEY_CHECKS = 1; at the very bottom, after DELETE FROM ... – Oleg Jul 9 '13 at 18:09
4

The simple answer is No it is not safe to truncate these tables, at least if you don't know the consequence:

  • Truncate all rewrite tables and running re-index leads to a working site

However:

  • You will loose all custom rewrites ( that is normal )
  • Catalog -> Url Redirect will be empty (on EE 1.13.1) ( that looks like a bug according with Magento this is the expected behavior on 1.13.1) ( see also below comment )
  • 2
    I'd just like to add that Catalog -> Url Redirect shows only non-system rewrites. So, only your custom rewrites will show here. i.e. rows with enterprise_url_rewrite.system = 0. – musicliftsme Feb 5 '14 at 23:25
  • yes you are right, I have improved the answer with the last info I got from Magento Support Team. Feel free to improve my answer if you wish – Fra Feb 6 '14 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.