ER_NOT_SUPPORTED_AUTH_MODE: How to replace use_your_user?

General
1

Hi, I know this has been discussed elsewhere, but for the life of me, I can't seem to figure this out. All is fine on mysql 5. , but I need to use digitalocean, so I can only use mysql 8.

Getting this error:

"Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client"

DO's suggestion is the following:

As a workaround, you can connect using MySQL 8.x and issue the following statement, which will change the password type the server will accept for the current user. Replace use_your_user with your MySQL username, and replace use_your_password with the user’s current password. After successfully executing the statement, you should be able to connect to MySQL with a 5.x client or an application that does not support caching_sha2_password

Questions:

  1. Where in nodered do I replace use_your_user with my MySQL username?
  2. Where in nodered do I replace use_your_password with the user's current password?

I assume it should be done in some "raw code" view, but I can't find any. Any ideas would be greatly appreciated.

Edit: I'm using the node-red-node-mysql node

2

Sorry, a question in reply: You are using the node-red-node-mysql node?

3

Thanks, yes, that is what I'm using: node-red-node-mysql

4

Have you considered just importing mysql into node directly using npm and writing a Node-RED function to do your mysql queries?

That is what I do (to get past issues with node-red-node-mysql) and it works very well (flawlessly in fact).

5

thanks,

  1. I did import the node using npm
  2. Not sure how to go about writing that function in nodered. Would you have any pointers / templates to share? I'm on an extremely basic level in my coding knowledge, but very eager to learn. Thanks again
6

Here is an example (but the app is different, you will need to modify, of course):

var mysql = global.get("mysql");
var moment = global.get("moment");
var connection = mysql.createConnection({
  host: "localhost",
  user: "my_cool_user_name",
  port: "/var/run/mysqld/mysqld.sock",
  password: "my_clever_password",
  database: "my_awesome_database"
});
var newMsg = {};
var thetopic;
var clientid = "my_client_id";
var unixtime = moment().unix();
if (unixtime < 1) {
  unixtime = 9999;
}
if (msg.topic) {
  thetopic = msg.topic;
} else {
  thetopic = "TBD";
}
var thepayload;
var campaignid;
if (msg.payload) {
  thepayload = msg.payload;
  var a = thetopic.split("/");
  var cid = a[2];
  campaignid = parseInt(cid);
} else {
 thepayload  = "TBD";
}

if (campaignid > 0 && parseInt(thepayload) > -1) {
  connection.query(

    "UPDATE ad_server_campaigns set weight = " +
      thepayload +
      " WHERE campaignid = " +
      campaignid,
    function(err, results, fields) {
      console.log(err);
      console.log(results);
      connection.end();
    }
  );
}
return msg;
2 Likes
7

Thanks! Will try this out

8

Welcome.

Don't forgot to add any required files you need to settings.js , for example from the code above:

var mysql = global.get("mysql");
var moment = global.get("moment");

Have you done that before?

9

Haven't done this before. Figuring everything out as I go along. I think I'll need to go through a tutorial on how to write a javascript function to connect to mysql - to not miss any steps here.

PS: Any known drawbacks when using python instead of JS inside nodered?

10

Here ya go... an example in settings.js

   functionGlobalContext: {
        mysql:require('mysql'),
        moment:require('moment')
    },

Of course, you will need to npm these libs into node if you have not already.

Also, many will advise you, you do not need moment, but for some reason, it works better for me this way. You can just omit it (moment) if you want.

No comment on Python in Node-RED. I'm a big Python fan but only write JS in Node-RED (since it is based on node.js).

Cheers and Good Luck.

Hope this helped in some small way.

2 Likes
11

Thanks a lot! I'm sure it will, trying it out now

12

That is the user and password that you enter in the mysql node.
image

13

Looking at your question again, when it says 'replace' what it means is that have to run the command that (presumably) is on the next line, using your use and pwd in the command. The command is (I imagine) supposed to be run in the mysql command line client, it is nothing to do with node-red.

14

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.