MySQL Global Status values that should be strings aren't

[skord]

Feature Request

Many of the SHOW GLOBAL STATUS values from the MySQL plugin should be interpreted as strings, but aren't. A quick list of the keys from a MariaDB 10.1 instance:

• Compression
• Innodb_buffer_pool_dump_status
• Innodb_buffer_pool_load_status
• Innodb_have_atomic_builtins
• Innodb_have_lz4
• Innodb_have_lzo
• Innodb_have_lzma
• Innodb_have_bzip2
• Innodb_have_snappy
• Rpl_status
• Ssl_session_cache_mode
• wsrep_cluster_state_uuid
• wsrep_cluster_status
• wsrep_connected
• wsrep_evs_repl_latency
• wsrep_evs_state
• wsrep_gcomm_uuid
• wsrep_incoming_addresses
• wsrep_local_state_comment
• wsrep_local_state_uuid
• wsrep_provider_name
• wsrep_provider_vendor
• wsrep_provider_version
• wsrep_ready

Proposal:

Ship the values as strings.

Use case:

A few use cases.

• Running a quick query against the data store to figure out if the replication cluster is in sync and which members are out of sync.
• Being able to pinpoint when a configuration change led to a performance change.

[h44z]

+1 would be usefull to have this values

[ugirirajan]

select wsrep_incoming_addresses from mysql where time > now() - 9h order by time desc limit 10;
name: mysql
time wsrep_incoming_addresses

---

1507875280000000000 0
1507875280000000000 0
1507875280000000000 0
1507875276000000000 0
1507875271000000000 0
1507875270000000000 0
1507875270000000000 0
1507875270000000000 0
1507875266000000000 0
1507875261000000000 0

Where as in cluter we get

| wsrep_incoming_addresses | 192.168.1.194:3306,192.168.1.197:3306,192.168.1.198:3306 |

DataType needs to be a string instead integer.

[gabtastic]

+1

I have found that monitoring wsrep_ready is an efficient way to monitor node statuses in a Galera cluster.

Unfortunately, telegraf always report the value as 0...

[adrianlzt]

The problem is this line hardcoding all to integer
https://github.com/influxdata/telegraf/blame/7442b5645f3d8517f28ff4bf923e28ac40f0c093/plugins/inputs/mysql/mysql.go#L860

// convert numeric values to integer
i, _ := strconv.Atoi(string(val.([]byte)))

We are also loosing important float values. For example, in a Galera cluster the value wsrep_local_send_queue_avg should be below 0.0

[skord]

@adrianlzt Hrm. That's no fun. I used parseValue in that function on a local branch but the metrics were already typed integer on the influx server so they were tossed aside. There's a few booleans and floats in the global status, not just the Galera status. It seems like the default thing to do is parse boolean, then convert to float, but not in this particular spot.

Fixing telegraf is easy, but not so much the historical data.

[skord]

There are a number of issues that are conflicting. #2910 suggests a fallback query for percona/mariadb/mainline but not having a separate plugin. #1645 suggests providing a separate plugin for percona.

MariaDB has 10 distinct data types for system variables/globals: boolean, set, bigint, varchar, bigint unsigned, int unsigned, flagset, enum, double, and int. Flagsets, enums, varchars and sets are strings. Double maps to influx's float and the rest map to influx's integer type.

A quick count on a vanilla server has this missing 172 variables that could be tracked, in addition to the Galera statuses that are missing.

A refactor of parseValue to regex match these data types appropriately will break existing installations, but provide the correct data types to outputs.

@danielnelson do you suggest a fork or a fix of the mysql plugin?

[danielnelson]

Unless the plugin config is unworkable we should try to update the current plugin. I think in this case we may need to break backwards compatibility on the output format, by changing types, because it sounds like it is just broken. We can add a release note about this.

When converting types can't we use the column's database type instead of the column name?

[skord]

@danielnelson The data type isn't exposed everywhere (only global variables) and it's a separate query to the information schema that's not available on all MySQL versions and distributions. It could be added as a feature flag, but since global statuses don't have that data the type evaluation would still have to happen for that feature.

I'll try fixing the existing plugin, run it against maria/percona/mainline, and if there are too many differences, fork then. I don't foresee that, but I'll need to run some integration testing to be sure.

[danielnelson]

Okay, let me know if you think you will need to split the plugin, I'd rather avoid this. You have probably seen it but also check #3382

[danielnelson]

Fixed in #3554