# Read from mysql db

**URL:** <https://discourse.nodered.org/t/read-from-mysql-db/2340>\
**Category:** Dashboard\
**Created:** [16 August 2018 12:49 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340 "2018-08-16T12:49:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ahd](https://avatars.discourse-cdn.com/v4/letter/a/e9a140/32.png) [@Ahd](https://discourse.nodered.org/u/Ahd)\
**Post date:** [16 August 2018 12:49 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/1 "2018-08-16T12:49:25Z")

</div>

I am tried to read data from MySQL db from node red flow. I have created the flow below but it doesn't show me any an error or anything at all.any help regard this please?

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

This my flow shown below

```auto
[{"id":"a39c7184.d9df6","type":"mysql","z":"627ecc4d.710c94","mydb":"494c5b78.7c6a84","name":"hello","x":409,"y":158,"wires":[["946d1bad.7af978"]]},{"id":"6c996cfc.b19b04","type":"inject","z":"627ecc4d.710c94","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":160,"wires":[["1d492521.7c287b"]]},{"id":"e75c49b9.08d068","type":"debug","z":"627ecc4d.710c94","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":630,"y":246,"wires":[]},{"id":"946d1bad.7af978","type":"function","z":"627ecc4d.710c94","name":"output","func":"if (msg.payload >0){\nreturn msg.payload;\n}","outputs":1,"noerr":0,"x":553,"y":160,"wires":[["e75c49b9.08d068"]]},{"id":"1d492521.7c287b","type":"function","z":"627ecc4d.710c94","name":"select","func":"\nmsg.topic = \"SELECT * FROM location \";\n","outputs":1,"noerr":0,"x":263,"y":159,"wires":[["a39c7184.d9df6"]]},{"id":"494c5b78.7c6a84","type":"MySQLdatabase","z":"","host":"sl-eu-lon-2-portal.11.dblayer.com","port":"27707 ","db":"compose","tz":""}]

```

---

<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:** [16 August 2018 13:05 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/2 "2018-08-16T13:05:05Z")

</div>

You flow is not importable. You need to edit your previous flow and put a line with three backtick characters before it and another after it.

---

<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:** [16 August 2018 13:09 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/3 "2018-08-16T13:09:32Z")

</div>

Also I suspect that you may not have tried putting a debug node after the first function block to make sure the message it sends is what you expect. Have you done that?

---

<div class="post-metadata">

**Author:** ![Ahd](https://avatars.discourse-cdn.com/v4/letter/a/e9a140/32.png) [@Ahd](https://discourse.nodered.org/u/Ahd)\
**Post date:** [16 August 2018 13:23 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/4 "2018-08-16T13:23:39Z")

</div>

sorry ,but do you mean to add ``` the start and the end of the flow

```auto
[{"id":"a39c7184.d9df6","type":"mysql","z":"627ecc4d.710c94","mydb":"494c5b78.7c6a84","name":"hello","x":464,"y":92,"wires":[["946d1bad.7af978","e75c49b9.08d068"]]},{"id":"6c996cfc.b19b04","type":"inject","z":"627ecc4d.710c94","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":160,"wires":[["1d492521.7c287b"]]},{"id":"e75c49b9.08d068","type":"debug","z":"627ecc4d.710c94","name":"","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","x":640,"y":246,"wires":[]},{"id":"946d1bad.7af978","type":"function","z":"627ecc4d.710c94","name":"output","func":"if (msg.payload >0){\nreturn msg.payload;\n}","outputs":1,"noerr":0,"x":553,"y":160,"wires":[["e75c49b9.08d068"]]},{"id":"1d492521.7c287b","type":"function","z":"627ecc4d.710c94","name":"select","func":"\nmsg.topic = 'SELECT mac FROM location';\n// msg.topic = msg.payload;\nreturn msg.topic;\n","outputs":1,"noerr":0,"x":263,"y":159,"wires":[["a39c7184.d9df6","e75c49b9.08d068"]]},{"id":"494c5b78.7c6a84","type":"MySQLdatabase","z":"","host":"sl-eu-lon-2-portal.11.dblayer.com","port":"27707 ","db":"compose","tz":""}] 

```

---

<div class="post-metadata">

**Author:** ![Ahd](https://avatars.discourse-cdn.com/v4/letter/a/e9a140/32.png) [@Ahd](https://discourse.nodered.org/u/Ahd)\
**Post date:** [16 August 2018 13:29 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/5 "2018-08-16T13:29:32Z")

</div>

I have putting now a debug node after the first function block and, it shows an error as shown below

"Function tried to send a message of type string"

---

<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:** [16 August 2018 13:59 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/6 "2018-08-16T13:59:12Z")

</div>

As I said you should put the three backticks on separate lines before and after the flow, so the forum knows it is code. I am not sure exactly what you did but anyway I managed to import the flow.

The message says you are return a string. You are returning msg.topic which is a string, so the message is quite correct. You must always return a complete message, so your last line should be  
`return msg;`  
See [https://nodered.org/docs/writing-functions](https://nodered.org/docs/writing-functions) for more information about writing functions.

---

<div class="post-metadata">

**Author:** ![Ahd](https://avatars.discourse-cdn.com/v4/letter/a/e9a140/32.png) [@Ahd](https://discourse.nodered.org/u/Ahd)\
**Post date:** [16 August 2018 14:11 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/7 "2018-08-16T14:11:52Z")

</div>

Thanks ,it solve it.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [10 November 2018 00:48 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/8 "2018-11-10T00:48:12Z")

</div>

Hello, please I need your help, I am subscribed to different topics and the values ​​of these topics I wish to store them in a Mysql database, but when I perform the data insertion I have these results. That is, the same value of a single topic is repeated in the columns, and I want each value of each topic to be inserted in the corresponding column, please how could this be solved

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

---

<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:** [10 November 2018 02:35 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/9 "2018-11-10T02:35:27Z")

</div>

Well from what you show, it is working fine. you have told it to insert five '16's.

so the question is how are you building your insert statement and what is the data you feed it (you can find this by using a debug node attached to the node feeding what ever node you are using to build the sql statement.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [10 November 2018 03:05 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/10 "2018-11-10T03:05:54Z")

</div>

Thanks for answering me Zenofmud, and the node function this using the following:

var pld = "INSERT INTO dispositivo2 "  
pld = pld + "(temp\_amb, temp\_corp )"  
pld = pld + "VALUES ('"+msg.payload+"','"+msg.payload+"');"  
msg.topic = pld  
return msg;

The data I get from 2 different topics

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [10 November 2018 03:13 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/11 "2018-11-10T03:13:00Z")

</div>

I also use this flow

 ![Captura5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/15d131459c9cad9d5ac292380093a5ec5bbafd7b.png)

---

<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:** [10 November 2018 05:33 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/12 "2018-11-10T05:33:55Z")

</div>

The fact that you have two `mqtt in` nodes wired to the same `function` node will **not** automagically combine two separate msg objects into 1 -- you will need a `join` node for that. You will probably want it to wait for both topics to arrive, and combine them into a single key/value msg, using the topic as the key name.

Following the join node, I like to use a `template` node with the sql query string, substituting the two incoming value with some "mustache" syntax, similar to this:

```
INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES ({{payload.temp_amb}}, {{payload.temp_corp}});

```

... makes it much easier to build the string, and the `template` node will allow you to put the output string directly into the `msg.topic` property, where the sql node looks for it.

BTW, it is recommended that MQTT topics should **not start or end** with a slash character...

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [11 November 2018 01:14 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/14 "2018-11-11T01:14:21Z")

</div>

Thank you for answering my question Shrickus, I have made all the mentioned changes; but now I have this error; I'm new to node, could you please help me with this?

 ![Captura10](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/b0dd81d424fad981f11abb63d0ced05e4c30c13c.png)  
 ![Captura7](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e39877d98fdc8790b3922757a6871264f883c589.png)  
 ![Captura8](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/d/d92b375cbddfb01a8587d63c79d4aa2b54f5feb2.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:** [11 November 2018 11:01 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/15 "2018-11-11T11:01:50Z")

</div>

You have the template node setting msg.payload instead of msg.topic

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [13 November 2018 19:48 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/16 "2018-11-13T19:48:05Z")

</div>

Thank you for your response Colin, but you made the change and now I get this message, in reality there are no changes that can be made to avoid these errors.

![Captura1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/9b9f3cbbd2bbef73aa860e8c80613b16fbed83d6.png)  
 ![Captura3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/410b0467b0177241dbdf344350efef3a3facec21.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:** [13 November 2018 20:42 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/17 "2018-11-13T20:42:31Z")

</div>

It is pointless showing debug output unless you make it clear which node is giving which debug output. All you showed was a join node and debug outputs.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [13 November 2018 20:58 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/18 "2018-11-13T20:58:40Z")

</div>

It is true, Colin, but I still can not insert the data in the database, I have placed a node debug to the output and I still get this message:

"msg.topic : the query is not defined as a string"

---

<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:** [13 November 2018 21:00 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/19 "2018-11-13T21:00:33Z")

</div>

Where is that message coming from? If it is coming out of the sql node, then we need to see what is going into it.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [13 November 2018 21:04 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/20 "2018-11-13T21:04:33Z")

</div>

Colin, that's how I have my flow; I have made the aforementioned changes but I can not insert the data

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/81ddeb7da28c7c2cf1cfc84b1f8d3a6a677deb98.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:** [13 November 2018 21:07 UTC](https://discourse.nodered.org/t/read-from-mysql-db/2340/21 "2018-11-13T21:07:59Z")

</div>

As I said, we need to see what is going into each node. Put a debug at each point and post them here. Give the debug nodes names then it will be clear from the output which is which. Post another picture of the flow obviously so we can see which is which.

[Next page](https://discourse.nodered.org/t/read-from-mysql-db/2340.md?page=2)