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: UPDATEcatalog_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.
Best Answer
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 MySQLTunerI have the
innodb_lock_wait_timeout
set to7200
(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 aerror 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, thentruncate
then do your restructuring. This will cut down enormously on the size of your URL RewritesSince 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