1

I am faced with an issue, whereby after updating magento to 1.9.3.9 from 1.9.3.8 my catalog_url_rewrite index is stuck on processing. Checking process list I cannot see anything untoward. Also, checking status via shell I see running. Why after updating a store would the reindex now be stuck on processing?

Checking the table catalog_product_index_eav there is no data in there at all. Now, I am not sure if this should be empty or not. But, this was also empty before the update.

I have also tried to reindex via shell same result.

The php error cropping up is:

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, my memory limit has not been changed whatsoever and is in fact at the 256mb I set. On my local I even set the limit to a rather massive 3GB to test, yet the same result.

What could be causing this from the update? On the same server I also have a 1.9.3.8 version, and the reindex completed within 30 seconds.

To rule out database issues I have ran the database repair tool against a fresh new install:

reapair tool clear

And, as seen there are no issues at all with database.

"...Database doesnt require changes" (db repair tool, 2018)

I even disabled all local extensions in local.xml:

 <disable_local_modules>true</disable_local_modules>

But, the same thing occurs: It runs for awhile then blanks out, and in the php log you can see the above memory size exhausted issue. Rather perplexed as I am not sure what is happening here now.

Interestingly, when I click any product and hit save, I can see that the catalog url rewrite index has been reindex, without the aforementioned issue.

Running the following query I get 196120 rows in the table:

SELECT COUNT(url_rewrite_id) FROM core_url_rewrite;
  • 23389 of which are defined as custom;
  • And 172722 of which are defined as system.

I am contemplating truncating as a solution. Thus, is it safe to do so, and what are the ramifications?

0

If you already tried killing MySql Process, then please try below to update status.

UPDATE `index_process` SET `status` = 'pending' WHERE `status` = 'working';
  • No process was in the mysql process list for the reindex. – Vaishal Patel Jul 20 '18 at 6:05
  • okay, please check status for catalog_url_rewrite in index_process table. If status is working then change it to pending – Nalin Savaliya Jul 20 '18 at 7:23
  • but then it will do the same next time I reindex :s – Vaishal Patel Jul 20 '18 at 7:51
  • It may not do the same next time just make this changes and try reindex. – Nalin Savaliya Jul 20 '18 at 7:57
  • Just as I thought, the reindex still casing the same problem – Vaishal Patel Jul 20 '18 at 8:14
0

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.

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.