Node-RED: Writing MQTT Messages to MySQL DB with UNIX timestamp


 
Thread Tools Search this Thread
Top Forums Programming Node-RED: Writing MQTT Messages to MySQL DB with UNIX timestamp
# 1  
Old 02-09-2020
Node-RED: Writing MQTT Messages to MySQL DB with UNIX timestamp

First, I want to thank Neo (LOL) for this post from 2018, Node.js and mysql - ER_ACCESS_DENIED_ERROR

I could not get the Node-RED mysql module to work and searched Google until all my links were purple! I kept getting ER_ACCESS_DENIED_ERROR with the right credentials. Nothing on the web was helpful.

Then, I got out of Node-RED and tested using node directly on the command line. Same error. No joy on the net. Google links all purple.

Finally, I found a post by Neo of unix.com (Thanks again Neo!! LOL) which solved this problem!

Code:
https://www.unix.com/web-programming/279476-node-js-mysql-er_access_denied_error.html

The problem with the Node-RED module for mysql is that it only accepts an int as a port number, but my Ubuntu Linux MySQL configuration requires a socket as the port (a string); for other wayward travelers working on Node-RED, here is a sure-fire way to get MySQL working.

Do not use the Node-RED mysql module (node) or the Node-RED moment module (nodes) in Node-RED directly (you can still install and use for other flows, fo course); but you need to install mysql and moment for node.js as a JS lib, like so:

Code:
ubuntu$ npm install mysql
ubuntu$ npm install moment

Then, edit your Node-RED settings.js file:

Code:
ubuntu$ cd ~/.node-red
vi settings.js

and add this code:

Code:
functionGlobalContext: {
        // whatever you might have had here before
       // add these two libs
        mysql:require('mysql'),
        moment:require('moment')
    },

Restart Node-RED.

Now, in Node-RED create a (JS) function like this one:

Code:
/****************************************************************
 *   Node-RED MYSQL INSERT using Node.js mysql and moment modules
 *   Neo:  www.unix.com 9 Feb 2020
 *   Use as you like.  It's in the public domain! Hope it helps.
 * **************************************************************/

var mysql = global.get("mysql");
var moment = global.get("moment");
var connection = mysql.createConnection({
  host: "localhost",
  user: "YOUR_USER_NAME",
  password: "YOUR_SECURE_PASSWORD",
  // Check your Linux server for the exact location
  // of your mysql socket, this is KEY
  port: "/var/run/mysqld/mysqld.sock",
  database: "YOUR_DATABASE_OBVIOUSLY"
});

var thetopic;
var thepayload;

// EXTRA:  add a string for your clientid, or get it by adding a clientid to your MQTT topic (or payload)
// I just use "mqtt" for now.
var clientid = "mqtt";
var unixtime = moment().unix();

if(unixtime < 10000)
{
  unixtime = 99999;
}

if (msg.topic) {
// some basic topic filtering
  thetopic = msg.topic.replace(/[^a-zA-Z0-9\/ ]/g, "");
} else {
  thetopic = "no_topic";
}

if(msg.payload)
{
// some basic string filtering
 thepayload = msg.payload.replace(/[^a-zA-Z0-9\/\. ]/g, "");
}
else
{
 thepayload = "no_payload";
}

/*************************************************************************
 *  In the INSERT example, I use an IOT table I created as follows, FYI ONLY
 *
 *
 *  mysql> describe neo_iotonoff
 *
 * +----------+------------------+------+-----+---------+----------------+
 * | Field    | Type             | Null | Key | Default | Extra          |
 * +----------+------------------+------+-----+---------+----------------+
 * | id       | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
 * | topic    | varchar(64)      | NO   |     | NULL    |                |
 * | message  | varchar(64)      | NO   |     | NULL    |                |
 * | clientid | varchar(128)     | NO   |     | NULL    |                |
 * | dateline | int(20) unsigned | NO   |     | NULL    |                |
 * +----------+------------------+------+-----+---------+----------------+
 * 5 rows in set (0.00 sec)
 *
 *************************************************************************/

// . you should trap all MySQL values inserted for NULL and errors first (as  you like to trap errors)
// and filter all mqtt input to insure DB integrity (no mysql injection)

