Percona 8.0 User statistics not working due to changed type

[fxedel]

Relevant telegraf.conf:

[[inputs.mysql]]
  # ...
  gather_user_statistics = true

System info:

Telegraf 1.14.0 (git: HEAD fefd7ff)
Debian GNU/Linux 10 (buster)

Steps to reproduce:

1. Run th plugin
2. You get following error: E! [inputs.mysql] Error in plugin: sql: Scan error on column index 3, name "CONNECTED_TIME": converting driver.Value type []uint8 ("791.4448604999997") to a int64: invalid syntax

Expected behavior:

Telegraf should find out the correct type.

Actual behavior:

Telegraf throws an error.

Additional info:

Since 8.0, Percona uses double type for some variables in user statistics:

mysql> DESCRIBE INFORMATION_SCHEMA.CLIENT_STATISTICS;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| CLIENT                 | varchar(64)           | NO   |     |         |       |
| TOTAL_CONNECTIONS      | bigint(21) unsigned   | NO   |     |         |       |
| CONCURRENT_CONNECTIONS | bigint(21) unsigned   | NO   |     |         |       |
| CONNECTED_TIME         | double(21,0) unsigned | NO   |     |         |       |
| BUSY_TIME              | double(21,0) unsigned | NO   |     |         |       |
| CPU_TIME               | double(21,0) unsigned | NO   |     |         |       |
| BYTES_RECEIVED         | bigint(21) unsigned   | NO   |     |         |       |
| BYTES_SENT             | bigint(21) unsigned   | NO   |     |         |       |
| BINLOG_BYTES_WRITTEN   | bigint(21) unsigned   | NO   |     |         |       |
| ROWS_FETCHED           | bigint(21) unsigned   | NO   |     |         |       |
| ROWS_UPDATED           | bigint(21) unsigned   | NO   |     |         |       |
| TABLE_ROWS_READ        | bigint(21) unsigned   | NO   |     |         |       |
| SELECT_COMMANDS        | bigint(21) unsigned   | NO   |     |         |       |
| UPDATE_COMMANDS        | bigint(21) unsigned   | NO   |     |         |       |
| OTHER_COMMANDS         | bigint(21) unsigned   | NO   |     |         |       |
| COMMIT_TRANSACTIONS    | bigint(21) unsigned   | NO   |     |         |       |
| ROLLBACK_TRANSACTIONS  | bigint(21) unsigned   | NO   |     |         |       |
| DENIED_CONNECTIONS     | bigint(21) unsigned   | NO   |     |         |       |
| LOST_CONNECTIONS       | bigint(21) unsigned   | NO   |     |         |       |
| ACCESS_DENIED          | bigint(21) unsigned   | NO   |     |         |       |
| EMPTY_QUERIES          | bigint(21) unsigned   | NO   |     |         |       |
| TOTAL_SSL_CONNECTIONS  | bigint(21) unsigned   | NO   |     |         |       |
+------------------------+-----------------------+------+-----+---------+-------+

For comparison, with Percona 5.7:

mysql> DESCRIBE INFORMATION_SCHEMA.CLIENT_STATISTICS;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| CLIENT                 | varchar(64)         | NO   |     |         |       |
| TOTAL_CONNECTIONS      | bigint(21) unsigned | NO   |     | 0       |       |
| CONCURRENT_CONNECTIONS | bigint(21) unsigned | NO   |     | 0       |       |
| CONNECTED_TIME         | bigint(21) unsigned | NO   |     | 0       |       |
| BUSY_TIME              | bigint(21) unsigned | NO   |     | 0       |       |
| CPU_TIME               | bigint(21) unsigned | NO   |     | 0       |       |
| BYTES_RECEIVED         | bigint(21) unsigned | NO   |     | 0       |       |
| BYTES_SENT             | bigint(21) unsigned | NO   |     | 0       |       |
| BINLOG_BYTES_WRITTEN   | bigint(21) unsigned | NO   |     | 0       |       |
| ROWS_FETCHED           | bigint(21) unsigned | NO   |     | 0       |       |
| ROWS_UPDATED           | bigint(21) unsigned | NO   |     | 0       |       |
| TABLE_ROWS_READ        | bigint(21) unsigned | NO   |     | 0       |       |
| SELECT_COMMANDS        | bigint(21) unsigned | NO   |     | 0       |       |
| UPDATE_COMMANDS        | bigint(21) unsigned | NO   |     | 0       |       |
| OTHER_COMMANDS         | bigint(21) unsigned | NO   |     | 0       |       |
| COMMIT_TRANSACTIONS    | bigint(21) unsigned | NO   |     | 0       |       |
| ROLLBACK_TRANSACTIONS  | bigint(21) unsigned | NO   |     | 0       |       |
| DENIED_CONNECTIONS     | bigint(21) unsigned | NO   |     | 0       |       |
| LOST_CONNECTIONS       | bigint(21) unsigned | NO   |     | 0       |       |
| ACCESS_DENIED          | bigint(21) unsigned | NO   |     | 0       |       |
| EMPTY_QUERIES          | bigint(21) unsigned | NO   |     | 0       |       |
| TOTAL_SSL_CONNECTIONS  | bigint(21) unsigned | NO   |     | 0       |       |
+------------------------+---------------------+------+-----+---------+-------+

[powersj]

I believe this was fixed in #9966, but if not please do come back and let us know. Thanks for the issue and the PR that you put up!

[fxedel]

Nope, the issue won't be fixed with #9966. The problem is that getColSlice will return a reference to an int64 for totalConnections, regardless of the Percona version. The plugin then tries to read a decimal value into an int, which fails.

[powersj]

The problem is that getColSlice will return a reference to an int64 for totalConnections, regardless of the Percona version.

For totalConnections that is still correct as it is still a bigint in 5.7 and 8.0 per your original post, right?

The issue you are calling out is that connectedTime, busyTime, and cpuTime are now doubles in Percona 8.0?

[fxedel]

Yeah, I meant connectedTime, busyTime, and cpuTime.

[powersj]

ok thanks for letting us know that the previous PR is unrelated. Did you have a fix in mind? It seems the getColSlice logic may need to learn more about what version of percona we are dealing with.

[fxedel]

I have several possibilities in my mind.

One could update getColSlice to depend on the SQL variant and version, which probably is a better indicator than the number of columns. This would require recognizing SQL variant and version beforehand, and we still needed to hardcode the differences between variants and versions.

We could also use the DESCRIBE command I posted above to determine the types, or use rows.ColumnTypes(), which probably gives the same types (and is easier to use). A simple mapping (like bigint(21) unsigned -> uint64) would spare us the need for hardcoding column names. However, I'm not sure how standard the different SQL type names are.

We don't need type guessing with the ParseValue function like for global status, as the columns are typed and not given as strings.

[odoucet]

Hello,
any update ? the bug is still present in latest telegraf version (1.24.2 (git: HEAD@9550e7a5))

[fferraro87]

yes we have still that problem, i follow this bug

[odoucet]

Can we cast double() to uint64 ? I prefer losing precision a bit than having a big error...

[srebhan]

@odoucet, @fxedel, @fferraro87 and others interested in this issue, please test the binary in #15012 available as-soon-as CI finished the tests successfully! Let me know if the PR fixes the issue!