mysql: Error: ER_NOT_SUPPORTED_AUTH_MODE with auth_socket

Yesterday I upgraded my ubuntu distribution an MySql Server was updated too. But when I connect I get this error Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

My connection code:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test_db',
  // insecureAuth : true,
});

connection.query('SELECT * FROM users', function(err, rows, fields) {
  if (err) throw err;
});

I tried to set insecureAuth: true but no changes

About this issue

Original URL
State: open
Created 8 years ago
Reactions: 6
Comments: 29 (12 by maintainers)

Links to this issue

Most upvoted comments

Problem solved!

use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';

http://stackoverflow.com/a/36234358/1431224

Changing plugin to mysql_native_password solved the problem 😃!

+38

abou7mied on Aug 27, 2016

https://github.com/mysqljs/mysql/issues/1507#issuecomment-242885003 worked for me. Don’t forget to flush privileges; after.

+11

LeZuse on Jan 10, 2017

https://github.com/mysqljs/mysql/issues/1507#issuecomment-242885003 worked for me, too. 👯‍♂️

and need flush privileges; after follow https://github.com/mysqljs/mysql/issues/1507#issuecomment-271690367

quytm2239 on Apr 26, 2017

@joe-angell relatively easy to add with mysql2 and authSwitchHandler - see https://github.com/sidorares/node-mysql2/blob/master/documentation/Authentication-Switch.md

sidorares on Jun 29, 2017

Actually I didn’t try to figure out what was the old value, I just liked the part when I changed the plugin then it worked 😄

Thanks all guys for your concern

abou7mied on Aug 27, 2016

This one worked for me:

docker exec -it YOUR_CONTAINER mysql -u root -p
Enter password:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '{your password}';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{your password}';
SELECT plugin FROM mysql.user WHERE User = 'root';

drewsmith on Apr 29, 2018

Just ran into the same issue.

The root user by default (if you didn’t set any password during installation) appears to be:

mysql> select Host,User,plugin,authentication_string from user where User='root'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
               plugin: auth_socket
authentication_string: 
1 row in set (0.00 sec)

In my case, I solved the problem by fixing my nodejs code to stop using root user to connect.

DreadPirateShawn on Nov 17, 2016

Haha, we all replied at the same time 😃 I’m not sure why mine worked out of the box, but glad to get this figured out.

dougwilson on Aug 27, 2016