ER_ACCESS_DENIED_ERROR: Access denied on nodeJS

Question

MySQL is connecting to a different unknown IP when trying to connect. Code Below:

.env

MYSQL_HOST=domain.example.com
MYSQL_USER=****
MYSQL_PASSWORD=****
MYSQL_DB=****
MYSQL_PORT=3306

connection.js

const mysql = require("mysql");

exports.pool = mysql.createPool({
  connectionLimit: 10,
  host: process.env.MYSQL_HOST,
  port: process.env.MYSQL_PORT,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
});

controller/cron

pool.getConnection((err, connection) => {
        if (err) {
          console.log("ERROR: ", err);
        } else { ... insert data .... }
});

With this i'm getting error:

try connecting to remote mysql...
Error:  Error: ER_ACCESS_DENIED_ERROR: Access denied for user '****'@'112.200.198.66' (using password: YES)

Tested in MySQL Workbench and successfully connected. I'm just curious where the IP came from when the public IP of that domain is different. Also tried using the IP instead to connect and still giving me the same error.

Upon checking the IP '112.200.198.66' is my public IP. I think the code is using localhost as default. is there anyway i can set the host to a remote IP by using the domain provided to me — Ralph Stephen Bernal Jularbal, Dec 07 '21 at 12:59
Have you tried connecting through a simple createConnection first rather than using a connection pool ? Is it required by the way ? — Hybris95, Dec 07 '21 at 13:17
How do you recover the "pool" variable in your "controller/cron" ? Are you sure the pool variable is the one that you initialized before ? Is the user the one you typed on your pool initialization ? — Hybris95, Dec 07 '21 at 13:19
@Hybris95 I import the pool when my controller needs it. it works when I use my Localhost database. Now in production, it is required to use a remote server as it works as a central server for different POS systems for sync purposes. — Ralph Stephen Bernal Jularbal, Dec 07 '21 at 13:22
Have you tried hard-coding the pool for simple tests ? To make sure it's not a matter of transferring parameters from the environment file or when you transfer pool from connection.js to controller. — Hybris95, Dec 07 '21 at 13:23
Just checked using createConnection and not working still. its still trying to connect locally. Double checked all connection strings it really uses the remote host. but when connecting its trying to connect to my localhost — Ralph Stephen Bernal Jularbal, Dec 07 '21 at 13:24
MySQL authenticates based on the client host - have you verified that you can connect to the MySQL server using those credentials from the machine that you're running the Node server on? — Gus Hogg-Blake, Dec 07 '21 at 13:24
yup I tried to Hardcode it first for troubleshooting. and when i got the error it still says that its trying to connect to my ipv4 address. not the ip address or domain i provided — Ralph Stephen Bernal Jularbal, Dec 07 '21 at 13:25
That is, if the user you're logging into MySQL as is defined as 'user'@'localhost' and you try to log in as 'user' from another machine, it won't let you log in — Gus Hogg-Blake, Dec 07 '21 at 13:25
To allow logging into a MySQL server from machines other than the one the MySQL process is running on, the user has to be defined as 'user'@'*' (wildcard) or as the specific public IP of the machine you want to log in from — Gus Hogg-Blake, Dec 07 '21 at 13:27
To clarify, it's not trying to connect *to* your ipv4 address, it's complaining that it won't accept connections *from* that address. The error is coming from MySQL itself, not the Node mysql driver — Gus Hogg-Blake, Dec 07 '21 at 13:29
Well, using the same machine i tried to login via MySQL workbench and i can connect/insert/select. appreciate the help! — Ralph Stephen Bernal Jularbal, Dec 07 '21 at 13:33
Ah, this could be to do with the auth plugin used -- I think the user has to be defined as `IDENTIFIED WITH mysql_native_password` to work with the Node driver — Gus Hogg-Blake, Dec 07 '21 at 13:44
Had a similar issue before and the top answer on this question sorted it for me: https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost — Gus Hogg-Blake, Dec 07 '21 at 13:45

score 0 · Answer 1 · answered Dec 07 '21 at 13:52

0

If you're using MySQL 8 the issue could be that the Node mysql driver uses a different authentication plugin - see Node.js can't authenticate to MySQL 8.0

answered Dec 07 '21 at 13:52

Gus Hogg-Blake

2,393
2
21
31

ER_ACCESS_DENIED_ERROR: Access denied on nodeJS

1 Answers1