Bug #119798 Feature request: Add a Thread State for dropping tables.

• Submitted: 27 Jan 20:24
• Reporter: Leonardo Fernandes
• Status: Open
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[27 Jan 20:24] Leonardo Fernandes

Description:
There are multiple thread states (https://dev.mysql.com/doc/refman/9.4/en/general-thread-states.html) related to table operations, such as "altering table", "creating table", "Opening tables", and so on, but there isn't a thread state for dropping a table. 

The operation itself gets tracked with the state "checking permissions", which is confusing. For example:

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000212 |
| Executing hook on transaction  | 0.000008 |
| starting                       | 0.000022 |
| checking permissions           | 2.137169 |
| waiting for handler commit     | 0.000014 |
| waiting for handler commit     | 0.002795 |
| query end                      | 0.000017 |
| closing tables                 | 0.000004 |
| waiting for handler commit     | 0.000016 |
| freeing items                  | 0.000148 |
| cleaning up                    | 0.000025 |
+--------------------------------+----------+
11 rows in set, 1 warning (0.00 sec)

How to repeat:
Create a large table (with AHI enabled, as purging the AHI entries can slow down the operation), then:

set profiling=1;

DROP TABLE <...>;

show profile for query N; 

Check that the majority of the time spent is in "checking permissions" due to the lack of a thread state dedicated to dropping tables.

Suggested fix:
Add a thread state for dropping a table.