[inputs.mysql] Error in plugin: Error 1109: Unknown table 'user_statistics' in information_schema

luisete2106 · March 11, 2020, 11:27am

Hello
I get this error from telegraf log and I check that I have this parameter to TRUE:

## gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS

gather_user_statistics = true
What else can I check?

My system is Centos 7
Telegraf 1.13.4
Let me know !

Many thanks

Giovanni_Luisotto · March 11, 2020, 1:32pm

I’ve never used the MySQL input plugin (but I use the SQL server one), this obviously is a database related problem, and that table (information_schema.user_statistics) does not exist in your DB

Here you can find the query executed by telegraf:

github.com

influxdata/telegraf/blob/389723c08afb5e852b92defbd8413bef3b900f0c/plugins/inputs/mysql/mysql.go#L295


      
          	globalStatusQuery          = `SHOW GLOBAL STATUS`
          	globalVariablesQuery       = `SHOW GLOBAL VARIABLES`
          	slaveStatusQuery           = `SHOW SLAVE STATUS`
          	binaryLogsQuery            = `SHOW BINARY LOGS`
          	infoSchemaProcessListQuery = `
                  SELECT COALESCE(command,''),COALESCE(state,''),count(*)
                  FROM information_schema.processlist
                  WHERE ID != connection_id()
                  GROUP BY command,state
                  ORDER BY null`
          	infoSchemaUserStatisticsQuery = `
                  SELECT *
                  FROM information_schema.user_statistics`
          	infoSchemaAutoIncQuery = `
                  SELECT table_schema, table_name, column_name, auto_increment,
                    CAST(pow(2, case data_type
                      when 'tinyint'   then 7
                      when 'smallint'  then 15
                      when 'mediumint' then 23
                      when 'int'       then 31
                      when 'bigint'    then 63

I’ve looked around but I’m not able to find documentation for that table, my guess is that you are using an old version of MySql and that table does not exist.

you can exclude the query in the telegraf MySQL configuration by adding

gather_user_statistics = false

luisete2106 · March 11, 2020, 1:57pm

Hello Giovanni
Mmm you’re right. This is a plugin named userstat… and it not enable by default … As I can read this plugin create the table missing when it’s enabled… Ok
Many thanks, so I can avoid this error.

Have a nice day !

Topic		Replies	Views
Problem with telegraf config (snmp table)	8	4079	April 12, 2019
How to set Plugin SQL Input into bucket telegraf Telegraf telegraf	1	464	July 26, 2023
Telegraf > Procstat > E! Error in plugin [inputs.procstat] Telegraf telegraf , grafana	8	4730	December 13, 2017
Inputs.docker section in telegraf.conf , throwing errors, when uncommented Telegraf influxdb , telegraf , chronograf	3	2835	December 20, 2021
Minimum permissions for MySQL plugin Telegraf telegraf	3	7317	September 11, 2018

[inputs.mysql] Error in plugin: Error 1109: Unknown table 'user_statistics' in information_schema

## gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS

Related Topics