# Problem with inserting duplicate data MySQL

**URL:** <https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726>\
**Category:** General\
**Created:** [26 April 2021 07:25 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726 "2021-04-26T07:25:26Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 07:25 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/1 "2021-04-26T07:25:26Z")

</div>

Hi, I have a problem and I don't know how to solve it. I'm trying for day but nothing.  
I have 2 sensorsand whe they insert data to MySQL, their ID is duplicated but data is not, I mean, one sensor has ID 1 and it has different values than the other one.  
When data is save I have this:  
 ![Screenshot_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/6/b6c06b7132f92173ead6a340a85d78e500273427.png)  
This is the ouput:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/2/123d55584ebec1c7c62f52e2cada6f99eddbed41.png)  
I don't know how to save the first one with ID 1 instead of 2 and and I do not know why they save different data but the ID doesn't change.  
In database, they use this for collect data:

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

Thanks in advance.

---

<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:** [26 April 2021 07:30 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/2 "2021-04-26T07:30:42Z")

</div>

Looking at the value you write, I guess you are doing string manipulation on a JSON string? Or appending the values.

Either way, without exporting your flow it is really impossible to tell.

---

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 07:38 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/3 "2021-04-26T07:38:05Z")

</div>

It's the flow. I don't know why but this code works in another raspberry with Node-Red 0.19.5 but it doesn't work in 1.2.9

```auto
[{"id":"826dce9d.b141c","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"17534d5b.2c86b3","type":"inject","z":"826dce9d.b141c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":120,"wires":[["31a9cd49.fc5e52"]]},{"id":"31a9cd49.fc5e52","type":"function","z":"826dce9d.b141c","name":"","func":"msg.topic=\"SELECT * FROM devices;\" //where sensor_unitid=1;\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":120,"wires":[["c1173712.dc35f8"]]},{"id":"81aa9b4f.1e65b8","type":"function","z":"826dce9d.b141c","name":"global vars","func":"global.set(\"var\", 0);\n//global.set(\"ts\", 5);\nmsg.delay = (1* 0.250 * 1000*3);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":210,"y":200,"wires":[["1b1e7c92.e84c23"]]},{"id":"c7ad58ef.950038","type":"delay","z":"826dce9d.b141c","name":"","pauseType":"delayv","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":200,"y":280,"wires":[["e7b9ed5e.2b86c"]]},{"id":"8f1d3192.a684d","type":"function","z":"826dce9d.b141c","name":"sensor2obj","func":"var typeArray = msg.payload[\"sensor_type\"].split(\"/\");\nvar types = {};\nfor (var i = 0; i < typeArray.length; i++){\n types[typeArray[i]] = i;\n}\n\n\nvar obj = {\n mac: msg.payload[\"sensor_mac\"],\n name: msg.payload[\"sensor_name\"],\n unit: msg.payload[\"sensor_unit\"],\n type: msg.payload[\"sensor_type\"],\n address: msg.payload[\"sensor_address\"],\n unitid: msg.payload[\"sensor_unitid\"],\n quantity: msg.payload[\"sensor_quantity\"],\n fc: msg.payload[\"sensor_fc\"]\n}\nmsg.payload = global.set(\"types\",types);\n\nmsg.payload=global.set(\"salida\",obj);\nmsg.payload=obj;\nmsg.delay = (global.get(\"var\") * 0.250 * 1000*3);\nglobal.set(\"var\", global.get(\"var\") + 1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":690,"y":200,"wires":[["c7ad58ef.950038"]]},{"id":"e7b9ed5e.2b86c","type":"modbus-flex-getter","z":"826dce9d.b141c","name":"Sensor 1","showStatusActivities":true,"showErrors":true,"logIOActivities":false,"server":"26f62ce8.93efec","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":440,"y":280,"wires":[[],["b5a60dba.50242"]]},{"id":"b5a60dba.50242","type":"function","z":"826dce9d.b141c","name":"obj2data","func":"//---------------------------------//\n//----------Fecha actual-----------//\n//---------------------------------//\nvar today = new Date();\nvar dd = String(today.getDate()).padStart(2, '0');\nvar mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!\nvar yyyy = today.getFullYear();\nvar h = today.getHours();\nvar m = today.getMinutes();\nvar s = today.getSeconds();\n//m = Math.floor(m / global.get(\"ts\")) * global.get(\"ts\");\nif(h < 10)\n h = \"0\" + h;\nif(m < 10)\n m = \"0\" + m;\nif(s < 10)\n s = \"0\" + s;\ntoday = yyyy + \"-\" + mm + \"-\" + dd + \" \" + h + \":\" + m + \":\" + s;\n\n//---------------------------------//\n//-------------Values--------------//\n//---------------------------------//\nvar salida = global.get(\"salida\");\nvar types = global.get(\"types\");\nvar values_out = \"\";\nfor(var type in types){\n values_out = values_out + type + \":\" + (msg.values[types[type]]*0.1).toFixed(2) + \",\";\n}\n\n//---------------------------------//\n//----------Write payload----------//\n//---------------------------------//\n\nmsg.payload = \"[{\\r\\n\\t\\\"sensor_id\\\": \\\"\"+salida[\"mac\"]+\"\\\",\\r\\n\\t\\\"sensor_type\\\": \\\"\"+salida[\"type\"]+\"\\\",\\r\\n\\t\\\"sensor_unit\\\": \\\"\"+salida[\"unit\"]+\"\\\",\\r\\n\\t\\\"timestamp\\\": \\\"\"+today+\"\\\",\\r\\n\\t\\\"values\\\": \\\"{ \" + values_out.substring(0, values_out.length - 1) + \"}\\\"\\r\\n}\\r\\n\\r\\n]\\r\\n\\r\\n\\r\\n\\r\\n\\r\\n\\r\\n\";\nmsg.topic = \"INSERT INTO datos_tr(sensor_id,sensor_type,sensor_unit,timestamp,valor) VALUES ('\"+salida[\"mac\"]+\"','\"+salida[\"type\"]+\"','\"+salida[\"unit\"]+\"','\"+today+\"','\"+ values_out.substring(0, values_out.length - 1) + \"')\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":620,"y":280,"wires":[["687890a7.2730e"]]},{"id":"1b1e7c92.e84c23","type":"split","z":"826dce9d.b141c","name":"","splt":"\\n","spltType":"str","arraySplt":"1","arraySpltType":"len","stream":false,"addname":"","x":430,"y":200,"wires":[["8f1d3192.a684d"]]},{"id":"2021251e.11e5aa","type":"debug","z":"826dce9d.b141c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":390,"y":360,"wires":[]},{"id":"687890a7.2730e","type":"mysql","z":"826dce9d.b141c","mydb":"2615045b.26712c","name":"sensores","x":180,"y":360,"wires":[["2021251e.11e5aa"]]},{"id":"c1173712.dc35f8","type":"mysql","z":"826dce9d.b141c","mydb":"2615045b.26712c","name":"repositorio","x":510,"y":120,"wires":[["81aa9b4f.1e65b8"]]},{"id":"26f62ce8.93efec","type":"modbus-client","name":"Sensor","clienttype":"serial","bufferCommands":false,"stateLogEnabled":true,"queueLogEnabled":false,"tcpHost":"localhost","tcpPort":"10502","tcpType":"TPC-RTU-BUFFERED","serialPort":"/dev/ttyUSB0","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"500","unit_id":"","commandDelay":"","clientTimeout":3000,"reconnectOnTimeout":false,"reconnectTimeout":700,"parallelUnitIdsAllowed":false},{"id":"2615045b.26712c","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"","tz":"","charset":"UTF8"}]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [26 April 2021 09:10 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/4 "2021-04-26T09:10:08Z")

</div>

Starting in release 1.0.0 flows became asynchronous by default. (see [Making flows asynchronous by default : Node-RED](https://nodered.org/blog/2019/08/16/going-async))

There is a new setting in settings.js to default to the 'old' way but you should work on changing the flow. Why not add the ojb to the msg (payload.obj = obj) and then retreive it when creating the insert? (This is assuminng the modbus node doesn't destroy the incoming object)

And can you explaing this code:

```auto
msg.payload = global.set("types",types);

