--threads which are holding metadata locks.
|
MariaDB [(none)]> SELECT CONCAT('Thread ',P.ID,' executing "',P.INFO,'" IS LOCKED BY Thread ',M.THREAD_ID) WhoLocksWho FROM INFORMATION_SCHEMA.PROCESSLIST P,INFORMATION_SCHEMA.METADATA_LOCK_INFO M WHERE LOCATE(lcase(LOCK_TYPE), lcase(STATE))>0 limit 5;
|
+-----------------------------------------------------------------------------------------------+
|
| WhoLocksWho |
|
+-----------------------------------------------------------------------------------------------+
|
| Thread 1122427 executing "TRUNCATE TABLE dm_master.dim_structure" IS LOCKED BY Thread 1396804 |
|
| Thread 1122427 executing "TRUNCATE TABLE dm_master.dim_structure" IS LOCKED BY Thread 1355574 |
|
| Thread 1122427 executing "TRUNCATE TABLE dm_master.dim_structure" IS LOCKED BY Thread 1361915 |
|
| Thread 1122427 executing "TRUNCATE TABLE dm_master.dim_structure" IS LOCKED BY Thread 1355372 |
|
| Thread 1122427 executing "TRUNCATE TABLE dm_master.dim_structure" IS LOCKED BY Thread 1361977 |
|
+-----------------------------------------------------------------------------------------------+
|
5 rows in set (0.008 sec)
|
|
MariaDB [(none)]> select ID,USER,HOST,DB,COMMAND,TIME,INFO from information_schema.processlist where ID=1396804;
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ID | USER | HOST | DB | COMMAND | TIME | INFO |
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1396804 | userbi | mxs1:37548 | dm_fait | Query | 5562 | SELECT 'dm_fait.fait_orders' AS 'COL', ENRG_A_SUPP AS ENRG_A_SUPP, COUNT(1) AS NB_LIGNES, CAST(MIN(DATE_MAJ) AS CHAR(20)) AS 'DATE_MIN', CAST(MAX(DATE_MAJ) AS CHAR(20)) AS 'DATE_MAX'
|
FROM dm_fait.fait_orders GROUP BY 1,2 |
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
MariaDB [(none)]> select ID,USER,HOST,DB,COMMAND,TIME,INFO from information_schema.processlist where ID=1355574;
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ID | USER | HOST | DB | COMMAND | TIME | INFO |
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1355574 | userbi | mxs1:56124 | dm_fait | Query | 39752 | SELECT
|
u.CODE_TYPE_USER AS CODE_TYPE_USER_UT,
|
GROUP_CONCAT(DISTINCT RA.HEAD_ID) RUBRIQUE
|
FROM
|
dm_fait.fait_ra AS RA
|
INNER JOIN dm_master.uti_uti_org AS uo ON
|
RA.ORG_ID_STRUCTURE_EU = uo.ORG_ID
|
INNER JOIN dm_master.uti_utilisateur AS u ON
|
u.uti_id = uo.uti_id
|
WHERE
|
RA.ENRG_A_SUPP = 0
|
AND uo.UTI_LDAP_ID = 'supervista2@pixid.biz'
|
GROUP BY 1 |
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.003 sec)
|
|
MariaDB [(none)]> select ID,USER,HOST,DB,COMMAND,TIME,INFO from information_schema.processlist where ID=1361915;
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ID | USER | HOST | DB | COMMAND | TIME | INFO |
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1361915 | userbi | mxs1:55092 | dm_fait | Query | 39819 | SELECT
|
u.CODE_TYPE_USER AS CODE_TYPE_USER_UT,
|
GROUP_CONCAT(DISTINCT RA.HEAD_ID) RUBRIQUE
|
FROM
|
dm_fait.fait_ra AS RA
|
INNER JOIN dm_master.uti_uti_org AS uo ON
|
RA.ORG_ID_STRUCTURE_EU = uo.ORG_ID
|
INNER JOIN dm_master.uti_utilisateur AS u ON
|
u.uti_id = uo.uti_id
|
WHERE
|
RA.ENRG_A_SUPP = 0
|
AND uo.UTI_LDAP_ID = 'supervista2@pixid.biz'
|
GROUP BY 1 |
|
+---------+--------+------------+---------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
--killing one of the blocking queries and thread remains in killed state
|
MariaDB [(none)]> kill 1396804;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [(none)]> select ID,USER,HOST,DB,COMMAND,TIME,INFO from information_schema.processlist where ID=1396804;
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ID | USER | HOST | DB | COMMAND | TIME | INFO |
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1396804 | userbi | mxs1:37548 | dm_fait | Killed | 6162 | SELECT 'dm_fait.fait_orders' AS 'COL', ENRG_A_SUPP AS ENRG_A_SUPP, COUNT(1) AS NB_LIGNES, CAST(MIN(DATE_MAJ) AS CHAR(20)) AS 'DATE_MIN', CAST(MAX(DATE_MAJ) AS CHAR(20)) AS 'DATE_MAX'
|
FROM dm_fait.fait_orders GROUP BY 1,2 |
|
+---------+--------+------------+---------+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.003 sec)
|
|
-- after mariadb restart
|
MariaDB [(none)]> show processlist;
|
+-----+-------------+------------------+------+-----------+------+--------------------------------------------------------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+-------------+------------------+------+-----------+------+--------------------------------------------------------+------------------+----------+
|
| 5 | system user | | NULL | Slave_IO | 18 | Waiting for master to send event | NULL | 0.000 |
|
| 6 | system user | | NULL | Slave_SQL | 0 | Slave has read all relay log; waiting for more updates | NULL | 0.000 |
|
| 7 | mxs | mxs1:44510 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 10 | monitoring | 10.10.1.98:38794 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 24 | monitoring | mxs1:44520 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 105 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
|
+-----+-------------+------------------+------+-----------+------+--------------------------------------------------------+------------------+----------+
|
6 rows in set (0.000 sec)
|