# MQTT store in mysql

**URL:** <https://discourse.nodered.org/t/mqtt-store-in-mysql/47978>\
**Category:** General\
**Created:** [3 July 2021 14:02 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978 "2021-07-03T14:02:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [3 July 2021 14:02 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/1 "2021-07-03T14:02:38Z")

</div>

Hello Guys, I need help correct my code, i fail store data on mysql  
this my flow:

 ![Flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d53ccb77eec0641a4036e8d658231e864383986.png)  
this my function code:  
msg.topic = msg.payload  
var tag1 = msg.payload.P1-basic/L1\_N\_Voltage(V)  
var tag2 = msg.payloadP1-basic/L1\_L2\_Voltage(V)  
msg.topic="INSERT INTO test (L1\_N\_VOLTAGE, L1\_L2\_VOLTAGE ) VALUES ('"+ tag1 +"', '"+ tag2 +"')";  
return msg;

and this my error  
 ![error](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/2/f20a197d39b0455e96d8824a34f431399fc71519.png)

Thanks Guys

---

<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:** [3 July 2021 14:20 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/2 "2021-07-03T14:20:00Z")

</div>

> [@Apoki21](#):
>
> var tag1 = msg.payload.P1-basic/L1\_N\_Voltage(V)

If you have a minus sign in a property name (which is a bad idea, don't do it unless you have not choice) then you can't use the dot shortcut for property access. The interpreter sees the dash and thinks it is a minus operator. You will have to use  
`var tag1 = msg.payload["P1-basic"]/L1_N_Voltage(V)`

Is `L1_N_Voltage` a function? if not then what is `L1_N_Voltage(V)` supposed to mean?  
Or perhaps `P1-basic/L1_N_Voltage(V)` is the property name, in which case put it all in quotes in brackets.

Also you have a dot missing in the next line.

**[Edit]** Also, before connecting to the database node, feed it into a debug node and check the query looks correct.

---

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [3 July 2021 14:41 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/3 "2021-07-03T14:41:27Z")

</div>

> [@Colin](#):
>
> ["P1-basic"]/L1\_N\_Voltage(V)

thanks for response collin.  
P1-basic/L1\_N\_Voltage(V) is my topic mqtt publish.

I tried the code you gave it doesn't work.  
msg.topic = msg.payload  
var tag1 = msg.payload["P1-basic/L1\_N\_Voltage(V)"]  
var tag2 = msg.payload["P1-basic/L1\_L2\_Voltage(V)"]  
//var tag2 = msg.payload.P1-basic/L1\_L2\_Voltage(V)

msg.topic="INSERT INTO test (L1\_N\_VOLTAGE, L1\_L2\_VOLTAGE ) VALUES ('"+ tag1 +"', '"+ tag2 +"')";  
return msg;  
debug before conneting database:

 ![Screenshot=1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd39cbd4928de7a17db2bee4f4ac22e03761e902.png)

error code:

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

"Error: WARN\_DATA\_TRUNCATED: Data truncated for column 'L1\_N\_VOLTAGE' at row 1"

Fyi debug response data mqtt:

 ![reponse mqtt debug](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/c/cc49be4e363c45e76345fad5cf527bc3d243f30d.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 July 2021 15:40 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/4 "2021-07-03T15:40:19Z")

</div>

Your mqtt messages arrive at different times. You will need to join the mqtt messages, use a join node and join manually as key/value object using msg.topic as key, Set the count to 2.

---

<div class="post-metadata">

**Author:** ![Apoki21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/apoki21/32/44302_2.png) [@Apoki21](https://discourse.nodered.org/u/Apoki21)\
**Post date:** [3 July 2021 15:50 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/5 "2021-07-03T15:50:55Z")

</div>

Thank Guys this promblem solve

---

<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:** [17 July 2021 15:51 UTC](https://discourse.nodered.org/t/mqtt-store-in-mysql/47978/6 "2021-07-17T15:51:02Z")

</div>

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