# Log MQTT to MySQL

**URL:** <https://discourse.nodered.org/t/log-mqtt-to-mysql/11806>\
**Category:** General\
**Created:** [3 June 2019 19:36 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806 "2019-06-03T19:36:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [3 June 2019 19:36 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/1 "2019-06-03T19:36:25Z")

</div>

Can anyone help

[https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89](https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89)

Getting this error

"Error: ER\_TRUNCATED\_WRONG\_VALUE: Incorrect datetime value: '2019-06-03T19:33:43.296Z' for column 'timestamp' at row 1"

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

Ubuntu server, latest version of node red

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 19:42 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/2 "2019-06-03T19:42:05Z")

</div>

The correct format is here:

[https://dev.mysql.com/doc/refman/8.0/en/datetime.html](https://dev.mysql.com/doc/refman/8.0/en/datetime.html)

You have used an ISO date/time format. Generally a good format to use except that MySQL hasn't quite caught up with the 21st C quite yet. 🙂

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [3 June 2019 19:47 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/3 "2019-06-03T19:47:12Z")

</div>

Hi thanks for the reply. I am new to this. I presume it is the z on the end that is the issue. Not sure what I need to do to fix it. Can you give some guidance please.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 19:58 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/4 "2019-06-03T19:58:47Z")

</div>

You have:

```auto
2019-06-03T19:33:43.296Z

```

MySQL wants

```auto
2019-06-03 19:33:43

```

So the date is OK, but you need to replace the "T" with a space and then lose everything after the seconds value.

The simplest way to do that would be to use my node-red-contrib-moment node. A slightly more complex way would be to use a change node or a function node to fixup the text.

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [3 June 2019 20:10 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/5 "2019-06-03T20:10:16Z")

</div>

Thanks for that information. I will try that.

It has raised another question for me, would you advise that I use a different database from mysql, due to this sort of limitation. I don't want to spend a load of time learning something that is in the dark ages so to speak. Do you have any suggestions for an alternative. This is to hold data that will be used for reports and plotting on graphs.

Also the install of your node fails with this,  
2019-06-03T20:16:33.552Z Install : node-red-contrib-moment 3.0.2

2019-06-03T20:16:31.949Z npm install --no-audit --no-update-notifier --save --save-prefix="~" --production node-red-contrib-moment@3.0.2  
2019-06-03T20:16:39.117Z [err] npm  
2019-06-03T20:16:39.117Z [err] ERR! path /root/snap/node-red/309/node\_modules/node-red-contrib-uibuilder  
2019-06-03T20:16:39.117Z [err] npm ERR!  
2019-06-03T20:16:39.117Z [err] code  
2019-06-03T20:16:39.117Z [err] EISGIT  
2019-06-03T20:16:39.117Z [err] npm ERR!  
2019-06-03T20:16:39.118Z [err] git /root/snap/node-red/309/node\_modules/node-red-contrib-uibuilder: Appears to be a git repo or submodule.  
2019-06-03T20:16:39.118Z [err] npm ERR!  
2019-06-03T20:16:39.118Z [err] git  
2019-06-03T20:16:39.118Z [err] /root/snap/node-red/309/node\_modules/node-red-contrib-uibuilder  
2019-06-03T20:16:39.118Z [err] npm  
2019-06-03T20:16:39.118Z [err] ERR!  
2019-06-03T20:16:39.118Z [err] git Refusing to remove it. Update manually,  
2019-06-03T20:16:39.118Z [err] npm  
2019-06-03T20:16:39.118Z [err] ERR!  
2019-06-03T20:16:39.118Z [err] git or move it out of the way first.  
2019-06-03T20:16:39.178Z [err]  
2019-06-03T20:16:39.178Z [err] npm ERR! A complete log of this run can be found in:  
2019-06-03T20:16:39.178Z [err] npm  
2019-06-03T20:16:39.178Z [err] ERR! /root/snap/node-red/309/.npm/\_logs/2019-06-03T20\_16\_39\_120Z-debug.log  
2019-06-03T20:16:39.189Z rc=1

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 20:19 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/6 "2019-06-03T20:19:53Z")

</div>

