Performance metricsets of Metricbeat MySQL module does not working with MySQL 5.7

[sw-jung]

• Version: 7.10.x
• Steps to Reproduce:
  • Install MySQL 5.7
  • Install Metricbeat with enabling MySQL module
  • Enable performance metricsets for the installed MySQL

Metricbeat - MySQL - performance metricsets fails with the following errors when enabling it for MySQL 5.7.

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')

For the quantile_95 field, I can find it on 8.0 Manual but not on 5.7 manual. So, it seems this metricsets is not supported MySQL 5.7 but our document says MySQL module is expected to work with all versions >=5.7.0.

The mysql MetricSets were tested with MySQL and Percona 5.7 and 8.0 and are expected to work with all versions >= 5.7.0. It is also tested with MariaDB 10.2, 10.3 and 10.4.

If the quantile_95 column is required, we need to clarify this restriction in the documentation.

[elasticmachine]

Pinging @elastic/integrations (Team:Integrations)

[aadlung]

Hi, I have the same Issue with MariaDB (10.5 and 10.6) - there the quantile_95 column just does not exist inperformance_schema.events_statements_summary_by_digest. Is there a solution do adapt the query, or should I just disable the performance metricset completely?

[Foxeronie]

Hi!

I currently have the same probleme with my MariaDB 10.6 servers. MySQL has this column (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html), but MariaDB doesn't seem to implemented that column (https://mariadb.com/kb/en/performance-schema-events_statements_summary_by_digest-table/).

Could you please make this query optional, so the other metrics can be collected from MariaDB?

Thanks and best regards,
Patrick

[kebshell]

Not sure what exact MySQL version added quantile_95 to the events_statements_summary_by_digest table, but using a comment version restriction would help here

From a MySQL 5.7 instance

mysql> 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;
ERROR 1054 (42S22): Unknown column 'quantile_95' in 'field list'

mysql> SELECT digest_text, count_star, avg_timer_wait, max_timer_wait, last_seen /*!80000 ,quantile_95 */ FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10;
Empty set (0.00 sec)

[shaunbugler]

This is still an issue, I see thew tests were changed to remove the "offending" versions of MySQL and MariaDB to make them pass, but there is no practical fix for this near 3 years after it was reported?

[kush-elastic]

Updates:
I checked this out and as mentioned by @kebshell earlier, we can stick with the same query they suggested. However, from my research, I recommend skipping 8.0.0 and going for 8.0.1, which supports quantile_95.

I'll test this and send a pull request.

Just a heads up, in MariaDB, the condition won't work for adding quantile_95 (source).

For Percona, the condition works fine. They've moved to versions starting from 8.0.12-rc, which already have support for it. Even in Percona's migrated 5.7 version, /**/ is treated as a comment, not executable MySQL code.

Thanks!

[kush-elastic]

Update:
I've tested the solution across various MariaDB and Percona versions, and it functions as described in the earlier comment. Additionally, I've submitted a pull request for the fix. Kindly review it.

[sebastien-prudhomme]

I still have the bug with MariaDB 10.5.22 and Elastic Agent 8.14.3. It should be fixed in Metricbeat 8.14.3 https://www.elastic.co/guide/en/beats/libbeat/8.14/release-notes-8.14.3.html

But I'm not sure if Elastic Agent and Metricbeat versions are aligned.

[kush-elastic]

I still have the bug with MariaDB 10.5.22 and Elastic Agent 8.14.3. It should be fixed in Metricbeat 8.14.3 https://www.elastic.co/guide/en/beats/libbeat/8.14/release-notes-8.14.3.html

But I'm not sure if Elastic Agent and Metricbeat versions are aligned.

Hey @sebastien-prudhomme,
thank you for reporting this.
I checked if the changes are available in 8.14.3 or not, but I think somehow changes are not reflected in beats 8.14.3.
I have backported the PR. so let's wait for it to release again.
I will try to investigate why Changelog is available in the release but not the changes.

[shaunbugler]

Definitely fixed for me in 8.15.0