4

What triggers inserts/updates on the enterprise_url_rewrite table in EE 1.13? We have a very large catalog (about 700,000 products) and about 500,000 of those do not have corresponding enterprise_url_rewrite entries, even though they do have a url_key set in catalog_product_entity_varchar.

(Note that enterprise_url_rewrite is not the same table as core_url_rewrite)

Enterprise Url Rewrite

+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| url_rewrite_id | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| request_path   | varchar(255)         | NO   | UNI | NULL    |                |
| target_path    | varchar(255)         | NO   |     | NULL    |                |
| is_system      | smallint(5) unsigned | NO   |     | NULL    |                |
| guid           | varchar(32)          | NO   |     | NULL    |                |
| identifier     | varchar(255)         | NO   | MUL | NULL    |                |
| inc            | int(10) unsigned     | NO   |     | 1       |                |
| value_id       | int(10) unsigned     | NO   | MUL | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+

Core Url Rewrite

+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| url_rewrite_id | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| store_id       | smallint(5) unsigned | NO   | MUL | 0       |                |
| id_path        | varchar(255)         | YES  | MUL | NULL    |                |
| request_path   | varchar(255)         | YES  | MUL | NULL    |                |
| target_path    | varchar(255)         | YES  | MUL | NULL    |                |
| is_system      | smallint(5) unsigned | YES  |     | 1       |                |
| options        | varchar(255)         | YES  |     | NULL    |                |
| description    | varchar(255)         | YES  |     | NULL    |                |
| category_id    | int(10) unsigned     | YES  | MUL | NULL    |                |
| product_id     | int(10) unsigned     | YES  | MUL | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
2

Late reply but might be helpful. We were suffering from the same issue in Magento EE Release Notes (1.13) as a result from switching to the catalog_product_entity_url_key table for storing the url_key values.

Before using Magmi, random products imported via Dataflow had urls like (/catalog/product/view/_cache_category/1/id//category//). After installing Magmi, the issue became more serious and all the urls appeared like this because Magmi isn't aware of the new table catalog_product_entity_url_key and only inserts into catalog_product_entity_varchar.

I added the following code inside the function (importItem) to the file magmi/engines/magmi_productimportengine.php:

if (!$this->_same) { // update stock $this->updateStock($pid, $item, $isnew); }

// Fix product url_key after import
        if(isset($pid) && isset( $item['url_key'] ) && $item['url_key'] != "" ) {
            $store_ids = $this->getItemStoreIds($item);
            $new_url_key = $item['url_key'] . '-' . $pid;
            //if($isnew) {
                foreach ($store_ids as $store_id) {
                    try {
                        $cpeuk_sql = "INSERT INTO catalog_product_entity_url_key(
                            SELECT value_id, entity_type_id, attribute_id, store_id, entity_id, '" . $new_url_key ."' FROM catalog_product_entity_varchar
                            WHERE entity_id = " . $pid . " AND attribute_id = 97 AND store_id = " . $store_id .
                        ")";
                        $this->insert($cpeuk_sql);
                        // $this->log("Recover/Insert new url_key: ".$new_url_key, "startup");
                    } catch (\PDOException $e) {
                        $cpeuk_sql = "UPDATE catalog_product_entity_url_key SET value = '" . $new_url_key . "'
                            WHERE entity_id = " . $pid . " AND attribute_id = 97 AND store_id = " . $store_id;
                        $this->update($cpeuk_sql);
                        // $this->log("Recover/Update url_key: ".$new_url_key, "startup");
                    }
                }
            //}
        }
        ////

$this->touchProduct($pid);

This has fixed the urls for most of the uploaded items and appends the product id to the url to prevent duplicated urls. However, some of them are getting only the product id! (e.g. /-1111). As far as I think this is not related to our fix because we ignore adding blank url_key values.

Maybe there is a better fix but we didn't have much time to dig into Magmi's code more.

1

Turns out the issue was that Magento is using yet another table to store url keys that we weren't even aware of.

During import-crunch-time, we ended up using the Magmi importer (Sorry everyone!), which is designed for 1.12 and below and of course doesn't know about the catalog_product_entity_url_key table. The 100,000 or so products with working rewrites worked because they were created using an older (slower, more reliable) method.

To protect people in the future from their own clipboards, I won't paste the code I used to synchronize the two non-index url tables, but taking the entity_id and value from catalog_product_entity_varchar where the value's attribute is url_key and inserting into the catalog_product_entity_url_key table did the trick.

References: Clear All URL Rewrites - Enterprise

  • A note: you should also clear all url_key attribute values from the varchar. Magento support have confirmed that they will never be used any more, but they will cause the admin UI to stop the "use default value" button being checked if a value exists in that table. – Robbie Averill May 18 '16 at 3:43
0

Please see my comment about how 1.13 EE uses MySQL Triggers for inserting flat data on EAV insert/updates/etc.

You can still use the command line to "force" a regeneration of the index via:

php shell/indexer.php reindexall (see -- help) for options.

Also I've run into duplicate URL keys before, Vinai has written a nice shell script to help fix this issue as well:

Hope this helps.

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.