Magento 1.9.3.9 Catalog URL Rewrites stuck in processing

[vpatel93]

Dear Community,

I have updated my store from 1.9.3.8 to 1.9.3.9 and now when I attempt to reindex, the Catalog URL Rewrites index gets left in a processing state after running for awhile it throws a 500 error and causes the following error the in php log:

Allowed memory size of 268435456 bytes exhausted (tried to allocate 130968 bytes) in /magento/app/code/core/Zend/Db/Select.php on line 281

Now, I understand this message and have tested on local also with the memory set to 3GB: more than enough memory;  yet still causes this issue. 

I have also ran the db repair tool against a new fresh install and got:

...Database doesnt require changes

So, the database is perfectly fine. I have also checked the process list to see if magento is still working on it, and nothing no inserts and updates present.

Reindexing the said index via shell is also indifferent and causes same result. 

What has changed in 1.9.3.9 that could cause this?

[gelanivishal]

Hello @vpatel93,

The problem comes from .htaccess. One of the values is php_value memory_limit 256M. This doesn't make sense on the first view, but on the second it is explainable.

\Mage_Shell_Abstract::_applyPhpVariables
protected function _applyPhpVariables()
{
    $htaccess = $this->_getRootPath() . '.htaccess';
    if (file_exists($htaccess)) {
        // parse htaccess file
        $data = file_get_contents($htaccess);
        $matches = array();
        preg_match_all('#^\s+?php_value\s+([a-z_]+)\s+(.+)$#siUm', $data, $matches, PREG_SET_ORDER);
        if ($matches) {
            foreach ($matches as $match) {
                @ini_set($match[1], str_replace("\r", '', $match[2]));
            }
        }
        preg_match_all('#^\s+?php_flag\s+([a-z_]+)\s+(.+)$#siUm', $data, $matches, PREG_SET_ORDER);
        if ($matches) {
            foreach ($matches as $match) {
                @ini_set($match[1], str_replace("\r", '', $match[2]));
            }
        }
    }
}

--
If you've found one of my answers useful, please give Kudos or Accept as Solution

[vpatel93]

The reason was due to the core_url_rewrite table becoming too big. 

[Henry van Megen]

There's a fix for that in magento-lts over @ https://github.com/openmage/magento-lts .. in fact, there's tons more bugs fixed in that version that Magento even cares to fix themselves for whatever reason.

[AQELTech]

It seems to be an issue with the PHP configuration, but you can still try to reindex using SSH:

php -f shell/indexer.php --reindexall

If did not work, then just drop me a message and I will be more than glad to tick this issue off for you.

Sincerely, 

[vpatel93]

MageSuper that had no bearing. I had to truncate system entries in the url_rewrite table. Something caused the table to extrapolate rather massively.

[peter_modryk]

Hello. Could you explain exactly how did you fix this? We have imported pictures for 40k of products and have the same thing :-/

[vpatel93 · Accepted solution]

@peter_modryk  After a great length of time spent investigating and researching this issue, I have a solution.

But, before getting into the solution it is key to note that there are at least three issues with the catalog URL rewrite indexer and URL rewrite management in Magento:

• A reindex will add a new rows for products with duplicate url_key's - every single time you reindex;
• Disabled products generate new rows;
• and products that are not-visible individually generate new rows

This will make your table grow into the millions of rows and hundreds of Megabytes. I guess the best way to fix it is to never have duplicate URL-keys. You can create a module that will prevent you from saving the product if another product exists with the same URL key.

Moreover, like in my case if your table is already too big, see if it’s actually due to duplicate keys.

You can find out how many duplicate url key's you have with the following query:

SELECT COUNT(DISTINCT entity_id) AS amount, `value`, entity_id
FROM catalog_product_entity_varchar v
WHERE EXISTS (
  SELECT *
  FROM eav_attribute a
  WHERE attribute_code = "url_key"
  AND v.attribute_id = a.attribute_id
  AND EXISTS (
     SELECT *
     FROM eav_entity_type e
     WHERE entity_type_code = "catalog_product"
     AND a.entity_type_id = e.entity_type_id
  )
)
GROUP BY v.VALUE
HAVING amount > 1
ORDER BY `amount` DESC;

This returned approximately 256 results for myself, with each on average around 4 duplicates. A rather substantial amount. I spent time correcting this, ensuring all products have unique URLS's. I then went a step further and removed very old disabled products that were no longer required.

Then instead of truncating the whole table, you can use the following query to clear out only the unnecessary rewrites (and make sure to create a backup first):

DELETE
FROM core_url_rewrite
WHERE is_system <> 1
AND id_path REGEXP "^[0-9]+_[0-9]+$";

Now, I am able to reindex the catalog_url_rewrite index without issues. Looking over the table I can also see that the rows have decreased from the previous amount of 196120 to 22840.