MySQL Data Logging Issue

reachrao01 · 9 March 2021 02:44

Dear All,
We are trying to insert some data to MySQL - Table but data not adding. Any issue with the following configuration since data logging is not happening. Table showing no records.

Steve-Mcl · 9 March 2021 07:14

You have 6 question marks and only 5 values in the payload.

Also, you don't appear to have connected the function node output to a mySQL node.

reachrao01 · 9 March 2021 07:24

First Column is Auto increment.

msg.topic = "insert into tblDB1_Test values (SLNo,?,?,?,?,?,Now(),'Admin')";
msg.payload = [
msg.payload.Word0,
msg.payload.Word2,
msg.payload.Word4,
msg.payload.Word6,
msg.payload.Word8,
]
return msg;

Steve-Mcl · 9 March 2021 07:31

Try msg.topic = "insert into tblDB1_Test values (?,?,?,?,?,Now(),'Admin')
^ 5 question marks to match 5 items in payload

If that doesn't work, include the field names in the insert query.

reachrao01 · 9 March 2021 07:46

msg.topic = "insert into tblDB1_Test (SLNo,Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (SLNo,?,?,?,?,?,Now(),'Admin')";
msg.payload = [
msg.payload.Word0,
msg.payload.Word2,
msg.payload.Word4,
msg.payload.Word6,
msg.payload.Word8,
]
return msg;

Error:
Error: ER_WARN_DATA_OUT_OF_RANGE: Out of range value for column 'Word0' at row 1

Steve-Mcl · 9 March 2021 07:55

As i have said twice now, dont include the auto inc column.

msg.topic = "insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (?,?,?,?,?,Now(),'Admin')";
msg.payload = [
msg.payload.Word0,
msg.payload.Word2,
msg.payload.Word4,
msg.payload.Word6,
msg.payload.Word8,
]
return msg;

When you post an error like this - it is a good idea to include the data going in to the mySQL node so we can see what the value is & have an idea why it might be out of range.

reachrao01 · 9 March 2021 08:11

Added but getting the following error. looks like Issue is not with Auto increment number but its related to PARSING.

error: 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 '?,?,?,?,?,Now(),'Admin')' at line 1

i have another table which has auto increment column. While data inserting, we use SLNO and it is working fine. (Pls refer the below query)

var num = msg.payload
msg.topic = "insert into tblDB1_DBW12 values (SLNo,'DB1.WORD12',"+num+",Now(),'Admin')";
return msg;

Steve-Mcl · 9 March 2021 08:32

what mySQL node are you using? (share a link to it from the flows library )

I dont know about the others but node-red-node-mysql works with prepared statements syntax.

FYI: Building SQL dynamically like this ↑ opens you up to SQL injection

Another attempt...

msg.topic = "insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (?,?,?,?,?,Now(),?)";
msg.payload = [
msg.payload.Word0,
msg.payload.Word2,
msg.payload.Word4,
msg.payload.Word6,
msg.payload.Word8,
'Admin'
]
return msg;

reachrao01 · 9 March 2021 08:37

I am using Node-Red-Node-Mysql node
Exporting the flow for your reference.

