MySQL Node.js module: Unable to create connection pool. Error: Access denied for user

Question

I am trying to create a connection pool using:

  mysql.createPool(dbConnOptions)

My dbConnOptions object is:

                var dbConnOptions = {
                    host: '192.168.5.11',
                    port:  3306, 
                    user: 'johnDoe', 
                    connectionLimit: 10,
                    charset: "utf8mb4",
                    database: 'myDatabase', 
                    password: 'myPassword',
                    multipleStatements: true
                };

I am on a Ubuntu server with /etc/hostname having an entry set to 'myHostname' I have created a MySql user as

         create user 'johnDoe'@'localhost' identified by 'myPassword';
         grant all privileges on myDatabase.* to 'johnDoe'@'localhost';

The following error is reported:

  Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'johnDoe'@'myHostname' (using password: YES)

So I tried the following:

 grant all privileges on myDatabase.* to 'johnDoe'@'%';

That was rejected with this error:

ERROR 1133 (42000): Can't find any matching row in the user table

Any suggestion would be greatly appreciated. It has been frustrating.

You have created a user johndoe@localhost but you try to access the db with johndoe@myhost and there is no user johndoe@% so you can't grant any privileges to that user. johndoe@host1 and johndoe@host2 are two totally different users (eventhough they may share the same username) with different priviledges. — derpirscher
– derpirscher, Commented Jul 1, 2022 at 15:00
@derpirscher I realized your obvious conclusion but if I create a user johnDoe@myhostname and grant permissions and then later if the entry in /etc/hostname changes, I will have to run another SQL script to grant privileges. I thought that 'localhost' is meant to handle this situation no matter what the host is named. — Sunny
– Sunny, Commented Jul 1, 2022 at 15:05
then you should use 127.0.0.1 or localhost as host ... Because if you are using a custom hostname or the actual IP address, you are not connecting via the loopback device and thus, are not identified as @localhost — derpirscher
– derpirscher, Commented Jul 1, 2022 at 15:06
@derpirscher That did it. I have another problem elsewhere. Not sure if it is related to this issue. Will try and comment later. In the meanwhile, can you submit yoiur comment as an answer? I will accept it as it resolved my main question and certainly was informative (and will be to others). — Sunny
– Sunny, Commented Jul 1, 2022 at 16:44

derpirscher · Accepted Answer · 2022-07-02 09:45:18Z

2

You have created a user johndoe@localhost but you try to access the db with johndoe@myhost and there is no user johndoe@% so you can't grant any privileges to that user.

johndoe@host1 and johndoe@host2 are two totally different users (eventhough they may share the same username) with different priviledges.

And if you want to use the privileges for @localhost you should use 127.0.0.1 or localhost as host ... Because if you are using a custom hostname or the actual IP address, you are not connecting via the loopback device and thus, are not identified as @localhost

answered Jul 2, 2022 at 9:45

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Node.js module: Unable to create connection pool. Error: Access denied for user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related