Log MQTT to MySQL

General
1

Can anyone help

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
image.png1260Ă—182 31 KB

Ubuntu server, latest version of node red

2

The correct format is here:

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. :slight_smile:

3

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.

4

You have:

2019-06-03T19:33:43.296Z

MySQL wants

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.

5

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

6

Well perhaps I exaggerated for effect a little :slight_smile:

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

7

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 ?

8

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:

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.

9

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

10

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

11

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

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.

12

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:

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.

13

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.

14

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.

15

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

image
image.png607Ă—700 57.1 KB

16

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)

17

Lost me, teh ???

18

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.

19

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

image
image.png1268Ă—609 56.4 KB

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

20

Colin what was the fix for your suggestion please