How do I determine what the SQL statement is of the thread ID showing up in a metadata lock info row (SELECT * FROM information_schema.metadata_lock_info) on MariaDB?
Server version: 10.0.15-MariaDB MariaDB Server
All of the related questions dive into the "Waiting for table metadata lock" from a MySQL perspective, but that does not help with MariaDB since their introspection is implemented differently from what I can tell. Googling around does not turn up a whole lot.
A "show full processlist" gives rows like:
| 57295 | main | localhost | joints | Execute | 50 | Waiting for table metadata lock | select ...
Which does show the statement, but does not show that it has the lock either. So, I turned on metadata lock info as explained here [0]. This only provides the thread ID of the lock holder, but not the statement:
MariaDB [joints]> SELECT * FROM information_schema.metadata_lock_info;
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
| 57322 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
| 57322 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT | Table metadata lock | joints | 16_study |
| 57322 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | joints | |
| 57269 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | authentication |
| 57301 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | authentication |
| 57280 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | authentication |
| 57317 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | ship |
| 57271 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | administration |
| 57264 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | joints | server |
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
What I really want is to see the "join" of both of those outputs at the moment the locking is happening. I do not see a way to join the data from these two "tables" since the former does not appear to be a table. I'd like to avoid getting:
ERROR 1933 (HY000): Target is not running an EXPLAINable command
while attempting to do it in real-time, due to the thread ending while being inspected.
THREAD_ID maps to information_schema.PROCESSLIST.ID (the first column in show
[full] processlist;. ie:
SELECT * FROM information_schema.METADATA_LOCK_INFO AS mli
JOIN information_schema.PROCESSLIST AS pl ON mli.THREAD_ID = pl.ID
I am preferential towards something like the following to make it easier to see the what is happening (the newlines don't work well with the cli though):
SELECT
mli.THREAD_ID, mli.LOCK_MODE, mli.LOCK_TYPE,
CAST(GROUP_CONCAT(DISTINCT CONCAT(mli.TABLE_SCHEMA, '.', mli.TABLE_NAME) ORDER BY mli.TABLE_SCHEMA, mli.TABLE_NAME SEPARATOR '\n') AS CHAR) AS locked_tables,
pl.USER, pl.HOST, pl.DB, pl.COMMAND, pl.TIME, pl.STATE, pl.INFO, pl.QUERY_ID, pl.TID
FROM information_schema.METADATA_LOCK_INFO AS mli
JOIN information_schema.PROCESSLIST AS pl ON mli.THREAD_ID = pl.ID
GROUP BY mli.THREAD_ID, mli.LOCK_MODE, mli.LOCK_TYPE
ORDER BY time DESC, pl.ID;
Especially interesting is when pl.COMMAND = 'Sleep' as that indicates some connection pool or other (mostly read-only) program is holding open connections that have locks on them.