Issues with QAN in PMM 2.42.0: Error 1064 and 1345 when processing SQL queries

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

Description:

I am experiencing issues with Query Analytics (QAN) in PMM 2.42.0, where certain SQL queries fail to execute and return errors. Specifically:

  • Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘? LEFT OUTER JOIN…’ at line 1
    This occurs when processing queries in QAN. The queries work fine when run manually in MySQL.
  • Error 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
    This happens when attempting to use the “Explain” functionality in QAN. It appears to be related to insufficient permissions for the user configured in PMM.

Steps to Reproduce:

  • Install PMM Server 2.42.0 on a CentOS 9 Stream server.
  • Install PMM Client 2.42.0 on a CentOS 7.9 server.
  • Connect PMM Client to PMM Server and add a MySQL database (Percona Server 8.0.40) as a monitored instance.
  • Run Query Analytics in the PMM dashboard for a query containing placeholders (?).
  • Attempt to use the “Explain” feature for a specific query in QAN.

Version:

PMM Server:** 2.42.0 (CentOS 9 Stream)
PMM Client:** 2.42.0 (CentOS 7.9)
Database:** Percona Server for MySQL 8.0.40

Logs:

Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘? LEFT OUTER JOIN…’ at line 1

Error 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Expected Result:

  • Query Analytics should process SQL queries without syntax errors.
  • The “Explain” feature in QAN should work correctly when analyzing queries.

Actual Result:

  • Query Analytics fails to process certain queries and returns Error 1064.
  • The “Explain” functionality fails with Error 1345, indicating insufficient privileges for the configured PMM user.

Additional Information:

The performance_schema is enabled, and performance_schema_max_sql_text_length has been increased to capture longer queries. (perfschema)

2

You need to increase performance_schema_max_digest_length to something large, like 4096 or greater. This will solve the 1064 errors.
The pmm user you created does not have proper permissions to execute EXPLAIN. Grant SELECT to the pmm user and that will solve the 1345 issue.

3

Thank you for the suggestions! After reviewing the current configuration:

  1. performance_schema_max_digest_length`: The current value is already set to 20240, which exceeds the recommended 4096. Therefore, no changes are needed in this regard.
  2. Permissions for the pmm user:The user pmm has the following grants:
  • SELECT, RELOAD, PROCESS, REPLICATION CLIENT, SHOW VIEW on *.*
  • Specific SELECT permissions on the performance_schema and another application database.I have also confirmed that EXPLAIN works as expected with these permissions. If further adjustments are required, please let me know.

Thanks again for your guidance! Let me know if there’s anything else I should verify or adjust.