mySQL parse error after update

General
1

Hello all,

I'm using the mySQL node to generate a DB log for our mobile robots. I used prepared queries with named parameters, and it was running fine before the last update.

After the update, I get the following error:

29/9/2021, 10:57:01node: Data Trackingmsg : error

"Error: ER_PARSE_ERROR: 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 ':agvName, :agvVerbose, :agv)' at line 1"

This is the message I use to generate the SQL command:

29/9/2021, 10:57:00node: 31dfae6f.dde8e2
reload : msg : Object
object
topic: "reload"
payload: object
trigger: object
_msgid: "edd01410.7007f8"
agv: object
config: object
FMS: "192.168.2.241:443"
IP: "192.168.2.247"
name: "AGV53"
robot_name: "robot_10053"
AIC: object
fsm_state: "IDLE_NO_POS"
battery_status: "battery topping"
job_type: ""
job_id: ""
job_port: ""
nav_target: ""
nav_error: false
nav_retries: 0
finepos_error: false
finepos_retries: 0
turn_target_angle: 0
turn_offset_angle: 0
turn_error: false
turn_retries: 0
turn_dir: 0
dock_dist: ""
dock_docked: false
loadState: "unknown"
verbose: "AGV53 is idle somewhere"
plan: object
set: false
commited: false
incubed: object
availability: "REGISTERED"
action: "GOTO_TARGET"
actionState: "FINISHED"
actionError: "NO_ERROR"
goal: "24-145"
executionMode: "NONE"
voltage: 54.2
amperage: -2.4
chargestage: "DISCONNECTED"
batteryissue: "NO_ISSUE"
errorcode: 0
linearspd: 0
angularspd: 0
xpos: 28.748
ypos: 25.492
bearing: -178.611
cpuUsage: 120.2
coreTemp: 50
url: "https://192.168.2.241:443/service/robots/robot_10053"
headers: object
content-length: "3221"
content-type: "application/json"
date: "Wed, 29 Sep 2021 08:56:57 GMT"
cache-control: "no-cache, no-store, max-age=0, must-revalidate"
pragma: "no-cache"
expires: "0"
x-content-type-options: "nosniff"
x-frame-options: "DENY"
x-xss-protection: "1 ; mode=block"
referrer-policy: "no-referrer"
connection: "close"
x-node-red-request-node: "2a0768db"
statusCode: 200
responseUrl: "https://192.168.2.241:443/service/robots/robot_10053"
redirectList: array[0]
_event: "node:378178fe.75e538"

And this is the code I use to prepare the SQL command:

// Prepare the query

msg.payload={};
msg.payload.agvName = msg.agv.config.name;
msg.payload.agvVerbose = msg.agv.AIC.verbose;
msg.payload.agv = JSON.stringify(msg.agv);
msg.topic="INSERT INTO unit_1.agv_verbose (log_id, log_ts, agv_name, status_line, agv_obj) VALUES (uuID(), current_timestamp(), :agvName, :agvVerbose, :agv);";

return msg;

I'm quite clueless why now it does not work. In the last update they changed the default charset, so I made sure the charset is the same on the node configuration and the DB, but I have no idea why this happens.

Any ideas?

2

Okay, self-solution here:

It seems the problems are the parameters. I tried without named variables, by using this other code:

// Prepare the query

msg.payload = [ msg.agv.config.name, msg.agv.AIC.verbose, JSON.stringify(msg.agv)];
msg.topic="INSERT INTO unit_1.agv_verbose (log_id, log_ts, agv_name, status_line, agv_obj) VALUES (uuID(), current_timestamp(), ?, ?, ?);";

return msg;

And for some reason, now it's working properly. If you have the same problem, I suggest you dodge the names parameters and use this other method.

3

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