Checking permissions issue in mysql processlist

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi Expert,

I checked in process list there are so many connection which stuck in checking permissions state

mysql>show processlist;

| 12211246 | myuser1 | ...:14919 | itbc | Query | 7861 | checking permissions | CREATE TABLE IF NOT EXISTS zones ( name varchar(255) not null primary key, status varchar

mysql> select count() from mysql.user;

±---------+

| count() |

±---------+

| 41 |

±---------+

1 row in set (0.00 sec)

mysql> select count() from mysql.db;

±---------+

| count() |

±---------+

| 55 |

±---------+

please help me on this.

2

Hi, these could mean a lot of different things unfortunately. Mostly related to contention issues. Check show engine innodb status to see if anything stands out. Also I suggest installing PMM if you get a chance, having metrics will help you find out where is the problem more effectively.

3

@@igroene I found in show engine innodb status output

and there are 100 connection state showing wsrep: initiating replication for write set (-1)

—TRANSACTION 8236194995, ACTIVE 37273 sec

mysql tables in use 1, locked 1

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 12201711, OS thread handle 140594681710336, query id 3876413072 hostname myuser1 wsrep: initiating replication for write set (-1)

UPDATE zxc

SET

toAt = ‘2020-12-04 08:04:31.814’,

fromAt = null,

class = null,

goodTypeId = 1,

badTypeId = NULL,

samId = NULL

WHERE

id = 4738276

LIMIT 1

—TRANSACTION 8236194994, ACTIVE 37273 sec

4

Ok from the output it seems you have some uncomitted transactions from long ago. I suggest dealing with that problem first, it is possible it is related to the checking permissions issue