# Uploading data to a mySQL database

**URL:** <https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844>\
**Category:** General\
**Tags:** database\
**Created:** [7 December 2021 20:31 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844 "2021-12-07T20:31:31Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [7 December 2021 20:31 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/1 "2021-12-07T20:31:31Z")

</div>

hello,

i'm trying to upload diffrent variables to my mySQL database but i can't get it to inject the info correctly. this has something to do with my queries. Can someone help me?

This is one of the queries i'm using:

```auto
INSERT INTO algemeen
    (machine, persoon, status, overtime, timestamp)
VALUES
    ("+ flow.get('machine') +","
    + flow.get('persoon') +","
    + flow.get('statusGoed') +","
    + flow.get('overtime') +",
    CURRENT_TIMESTAMP);

```

---

<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:** [7 December 2021 20:54 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/2 "2021-12-07T20:54:22Z")

</div>

Are you putting that into msg.topic? I think you are just missing a quote or two.  
Edit - If your data contains strings you may need to wrap it in single quotes, for example  
`+ " '" + flow.get('machine') +"',"` (Single quotes inside the double quotes)

```auto
msg.topic = "INSERT INTO algemeen (machine, persoon, status, overtime, timestamp) "
+ "VALUES ("
+ flow.get('machine') +","
+ flow.get('persoon') +","
+ flow.get('statusGoed') +","
+ flow.get('overtime') +","
+ CURRENT_TIMESTAMP);

```

Do you actually have to give a value to timestamp or does the database definition give it a default?

I have seen other, perhaps cleaner, ways explained in this forum to insert records.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [7 December 2021 21:18 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/3 "2021-12-07T21:18:03Z")

</div>

The `template` node is also good for building strings like this, with embedded quotes...

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/6/c63fbb1492a0bd6bb260b5aee3f4fbca1401d11d.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:** [7 December 2021 21:18 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/4 "2021-12-07T21:18:25Z")

</div>

Also, before you feed it into the db node feed it into a debug node set to Output Complete Message and check that msg.topic contains what you expect.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 08:07 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/5 "2021-12-08T08:07:17Z")

</div>

thanks, i already saw one of your posts and that's what i used

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 08:14 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/6 "2021-12-08T08:14:46Z")

</div>

My query does not contain strings so thats unnecessary.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c31750fd29823d1f2b319d8977a4bebaa913b75f.png)  
and i did put the query in the topic.

You can probably configure the database in a way that it automatically logs the timestamp but this works quite well so i'm not going to change it

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 08:46 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/7 "2021-12-08T08:46:56Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/6/361f3478d3efcc5aa1e8dc68b8b048e3e55e79bd.png)  
this is the output i got:

```auto
INSERT INTO algemeen 
(machine, persoon, status, overtime, timestamp) VALUES ("+ flow.get('machine') +, + flow.get('persoon') +, + flow.get('statusGoed') +, + flow.get('overtime') +, CURRENT_TIMESTAMP); : msg.payload : string[4]

```

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 08:56 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/8 "2021-12-08T08:56:55Z")

</div>

update:  
I think i managed to fix the query but i got a new error code

Current query:

```auto
INSERT INTO algemeen
    (machine, persoon, status, overtime)
VALUES
    (flow.get('machine') +','
    + flow.get('persoon') +','
    + flow.get('statusGoed') +','
    + flow.get('overtime'));

```

error message:

```auto
"Error: ER_PROCACCESS_DENIED_ERROR: execute command denied to user 'lgout_dev'@'%' for routine 'flow.get'"

```

---

<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:** [8 December 2021 10:20 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/9 "2021-12-08T10:20:22Z")

</div>

> [@evanic14](#):
>
> `execute command denied to user 'lgout_dev'@'%' `

Is that the user you have put in the mysql node? It doesn't look like a valid user name.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 10:22 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/10 "2021-12-08T10:22:08Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/5/65c4f17900be7244be88963c1858707815956ca4.png)  
yes it is.

---

<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:** [8 December 2021 10:37 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/11 "2021-12-08T10:37:40Z")

</div>

I misinterpreted the error. It is saying user lgout\_dev on machine %. I don't know what the % means.  
Can you connect to the database using a different sql client from the machine running node-red?

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 10:48 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/12 "2021-12-08T10:48:29Z")

</div>

well the strange thing is. i can recieve data from the database. I manually put some data in the database and i am able to recieve that data.

---

<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:** [8 December 2021 11:07 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/13 "2021-12-08T11:07:28Z")

</div>

Does the user have privileges to write to the database?

---

<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:** [8 December 2021 11:08 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/14 "2021-12-08T11:08:51Z")

</div>

In that case it is the permissions that the user has in the database. The user possibly only has read permissions. If the permissions look ok then can that user write to the database from the machine running node red, using a different mysql client (such as the command line client)?

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 11:24 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/15 "2021-12-08T11:24:14Z")

</div>

@zenofmud & @Colin I thought that as well but i checked of the user has the required permission and those are all selected so that can't be the problem. But what dou you mean with client? You mean a library you can download from the manage palette section?

---

<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:** [8 December 2021 11:27 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/16 "2021-12-08T11:27:46Z")

</div>

> [@evanic14](#):
>
> But what dou you mean with client?

A client is an application using another application. So in this case node-red is a client trying to use the mysql server. In order to determine whether it is a node-red problem then you can connect to the server using a different piece of s/w. Such as this one: [https://dev.mysql.com/doc/refman/8.0/en/mysql.html](https://dev.mysql.com/doc/refman/8.0/en/mysql.html)

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [8 December 2021 12:45 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/17 "2021-12-08T12:45:35Z")

</div>

> [@evanic14](#):
>
> ```auto
> (flow.get('machine') +','
> + flow.get('persoon') +','
> + flow.get('statusGoed') +','
> + flow.get('overtime'));
> 
> ```

The syntax error shown in your debug output is complaining about the "+" signs in your select statement. This section is written as if it is being interpreted by a `function` node, NOT the `template` node -- which just does simple string substitution. If all of your values are numbers, then your template should look more like this:

```auto
(
    {{ flow.get('machine') }},
    {{ flow.get('persoon') }},
    {{ flow.get('statusGoed') }},
    {{ flow.get('overtime') }}
)

```

This will only work IF mustache substitution works for `flow.get('xxx')` inside the curly braces -- which I admit I have never tried to do. The use of flow context should generally be avoided, and the query values would then come from msg fields instead.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [8 December 2021 18:29 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/18 "2021-12-08T18:29:16Z")

</div>

I have tried your piece of query code which looks like this:

```auto
INSERT INTO algemeen
    (machine, persoon, status, overtime)
VALUES
(
    {{ flow.get('machine') }},
    {{ flow.get('persoon') }},
    {{ flow.get('statusGoed') }},
    {{ flow.get('overtime') }}
)

```

unfortunatly i'm still getting an error code:

```auto
"Invalid property expression: unexpected ' at position 4"

```

---

<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:** [8 December 2021 18:51 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/19 "2021-12-08T18:51:18Z")

</div>

Add a `debug` node (set to display the complete msg object) connected to the output of the template. Run the flow and expand the debug output to show the topic and copy and paste that to a reply. This way we can see what the statement is.

---

<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:** [8 December 2021 18:52 UTC](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844/20 "2021-12-08T18:52:21Z")

</div>

I thought you had got past the syntax error and were getting the permission denied error now.

[Next page](https://discourse.nodered.org/t/uploading-data-to-a-mysql-database/54844.md?page=2)