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!
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:
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 LOL
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?
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)
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)
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)
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)
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)
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)
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)
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)