Mysql: Err access denied, not sure what else to try

[travis5491811]

Hello, I'm using mysql in my node app and I"m not sure how to get past this error:

code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlMessage:
   'Access denied for user \'magento\'@\'me.localtestdomain\' (using password: YES)',
  sqlState: '28000',
  fatal: true

On the target machine (remote machine), I'm able to connect using mysql workbench without issues. I can even connect using mysql with the port being a ssh-tunnel no problem. But i get that error above when i try to connect straight like I'm doing in mysql workbench. Given i can connect using mysql workbench without ssh, the machine is listening on all interfaces. I have the config set to bind 0.0.0.0.

As root if i run mysql> SHOW GRANTS FOR 'magento'@'%';, i get:

| Grants for magento@%                                           |
+----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'magento'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `database`.`magento` TO 'magento'@'%'  |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

Not sure how to remove the second line, but the first line should be enough to get me back working (unless i'm wrong). I've ran mysql> FLUSH PRIVILEGES;, manually restarted mysql with systemctl restart mysql.service and even rebooted the remote server. I'm still unable to get this module working for a direct connection.

Partial script

const connectionParameters = () => {    
  return mysql.createConnection({
    host: <remote ip>,
    port: 3306,
    user: 'magento',
    password: 'password',
    database: 'magento'
  });
};

let connection = connectionParameters();
// connection.connect();
connection.connect(function (err) {
  if (err) {
    console.log(err);
    console.log('Error', err.code) // 'ECONNREFUSED'
    console.log('Error', err.sqlMessage)
    console.log'Fatal', err.fatal)
  }
})

let query = 'SELECT * FROM core_config_data';
connection.query(query, function (error, results, fields) {
  if (error) return reject(error);
  console.log(JSON.stringify(results, null, 2))
});
connection.end();

It's Ubuntu 18.04 LTS, ufw has allow on port 3306. I'm lost. Looked at similar issues and the people that opened the tickets often didn't reply or didn't give the information requested while you all were trying to help. Hopefully you will be able t help me. I've provided details above and can provide more.

Any ideas on what to do?

[dougwilson]

Hi @travis5491811 sorry you're having trouble. So I just waned to clarify a discrepancy I noticed in your post, as the difference will result in very different troubleshooting steps:

The top post has ER_ACCESS_DENIED_ERROR as the error code, but I also see in your code snippet it seems to indicate that the code coming back is ECONNREFUSED. At least in the current state, can you confirm which code you're getting at this time?

[travis5491811]

Hello @dougwilson, The error that i'm getting is in the section right after "get past this error..."

The following line in the _code snippet_ is just a paste from the "error handling" section of your docs. I just modified it to also say "Error" and left the original // 'ECONNREFUSED' comment :

console.log('Error', err.code) // 'ECONNREFUSED'

[travis5491811]

Here is a simpler partial script that generates that same connection refused.

const mysql = require('mysql');

var connection = mysql.createConnection({
  host     : '192.168.8.151',
  port: 3306,
  user     : 'magento',
  password : 'magento',
  database : 'magento'
});

connection.connect();

connection.query('SELECT * FROM core_config_data', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});

connection.end();

Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'magento'@'me.localtestdomain' (using password: YES)

But with the same connection details I can connect using mysql workbench. And with the same connection details but using this module and change host to 'localhost' and port to a local ssh-tunnel port, i can connect. Original post still has most detail.

[dougwilson]

Hi @travis5491811 thanks for the clarification. Your now most recent comment has me confused again, I'm sorry, and I just want to make sure we go down the right path as to what error we're going to diagnose. You said

generates that same connection refused

But then you have

ER_ACCESS_DENIED_ERROR: Access denied for user 'magento'@'me.localtestdomain' (using password: YES)

Are you getting connection refused or access deined?

[travis5491811]

Hello @dougwilson, i never said i was getting connection refused.

[dougwilson]

Sorry, I swear that is exactly what the first sentence says in https://github.com/mysqljs/mysql/issues/2175#issuecomment-461037036. Perhaps can you clarify what that means if it's not connection refused?

Here is a simpler partial script that generates that same connection refused.

[travis5491811]

Sorry, missedwording in text while trying to answer your question about the original post. Still not sure how this is still confusing given my post has the exact error message highlighted and prefaced with "get past this error:" which includes the sql error code and the original post is accurate.

Lets start over, ignore all other messages.

I'm trying to use the driver. I have this error:

ER_ACCESS_DENIED_ERROR: Access denied for user 'magento'@'me.localtestdomain' (using password: YES)

[travis5491811]

@travis5491811, that's strange. can you login into the server as root and deleting the user from mysql, re-create them, grant privileges and flush privileges?

[travis5491811]

Umm, that worked. Not sure why but ok.