Outputs.sql configuration for mysql db

Telegraf
, , ,
1

Hi.
I am trying to send a simple mock sin wave to mysql db using outputs.sql plugin. I get this error:

[agent] Error writing to outputs.sql: execution failed: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"mock01"("timestamp","host","wave01") VALUES(?,?,?)' at line 1

The table is made in mysql db, but it looks the Telegraf is unable to write data in the table. It looks my configuration is not the rootcause and Telegraf internal engine is unable to work with mysql commands since the table iscreated by the command I putted in the config file, but the Telegraf cannot fill the table. Any comment or idea is appreciated since there is not even 1 example of outputs.sql in this forum or internet!!

figure
figure1096×2129 192 KB

2

Hello @ali4niaki,
Here’s an example:

Can you get a basic example working?

The error you’re encountering is due to the way Telegraf’s outputs.sql plugin is forming the SQL query. MySQL uses backticks () for table and column names, while Telegraf seems to be generating a query using double quotes (“mock01”`), which is invalid in MySQL.

I believe you can:

table_template = "CREATE TABLE IF NOT EXISTS `{TABLE}` ({COLUMNS})"

Which ensures backticks (`) are used around table and column names, which is the correct quoting method for MySQL.

Does this help?

1 Like
3

Hi @Anaisdg thank you for the support.
I just tried your recommend as below:

[[outputs.sql]]
  driver = "mysql"
  data_source_name = "admin:*********@tcp(********.*******ua0czw.ca-central- 
 1.rds.amazonaws.com:3306)/vtis_3"
  timestamp_column = "timestamp"
  table_template = "CREATE TABLE IF NOT EXISTS `{TABLE}` ({COLUMNS})"
  table_exists_template = "SELECT 1 FROM `{TABLE}` LIMIT 1"
  init_sql = ""
  connection_max_idle_time = "10m"
  connection_max_lifetime = "1h"
  connection_max_idle = 5
  connection_max_open = 20
 
  [outputs.sql.convert]
    integer              = "INT"
    real                 = "DOUBLE"
    text                 = "TEXT"
    timestamp            = "TIMESTAMP"
    defaultvalue         = "TEXT"
    unsigned             = "UNSIGNED"
    bool                 = "BOOL"
    conversion_style = "unsigned_suffix"

The Error I faced:

[agent] Error writing to outputs.sql: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘“timestamp” TIMESTAMP,“host” TEXT,“wave01” DOUBLE)’ at line 1

4

@Anaisdg It seems there is no way to force output.sql to use backticks for columns in the table. How I can tell the Telegraf to use backticks for ({COLUMNS}) ?? Is there any idea? That is why I get this error:

E! [agent] Error writing to outputs.sql: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(“timestamp” TIMESTAMP,“host” TEXT,“wave01” DOUBLE)' at line 1

5

Finally I found the final solution. The thing is there is no way to force Telegraf to use backticks for ({COLUMNS}). Alternatively, I add a new parameter groups to my AWS RDS Mysql instance and added the ANSI_QUOTES to sql_mode. Now Mysql db accepts the " as well! Here is the final statement I used in my config file:

[[outputs.sql]]
  driver = "mysql"
  data_source_name = "admin:*********@tcp(********.*******ua0czw.ca-central- 
 1.rds.amazonaws.com:3306)/vtis_3"
  timestamp_column = "timestamp"
  table_template = "CREATE TABLE IF NOT EXISTS {TABLE} ({COLUMNS})"
  table_exists_template = "SELECT 1 FROM {TABLE} LIMIT 1"
  init_sql = ""
  connection_max_idle_time = "10m"
  connection_max_lifetime = "1h"
  connection_max_idle = 5
  connection_max_open = 20
 
 [outputs.sql.convert]
    integer              = "INT"
    real                 = "DOUBLE"
    text                 = "TEXT"
    timestamp            = "TIMESTAMP"
    defaultvalue         = "TEXT"
    unsigned             = "UNSIGNED"
    bool                 = "BOOL"
    conversion_style = "unsigned_suffix"