0

For testing/debug purposes I recently set my root mysql password to root.

And I can access my mysql server from the command line like so:

$ mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.12 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To be sure, I get access denied if I try to use an empty password

$ mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

OK, so the password is now root right?

Well, I'm trying to use the mysql package (Node) and they provide a simple test file. When I use it like so I get access denied:

### FILE `test.js`
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'root',
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();
### end of `test.js`

$ node test.js
ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)

The reason I'm posting this is that if I just change the password above to an empty string I get the expected output:

$ node test.js
The solution is:  2

How can this be?

Details:

  • mysql Ver 8.0.12 for osx10.13 on x86_64 (Homebrew)
  • Node v9.10.1
  • mysql package version: 2.16
KCE
  • 1,159
  • 8
  • 25
  • Are you sure you're connecting to the same database from the command line? What's in the `host` setting in `.my.cnf`? – Barmar Sep 26 '18 at 21:37
  • Hmm, I'm not sure about that. From the commandline I can use `show databases;` so I'm not connecting to any one in particular. For Node, I left the database field blank for that reason. As for .my.cnf, I can't find one in any of the locations (https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location) I only found this: `/etc/my.cnf.conf` and I don't see a host setting. – KCE Sep 27 '18 at 06:20
  • Which command did you use to change `root`'s password? – ruiquelhas Sep 27 '18 at 08:35
  • I believe I used `mysql_secure_installation` once I got some of my other problems resolved (I ended up reinstalling / upgrading it with homebrew when `mysql_secure_installation` failed on my first attempt. Though at one point I used the init.txt method (https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html) .. but now I'm not sure if that was before or after the reinstall, perhaps I should go that route again? – KCE Sep 27 '18 at 09:11
  • I should add, I can use `mysqladmin -u root -p password root` and type in `root` at the prompt with no issue. – KCE Sep 27 '18 at 09:11
  • It seems like there must be a `~/.my.cnf` file, because when you run `mysql` without the `-p` option it still says `using password: YES`. That implies that it's getting the password from a configuration file, but the password there is incorrect (maybe it's the password for a different account than `root`). – Barmar Sep 27 '18 at 15:32
  • I see what you mean, but I haven't been able to find any mention of a password in any config files. I did find a couple so that was new. I'm going to dig into the node package and see if I can see what's different about the environment it's calling from. – KCE Sep 28 '18 at 13:42

0 Answers0