Bug #86057 SHOW OPEN TABLES incorrectly reports tables as locked in-place ALTER TABLE

• Submitted: 24 Apr 2017 15:45
• Modified: 11 May 2017 14:01
• Reporter: Thomas Wozniakowski
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7+
• OS: MacOS
• CPU Architecture: Any

[24 Apr 2017 15:45] Thomas Wozniakowski

Description:
The SHOW OPEN TABLES statement incorrectly reports that a thread is holding a table lock on tables that are undergoing in-place ALTER TABLE statements (online DDL).

How to repeat:
Create a table with a bunch of dummy data, such that adding a column will take a minute or so.

Execute an ALTER TABLE ADD COLUMN statement with LOCK=NONE.

While it executes, open another connection and execute "SHOW OPEN TABLES".

The table in question will show with in_use as 1.

To verify the table is not locked, perform some reads and writes against it while the ALTER TABLE continues to execute.

Suggested fix:
Make SHOW OPEN TABLES not report the table as locked when online ALTER TABLE statements are being used.

[11 May 2017 14:01] MySQL Verification Team

Hi!

This is not actually lock at all, but a lock requests issued by SHOW OPEN TABLES ....