EXPLAIN command fails for statements with comment prefix #46587
Description
Component(s)
receiver/mysql
What happened?
Description
While testing the explain plan functionality on the top_query data flow, I noticed an ingested record for a sample query I ran, to have explain plan missing.
Steps to Reproduce
Using a DBMS tools like DBeaver, I connected to MySQL instance with Adventureworks schema, and ran the following statement many times, while having the mysql receiver set up to monitor for top_query
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 'MYPPSN-123456'
Expected Result
I expected the explain to work correctly, fetching me an explain plan for my statement in the top_query flow, like the following:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "30.50"
},
"table": {
"table_name": "Employee",
"access_type": "ALL",
"rows_examined_per_scan": 290,
"rows_produced_per_join": 29,
"filtered": "10.00",
"cost_info": {
"read_cost": "27.60",
"eval_cost": "2.90",
"prefix_cost": "30.50",
"data_read_per_join": "70K"
},
"used_columns": [
"BusinessEntityID",
"NationalIDNumber",
"LoginID",
"OrganizationNode",
"OrganizationLevel",
"JobTitle",
"BirthDate",
"MaritalStatus",
"Gender",
"HireDate",
"SalariedFlag",
"VacationHours",
"SickLeaveHours",
"CurrentFlag",
"rowguid",
"ModifiedDate"
],
"attached_condition": "(`HumanResources`.`Employee`.`NationalIDNumber` = '\\0M\\0Y\\0P\\0P\\0S\\0N\\0-\\01\\02\\03\\04\\05\\06')"
}
}
}
Actual Result
I inspected the log record and observed the plan to be empty
"mysql.query_plan": "",
Collector version
v0.147.0
Environment information
Environment
OS: (e.g., "Ubuntu 20.04")
Compiler(if manually compiled): (e.g., "go 14.2")
OpenTelemetry Collector configuration
Log output
Additional context
The root cause is that the EXPLAIN function doesnt consider sql statements that start with a comment. In my case, since I executed the statement using a DBMS tool, the QUERY_SAMPLE_TEXT value from the top_query template fetched
/* ApplicationName=DBeaver 25.3.5 - SQLEditor <Script-11.sql> */ SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 'MYPPSN-123456'
LIMIT 0, 200
As a result, client.go lines 819-837 are not handling this.
Proposed fix (Cursor recommendation):
func isQueryExplainable(query string) bool {
sqlStartingKeywords := []string{
"select", "delete", "insert", "replace", "update",
}
trimmedQuery := strings.TrimSpace(query)
// Strip leading block comments (/* ... */)
for strings.HasPrefix(trimmedQuery, "/*") {
end := strings.Index(trimmedQuery, "*/")
if end == -1 {
break
}
trimmedQuery = strings.TrimSpace(trimmedQuery[end+2:])
}
lowerQuery := strings.ToLower(trimmedQuery)
for _, keyword := range sqlStartingKeywords {
if strings.HasPrefix(lowerQuery, keyword) {
return true
}
}
return false
}
NOTE: While doing this exercise, we can cover this scenario where we have a leading comment. It is also worth considering other situations that we need to catch like Line comment (--) etc.
Tip
React with 👍 to help prioritize this issue. Please use comments to provide useful context, avoiding +1 or me too, to help us triage it. Learn more here.