7

Setup

  • Expressjs (Node) running in at localhost:3000 on a Windows 10 PC.
  • MySQL ran in a Docker container at 192.168.99.100:3306 via docker-machine.

Problem

When trying to connect server to MySQL using:

const connection = mysql.createConnection({
  host: '192.168.99.100',
  user: 'root',
  password: 'foo123',
  database: 'foo_db'
});

Getting:

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgr ading MySQL client

Checked many answer here at StackOverflow. Most of them use (example):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

However I get:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass'; Query OK, 0 rows affected (0.06 sec)

And the server still doesn't want to connect. Using MySQL 5.7 works fine.

Any ideas how to fix this for MySQL 8?

ADDED:

Just to be crystal clear:

After connecting to MySQL, running:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'foo123';
FLUSH PRIVILEGES;

Restarting Node.js and MySQL container and nothing happens.

0leg
  • 13,464
  • 16
  • 70
  • 94
  • 1
    `FLUSH PRIVILEGES;` ? – Mihai Oct 02 '18 at 14:12
  • Tried that. And also tried adding new user. – 0leg Oct 02 '18 at 14:15
  • This is basically a FAQ at this point. `update mysql.user set plugin = 'mysql_native_password' where user = 'root'; flush privileges;` https://stackoverflow.com/questions/2101694/how-to-set-root-password-to-null/36234358#36234358 – dossy Oct 02 '18 at 19:34
  • @dossy setting plugin like this seem to do the trick - `mysql_native_password` is set for both of my *root* users. However after that I am never allowed to connect to MySQL: *ERROR 1130 (HY000): Host '172.17.0.3' is not allowed to connect to this MySQL server*. – 0leg Oct 04 '18 at 11:12
  • Going to just roll back to 5.7 until the dev-team fixes this properly. Don't want to use hacks for things that suppose to work right off the bat. – 0leg Oct 04 '18 at 11:13
  • 1
    Did you create the user `root@'172.17.0.3'` or `root@'%'`? Otherwise, I think the default is `root@'localhost'` and if you're not running your code inside the docker container that's running MySQL, you're not connecting from localhost. – dossy Oct 04 '18 at 13:23

1 Answers1

7

I hope you are not using literally the value 'MyNewPass', but it surely looks like it. That's supposed to be an example placeholder.

In your case you would have to do:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'foo123';
ruiquelhas
  • 1,905
  • 1
  • 17
  • 17
  • Unfortunately, have tried `foo123` as well. I wonder if the problem can be related to the fact that I am running MySQL from inside a Docker container. – 0leg Oct 02 '18 at 19:00
  • Updated my description. If this doesn't help might just go for https://www.npmjs.com/package/@mysql/xdevapi for production. – 0leg Oct 02 '18 at 19:15
  • The important part here is the `WITH mysql_native_password`. – dossy Oct 02 '18 at 19:36
  • 1
    `SELECT plugin FROM mysql.user WHERE User = 'root';` what does this tell you? – ruiquelhas Oct 03 '18 at 14:31
  • @ruiquelhas it returns: `+-----------------------+ | plugin | +-----------------------+ | caching_sha2_password | | mysql_native_password | +-----------------------+ 2 rows in set (0.00 sec)` – 0leg Oct 04 '18 at 11:09