Logging and JSON backup to MySQL

Hi,

In my current project, I have several finite state machines that control a few AGVs that transport material.

As its current state, the AGVs generate a readable verbose output each time they transit from one state to the next (even if they just reload the state after some time). I filter the output by using an RBE node, so the verbose is only generated if the new state is different from the previous one.

For better debugging, I added a JSON column to the MySQL database, and was planning to pass the whole JSON object holding all the information for the AGV (configuration, job and status information, feedback from the fleet manager).

A full agv JSON object looks like this:

{"config":{"FMS":"192.168.2.241:443","IP":"192.168.2.247","name":"AGV53","robot_name":"robot_10053"},"AIC":{"fsm_state":"IDLE_NO_POS","verbose":"AGV53 is idle somewhere"},"incubed":{"availability":"REGISTERED","action":"GOTO_TARGET","actionState":"FINISHED","actionError":"NO_ERROR","goal":"14-8","executionMode":"NONE","voltage":53.12,"amperage":-2.7,"chargestage":"DISCONNECTED","batteryissue":"NO_ISSUE","errorcode":0,"linearspd":0,"angularspd":0,"xpos":14.144,"ypos":41.178,"bearing":-91.631,"cpuUsage":120.1,"coreTemp":53}}

And the function I'm currently using to write to the MySQL DB is the following:

// Prepare the SQL string for GLOBAL

var SQLstr = "INSERT INTO unit_1.agv_verbose (log_id, log_ts, agv_name, status_line)";
SQLstr += " VALUES ( UUID(), current_timestamp(), \"" + msg.agv.config.name + "\", \"" + msg.agv.AIC.verbose + "\");";

msg.topic = SQLstr;

return msg;

Now, the DB has an additional column called agv_obj that it is defined as a JSON object, but I don't know how to pass it onto the DB.

What I thought first is to just queue the JSON string, but then I need to replace all the quotes in the JSON by ("), or it will have parse errors when I pass it onto the MYSQL node, right?

Is someone aware of a more efficient way of doing it?

There are a couple of ways around this but the best solution is to use a prepared query & pass the values in the payload instead of building a string query.

Look at the readme for the mySQL node - there is a section on preparing queries.

Lastly, you might want to read this...

"How To Work with JSON in MySQL | DigitalOcean" How To Work with JSON in MySQL | DigitalOcean

Based on what you said, I tried this:

// Prepare the query

msg.payload={}
msg.payload.agvName = msg.agv.config.name;
msg.payload.agvVerbose = msg.agv.AIC.verbose;
msg.payload.agv = "\"" + 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;

The full msg object I'm passing into the DB is the following:

{"topic":"INSERT INTO unit_1.agv_verbose (log_id, log_ts, agv_name, status_line, agv_obj) VALUES (uuID(), current_timestamp(), :agvName, :agvVerbose, :agv);","payload":{"agvName":"AGV53","agvVerbose":"AGV53 is online","agv":"\"[object Object]\""},"_msgid":"fd8b6036.c8e18","agv":{"config":{"FMS":"192.168.2.241:443","IP":"192.168.2.247","name":"AGV53","robot_name":"robot_10053"},"AIC":{"fsm_state":"OFFLINE","verbose":"AGV53 is online"},"incubed":{"availability":"REGISTERED","action":"GOTO_TARGET","actionState":"FINISHED","actionError":"NO_ERROR","goal":"14-28","executionMode":"NONE","voltage":52.97,"amperage":-2.7,"chargestage":"DISCONNECTED","batteryissue":"NO_ISSUE","errorcode":0,"linearspd":0,"angularspd":0,"xpos":14.193,"ypos":41.172,"bearing":-93.193,"cpuUsage":127,"coreTemp":54}},"url":"https://192.168.2.241:443/service/robots/robot_10053","headers":{"content-length":"3210","content-type":"application/json","date":"Tue, 25 May 2021 09:42:27 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":"f1bf80cc"},"statusCode":200,"responseUrl":"https://192.168.2.241:443/service/robots/robot_10053","redirectList":[],"_event":"node:dfbc28b8.ce54d8"}

Looks like there are no error messages, and the MySQL DB from MySQL workbench looks like this:
image

So I assume the "[object Object]" entry is the JSON I passed. Now all that's left is how to retrieve it, because it looks like it's stored as a binary, and can't be accessed as a string.

Thanks for the input!

TBH, it looks like it has actually saved a string containing "[object Object]",

Try

msg.payload={}
msg.payload.agvName = msg.agv.config.name;
msg.payload.agvVerbose = msg.agv.AIC.verbose;
msg.payload.agv = 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;

↑ you should not need to turn the object into a string (and your method doesnt actually work)

So the above should work but if not, you can always "force it" ↓

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;
1 Like

The stringify trick did it, thank you very much!

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