Mysql insert error

General
1

Good afternoon,
Having an issue using the mysql-r2 node. I'm doing a single insert statement into a table but keep getting parse errors.

The only thing unusual about my sql is I used a reserved word for a column name. I wanted the naming convention to match the OWASP logging column name standards. I escaped the column names appropriately.

What I've done:

  • setup a debug node for the query and the result
  • changed the data to literal strings for debugging
  • changed column escape characters to see if mysql was in ANSI mode (fails, so it's not)
  • The query copied from the debug output works when pasted into the mysql client application. dBeaver app shows the table updated with new data

Any suggestions?

query from debug node output:

{
    "payload": [
        "2024-01-23 18:33:29",
        "tele/tasmota-2B94E3/STATE",
        "event",
        "INFO",
        "msg.payload"
    ],
    "sql": "INSERT INTO Log (`datetime`, `appid`, `event`, `level`, `description`) VALUES (?, ?, ?, ?, ?);",
    "database": "Kasa",
    "_msgid": "f4b958c81708620a"
}

response from mysql node

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?, ?, ?, ?, ?)' at line 1

Here's the code in the function node that generates the sql:

var newMsg = {};
newMsg.payload = [ mySqlTimestamp, msg.topic, 'event', 'INFO', 'msg.payload' ];
newMsg.sql = 'INSERT INTO Log (`datetime`, `appid`, `event`, `level`, `description`) VALUES (?, ?, ?, ?, ?);';
newMsg.database = 'Kasa';
return newMsg;
2

I installed the plain mysql plugin, instead of the r2 plugin. It now seems to work fine

3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.