connection.query(
  "INSERT INTO neo_iotonoff (topic, message,clientid,dateline) VALUES ('" +
    thetopic +
    "','" +
    thepayload +
    "','" +
    clientid +
    "'," +
    unixtime +
    ")",

  function(err, results, fields) {
    console.log(err);
    console.log(results);
    connection.end();
  }
);
// maybe return any mysql error code or results boolean in msg in future
return msg;

In my case, I connected my Node-RED function to an MQTT node for an IOT app using some ESP devices, but of course you can the use return as your app requires:

Node-RED:  Writing MQTT Messages to MySQL DB with UNIX timestamp-screen-shot-2020-02-09-13928-pmjpg


Most interesting to me, I found it very cool that the only solution which worked (that I could find after hours of searching and turning all my links purple), across the net, was one posted on unix.com in 2018, two years ago, by that "jack-off-all-trades-master-of-none, systems engineer" Neo guy Smilie LOL

Reference:

Node.js and mysql - ER_ACCESS_DENIED_ERROR

What does that mean when we search the entire Internet for hours for a solution to a tech problem, with no joy, and we finally find a solution which works, and it was something that we posted years ago?
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Programming

Publish and Subscribe to AES-256 Encrypted MQTT Messages to Node-RED from PHP Scripts

Various Node-Red crypto modules do not work with PHP, so to send an encrypted message from a PHP script (in this case from a Ubuntu server) to Node-RED we need our own code. After a few hours of searching, testing various libs, more testing and debugging, I got this PHP to Node-RED code... (0 Replies)
Discussion started by: Neo
0 Replies

2. Infrastructure Monitoring

Using Node-RED and MQTT to Monitor Server and Application Stats

After setting up MQTT and testing some ESP8266 and ESP32 modules, where I noted that testing in Programming ESP32 (ESP-WROOM-32) as an MQTT Client Subscribed to Linux Server Load Average Messages, I was so impressed with MQTT that I installed MQTT on three different computers, instantly and... (2 Replies)
Discussion started by: Neo
2 Replies

3. Programming

ESP32 (ESP-WROOM-32) as an MQTT Client Subscribed to Linux Server Load Average Messages

Here we go.... Preface: ..... so in a galaxy far, far, far away from commercial, data sharing corporations..... For this project, I used the ESP-WROOM-32 as an MQTT (publish / subscribe) client which receives Linux server "load averages" as messages published as MQTT pub/sub messages.... (6 Replies)
Discussion started by: Neo
6 Replies

4. Web Development

Node.js and mysql - ER_ACCESS_DENIED_ERROR

This problem has been killing me all day, and I cannot solve it. Basically, I am using node.js with the mysql module and it will not connect to the database. Here is the JS code snippet in node.js: app.get("/test", function(req, res) { var mysql = require("mysql"); var con =... (4 Replies)
Discussion started by: Neo
4 Replies

5. Shell Programming and Scripting

AIX : Need to convert UNIX Timestamp to normal timestamp

Hello , I am working on AIX. I have to convert Unix timestamp to normal timestamp. Below is the file. The Unix timestamp will always be preceded by EFFECTIVE_TIME as first field as shown and there could be multiple EFFECTIVE_TIME in the file : 3.txt Contents of... (6 Replies)
Discussion started by: rahul2662
6 Replies

6. Homework & Coursework Questions

Accessing one UNIX node from another node of the same server

Hi Experts, I am in need of running a script from one node say node 1 via node 2. My scheduling tool dont have access to node2 , so i need to invoke the list file from node1 but the script needs to run from node2. because the server to which i am hitting, is having access only for the node... (5 Replies)
Discussion started by: arun1377
5 Replies

7. Shell Programming and Scripting

writing the timestamp to as a header in a file

hello mates, this is my first post. please help me out. i have got a file with some data in it. i am asked to write the timestamp as a header for that file. i mean the time the file created should be mentioned at the top of the file. i know we can use sed to insert a sentence but... (6 Replies)
Discussion started by: jdsony
6 Replies

8. UNIX for Dummies Questions & Answers

Timestamp in MySQL

Someone please help me with MySQL. I have a field in the table that contains the UNIX timestamp. Using PHP, I can format that timestamp in any way I like. Now, I want to fetch rows for a particular date. For example, all rows having timestamp equivalent to 11th November 2007 or all rows having... (1 Reply)
Discussion started by: nervous
1 Replies
Login or Register to Ask a Question