[{"id":"47169a4b.e85434","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"71460f4e.15746","type":"function","z":"47169a4b.e85434","name":"","func":"//var SLNo = 1;\n//msg.topic = \"insert into Test values (?,?,?,?,?,Now(),'Admin')\";\nmsg.topic = \"insert into Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (?,?,?,?,?,Now(),'Admin')\";\nmsg.payload = [\nmsg.payload.Word0,\nmsg.payload.Word2,\nmsg.payload.Word4,\nmsg.payload.Word6,\nmsg.payload.Word8,\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":240,"y":60,"wires":[["43f32050.3b7a2"]]},{"id":"a5452b26.8604f8","type":"debug","z":"47169a4b.e85434","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":230,"y":120,"wires":[]},{"id":"3bff03e0.bc9c2c","type":"debug","z":"47169a4b.e85434","name":"","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":590,"y":60,"wires":[]},{"id":"71a905de.b51bdc","type":"s7 in","z":"47169a4b.e85434","endpoint":"d6429ddd.49b4f","mode":"all","variable":"","diff":true,"name":"PLC Data","x":80,"y":100,"wires":[["a5452b26.8604f8","71460f4e.15746"]]},{"id":"43f32050.3b7a2","type":"mysql","z":"47169a4b.e85434","mydb":"9721b43b.2be718","name":"","x":430,"y":60,"wires":[["3bff03e0.bc9c2c"]]},{"id":"d6429ddd.49b4f","type":"s7 endpoint","transport":"iso-on-tcp","address":"192.168.43.2","port":"102","rack":"0","slot":"1","localtsaphi":"01","localtsaplo":"00","remotetsaphi":"01","remotetsaplo":"00","connmode":"rack-slot","adapter":"","busaddr":"2","cycletime":"1000","timeout":"2000","name":"s7 in","vartable":[{"addr":"DB1,WORD0","name":"Word0"},{"addr":"DB1,WORD2","name":"Word2"},{"addr":"DB1,WORD4","name":"Word4"},{"addr":"DB1,WORD6","name":"Word6"},{"addr":"DB1,WORD8","name":"Word8"}]},{"id":"9721b43b.2be718","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"Plant","tz":"","charset":"UTF8"}]

ADMIN Node. Wrapped your flow in a code block to fix forum formatting and permit it to be imported into node-red

reachrao01 · 9 March 2021 08:43

sorry no Luck.

"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 '?,?,?,?,?,Now(),?)' at line 1"

Colin · 9 March 2021 09:18

Feed what is going into the sql node into a Debug node set to show Complete message and then show both what is going into the sql node and what is coming out.

Steve-Mcl · 9 March 2021 09:23

well, I am at a loss. Perhaps you are using an old version?

You can drop back to unsafe dynamic SQL

var data = msg.payload;
msg.topic = `insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) 
                values (${data.Word0},${data.Word2},${data.Word4},${data.Word6},${data.Word8},Now(),'Admin')`;
node.warn(msg.topic); //for debugging - remove me when working!
msg.payload = null;
return msg;

reachrao01 · 9 March 2021 10:03

i am using latest version of MySQL.
MariaDB [(none)]> SELECT VERSION();
+---------------------------+
| VERSION() |
+---------------------------+
| 10.3.27-MariaDB-0+deb10u1 |
+---------------------------+

Following are the result.

WARN:
//msg.topic = "insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values ({data.Word0},{data.Word2},{data.Word4},{data.Word6},${data.Word8},Now(),'Admin')";

ERROR:
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 '{data.Word0},{data.Word2},{data.Word4},{data.Word6},{data.Word8},Now(),'A...' at line 1

Steve-Mcl · 9 March 2021 10:07

you did something wrong. that should get changed to ...

insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) 
                values (1,2,3,4,5,Now(),'Admin');

what nodejs version are you using?

reachrao01 · 9 March 2021 10:08

i tried using both.

msg.topic = "insert into tblDB1_Test (SLNo,Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (SLNo,{data.Word0},{data.Word2},{data.Word4},{data.Word6},${data.Word8},Now(),'Admin')";
msg.topic = "insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values ({data.Word0},{data.Word2},{data.Word4},{data.Word6},${data.Word8},Now(),'Admin')";

Results are as follows.

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 '{data.Word0},{data.Word2},{data.Word4},{data.Word6},{data.Word8},Now(),'A...' at line 1

WARN:
insert into tblDB1_Test (SLNo,Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) values (SLNo,{data.Word0},{data.Word2},{data.Word4},{data.Word6},${data.Word8},Now(),'Admin')

Steve-Mcl · 9 March 2021 10:10

Thats not what I told you to do. you have changed the ` backticks to double quotes.

i said...

var data = msg.payload;
msg.topic = `insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) 
                values (${data.Word0},${data.Word2},${data.Word4},${data.Word6},${data.Word8},Now(),'Admin')`;
node.warn(msg.topic); //for debugging - remove me when working!
msg.payload = null;
return msg;

NOT msg.topic = "insert into tblDB1_Test

See the difference?

I used msg.topic = `insert into tblDB1_Test

reachrao01 · 9 March 2021 10:14

even i tried with this...

Steve-Mcl · 9 March 2021 10:24

reachrao01:

even i tried with this...

Well it works for me. It is a valid SQL statement

I did this...

create database Plant;
use Plant;

CREATE TABLE tblDB1_Test (
    SLNo int NOT NULL AUTO_INCREMENT,
    Word0 decimal(10,9),
    Word2 decimal(10,9),
    Word4 decimal(10,9),
    Word6 decimal(10,9),
    Word8 decimal(10,9),
    LastUpdatedOn datetime,
    LastUpdatedBy varchar(100),
    PRIMARY KEY (SLNo)
);

then used this...

[{"id":"cf9f8329.ccd46","type":"function","z":"47169a4b.e85434","name":"","func":"\nvar data = msg.payload;\nmsg.topic = `insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) \n                values (${data.Word0},${data.Word2},${data.Word4},${data.Word6},${data.Word8},Now(),'Admin')`;\nnode.warn(msg.topic);\nmsg.payload = null;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":652,"y":160,"wires":[["f2946f08.114f7","7324a8c6.66c808"]]},{"id":"3c4a55e4.1e54aa","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Word0\":1,\"Word2\":2,\"Word4\":3,\"Word6\":4,\"Word8\":5}","payloadType":"json","x":482,"y":160,"wires":[["cf9f8329.ccd46"]]},{"id":"7324a8c6.66c808","type":"mysql","z":"47169a4b.e85434","mydb":"9721b43b.2be718","name":"","x":818,"y":160,"wires":[[]]},{"id":"f2946f08.114f7","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":658,"y":208,"wires":[]},{"id":"9721b43b.2be718","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"Plant","tz":"","charset":"UTF8"}]

reachrao01 · 9 March 2021 10:38

Will try this. Tried most of the stuff...
meanwhile can you please share the flow.

Steve-Mcl · 9 March 2021 10:39

Already did...

Topic		Replies	Views
Inserting a record in MySQL from a payload: Null values are being inserted in fields General	2	469	26 July 2021
Inserting to mySql database General	3	2175	5 June 2020
Newbie - msg.payload into SQL General	7	2273	13 December 2019
MySQL (Mariadb) INSERT returns msg.payload = "[object Object]" General database	6	334	5 January 2023
Insert Into MySQL Database General	16	13482	3 October 2020

MySQL Data Logging Issue

msg.topic = "insert into tblDB1_Test values (SLNo,?,?,?,?,?,Now(),'Admin')"; msg.payload = [ msg.payload.Word0, msg.payload.Word2, msg.payload.Word4, msg.payload.Word6, msg.payload.Word8, ] return msg;

Related topics

msg.topic = "insert into tblDB1_Test values (SLNo,?,?,?,?,?,Now(),'Admin')";
msg.payload = [
msg.payload.Word0,
msg.payload.Word2,
msg.payload.Word4,
msg.payload.Word6,
msg.payload.Word8,
]
return msg;