MySQL EXPLAIN does not display when querysource=perfschema + query type is SELECT
Description
How to test
How to document
Attachments
Smart Checklist
Activity
Lalit Choudhary October 19, 2021 at 6:56 AM
Thank you for the update, Francisco.
Closing this case.
Francisco Bordenave October 18, 2021 at 7:18 PM
Lalit, I was told that after upgrading the PS version things started to work correctly, seems this was caused by some deployment issue and not by PMM itself, I'll close this ticket and re open if something happens again.
Francisco Bordenave October 18, 2021 at 4:30 PM
Lalit,
This the version used:
# rpm -qa | grep PerconaPercona-Server-shared-compat-57-5.7.35-38.1.el7.x86_64Percona-Server-client-57-5.7.35-38.1.el7.x86_64Percona-Server-shared-57-5.7.35-38.1.el7.x86_64Percona-Server-server-57-5.7.35-38.1.el7.x86_64
I though that comments can be the reason because this happens only with SELECT type of queries which are the only ones getting these comments in between, all other queries are working correctly. Any hint to try? if --query-source=slowlog things works ok.
Lalit Choudhary October 18, 2021 at 9:34 AM
Hi
I tested same with PMM 2.22.0 / PS 5.7.35 and i can see explain for the SELECT query with comment.
What version of MySQL you are using ? is it a MariaDB?
Roma Novikov October 18, 2021 at 7:32 AM
Thanks !
can you check this and help us with some easy to reproduce cases. Thanks
When we enable querysource=perfschema + query type is SELECT we see that there are Examples & metrics about the query, but the EXPLAIN is not rendered. It does not error, but says "EXPLAIN not available".
We tested with querysource=slowlog and it seems to properly generate EXPLAIN.
We suspect that a SQL comment is breaking the parser:
SELECT /* YWhgmy9UHVPbQ5sDi3gt6gAAAAw */ `feedback_config_pk` AS feedbackConfigPK, `program_fk` AS ….The environment is able to ATTEMPT to generate EXPLAIN on UPDATE/INSERT commands, but throws an error because we restricted the privileges of hte PMM user to SELECT only.
SQL Comments in this environment are added to SELECT queries only the pattern is that other types of queries (i.e. INSERT/UPDATE/DELETE) are not including comments in the format /...comment.../ type.
So it looks like the combination of query_source=performance_schema and comments in queries may be causing the issue.
We enabled debug: true in pmm-agent.yaml
Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: DEBU[2021-10-14T16:55:06.012+00:00] Received message (766 bytes):Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: id: 41Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: start_action: <Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: action_id: "/action_id/69ce389f-44ae-4094-afac-fe64ba4525a5"Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: mysql_explain_params: <Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: dsn: "pmm:CPyP&M?nsCX9@tcp(127.0.0.1:13306)/shard_3373?clientFoundRows=true&parseTime=true&timeout=1s"Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: query: "SELECT /* YWhgmy9UHVPbQ5sDi3gt6gAAAAw */ `feedback_config_pk` AS feedbackConfigPK, `program_fk` AS programFK, `enabled` AS enabled, `account_id` AS accountId, `campaign_string` AS campaignString, `campaign_id` AS campaignId, `cap_permission_groups` AS capPermOct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: output_format: MYSQL_EXPLAIN_OUTPUT_FORMAT_JSONOct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: tls_files: <Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: template_left_delim: "{{"Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: template_right_delim: ""}}Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: >Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: >Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: timeout: <Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: seconds: 10Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: >Oct 14 16:55:06 uk1-prd-mysql-b-1 pmm-agent[7531]: >Looking in the PG database for this action_id we see the following:
/action_id/69ce389f-44ae-4094-afac-fe64ba4525a5 | /agent_id/7551e802-8e4b-44ba-b1d2-2af7e67ec098 | t | | 0001-01-01 00:00:00 | 2021-10-14 16:55:06.11505 | \x7b2271756572795f626c6f636b223a7b22636f73745f696e666f223a7b2271756572795f636f7374223a22312e3230227d2c226f72646572696e675f6f7065726174696f6e223a7b227461626c65223a7b226163636573735f74797065223a22726566222c22636f73745f696e666f223a7b22646174615f726561645f7065725f6a6f696e223a22314b222c226576616c5f636f7374223a22302e3230222c227072656669785f636f7374223a22312e3230222c22726561645f636f7374223a22312e3030227d2c2266696c7465726564223a223130302e3030222c22696e6465785f636f6e646974696f6e223a22286073686172645f33333733602e60666565646261636b5f636f6e666967602e6070726f6772616d5f666b60205c75303033633d5c75303033652027333337332729222c226b6579223a2270726f6772616d5f666b222c226b65795f6c656e677468223a2234222c22706f737369626c655f6b657973223a5b2270726f6772616d5f666b225d2c22726566223a5b22636f6e7374225d2c22726f77735f6578616d696e65645f7065725f7363616e223a312c22726f77735f70726f64756365645f7065725f6a6f696e223a312c227461626c655f6e616d65223a22666565646261636b5f636f6e666967222c22757365645f636f6c756d6e73223a5b22666565646261636b5f636f6e6669675f706b222c2270726f6772616d5f666b222c22656e61626c6564222c226163636f756e745f6964222c2263616d706169676e5f737472696e67222c227375727665795f74616b655f7065726d697373696f6e5f67726f757073222c227375727665795f61646d696e5f7065726d697373696f6e5f67726f757073222c227265636f72645f646174655f63726561746564222c227265636f72645f646174655f6c6173745f6d6f646966696564222c226361705f7065726d697373696f6e5f67726f757073222c2263616d706169676e5f6964225d2c22757365645f6b65795f7061727473223a5b2270726f6772616d5f666b225d7d2c227573696e675f66696c65736f7274223a747275657d2c2273656c6563745f6964223a317d2c227761726e696e6773223a5b7b22436f6465223a313030332c224c6576656c223a224e6f7465222c224d657373616765223a222f2a2073656c6563742331202a2f2073656c656374206073686172645f33333733602e60666565646261636b5f636f6e666967602e60666565646261636b5f636f6e6669675f706b602041532060666565646261636b436f6e666967504b602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e6070726f6772616d5f666b60204153206070726f6772616d464b602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e60656e61626c6564602041532060656e61626c6564602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e606163636f756e745f69646020415320606163636f756e744964602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e6063616d706169676e5f737472696e6760204153206063616d706169676e537472696e67602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e6063616d706169676e5f696460204153206063616d706169676e4964602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e606361705f7065726d697373696f6e5f67726f7570736020415320606361705065726d697373696f6e47726f757073602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e607375727665795f74616b655f7065726d697373696f6e5f67726f75707360204153206073757276657954616b655065726d697373696f6e47726f757073602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e607375727665795f61646d696e5f7065726d697373696f6e5f67726f75707360204153206073757276657941646d696e5065726d697373696f6e47726f757073602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e607265636f72645f646174655f637265617465646020415320607265636f72644461746543726561746564602c6073686172645f33333733602e60666565646261636b5f636f6e666967602e607265636f72645f646174655f6c6173745f6d6f6469666965646020415320607265636f7264446174654c6173744d6f646966696564602066726f6d206073686172645f33333733602e60666565646261636b5f636f6e6669676020776865726520286073686172645f33333733602e60666565646261636b5f636f6e666967602e6070726f6772616d5f666b60203d2027333337332729206f72646572206279206073686172645f33333733602e60666565646261636b5f636f6e666967602e607265636f72645f646174655f6c6173745f6d6f646966696564602064657363227d5d7d(1 row)