# MySQL Data Logging Issue

**URL:** <https://discourse.nodered.org/t/mysql-data-logging-issue/42294>\
**Category:** General\
**Created:** [9 March 2021 02:44 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294 "2021-03-09T02:44:38Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 02:44 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/1 "2021-03-09T02:44:38Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/e/be2e4930b645cac3985e4e51d53c3f50197ed23a.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 07:14 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/2 "2021-03-09T07:14:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 07:24 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/3 "2021-03-09T07:24:23Z")

</div>

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;

Table Structure:  
MariaDB [Plant]\> desc tblDB1\_Test;  
+---------------+---------------+------+-----+---------+----------------+  
| Field | Type | Null | Key | Default | Extra |  
+---------------+---------------+------+-----+---------+----------------+  
| SLNo | int(11) | NO | PRI | NULL | auto\_increment |  
| Word0 | decimal(10,9) | YES | | NULL | |  
| Word2 | decimal(10,9) | YES | | NULL | |  
| Word4 | decimal(10,9) | YES | | NULL | |  
| Word6 | decimal(10,9) | YES | | NULL | |  
| Word8 | decimal(10,9) | YES | | NULL | |  
| LastUpdatedOn | datetime | YES | | NULL | |  
| LastUpdatedBy | varchar(100) | YES | | NULL | |  
+---------------+---------------+------+-----+---------+----------------+  
8 rows in set (0.009 sec)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 07:31 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/4 "2021-03-09T07:31:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 07:46 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/5 "2021-03-09T07:46:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 07:55 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/6 "2021-03-09T07:55:01Z")

</div>

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

```auto
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;

```

> [@reachrao01](#):
>
> Error: ER\_WARN\_DATA\_OUT\_OF\_RANGE: Out of range value for column 'Word0' at row 1

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.

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 08:11 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/7 "2021-03-09T08:11:45Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 08:32 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/8 "2021-03-09T08:32:03Z")

</div>

what mySQL node are you using? (share a link to it from the [flows library](https://flows.nodered.org/search?term=mysql&type=node) )

I dont know about the others but [node-red-node-mysql](https://flows.nodered.org/node/node-red-node-mysql) works with prepared statements syntax.

> [@reachrao01](#):
>
> msg.topic = "insert into tblDB1\_DBW12 values (SLNo,'DB1.WORD12',"+num+",Now(),'Admin')";

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

* * *

Another attempt...

```javascript
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;

```

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 08:37 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/9 "2021-03-09T08:37:18Z")

</div>

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

```auto
[{"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

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 08:43 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/10 "2021-03-09T08:43:17Z")

</div>

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"

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 March 2021 09:18 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/11 "2021-03-09T09:18:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 09:23 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/12 "2021-03-09T09:23:47Z")

</div>

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

You can drop back to unsafe dynamic SQL

```javascript
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;

```

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 10:03 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/13 "2021-03-09T10:03:44Z")

</div>

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](https://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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 10:07 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/14 "2021-03-09T10:07:48Z")

</div>

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

```sql
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?

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 10:08 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/15 "2021-03-09T10:08:18Z")

</div>

i tried using both.

1. 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')";

2. 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')

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 10:10 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/16 "2021-03-09T10:10:40Z")

</div>

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

i said...

```javascript
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 `

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 10:14 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/17 "2021-03-09T10:14:35Z")

</div>

> [@Steve-Mcl](#):
>
> ```auto
> insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) 
> values (1,2,3,4,5,Now(),'Admin');
> 
> ```

even i tried with this...

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 10:24 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/18 "2021-03-09T10:24:24Z")

</div>

> [@reachrao01](#):
>
> > [@Steve-Mcl](#):
> >
> > ````auto
> > > insert into tblDB1_Test (Word0,Word2,Word4,Word6,Word8,LastUpdatedOn,LastUpdatedBy) 
> > values (1,2,3,4,5,Now(),'Admin');
> > > ```
> > 
> > ````
> 
> even i tried with this...

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

I did this...

```auto
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...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/e/1e3b614447c50bf3ab6386bf30ed3e0d09dcb869.png)

```auto
[{"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"}]

```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/1/e1329665758d309ce37596d820d2e930f7ae95fa.png)

---

<div class="post-metadata">

**Author:** ![reachrao01](https://avatars.discourse-cdn.com/v4/letter/r/e495f1/32.png) [@reachrao01](https://discourse.nodered.org/u/reachrao01)\
**Post date:** [9 March 2021 10:38 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/19 "2021-03-09T10:38:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 10:39 UTC](https://discourse.nodered.org/t/mysql-data-logging-issue/42294/20 "2021-03-09T10:39:49Z")

</div>

> [@reachrao01](#):
>
> meanwhile can you please share the flow.

Already did...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/5/15628afb94ad0137cc028761d3833909f8790ad5.png)

[Next page](https://discourse.nodered.org/t/mysql-data-logging-issue/42294.md?page=2)