> [@lozzer65](#):
>
> I don't want to spend a load of time learning something that is in the dark ages so to speak.

Well perhaps I exaggerated for effect a little 🙂

> [@lozzer65](#):
>
> ERR! path /root/snap/node-red/309/node\_modules/node-red-contrib-uibuilder

Oh shoot! Something is seriously wrong there! Looks like a different node has overwritten the moment node! I will investigate.

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [3 June 2019 20:31 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/7 "2019-06-03T20:31:17Z")

</div>

I am not so sure this is not an issue on my install with your node install. I can't seem to install any new nodes. Have you ever come across this before ?

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 20:42 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/8 "2019-06-03T20:42:22Z")

</div>

Nope, there was something wrong with the npm package.

If you can, please go to your userDir folder on the server (usually `~/.node-red`). Delete the file called `package-lock.json`. Then install moment manually using:

```auto
cd ~/.node-red
npm install node-red-contrib-moment

```

This should install v3.0.3 which has no changes other than the version number changed but seems to fix the issue.

---

<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:** [3 June 2019 21:09 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/9 "2019-06-03T21:09:30Z")

</div>

Wasn't there an issue with a version of uibuilder installing a .git folder that messed up npm? Or is my memory faulty again?

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [3 June 2019 21:13 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/10 "2019-06-03T21:13:43Z")

</div>

oh thats interesting. Can you remember a fix. Going round in circles here

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 21:14 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/11 "2019-06-03T21:14:37Z")

</div>

> [@Colin](#):
>
> Wasn't there an issue with a version of uibuilder installing a .git folder that messed up npm? Or is my memory faulty again?

Now, now Colin, no need to get nasty on me! 😄

Different issue I'm afraid. Somehow the moment npm package seems to have gotten packed with something related to one of my other nodes, uibuilder. Not sure how since the readme in npm and the version numbers are correct for moment not uibuilder.

Anyway, sorted now. It will just take the flows site a little while to catch up so you need to install manually for an hour or so.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 21:16 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/12 "2019-06-03T21:16:27Z")

</div>

If removing the package-lock.json file doesn't help. The easiest thing to do is to delete the `~/.node-red/node_modules` folder and then do:

```auto
cd ~/.node-red
npm install

```

Which will reinstall all your previous nodes.

As always, I'm assuming that your userDir folder is the standard one. Adjust if not.

---

<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:** [3 June 2019 21:21 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/13 "2019-06-03T21:21:49Z")

</div>

OK, it seemed to me that this is exactly the error that the uibuilder problem was causing.  
`root/snap/node-red/309/node_modules/node-red-contrib-uibuilder: Appears to be a git repo or submodule.`  
However if it is all sorted then that's fine, obviously.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2019 21:25 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/14 "2019-06-03T21:25:31Z")

</div>

No, I think that part is simply an echo of the code that got trapped into the moment node.

I suspect that I must have published a version of the uibuilder code to the moment npm package.

Anyway, all is sorted but sometimes npm gets confused all round and needs a kick where it hurts to sort it out again.

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [4 June 2019 07:23 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/15 "2019-06-04T07:23:09Z")

</div>

Ok guys, thanks for your help I will try this tonight at home. Rather odd, but tried the above with sending Mqtt data to MYSQL on my work test rig, which is exaclty the same as my home one. Home one does not work, due to the timestamp format, yet the work one does.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/36470fe3093f0113740f913175972a594c79c18c.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/0fccff0a2d34892417c32406ca8dba54a920b76c.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:** [4 June 2019 09:15 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/16 "2019-06-04T09:15:29Z")

</div>

Since you have a work rig and a home rig, I'm going to guess there are two different MySQL environment/databases. What is the column defined as in the work rig? (UPDATE: corrected the typo)

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [4 June 2019 09:51 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/17 "2019-06-04T09:51:00Z")

</div>

Lost me, teh ???

---

<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:** [4 June 2019 10:03 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/18 "2019-06-04T10:03:13Z")

</div>

That should be 'the'. The question is what type is the timestamp column on the work rig, and on the other one for that matter.

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [4 June 2019 16:59 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/19 "2019-06-04T16:59:44Z")

</div>

Trying the idea of deleting the package-lock.json. Struggling to find that file. This whats found when doing a search.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/622aeb71ca69ae7e667f729d158ca688bb29dbe3.png)

Colin I will answer your question after I have the nodes done

---

<div class="post-metadata">

**Author:** ![lozzer65](https://avatars.discourse-cdn.com/v4/letter/l/f475e1/32.png) [@lozzer65](https://discourse.nodered.org/u/lozzer65)\
**Post date:** [4 June 2019 17:02 UTC](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806/20 "2019-06-04T17:02:14Z")

</div>

> [@Colin](#):
>
> OK, it seemed to me that this is exactly the error that the uibuilder problem was causing.  
> `root/snap/node-red/309/node_modules/node-red-contrib-uibuilder: Appears to be a git repo or submodule.`  
> However if it is all sorted then that's fine, obviously.

Colin what was the fix for your suggestion please

[Next page](https://discourse.nodered.org/t/log-mqtt-to-mysql/11806.md?page=2)