# Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax;

**URL:** <https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188>\
**Category:** General\
**Created:** [24 May 2020 13:57 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188 "2020-05-24T13:57:13Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 13:57 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/1 "2020-05-24T13:57:14Z")

</div>

Hello everybody. I'm new to Node Red.  
I'm about to replace a JS module that has been running on my NAS for some time, with Node Red. It is about writing the content of an MQTT message into a MariaDB 5. Everything works fine but I get the above error message from the DB node, although the data is written correctly to the DB

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/a/2aa07d936d15843ea66b0e3f854e981f66bb0c26.png)

Message payload example: "22.0,22.4,16.2"  
Output of the DB node after an order:  
`{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}`

Code in the Function-Node:

```auto
if(msg.topic == 'Heizung/InnenAussen'){
    var splited = msg.payload.split(",");
    if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
        var setRoom = splited[0];
        var actRoom = splited[1];
        var actOut = splited[2];
        msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
	}
}
return msg;

```

Maybe something with the backticks or quotes or apostrophes?  
Thank You for your Help.

---

<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:** [24 May 2020 14:11 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/2 "2020-05-24T14:11:37Z")

</div>

> [@JSON\_Parser](#):
>
> Everything works fine but I get the above error message from the DB node, although the data is written correctly to the DB

What makes you think it's an error? Just looks like feedback to me (but then I'm not familiar with MariaDB).

---

<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:** [24 May 2020 14:34 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/3 "2020-05-24T14:34:35Z")

</div>

I imagine the error is coming from a different message, rather than the one that shows the good output. I notice that you have two db nodes, could the error be coming from the other one.  
Add debug nodes after the function nodes, showing what you are passing to the sql node and also show us the full error message.

---

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 14:38 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/4 "2020-05-24T14:38:02Z")

</div>

Hi Steve-Mci  
With every write job, I get the following messages in the debug window.  
I get this messages from both DB nodes (Maria DB 1 and Maria DB 2)

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

---

<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:** [24 May 2020 14:47 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/6 "2020-05-24T14:47:53Z")

</div>

> [@Colin](#):
>
> And what do you see going _into_ the db nodes?

And what do you see going _into_ the db nodes?  
Make sure you fully expand the debug output. Also set them to Show Complete Message, then it is easier to see the data.

---

<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:** [24 May 2020 14:51 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/7 "2020-05-24T14:51:28Z")

</div>

I'm not sure but off the top of my head, try setting msg.payload to null before the db node.

Also, try adding a semicolon to the end of the SQL in msg.topic.

Also, what node are you using for db inserts? Node-red-contrib-??????

There may be other mysql nodes that work better?

Edit. Also, that error doesn't seem to match your topic. Post a copy of your flow.

---

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 14:53 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/8 "2020-05-24T14:53:29Z")

</div>

This is what I am handing over to the DB node  
 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/80ad88862caa361e281b747c81f6af1235d422b4.png)

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [24 May 2020 14:53 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/9 "2020-05-24T14:53:39Z")

</div>

And suddenly it makes sense. Your function node checks for msg.topic to match an mqtt topic and if so changes topic and payload to a database query. But what if it doesn’t match the exact topic? Look at the last line of your function node: `return msg`. So in that cases the query used for the database is your mqtt topic. Put a debug node before the function node set to full message, and see what exactly goes in your function nodes.

Coming out of your mqtt node I suggest putting down a switch to route where the msgis going to prevent innenausen to go to the vorlauf and the other way around.

---

<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:** [24 May 2020 14:54 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/10 "2020-05-24T14:54:43Z")

</div>

Um doesn't the topic need to be a SQL statement? Like "insert into ...."

---

<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:** [24 May 2020 14:55 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/11 "2020-05-24T14:55:13Z")

</div>

Ah well spotted

As @afelix said.

Your function logic is not updating the topic to be a SQL statement due to badly coded logic.

---

<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:** [24 May 2020 14:58 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/12 "2020-05-24T14:58:20Z")

</div>

The fix....

```auto
if(msg.topic == 'Heizung/InnenAussen'){
    var splited = msg.payload.split(",");
    if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
        var setRoom = splited[0];
        var actRoom = splited[1];
        var actOut = splited[2];
        msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
        return msg;
	}
}
return null;

```

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [24 May 2020 15:00 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/13 "2020-05-24T15:00:08Z")

</div>

If instead you switch your routing logic from the function nodes to a switch node(s), then keep the changing of the topic in a function or change node and retry I think it might work out. But for actual database handling Steve is the best to answer. I’m just here to spot these small things 🙂

You could try the function steve posted just above this message, or keep the if/else logic in switch nodes prior to the function node.

---

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 15:03 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/14 "2020-05-24T15:03:02Z")

</div>

Uhh what a mistake. Thanks guys for the support!  
I tried Steve's solution like this and it works perfectly.

I wish everyone a nice Sunday and thanks again for the extremely quick help!

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [24 May 2020 15:07 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/15 "2020-05-24T15:07:00Z")

</div>

I wonder, are there any configuration differences between your 2 database nodes and how they’re set up? Or could it perhaps be simplified into going into the same db node?

---

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 15:14 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/16 "2020-05-24T15:14:59Z")

</div>

There are two tables in the same database.  
The whole thing could possibly be solved in one function with both queries in it.  
The MQTT subscription is: " Heizung/# ", so two topics arrive at the output of the MQTT node. The heating sends them immediately one after the other.  
It was clearer for me this way. Is there an advantage if I combine them?

---

<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:** [24 May 2020 15:33 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/17 "2020-05-24T15:33:08Z")

</div>

You should be able to send both function node outputs to the same db node, unless the configuration of the two db nodes is different. It will do one query then the other.

---

<div class="post-metadata">

**Author:** ![JSON\_Parser](https://avatars.discourse-cdn.com/v4/letter/j/90db22/32.png) [@JSON\_Parser](https://discourse.nodered.org/u/JSON_Parser)\
**Post date:** [24 May 2020 15:37 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/18 "2020-05-24T15:37:46Z")

</div>

OK, your right 😏  
I have combine it to one..

```auto
if(msg.topic == 'Heizung/InnenAussen'){
    var splited = msg.payload.split(",");
    if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
        var setRoom = splited[0];
        var actRoom = splited[1];
        var actOut = splited[2];
        msg.topic = "INSERT INTO `Heizung1` (`tempSet`,`tempAct`,`tempOut`) VALUES ("+setRoom+", "+actRoom+", "+actOut+")";
        return msg;
	}
}
if(msg.topic == 'Heizung/Vorlauf'){
    var splited = msg.payload.split(",");
    if (splited.length == 3){ // Damit wird absturz vermieden wenn unten drei value gefragt sind aber nur 2 gesendet
        var setVL = splited[0];
        var actVL = splited[1];
        var actSpeicher = splited[2];
        msg.topic = "INSERT INTO `Heizung2` (`vorlaufSoll`,`vorlaufIst`,`speicherIst`) VALUES ("+setVL+", "+actVL+", "+actSpeicher+")";
        return msg;
    }
}
return null;

```

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/e/2e180c3f1d790f08bc0ce4a162f555898ecddc08.png)

---

<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:** [7 June 2020 15:37 UTC](https://discourse.nodered.org/t/error-er-parse-error-you-have-an-error-in-your-sql-syntax/27188/19 "2020-06-07T15:37:47Z")

</div>

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