4

Quite often we get the following error in Magento when we try to make a change to one of our Categories.

SQLSTATE[HY000] [1205] Lock wait timeout exceeded; try restarting transaction, query was: UPDATE catalog_category_entity SET entity_type_id = ?, attribute_set_id = ?, parent_id = ?, created_at = '2015-10-30 11:24:57', updated_at = '2016-11-27 07:33:07', path = ?, position = ?, level = ?, children_count = ? WHERE (entity_id=250)

It seems to only be occurring after we have moved around or reorganised a category and I can see from System > Index Management that Catalog URL Rewrites is still being processed. Once the indexing is finished we can make changes to categories, but this is a recurring problem which really slows things down.

I've checked Catalog > URL Rewrite Management and we have almost 500,000 rewrites, we have 4 store views, 350,000 plus of them are custom although we obviously did not create them all. I believe this is what is slowing everything down.

I'm not sure what to do here, if I truncate the core_url_rewrite database table with TRUNCATE TABLE 'core_url_rewrite'; and re-index Catalog URL Rewrites in System > Index Management will that fix it? Is there anything I should be aware of before I cary try this? I'll do a table & db back up first of course and test it in a local environment.

4

The error is because the transaction is reaching its timeout period. If you did not set this in your my.cnf the default is 50 seconds I would highly recommend you look at your sever settings to ensure that they are optimized for your database using tools such as MySQLTuner

I have the innodb_lock_wait_timeout set to 7200 (2 hours) in a store with 500k products with 2 store views.

Example to add or change in my.cnf

innodb_lock_wait_timeout = 7200

DO NOT TRUNCATE.... yet

When you "moved around or reorganised a category" Magento will keep the references to these original links (hence the massive amount of links and 4 store views as well) and redirect them for visitors / bots / search engines that have book marked or indexed these pages.This can negatively affect your SEO, when you truncate they will receive a error 404 message. My personal recommendation is BEFORE doing major changes to the structure, ensure that nothing of the sort has been done for at least a month, then truncate then do your restructuring. This will cut down enormously on the size of your URL Rewrites

Since you've already re-arranged your categories, the best thing to do now is wait for a month to give visitors / bots / search engines the time to update the links --- then purge.

Also checkout:Magento core_url_rewrite table excessively large

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.