Error 1142 (42000): UPDATE command denied to user when Explain/Tables tab clicked

Percona Monitoring and Management (PMM) PMM 2.x
1

Description:

When Explain/Tables tab is clicked to get execution plan for DML statement error is popped.

Error 1142 (42000): UPDATE command denied to user 'pmm2mysql'@'127.0.0.1' for table 'table1'
or
Error 1142 (42000): INSERT, UPDATE command denied to user 'pmm2mysql'@'127.0.0.1' for table 'table2'

Looks like explain plan is not working for DML statements. Even tested with SUPERUSER privileges for “pmm2mysql” user and behavior is the same.

Is this expected?

Steps to Reproduce:

Click on DML query and check Explain plan.

Version:

2.39 (server and client)

Expected Result:

Get Explain plan.

Actual Result:

Error 1142 (42000): UPDATE command denied to user ‘pmm2mysql’@‘127.0.0.1’ for table ‘table1’

Additional Information:

Chrome

2

Hello Msutic,

Could you please share grants for the ‘pmm2mysql’@‘127.0.0.1’ user ?

Regards,
Denis Subbota.
Managed Services, Percona.

3

Hi Denis,

mysql> show grants for 'pmm2mysql'@'127.0.0.1';
+--------------------------------------------------------------------------------------------+
| Grants for pmm2mysql@127.0.0.1                                                             |
+--------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'pmm2mysql'@'127.0.0.1' |
+--------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Best regards,
Marko

4

Hi msutic,
With such grants it’s expected to see such kind of errors.
Try to add grants INSERT, UPDATE for user ‘pmm2mysql’@‘127.0.0.1’ it should help you to fix this issue:

GRANT SELECT, INSERT, UPDATE ,RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'pmm2mysql'@'127.0.0.1';

Regards,
Denis Subbota.
Managed Services, Percona.

5

Hi Denis,
Granting mentioned privileges fixes issue.

Do you think docs needs to be updated?

CREATE USER 'pmm'@'127.0.0.1' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'127.0.0.1';

With mentioned privileges you will not be able to get explain plan for DML statements.

Thanks

Best regards,
Marko