Description:
When running an upgrade from one version of MySQL to another I generally do something like:
# Assume MySQL is already running
ssh $h 'set -x; sudo /etc/init.d/mysqld stop; sudo yum update -y mysql-community-{libs,libs-compat,common,server,client}; sudo /etc/init.d/mysqld start; sudo mysql_upgrade; sudo /etc/init.d/mysqld restart'
This is for CentOS but you get the idea and is currently to allow me to upgrade between MySQL 5.7 versions. The same procedure also works for 5.6.
If on the second /etc/init.d/mysqld start you do nothing it's possible for applications to connect to the database and "do things". Monitoring processes typically try to do this quite aggressively: they want to know if the server is up and pull metrics about its state.
If they use sys or performance_schema it may be impossible for mysql_upgrade to rebuild these databases due to meta-data locks.
The solution is easy: kill these threads and everything works fine, but if you don't do that this basically can lock forever and is not visible.
How to repeat:
During this process I see:
me@myhost [(none)]> show processlist;
+-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL |
| 5 | some_user | myhost.example.com:45164 | performance_schema | Sleep | 109 | | NULL |
| 9 | root | localhost | mysql | Query | 108 | Waiting for table metadata lock | DROP DATABASE IF EXISTS performance_schema |
| 28 | some_user | myhost.example.com:45176 | performance_schema | Query | 99 | Waiting for table metadata lock | SELECT USER, SUBSTRING_INDEX(EVENT_NAME, '/', -1) as EVENT_NAME, COUNT_STAR FROM performance_schema. |
| 258 | me | localhost | NULL | Query | 0 | starting | show processlist |
+-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
This will block indefinitely unless something is done.
This is a real life situation and has happened several times to me.
Suggested fix:
Figure out how to avoid these users getting in, or check for this situation and kill the connections if needed.
A work around is to do the first start with --skip-networking but then you have to remember to do that, so behaviour is special. That would not work if the user connected though the unix socket of course.
Therefore having mysql_upgrade recognise that it might be blocked and resolving that would be good.
© 2026, Oracle Corporation and/or its affiliates
Description: When running an upgrade from one version of MySQL to another I generally do something like: # Assume MySQL is already running ssh $h 'set -x; sudo /etc/init.d/mysqld stop; sudo yum update -y mysql-community-{libs,libs-compat,common,server,client}; sudo /etc/init.d/mysqld start; sudo mysql_upgrade; sudo /etc/init.d/mysqld restart' This is for CentOS but you get the idea and is currently to allow me to upgrade between MySQL 5.7 versions. The same procedure also works for 5.6. If on the second /etc/init.d/mysqld start you do nothing it's possible for applications to connect to the database and "do things". Monitoring processes typically try to do this quite aggressively: they want to know if the server is up and pull metrics about its state. If they use sys or performance_schema it may be impossible for mysql_upgrade to rebuild these databases due to meta-data locks. The solution is easy: kill these threads and everything works fine, but if you don't do that this basically can lock forever and is not visible. How to repeat: During this process I see: me@myhost [(none)]> show processlist; +-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL | | 5 | some_user | myhost.example.com:45164 | performance_schema | Sleep | 109 | | NULL | | 9 | root | localhost | mysql | Query | 108 | Waiting for table metadata lock | DROP DATABASE IF EXISTS performance_schema | | 28 | some_user | myhost.example.com:45176 | performance_schema | Query | 99 | Waiting for table metadata lock | SELECT USER, SUBSTRING_INDEX(EVENT_NAME, '/', -1) as EVENT_NAME, COUNT_STAR FROM performance_schema. | | 258 | me | localhost | NULL | Query | 0 | starting | show processlist | +-----+-----------------+--------------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) This will block indefinitely unless something is done. This is a real life situation and has happened several times to me. Suggested fix: Figure out how to avoid these users getting in, or check for this situation and kill the connections if needed. A work around is to do the first start with --skip-networking but then you have to remember to do that, so behaviour is special. That would not work if the user connected though the unix socket of course. Therefore having mysql_upgrade recognise that it might be blocked and resolving that would be good.