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 - https://github.com/mysqljs/mysql/blob/96fdd0566b654436624e2375c7b6604b1f50f825/lib/protocol/packets/HandshakeInitializationPacket.js#L67

[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 :smile:

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]

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

[quytm2239]

https://github.com/mysqljs/mysql/issues/1507#issuecomment-242885003 worked for me, too. :dancing_men:

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

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

[janat08]

Maybe some one will add something if there's and issue to show for it.

[janat08]

or add it to install instructions...

[joe-angell]

Does mysql or any other node lib support sha256_password plugin? I'm getting this same error trying to connect to a server using this plugin.

[sidorares]

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

[mnakama]

I'm not sure that the issue is really resolved. How do we connect to mysql with auth_socket?

I'm specifically trying to get auth_socket working in place of password authentication, but I'm not sure if it's supported in this module.

[elemount]

I'll raise a PR to support pluggable auth plugin in mysqljs which is compatible with mysql2. And then add a auth_socket method can be considered.

[sidorares]

@mnakama I did a bit of research on how auth_socket works and dump notes here:

1) depends on SO_PEERCRED ( see http://welz.org.za/notes/on-peer-cred.html ) 2) afaik only for clients connected via domain socket 3) server is able to check user who created this domain socket connection and uses system user name as mysql user name

node bindings for SO_PEERCRED: https://github.com/XeCycle/get-peercred https://github.com/nathan7/peercred

looks like this is not part of libuv so requires binary module

The above is probably for server side of plugin. I have not found yet detailed description of client side of auth_socket plugin. Looks that just connecting to unix socket and responding to auth switch might be enough (but password has to be supplied somehow - not sure if it's standard mysql_native_password way of sending it)

[elemount]

@sidorares @mnakama , I've read the MySQL server code. This may be a server level auth plugin which do not need client to do something(Not sure yet). But the ER_NOT_SUPPORTED_AUTH_MODE will be always thrown when client driver do not support CLIENT_PLUGIN_AUTH , I assume that after my pull request #1776 is merged, it will be fixed automatically.

[dougwilson]

Ah, that makes sense @elemount . Yea, your PR implemented the CLIENT_PLUGIN_AUTH, so if that is what is the underlying cause, then that would fix it. Setting up a server with this plugin enabled would validate this. I'm going to rename the issue and reopen to better track this instead of it being in a closed issue :)

[mnakama]

@dougwilson , I just checked out PR #1776 and tried it. auth_socket works perfectly with this. Merging the PR would fix this issue. Thanks @elemount =)

Steps I used to test auth_socket

On the server side: Using a linux user named "client"

CREATE USER 'client'@'localhost' IDENTIFIED WITH auth_socket;
GRANT all ON *.* TO 'client'@'localhost';
FLUSH PRIVILEGES;

On the node.js client:

let sql = mysql.createPool({
    multipleStatements: true,
    charset: 'UTF8_GENERAL_CI',
    connectionLimit: 10,
    socketPath: '/run/mysqld/mysqld.sock',
    user: 'client', // this needs to match the node.js process user
    //password: (not used with auth_socket),
});

Then execute queries as usual.

@sidorares Correct, there is no need for SO_PEERCRED on the client side. No password needed, either. It's essentially the linux equivalent of the security guard who knows you by your face.

For those interested, this is the only official documentation I found: https://dev.mysql.com/doc/refman/5.7/en/socket-pluggable-authentication.html

It seems they left out the client part of the documentation completely. It says "None, see discussion", but I don't see a discussion anywhere on the page.

EDIT: It looks like I need the "user" parameter after all. It needs to match the node.js process user. Otherwise, the client tries to use "" as a username and fails.

[Badestrand]

Is there any chance this gets solved soon? As I understand this, I have to switch to another library to support logging in with the default root user? The linked pull request is in the making for 4 months already so will probably need another 6 months until merged in?

Edit: Sorry if I sound snarky, I am exhausted from bug hunting and in general I love working with mysqljs.

Edit 2: Does anyone know any node mysql library that supports this?

[mnakama]

@Badestrand Yeah, not sure what happened with the pull request. I used the pull request's commit in our main development branch, and it seems to work fine. I recommend trying it out yourself and possibly helping @elemount get the PR cleaned up. A possible alternative would be to find/use a python mysql library that is more mature.

If you want the code to be part of master branch, check out PR #1776. I think it just needs to be cleanly rebased onto master before it'll be accepted. I'd work on it myself, but I no longer have a need for it.

@dougwilson @elemount Please correct me if I'm wrong.

[tbjgod]

I use this to fix it: mysql > USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

[drewsmith]

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';

[robwhess]

I'm not sure what the status of this request is, but I thought I'd add my input. I'm getting the same error trying to connect to a MySQL 8.0 database. It looks like for MySQL 8.0, the default authentication plugin is caching_sha2_password:

https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

I've verified that my server (running from the mysql:latest Docker image) is indeed using caching_sha2_password for the root user and all newly-created users.

[dougwilson]

This is a request for support for auth_socket (as specified in the issue title), not for caching_sha2_password. The issue is clear what this is for. I'm locking this because the path forward is open a pull request with an implementation not just comment me too over and over :)