# mySQL INSERT INTO with multiple msg.xxx failing

**URL:** <https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169>\
**Category:** General\
**Created:** [19 November 2020 01:05 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169 "2020-11-19T01:05:54Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 01:05 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/1 "2020-11-19T01:05:54Z")

</div>

Been working on this for several days and have exhausted my abilities and various suggestions via the web and these forums...so thank you in advance for any assistance!

**Basis:**

1. Have 3 MQTT inputs/message payloads
2. In node red, converting payload to number(s)
3. Since all messages had same designation of msg.payload, using a change node/move rule to make unique transformations of 3 msg.payload (s) to (msg.Temp, msg.Humidity, msg.Pressure)
4. Have PHP MySQL running on Pi, Node red connection tested successfully with simple insert statements where the insert statement contains static values, verifying successful DB insert

**Issue/Error**  
The "INSERT INTO" function is:

var newMsg = {  
topic : "INSERT INTO exterior\_climate (Temp, Pressure, Humidity, `Light`, `Voltage`) VALUES ('"+msg.Temp+"','"+msg.Pressure+"','"+msg.Humidity+"',30,20)"  
};  
return newMsg

However no records are being inserted into the DB, shown below is the output from the debug window:

 ![nr1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/4/c461ee4bacf2b7541cfbd87a1b834d23ecc0c357.png)

All inserts into the DB fail. I am confused that the debug is outputting 3 messages staggered that in sum do show the proper msg.XXX in their correct location (BTW: by design the last 2 values are static for the moment), but that the insert does not appear to be fully populated with the 3 values, then transferred to the insert.

---

<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:** [19 November 2020 07:25 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/2 "2020-11-19T07:25:21Z")

</div>

Hi. I think this might help. Can't be certain as you haven't posted your flow or a screenshot...

See [this article in the cookbook](https://cookbook.nodered.org/basic/join-streams) for an example of how to join messages into one object.

---

<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:** [19 November 2020 07:35 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/3 "2020-11-19T07:35:10Z")

</div>

Just to add further clarification, in case you didn't realise, the problem is that your function assumes that each message contains all three values whereas, as you can see, each message has only one of them defined. The solution is to use a Join node as suggested. You won't need to move the values into individual properties, the Join node will do that for you.

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 15:11 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/4 "2020-11-19T15:11:31Z")

</div>

Thank you both! I had tried the Join earlier, but missed the "Send the message after xxx message parts" so obviously the join was not properly configured. With this properly configured I am able to get the proper values into the insert statement as shown:

![nr3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/3/f3c2cd372f1f678f14209a1d250b7eda6aea6e20.png)

However, the insert statement is not actually inserting any records into the DB, everything looks OK to me. Can anyone see an issue with my Insert statement?

var newMsg = {  
topic : "INSERT INTO exterior\_climate (`Temp`, `Pressure`,`Humidity`, `Light`, `Voltage`) VALUES ("+msg.payload.Outside\_Temp+","+msg.payload.Outside\_Pressure+","+msg.payload.Outside\_Humidity+",30,20)"  
}  
return newMsg;

As previously stated, I was able to do inserts with hand entered numbers prior.

---

<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:** [19 November 2020 15:30 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/5 "2020-11-19T15:30:42Z")

</div>

Are there any messages coming out of the sql node? Does a Catch node catch any errors from the sql node?

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 17:16 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/6 "2020-11-19T17:16:21Z")

</div>

Catch node does not capture any errors. DB is connected in NR.

I changed the insert function to this, but still no success in inserting a new record:

msg.topic="INSERT INTO exterior\_climate (Temp,Pressure,Humidity,Light,Voltage) VALUES ("+msg.payload.Outside\_Temp+","+msg.payload.Outside\_Pressure+","+msg.payload.Outside\_Humidity+",30,20)";  
msg.payload=[msg.payload.Temp,msg.payload.Pressure,msg.payload.Humidity,msg.payload.Light,msg.payload.Voltage];  
return msg;

---

<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:** [19 November 2020 17:44 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/7 "2020-11-19T17:44:50Z")

</div>

Don't show us what is in the function, what matters is what is going into the sql node. Use the little Copy Value button next to the value in the debug window.

You didn't answer the question about what is coming out of the sql node.

Also are you using node-red-node-mysql (look in Manage Palette to check). Have you installed any other sql nodes?

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 18:01 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/8 "2020-11-19T18:01:00Z")

</div>

Thank you for your patience !

Here is what is coming out of the debug node that is wired after the insert statement:

[null,null,null,null,null]

![nr4](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/a/fad8dc43f1cdfe2a2726f823596de1187fb89e06.png)

Yes I am using the Node Red MY SQL Node, properly configured and connected (as shown by the green dot). During previous troubleshooting using static values in the insert command, I was able to successfully insert records into the DB.

 ![nr5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/1/415fa5f8256fd433690249ed484db17bb4c2e5c8.png)

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 18:20 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/9 "2020-11-19T18:20:11Z")

