Error: ER_NOT_SUPPORTED_AUTH_MODE with auth_socket

[abou7mied]

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

[dougwilson]

What auth modes do you have configured for your server?

[abou7mied]

I'm working on local, and I didn't set any auth mode, maybe the default mode have been changed in the update of my-sql server?

Or what is the default of this mysql-driver then I set it in my config?
http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_default-auth

[abou7mied]

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 :)!

[dougwilson]

I just installed 5.17.13 on Ubuntu and was able to connect just fine without changing anything. I'm not sure how to proceed. Would it be possible to provide a packet capture of the traffic between this module and your server? Do other Node.js modules like "mysql2" work?

[sidorares]

could you console.log server hello packet? Add console.log(this) before this line -

mysql/lib/protocol/packets/HandshakeInitializationPacket.js

Line 67 in 96fdd05

};

[dougwilson]

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.

[sidorares]

@abou7mied what was the old value for plugin in mysql.user table? Yes, currently this driver only supports mysql_native_password and mysql_old_password auth methods ( mysql2 does not support mysql_old_password but allows to set AuthSwitch handler )

[abou7mied]

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

[DreadPirateShawn]

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.

[LeZuse]

#1507 (comment) worked for me. Don't forget to flush privileges; after.

[quytm2239]

#1507 (comment) worked for me, too. 👯‍♂️

and need flush privileges; after follow #1507 (comment)

[janat08]

@dougwilson Just in case this is totally happening, I think we should reopen the issue?

[dougwilson]

From my understanding there is nothing to change in the module. If you have an idea of what to change, please feel free to submit a PR :)