[8.14](backport #38710) [Metricbeat][MySQL] Performance metricset does not working with MySQL 5.7

[mergify[bot]]

Description

The following query is failing to run on MySQL version 5.7 because the quantile_95 is added as part of a new metric in MySQL version 8.0.1.

SELECT digest_text, count_star, avg_timer_wait, max_timer_wait, last_seen, quantile_95
    FROM performance_schema.events_statements_summary_by_digest
    ORDER BY avg_timer_wait DESC
    LIMIT 10

Updated Query to work with both version 5.7 and 8.0 with MySQL executable support. with version check, query parameters will be skipped if version 5.7 and 8.0.0 (which both do not have support for quantile_95).

SELECT digest_text, count_star, avg_timer_wait, max_timer_wait, last_seen /*!80001 ,quantile_95 */ 
    FROM performance_schema.events_statements_summary_by_digest 
    ORDER BY avg_timer_wait DESC 
    LIMIT 10;

Checklist

• [x] My code follows the style guidelines of this project
• [x] I have commented on my code, particularly in hard-to-understand areas
• [x] I have made corresponding changes to the documentation
• [x] I have made corresponding changes to the default configuration files
• [x] I have added tests that prove my fix is effective or that my feature works
• [x] I have added an entry in CHANGELOG.next.asciidoc or CHANGELOG-developer.next.asciidoc.

Author's Checklist

• [x] Check the query with the different versions from MariaDB and Percona

Related issues

• Closes #25069

Logs

ERROR [mysql.performance] query/query.go:92 error doing query {events_statements SELECT digest_text, count_star, avg_timer_wait, max_timer_wait, last_seen, quantile_95
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10
table %!s(bool=true)}%!(EXTRA *mysql.MySQLError=Error 1054: Unknown column 'quantile_95' in 'field list')

---

This is an automatic backport of pull request #38710 done by [Mergify](https://mergify.com).

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request is now in conflicts. Could you fix it? 🙏 To fixup this pull request, you can check out it locally. See documentation: https://help.github.com/articles/checking-out-pull-requests-locally/

git fetch upstream
git checkout -b mergify/bp/8.14/pr-38710 upstream/mergify/bp/8.14/pr-38710
git merge upstream/8.14
git push upstream mergify/bp/8.14/pr-38710

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[shmsr]

@kush-elastic Should we merge this?

[kush-elastic]

@shmsr We found that changes from this PR are somehow not included in the release of 8.14.3. While there is a changelog entry in release. so I added backport to solve this. Release notes: https://www.elastic.co/guide/en/beats/libbeat/current/release-notes-8.14.3.html

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[ishleenk17]

@kush-elastic : Is this backport needed ?

[mergify[bot]]

This pull request has not been merged yet. Could you please review and merge it @kush-elastic? 🙏

[kush-elastic]

I dont think we need this at the moment. i will close this PR for now.