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

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.

CodePudding user response：

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