msg.payload=global.set("salida",obj);
msg.payload=obj;

```

Why are you setting msg.payload to three diggerent things one right after the other?

---

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 09:19 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/5 "2021-04-26T09:19:59Z")

</div>

It was the only way to make the code work, when I imported the flow to the new raspberry and executed the code, that imported empty data since it did not store it, I tried several methods and it was the one that worked for me.  
I'm not good programming but I just found that solution.  
Anyway, I will try your way.

---

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 09:54 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/6 "2021-04-26T09:54:45Z")

</div>

It's not working.  
I just put this because in another node doesn´t detect the object.  
msg.payload = global.set("types",types);  
msg.payload=global.set("salida",obj);

And this because I got this error, msg.payload=obj;  
It's not reading property of obj:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd6af37c46afb77dd3c11dae73645837eac432b8.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [26 April 2021 10:23 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/7 "2021-04-26T10:23:46Z")

</div>

With that code you first set msg.payload to types then you wipe it out by setting it to salida.

What are you trying to do with that code?

Did you read the blog post I pointed you to?

---

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 10:42 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/8 "2021-04-26T10:42:04Z")

</div>

I'm silly, I didn't know, I'm starting to use node red sry hehehe.  
Mmm yes, I did. I understand why it works in 0.19.5  
I need a way to delay the output when the object is created before it creates the second object for the second sensor...

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [26 April 2021 11:53 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/9 "2021-04-26T11:53:23Z")

</div>

You could always put a `delay` node (set to 'Rate Limit') in the flow

---

<div class="post-metadata">

**Author:** ![fernando](https://avatars.discourse-cdn.com/v4/letter/f/779978/32.png) [@fernando](https://discourse.nodered.org/u/fernando)\
**Post date:** [26 April 2021 13:01 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/10 "2021-04-26T13:01:25Z")

</div>

Thanks man it works perfectly with delay!

---

<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:** [10 May 2021 13:01 UTC](https://discourse.nodered.org/t/problem-with-inserting-duplicate-data-mysql/44726/11 "2021-05-10T13:01:31Z")

</div>

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