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!
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:
ubuntu$ npm install mysql
ubuntu$ npm install moment
Then, edit your Node-RED settings.js
file:
ubuntu$ cd ~/.node-red
vi settings.js
and add this 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:
/****************************************************************
* 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
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?