# Trying to write to mariaDB with mysql node

**URL:** <https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974>\
**Category:** General\
**Created:** [17 December 2024 19:21 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974 "2024-12-17T19:21:36Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [17 December 2024 19:21 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/1 "2024-12-17T19:21:36Z")

</div>

Ok, so I have a function node to update a maria DB.

This function node will deploy and retrieve from the DB:

```auto
msg.topic = 'SELECT * FROM dwell_time_alerts LIMIT 1';

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

However, this query fails to even deploy w/ a no response from server error:

```auto
let cam_name = msg.payload.camera?.settings?.name || "Unknown";
let ip_add = msg.payload.camera?.settings?.ip || "0.0.0.0";
let mac_add = msg.payload.camera?.settings?.mac || "Unknown";
let area = msg.payload.camera?.settings?.area || "Unknown";
let timestamp = msg.payload.camera?.settings?.timestamp || new Date().toISOString();
let url = msg.payload.camera?.settings?.url || "";

msg.topic = `INSERT INTO dwell_time_alerts (camera_name, ip_add, mac_add, area, timestamp, url) 
            VALUES ('${cam_name}', '${ip_add}', '${mac_add}', '${area}', '${timestamp}', '${url}')`;

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

I'm at a loss.

---

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [17 December 2024 19:36 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/2 "2024-12-17T19:36:26Z")

</div>

Tried updating my query to this:

```auto
let cam_name = msg.payload.camera?.settings?.name || "Unknown";
let ip_add = msg.payload.camera?.settings?.ip || "0.0.0.0";
let mac_add = msg.payload.camera?.settings?.mac || "Unknown";
let area = msg.payload.camera?.settings?.area || "Unknown";
let timestamp = msg.payload.camera?.settings?.timestamp || new Date().toISOString();
let url = msg.payload.camera?.settings?.url || "";

msg.topic = "INSERT INTO 'nodered'.'dwell_time_alerts' ('camera_name', 'ip_add', 'mac_add', 'area', 'timestamp', 'url') VALUES ('"+ cam_name+"', '"+ ip_add+"', '"+ mac_add+"', '"+ area+"', '"+ timestamp+"', '"+ url+"')";

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

Same message:

Deploy failed: no response from server.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [17 December 2024 20:10 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/3 "2024-12-17T20:10:28Z")

</div>

Welcome to the forum @djackson-telaid .

Deploy failed: no response from server does not indicate any problem with your function code.  
It indicates a communication problem between the browser and Node-red. Perhaps Node-red is no longer running, perhaps it's a network issue.

Can you give us the details of what computer Node-red is installed on and what computer is running the browser?  
What URL did you use to access the editor?

That said, I do think there are issues with your code.

In the first version you seem to be mixing up this syntax for common-or-garden variables and that for environment variables (I rarely use environment variables so I'm not certain)

In the second you are hard coding the values into msg.topic. It's legal in MySQL but bad form because this kind of INSERT is less secure than a "prepared query", which I think your first attempt is supposed to do.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 December 2024 20:24 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/4 "2024-12-17T20:24:12Z")

</div>

I find using a "template" node helps simplify constructing the MySQL query.

 ![tues_mysql_A](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/a/cac7b466b1396274bcdc51c9fdb2f4ce29bb9645.png)

Make sure you set the property in the node to... msg.topic

 ![tues_mysql_B](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/c/fcdc9bbcc0994f3f685a49473d21ab888d941bab.png)  
This is what the query statement looks like... (note: In this example all the items are 'strings')  
If any of the items are numeric, then leave-off the leading and trailing primes as below...

```auto
{{payload.sp_max}}, 
{{payload.temperature}}, 
{{payload.sp_min}},

```

```auto
INSERT INTO mqtt_summary (uniqueID, date, time, ip, location) VALUES (
'{{uniqueID}}', 
'{{date}}', 
'{{time}}', 
'{{ip}}', 
'{{location}}'
)

```

---

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [17 December 2024 20:26 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/5 "2024-12-17T20:26:57Z")

</div>

It's running in a Docker container, and I've verified it's operational, as when I remove the INSERT code, it's able to deploy just fine. Again, I can pull data from the maria DB.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [17 December 2024 21:25 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/6 "2024-12-17T21:25:15Z")

</div>

Oh OK. Docker is a foreign country. They do things differently there.

---

<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:** [17 December 2024 21:43 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/7 "2024-12-17T21:43:51Z")

</div>

Look in the node red log and see what it shows when you deploy. I don't know how to do that in docker but hopefully you do, since you are using it.

Edit: in fact first disconnect the DB node input and instead feed the function node into a debug node set to output complete message and deploy that. Assuming it deploys, check the message is as you expect.

---

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [18 December 2024 15:30 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/8 "2024-12-18T15:30:00Z")

</div>

Ignore the syntax of the queries for a moment, as they don't matter currently. The problem is this builds and deploys:

```let
let ip_add = msg.payload.camera?.settings?.ip || "0.0.0.0";
let mac_add = msg.payload.camera?.settings?.mac || "Unknown";
let area = msg.payload.camera?.settings?.area || "Unknown";
let timestamp = msg.payload.camera?.settings?.timestamp || new Date().toISOString();
let url = msg.payload.camera?.settings?.url || "";

msg.topic = 'INSERT * dwell_time_alerts LIMIT 1';

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

This does NOT:

```auto
let cam_name = msg.payload.camera?.settings?.name || "Unknown";
let ip_add = msg.payload.camera?.settings?.ip || "0.0.0.0";
let mac_add = msg.payload.camera?.settings?.mac || "Unknown";
let area = msg.payload.camera?.settings?.area || "Unknown";
let timestamp = msg.payload.camera?.settings?.timestamp || new Date().toISOString();
let url = msg.payload.camera?.settings?.url || "";

msg.topic = 'INSERT INTO dwell_time_alerts LIMIT 1';

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

The word INTO is causing it to not build/deploy. How can that be?

---

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [18 December 2024 15:40 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/9 "2024-12-18T15:40:23Z")

</div>

Believe it or not, this builds:

```auto
let cam_name = msg.payload.camera?.settings?.name || "Unknown";
let ip_add = msg.payload.camera?.settings?.ip || "0.0.0.0";
let mac_add = msg.payload.camera?.settings?.mac || "Unknown";
let area = msg.payload.camera?.settings?.area || "Unknown";
let timestamp = msg.payload.camera?.settings?.timestamp || new Date().toISOString();
let url = msg.payload.camera?.settings?.url || "";

let insertKeyword = "INTO";
msg.topic = `INSERT ${insertKeyword} dwell_time_alerts`;

node.warn(msg.topic); // Log the query to Node-RED console

return msg;

```

go figure...

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [18 December 2024 16:00 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/10 "2024-12-18T16:00:01Z")

</div>

> [@djackson-telaid](#):
>
> This does NOT:
> 
> `msg.topic = 'INSERT INTO dwell_time_alerts LIMIT 1';`
> 
> The word INTO is causing it to not build/deploy. How can that be?

It can't be - INTO is part of a string literal.

Now Node-red is _not_ going to be validating SQL syntax in a string at deploy (or any other) time but what's going on with LIMIT1 at the end of the INSERT statement? Is that really valid syntax?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/0/0019fffdfa17013cd148b230abd931d779cae699.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/d/4ddbeaf446d2d7aba8b357c5d54a9c0d4cbe157a.png)

These use different single quote characters. Why?

---

<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:** [18 December 2024 16:06 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/11 "2024-12-18T16:06:43Z")

</div>

Are those with the db node input disconnected as I suggested?

---

<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:** [18 December 2024 16:15 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/12 "2024-12-18T16:15:12Z")

</div>

> [@jbudd](#):
>
> These use different single quote characters. Why?

That is so that the variable `insertKeyword` is inserted into the string. The backticks are for [Template String Interpolation](https://www.w3schools.com/js/js_string_templates.asp).

---

<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:** [18 December 2024 16:16 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/13 "2024-12-18T16:16:39Z")

</div>

> [@djackson-telaid](#):
>
> The word INTO is causing it to not build/deploy. How can that be?

I think it is more likely the `LIMIT 1` on an insert query that is causing the db node to become confused.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [18 December 2024 16:37 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/14 "2024-12-18T16:37:51Z")

</div>

> [@Colin](#):
>
> The backticks are for [Template String Interpolation](https://www.w3schools.com/js/js_string_templates.asp).

I did think of that but since I'm not familiar with it, I didn't check if it's ok for backticks to _replace_ single or double quotes.

> [@Colin](#):
>
> more likely the `LIMIT 1` on an insert query that is causing the db node to become confused.

But surely that's a runtime issue not at deploy?

---

<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:** [18 December 2024 16:59 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/15 "2024-12-18T16:59:41Z")

</div>

> [@jbudd](#):
>
> But surely that's a runtime issue not at deploy?

If the db node is hanging soon after deploy then it might look as if the Deploy is not completing. That is why I have asked @djackson-telaid to try it with the db node not connected, and also to show the log while trying to deploy, which he has also not responded to.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [18 December 2024 17:00 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/16 "2024-12-18T17:00:20Z")

</div>

@djackson-telaid Can you post an example of the msg.payload you are feeding into the function? (Use the copy value button in the debug sidebar, not a screenshot)

Also the exact SQL query syntax that your first posted function node is intended to generate from it.

---

<div class="post-metadata">

**Author:** ![djackson-telaid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/djackson-telaid/32/97296_2.png) [@djackson-telaid](https://discourse.nodered.org/u/djackson-telaid)\
**Post date:** [18 December 2024 18:32 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/17 "2024-12-18T18:32:10Z")

</div>

So it turns out, it was "deploying" the changes all along, but reporting no response from the server. After rebooting the machine and hard-refreshing the screen, the changes took regardless. Not sure why it's doing it, but thanks for all your help.

---

<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:** [18 December 2024 21:45 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/18 "2024-12-18T21:45:01Z")

</div>

> [@djackson-telaid](#):
>
> So it turns out, it was "deploying" the changes all along, but reporting no response from the server.

That is what we have been trying to tell you all along.

---

<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:** [1 January 2025 21:45 UTC](https://discourse.nodered.org/t/trying-to-write-to-mariadb-with-mysql-node/93974/19 "2025-01-01T21:45:23Z")

</div>

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