Skip to content

Instantly share code, notes, and snippets.


smadds/ Secret

Last active December 24, 2022 21:04
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?

Logs all or selected MQTT activity to a MySQL database.

By default logs everything - change the topic in the MQTT node to be more selective (e.g. "my\path\#" or "my\topic")

MySQL table needs the following columns:

  • id (integer, autoincrement) (optional)
  • timestamp (date-time)
  • topic (string 200)
  • data (string 200)

Requires node-red-node-mysql and configuration of your MQTT and MySQL server details

[{"id":"a06d74db.919808","type":"mqtt in","z":"b194ff77.b24e2","name":"","topic":"#","qos":"2","broker":"","x":210,"y":460,"wires":[["98201759.e4d558"]]},{"id":"6825dd23.d4ee44","type":"debug","z":"b194ff77.b24e2","name":"","active":true,"console":"false","complete":"true","x":610,"y":420,"wires":[]},{"id":"98201759.e4d558","type":"function","z":"b194ff77.b24e2","name":"Create query in topic","func":"var out = \"INSERT INTO **your_table_name_here** (timestamp,topic,data)\"\nout = out + \"VALUES ('\" + new Date().toISOString() + \"','\" \nout = out + msg.topic + \"','\" + msg.payload + \"');\"\n \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":460,"wires":[["6825dd23.d4ee44","ddb3e588.199508"]]},{"id":"be41a506.f7f9f8","type":"comment","z":"b194ff77.b24e2","name":"Log everything","info":"","x":200,"y":420,"wires":[]},{"id":"4265dd53.5b18a4","type":"debug","z":"b194ff77.b24e2","name":"","active":true,"console":"false","complete":"false","x":750,"y":460,"wires":[]},{"id":"ddb3e588.199508","type":"mysql","z":"b194ff77.b24e2","name":"","x":610,"y":460,"wires":[["4265dd53.5b18a4"]]}]
Copy link

cylew6 commented Sep 25, 2017

I have an issue, while uploading to mysql.
The timezone showed in the UTC 0,and I need to change it to UTC +8.
I have checked my system time zone which showed UTC +8.
Need some expert advice to resolve the issues.

Copy link

Thank you for providing this example. Is there any way I can test the connection? I keep seeing "Error: Pool is closed" in the debug. The pi isn't connected to a sensor so not sure if its trying to do anything.

Copy link

AndKe commented Dec 24, 2022

I've tried this example, but then I get:

"Error: Incorrect datetime value: '2022-12-24T07:17:28.040Z' for column 'timestamp' at row 1"

Copy link

AndKe commented Dec 24, 2022

solved by using Date().toISOString().slice(0, 19).replace('T', ' ')

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment