Critical/Major Bug related to MySQL Input -- "field type conflict" on ssl_verify_depth / ssl_ctx_verify_depth

[z0rx0r]

Over the weekend, without any user changing either the telegraf version, Influxdb version, or any configuration, our active thread mysql data started not reporting to influxdb. After a bit of work tracking down the issue, we tracked it back to two fields being created within out influxdb database with the same name as fields that already existed, but having the wrong fieldType. This causes Telegraf to be unable to write any metrics related to this to Influxdb, thus making our monitoring one legged, and not reporting all useful data needed to drive monitoring.

After searching and searching, there is no method to fix this as far as I can tell, either. If InfluxDB allowed us to either alter table, or delete fields, then this would be fixed with no issue, but it appears neither of these are supported within influxdb. The only method I have seemed to run across is to delete the entire mysql measurement (losing all data, for all hosts, for all time for MySQL), and then allowing the telegraf agent to recreate it when writing new metrics to the DB. This seems crazy, and there MUST be a better way. Please tell me there is a better way to recover from such a simple error (at least in my mind, but I am used to MySQL..)

This user seems to have the same issue as we have, yet there seems to be no fix presented to them: #5055

ANY HELP WOULD BE USEFUL!
Thank you!

[z0rx0r]

Error in Telegraf logs:
2019-03-04T21:42:00Z E! [outputs.influxdb]: when writing to [(REDACTED)]: received error partial write: field type conflict: input field "ssl_verify_depth" on measurement "mysql" is type float, already exists as type integer dropped=9; discarding points

Showing there are in fact multiple fields, with different fieldTypes:

show field keys from mysql
name: mysql
fieldKey fieldType
ssl_ctx_verify_depth float
ssl_ctx_verify_depth integer
ssl_verify_depth float
ssl_verify_depth integer

[z0rx0r]

SELECT "ssl_verify_depth"::integer FROM mysql limit 10
name: mysql
time ssl_verify_depth

---

1546447340000000000 -9223372036854775808
1546447350000000000 -9223372036854775808
1546447360000000000 -9223372036854775808
1546447370000000000 -9223372036854775808
1546447380000000000 -9223372036854775808
1546447390000000000 -9223372036854775808
1546447401000000000 -9223372036854775808
1546447410000000000 -9223372036854775808
1546447420000000000 -9223372036854775808
1546447430000000000 -9223372036854775808

SELECT "ssl_verify_depth"::float FROM mysql limit 10
name: mysql
time ssl_verify_depth

---

1546447340000000000 18446744073709552000
1546447350000000000 18446744073709552000
1546447360000000000 18446744073709552000
1546447370000000000 18446744073709552000
1546447380000000000 18446744073709552000
1546447390000000000 18446744073709552000
1546447401000000000 18446744073709552000
1546447410000000000 18446744073709552000
1546447420000000000 18446744073709552000
1546447430000000000 18446744073709552000

SELECT "ssl_ctx_verify_depth"::float FROM mysql limit 10
name: mysql
time ssl_ctx_verify_depth

---

1546447340000000000 18446744073709552000
1546447350000000000 18446744073709552000
1546447360000000000 18446744073709552000
1546447370000000000 18446744073709552000
1546447380000000000 18446744073709552000
1546447390000000000 18446744073709552000
1546447401000000000 18446744073709552000
1546447410000000000 18446744073709552000
1546447420000000000 18446744073709552000
1546447430000000000 18446744073709552000

SELECT "ssl_ctx_verify_depth"::integer FROM mysql limit 10
name: mysql
time ssl_ctx_verify_depth

---

1546447340000000000 -9223372036854775808
1546447350000000000 -9223372036854775808
1546447360000000000 -9223372036854775808
1546447370000000000 -9223372036854775808
1546447380000000000 -9223372036854775808
1546447390000000000 -9223372036854775808
1546447401000000000 -9223372036854775808
1546447410000000000 -9223372036854775808
1546447420000000000 -9223372036854775808
1546447430000000000 -9223372036854775808

[danielnelson]

This is related to #5055, as a workaround you can use the metric filtering options to remove the field.

[z0rx0r]

That worked, thank you.

[danielnelson]

@z0rx0r @Esity Could you, and anyone else affected by this issue, run these queries and report the results:

select * from information_schema.global_status where VARIABLE_NAME like '%ssl_ctx%';

select * from information_schema.global_variables where VARIABLE_NAME like '%version%';

[danielnelson]

My fix for this should ensure that ssl_verify_depth and ssl_ctx_verify_depth are reported as integers, never floats. This should solve the issue if the field is already an integer, but if the field is currently stored in your database as a float, you will need to either remove the measurement or use the metric filtering workaround until the next shard is active:

[[inputs.mysql]]
  fielddrop = ["ssl_ctx_verify_depth", "ssl_verify_depth"] # Workaround #5529