</div>

I Found and fixed some typos in the insert which now reads:

msg.topic="INSERT INTO exterior\_climate (Temp,Pressure,Humidity,Light,Voltage) VALUES ("+msg.payload.Outside\_Temp+","+msg.payload.Outside\_Pressure+","+msg.payload.Outside\_Humidity+",30,20)";  
msg.payload=[msg.payload.Outside\_Temp,msg.payload.Outside\_Pressure,msg.payload.Outside\_Humidity,20,30];  
return msg;

Now to output from the debug node shows:

 ![nr6](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/7/e73c24c77a5b03ff49c106a7b84151bc66701c28.png)

So now the values are in, but still no record being inserted into the DB

---

<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:** [19 November 2020 20:21 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/10 "2020-11-19T20:21:20Z")

</div>

Show us the debug node output from your test insert of static data. But before showing us, does it look the same as your actual data?

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [19 November 2020 20:35 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/11 "2020-11-19T20:35:33Z")

</div>

All, again...many thanks. I continued to fiddle with the insert statement and even though I am uncertain which of the many things I tried did work. The data is now inserting to SQL. I hope to get more proficient with NR so that at some time I may be of assistance to others. Kind Regards

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [28 November 2020 17:19 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/12 "2020-11-28T17:19:58Z")

</div>

Moving to the next step of selecting data from the MYSQL DB and pushing X records out to a chart. I have learned a lot and copied a lot of examples with marginal success, but have exhausted my abilities...so once again asking for assistance.

My goal is to take the outputs of the select statement which contain multiple fields/data points which use this within a template node:

SELECT Timestamp,Temp,Pressure,Humidity,Light FROM exterior\_climate ORDER BY TIMESTAMP DESC LIMIT 20

Which is wired to the MYSQL node. The output is:

 ![from select](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/d/6d13e5f4c4a6e83565f1a66c8b682f24781f7a54.png)  
With of course 20 objects from the select statement.

From there I have tried dozens of different functions, formats, and set nodes to prepare this message for output to a chart but with no real success. My goal is (perhaps lofty) to dynamically set the data series from the SQL table names as moving ahead I may add fields to the database and would like Node Red to accommodate these new fields without re-coding. To-date I have only been able to get the following results:

 ![From format](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/3/a3de3b82701ac7cfd2afcc41e8bc7e38c5b16978.png)

Using the following code within a function node:

* * *

let r = msg.payload;  
let series = [r[0]];  
let data = [r.map( v =\> ({  
"x": v.Timestamp,  
"y": v.Temp  
}))];

msg.payload = [{"series": series, "data": data}];  
return msg;

* * *

Obviously only currently getting the first series of Temp data, and unable to parse the field name out of the first object. Thank you in advance for any guidance!

---

<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:** [28 November 2020 17:48 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/13 "2020-11-28T17:48:27Z")

</div>

This thread has veered off from "mySQL INSERT INTO with multiple msg.xxx failing" to "how to send mySQL data to a chart"

It should really be a new thread (to attract the correct people)!

Anyhow - the below thread is almost identical - it should give you a head start.

> [@Data from MySQL db to Graph](https://discourse.nodered.org/t/data-from-mysql-db-to-graph/36115/15):
>
> OK, I'm back. Its the parsing, like i said I only had 5 minutes to knock that demo up (but no time to test it) So another 5 mins & its now pushing the data into the chart data in the correct format. Try this instead... [{"id":"d3d38637.1f7408","type":"inject","z":"a9fbaedc.8f9c1","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timeNRchar\":\"2020-11-18 18:25:48.906\",\"temp\":21.3,\"humi…

---

<div class="post-metadata">

**Author:** ![Tomadoggy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tomadoggy/32/34065_2.png) [@Tomadoggy](https://discourse.nodered.org/u/Tomadoggy)\
**Post date:** [28 November 2020 18:25 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/14 "2020-11-28T18:25:19Z")

</div>

Thank you so very much Steve! I will take your suggestion and move to a new thread. The solution you directed me to provides valuable progress, and I will continue to work towards dynamic series identification (if indeed that is at all possible).

Kind Regards

---

<div class="post-metadata">

**Author:** ![Paul-Reed](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/paul-reed/32/66906_2.png) [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)\
**Post date:** [28 November 2020 21:32 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/15 "2020-11-28T21:32:24Z")

</div>

New topic created at [Dynamic series identification from message, send data to Chart](https://discourse.nodered.org/t/dynamic-series-identification-from-message-send-data-to-chart/36682)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [14 December 2020 13:37 UTC](https://discourse.nodered.org/t/mysql-insert-into-with-multiple-msg-xxx-failing/36169/16 "2020-12-14T13:37:27Z")

</div>

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