Details
-
Improvement
-
Status: Done
-
Medium
-
Resolution: Fixed
-
None
-
http://mysterybox.surlyhorns.com:8080/graph Username: admin Password: surlymon http://mysterybox.surlyhorns.com:8080/graph/d/pmm-qan/pmm-query-analytics?columns=%5B%22load%22,%22num_queries%22,%22query_time%22%5D&var-environment=All&var-cluster=All&var-replication_set=All&var-database=All&var-schema=All&var-node_name=All&var-service_name=All&var-service_type=All&var-node_type=All&var-interval=$__auto_interval_interval&order_by=-query_time&group_by=queryid&from=now-12h&to=now&totals=false&details_tab=details
-
3
-
Yes
-
Yes
-
C/S Core
Description
The Problem: With the current level of permissions for PMM users (recommended one) it's impossible to get Explain for DELETE queries.
This leads to some unexpected UI errors and not full visibility over the Database.
Proposed solution: Convert Explain DELETE queries to Explain SELECT queries (See comments) to get some meaningful information
Original report:
I updated PMM to 2.9.0 and noticed that some of the explain plans were not populating. I seemed to isolate it to DELETE statements but it could also effect UPDATE (have not confirmed).
environment has the relevant link sorted where a delete should be the top result and a few complex join queries which also don't show tables, but show explains work.
I have also noticed that queries with comments tend to break the explain plan.
Proposed solution
Reimplement query transform as we had in qan-agent: https://github.com/percona/qan-agent/blob/master/query/plugin/mysql/explain/transform.go in https://github.com/percona/pmm-agent/blob/master/actions/mysql_explain_action.go#L56
The response from the backend is an array of bytes with the explain's output.
The new response should be a json encoded array of bytes from this structure:
{
"result": []bytes,
"equivalentSelect": []bytes
}
The front end should process this response and if the equivalentSelect field is not empty, it should display a message to let the user know the query was converted to run the explain.
Scenario: Explain non-DML query
When: User runs Explain on a non-DML query
Then: the backend returns the response with the equivalentSelect field empty
And: the front end shows the standard explain output
Scenario: Explain DML query
When: User runs Explain on a DML query
Then: the backend returns the response with the equivalentSelect field having the equivalent SELECT query ran by the BE
And: the front end shows the standard explain output plus a message saying the query was converted to an equivalent SELECT
