Problem with inserting duplicate data MySQL

[fernando]

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:

This is the ouput:

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:

Thanks in advance.

[Steve-Mcl]

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.

[fernando]

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

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

[zenofmud]

Starting in release 1.0.0 flows became asynchronous by default. (see Making flows asynchronous by default : Node-RED)

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:

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?

[fernando]

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.

[fernando]

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:

[zenofmud]

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?

[fernando]

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...

[zenofmud]

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

[fernando]

Thanks man it works perfectly with delay!