MySQL node has queue?

General
1

Hello

I am using node-red-node-mysql 2.0.0 node to insert data to MySQL database. I am trying to insert one row every 5 second. The problem is, that i have found out, that i have some inserted data which have only 1 second difference in insertion timestamp and these inserts are duplicates.
I am wondering if the node has some queue inside, so when it is not possible to make insert, it stores the insert statement and tries to insert it once more or more Times.
I have used the output from This note to note down, that the insert was sucessfull. If i do not Get any respond i am sending the same data in 5 second interval.
So I can not understand how else could the entries only 1 second apart happen...
Thanks for help.

2

I have a coupe of thoughts. Neither one directly answers your question though.

  1. Node is asynchronous. So IMHO, there is really no way to anticipate exactly when a record will get through NR and when it will actually get written by the MYSQL engine.

  2. If your records have a non-duplicating key you way wish to look at the SQL "ON DUPLICATE KEY". I use this so that I do not get duplicate records when multiple updates occur.

3

Thanks for your reply, @jmorris644 . In my case, the problem is not when it will be processed, but why it is processed multiple times.

I made a quick try out - I have connected MySQL node to external database, where the Node had access at first - i tried to make one Insert. It was sucessfull. Then I disabled login on the external database, tried to make two inserts from Node Red - the node showed error. Which is OK. When I then enabled access to external database, the node automatically connected to remote side, but did not send the two inserts which were not sucessfully sent. So I assume, it does not have any queue.

But I still do not have the answer - how it could happen, that I see 3 times the same data in my external DB, with one second difference in the timestamp, but I was sending the data from Node red every 5 seconds. What should happen, that the node will send 3 times the same Insert statement... It does not make sense to me...

4

I would have to see you flow to help further.

5

Hi

From what I see in the code, there is no queue. The message is sent directly to the MySQL connection.

Share your flow if you can.

HTH

6

would help to see this part of your flow...

7

This is my flow:

[
    {
        "id": "fadd60c5f197b58f",
        "type": "function",
        "z": "bc82c9919021ae85",
        "name": "OK=0",
        "func": "msg.topic = \"SELECT rowid, * FROM LocalData WHERE sent = \\\"0\\\" LIMIT 1\";\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 450,
        "y": 200,
        "wires": [
            [
                "9fa2a2d5ff688fce"
            ]
        ]
    },
    {
        "id": "0ce7bbef78982ed1",
        "type": "inject",
        "z": "bc82c9919021ae85",
        "name": "Every 5 sec.",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "5",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 280,
        "y": 200,
        "wires": [
            [
                "fadd60c5f197b58f"
            ]
        ]
    },
    {
        "id": "a86c5cc835767ef4",
        "type": "split",
        "z": "bc82c9919021ae85",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 730,
        "y": 200,
        "wires": [
            [
                "83f1457149d09b4e"
            ]
        ]
    },
    {
        "id": "83f1457149d09b4e",
        "type": "function",
        "z": "bc82c9919021ae85",
        "name": "Write CloudDB",
        "func": "msg.idLoc = msg.payload.rowid;\n\nmsg.topic = \"INSERT INTO Remote_data( date,time,product,timestamp) VALUES( \" +\n    \"\\\"\" + msg.payload.datum + \"\\\", \" +\n    \"\\\"\" + msg.payload.cas + \"\\\", \" +\n    \"\\\"\" + msg.payload.produkt + \"\\\", \" +\n    \"now()\" + \" );\";\n     \n\nreturn msg;\n\n",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 880,
        "y": 200,
        "wires": [
            [
                "02ef89f19b973001"
            ]
        ]
    },
    {
        "id": "8bb3d5fe03e3ddbe",
        "type": "function",
        "z": "bc82c9919021ae85",
        "name": "OK=idCloud",
        "func": "msg.idCloud = msg.parts.id\n\nmsg.topic = \"UPDATE LocalData SET sent = \\\"\" + msg.idCloud  +  \"\\\" WHERE rowid =  \" + msg.idLoc + \";\"\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1230,
        "y": 200,
        "wires": [
            [
                "668c50affb2cfc29"
            ]
        ]
    },
    {
        "id": "9fa2a2d5ff688fce",
        "type": "sqlite",
        "z": "bc82c9919021ae85",
        "mydb": "3e46bfdc4b6df798",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "LocDb",
        "x": 580,
        "y": 200,
        "wires": [
            [
                "a86c5cc835767ef4"
            ]
        ]
    },
    {
        "id": "668c50affb2cfc29",
        "type": "sqlite",
        "z": "bc82c9919021ae85",
        "mydb": "3e46bfdc4b6df798",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "LocDb",
        "x": 1380,
        "y": 200,
        "wires": [
            []
        ]
    },
    {
        "id": "02ef89f19b973001",
        "type": "mysql",
        "z": "bc82c9919021ae85",
        "mydb": "070b3434d337b06e",
        "name": "CloudDB",
        "x": 1060,
        "y": 200,
        "wires": [
            [
                "8bb3d5fe03e3ddbe"
            ]
        ]
    },
    {
        "id": "3e46bfdc4b6df798",
        "type": "sqlitedb",
        "db": "\\tmp\\bagy.db",
        "mode": "RWC"
    },
    {
        "id": "070b3434d337b06e",
        "type": "MySQLdatabase",
        "name": "SQL APP",
        "host": "",
        "port": "3306",
        "db": "appdev_2",
        "tz": "",
        "charset": "UTF8"
    }
]
8

Have you logged those occurrences to make sure it is not this that is adding duplicate records? From your description it sounds as if it could be the sql server taking too long to action the record, so your flow adds it again.

9

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