Bug #68165 mysql_upgrade can be blocked dropping PERFORMANCE_SCHEMA if a user is in the db.

• Submitted: 24 Jan 2013 13:28
• Modified: 11 Feb 2013 9:40
• Reporter: Simon Mudd (OCA)
• Status: Not a Bug
• Category: MySQL Server: Installing
• Severity: S3 (Non-critical)
• Version: 5.6.9-rc
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[24 Jan 2013 13:28] Simon Mudd

Description:
While perhaps an edge case I was doing a 5.5.23 to 5.6.9-rc upgrade. The server is a server which is monitored by other systems.

We have a script which monitors the %usage use of the slave sql thread by querying P_S. This script kicked in while I was doing the mysql_upgrade.

And the mysql_upgrade script was blocked and stuck. Only when I manually killed the process did the upgrade script complete.

How to repeat:
1. start mysqld
2. connect to P_S and do queries there.
3. run mysql_upgrade
4. See it hang as shown below:

So I saw:

| 34 | root          | localhost                                 | mysql              | Query   |  105 | Waiting for table metadata lock | DROP DATABASE IF EXISTS performance_schema
was waiting on cacti ...
|  2 | cacti         | myhost.mydomain.com:43824 | performance_schema | Sleep   |    4 |                                 | NULL                                       |

kill the thread, by logging into mysql again with a separate client.
Then things continue as normal.

Suggested fix:

There's clearly a need to protect the upgrade process from interference from other "users".
A single user mode or similar might be good, then the issue can not happen.

Until then it seems wise to:
1. check if there are any users in the P_S database,
2. kill them to ensure the 'DROP DATABASE IF EXISTS performance_schema' command will be able to continue, and warn about this in the log file (in case there's a race condition and they come back before the DROP DATABASE is executed...)

Again this may be an edge case but it's caught me, so it'll catch others.

[11 Feb 2013 9:39] Marc ALFF

The mysql_upgrade process is expected to run "alone" against a server.

This however, is not enforced, so that a client can connect to the server during the upgrade, and interfere.

A known case where a client can make the upgrade wait for a lock is:
- The client uses AUTOCOMMIT=OFF
- The client performs a select against a system table (performance_schema.* or mysql.*)
- The client never COMMIT or ROLLBACK transactions.

The server behaves as expected in this case.

The fix is to:
- either prevent any connection to the server during an upgrade,
- or at least do not create open ended transactions, that can be a bug in the application itself.