Visit The New, Modern Unix Linux Community


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  
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?

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #668
Difficulty: Medium
IEEE 802.11 is part of the IEEE 802 set of LAN protocols, and specifies the set of media access control (MAC) and physical layer (PHY) protocols for implementing virtual local area network (VLAN) Wi-Fi computer communication in various frequencies.
True or False?

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

Featured Tech Videos