gather_innodb_metrics fails on MariaDB 10.5.4+

[jorgemorgado]

Relevant telegraf.conf:

[[inputs.mysql]]
  gather_innodb_metrics = true

System info:

• Linux 3.10.0-1127.18.2.el7.x86_64
• MariaDB-server-10.5.4-1.el7.centos.x86_64

Steps to reproduce:

1. Enable gather InnoDB metrics in Telegraf's inputs_mysql.conf plugin (gather_innodb_metrics = true)
2. Restart Telegraf

Expected behaviour:

MySQL/MariaDB metrics successfully collected.

Actual behavior:

The following error will be reported:

E! [inputs.mysql] Error in plugin: Error 1054: Unknown column 'status' in 'where clause'

Additional info:

The problem is the following query in the mysql plugin:

innoDBMetricsQuery = `
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE status='enabled'
`

The status column has changed in recent versions of MariaDB (not sure if it's the same for MySQL). The correct query should be:

innoDBMetricsQuery = `
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE ENABLED=1;
`

[ssoroka]

Hmm. What do you think the correct behavior should be? Check to see which column the INNODB_METRICS table has, first? Or base it off of db and version numbers (which might be a bit harder to get right, but potentially faster).

[jorgemorgado]

Good question! Yes, you could simply check if the INNODB_METRICS has the ENABLED column - if yes, adapt the WHERE criteria to the new query; otherwise run the old WHERE clause. The DB version is a bit more tricky IMO, because I don't know exactly in which MariaDB version this was changed, if the same change will happen on MySQL forks and others...

But this solution still implies 2 queries on each metric collection: one to check which column exists, and a second query to collect the data, which for very intensive Telegraf collection metrics might become very inefficient (unless you can cache the results of the first query). If you can't cache the results, a better solution would be to add a new configuration directive. Something like this:

# old query
gather_innodb_metrics = true

# new query
gather_innodb_metrics_enabled = true

If you don't like my config directive naming feel free to use a different one. But you get the idea ;-) What do you think?

[ssoroka]

You could only do this check once and remember the result until Telegraf restarts. Seems fairly reasonable. This way the amortized cost is nearly zero, and there's no additional configuration to get wrong.

[jorgemorgado]

Yes, that's what I meant with caching the results. If you can easily do this in the plugin (I don't know Telegraf's plugin architecture), then I'd say it's the best solution.

[ssoroka]

Yep, definitely. It could check this on startup or first gather

[ghost]

Hi, just checking up on this one. I've found that even the latest 1.18.x telegraf is still having issues with MariaDB 10.5.x and gathering innodb metrics.

[hseagle2015]

Hello,

I'm too affected by the same issue with gathering innodb metrics. I can't really help much with development, but I can help with testing the fix across numerous Maria 10.5.x servers.

[danielmotaleite]

So after one year still no solution for this problem?
We just were forced to upgrade to 10.5 and now are locked out from those useful metrics

we now have the mariadb_dialect = true for the slave, a similar option (say mariadb_innodb_new_format = true) could be added to switch the query format, a quick and sure fix

[mjf]

Hello.

I confirm this bug for MariaDB version 10.6 as well.

E! [inputs.mysql] Error in plugin: Error 1054: Unknown column 'status' in 'where clause'

$ telegraf --version
Telegraf 1.20.4 (git: HEAD 34ad5aa1)

The gather_innodb_metrics = true seems to be the cause.

Both version 1 and version 2 metrics are affected.

The same error for both mariadb_dialect = true and mariadb_dialect = false.

$ mysqld -V
mysqld  Ver 10.6.4-MariaDB-log for Linux on x86_64 (MariaDB Server)

@ssoroka Could this be given a higher priority? Are there any chances any "hotfix" is released soon, please?

Also see MDEV-19940.

---

The field STATUS in table information_schema.innodb_metrics has been renamed to ENABLED and the type changed from variable character to integer in MariaDB 10.5.

I suggest to fix it for mariadb_dialect = true AND MariaDB version greater or equal to 10.5 combination (at least for now so that people get their tools working again).

[andreabjelogrlic]

Hi,

i tested the PR with MariaDB 10.5, 10.6, 10.7 and 10.8.
InnoDB metrics are gathered correctly and no issues were encountered.