magento2: bin/magento setup:upgrade causes metadata locks

Preconditions (*)

Magento 2.2.6
MySQL 5.7.23 (Amazon RDS)
Jenkins
A huge store with 250000 products, and each product has more than 200 attributes.

Steps to reproduce (*)

We’re using Jenkins for continuous integration.
When running “bin/magento setup:upgrade” from the jenkins build server, it takes a really long time (sometimes 1 hour+).
After viewing MySql server processes, I can see that there’s a lot queries with “Waiting for table metadata lock”.
It seems to be stuck on “Magento_Indexer”:

Data post-updates:
Module 'Magento_Store':
Module 'Amasty_Base':
Module 'Amasty_Geoip':
Module 'Amasty_Scroll':
Module 'Amasty_ShopbyBase':
Module 'Magento_Directory':
Module 'Amasty_ShopbyBrand':
Module 'Magento_Theme':
Running data recurring...
Module 'Magento_Eav':
Module 'Magento_Customer':
Module 'Magento_Indexer':

Expected result (*)

“bin/magento setup:upgrade” shouldn’t take so long.
No waiting for metadata locks.

Actual result (*)

screenshot 2018-11-15 at 10 36 29 2. screenshot 2018-11-15 at 10 43 23 3. We’re getting metadata locks and it can takes over an hour for them to disappear.

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 6
Comments: 35 (15 by maintainers)

Most upvoted comments

Same issue on 2.4.2.

marius-balteanu on Jul 8, 2021

Hi @Nazar65, what was the fix for this, I seem to be experiencing the same issue on a site running 2.2.5; we’re not currently in a position to upgrade.

nicholasscottfish on Feb 25, 2020

I just saw this on 2.3.4-p2

convenient on Jun 23, 2021

Hi @Nuranto,

We are reopening this issue.

Thanks.

engcom-Bravo on Jun 16, 2023

Hi @engcom-Hotel,

Can you consider reopen this issue ?

The issue was confirmed, and then closed by @Nazar65 without any solution (we don’t know which parameter he was refering to in his comment). Also, it would be better to fix this in code instead of custom settings in mysql/mariadb (which also seems risky).

And as you can see, this is still affecting many of us.

see also : #33386

Nuranto on Jun 14, 2023

@Flowlance I have an experience on this problem. I don’t know exactly why this happened. Probably you have huge product images in pub/media/catalog/product directory. You can temporary move the product directory to somewhere else (outside pub directory). then run again bin/magento setup:upgrade. This should be much faster this time. then move back the product directory to original location. This problem only occur when you run setup:upgrade command. This way may be not you expected but it’s working for me.

advcha on Nov 21, 2018