ER_NOT_SUPPORTED_AUTH_MODE unix_socket

[lal12]

Hello,

I cannot use socket_auth to identify via this library on my ARM device. I read several issues about this problem, without quite understanding wether this is a new feature or a bug and whether it is already implemened. The most current issue seem to be #2001 which indicates this is a new feature request.

However using socket_auth works fine on my desktop, but not on my arm board, despite running the same MariaDB Version 5.5.5-10.1.38-MariaDB. So I can only assume that my MariaDB behaves differently or the mysql.js lib behaves differently on the arm board. The mariadb on arm is self build and has non standard build options. However the native mysql utility still works with socket_auth.

So my main question probably is: What is the current status of this? It seems to be assumed that socket_auth is not yet implemented. However this cannot be since it works on my desktop.

And maybe someone even has more "specific" help for me? See below for more informations.

So the server sends its initial Handshake:

<-- HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.5.5-10.1.38-MariaDB',
  threadId: 955,
  scrambleBuff1: <Buffer 58 36 46 3b 36 3d 6c 2d>,
  filler1: <Buffer 00>,
  serverCapabilities1: 63487,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 41023,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 38 72 57 3b 62 59 7a 78 6a 2f 45 50>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

The mysql.js client replies with

00  2c 00 00 01 cf f3 06 00  00 00 00 00 21 00 00 00  |,...........!...|
10  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
20  00 00 00 00 72 6f 6f 74  00 00 77 75 74 64 62 00  |....root..wutdb.|

--> (990) ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer >,
  database: 'wutdb',
  protocol41: true }

mysql.js options:

{
  user: 'root',
  socketPath: '/tmp/mysqld.sock',
  database: 'wutdb',
  debug: true
}

The answer of the native mysqlclient is a bit longer:

00  a0 00 00 01 85 a6 3f 20  00 00 00 01 21 00 00 00  |......? ....!...|
10  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
20  00 00 00 00 72 6f 6f 74  00 00 6d 79 73 71 6c 5f  |....root..mysql_|
30  6e 61 74 69 76 65 5f 70  61 73 73 77 6f 72 64 00  |native_password.|
40  63 03 5f 6f 73 05 4c 69  6e 75 78 0c 5f 63 6c 69  |c._os.Linux._cli|
50  65 6e 74 5f 6e 61 6d 65  08 6c 69 62 6d 79 73 71  |ent_name.libmysq|
60  6c 04 5f 70 69 64 04 31  35 38 37 0f 5f 63 6c 69  |l._pid.1587._cli|
70  65 6e 74 5f 76 65 72 73  69 6f 6e 07 31 30 2e 31  |ent_version.10.1|
80  2e 33 38 09 5f 70 6c 61  74 66 6f 72 6d 03 61 72  |.38._platform.ar|
90  6d 0c 70 72 6f 67 72 61  6d 5f 6e 61 6d 65 05 6d  |m.program_name.m|
a0  79 73 71 6c                                       |ysql|
a4

[dougwilson]

Hi @lal12 thanks for the report. Your packet capture above is most helpful, but that is showing the server is asking for mysql_native_password auth type, not socket_auth. Is that the packet capture for the connection that is getting the error in the subject line? As far as I know, this module does not produce such an error, so It's really just a bit confusing all around in the report :)

Perhaps it could be helpful to show a full set up so I can reproduce the issue. I see you included the MariaDB version, but not a lot else. Can you help by including the following information as well?

https://github.com/mysqljs/mysql#debugging-and-reporting-problems

1. Version of this module you are using.
2. Version of Node.js you are using.
3. The JavaScript source code you are using that produces an error.
4. The error you are getting back, including the stack trace.
5. Add debug: true to your connection and run again and include the complete output here as well.

Thanks again for the report and I look forward to the updated information you can provide :)

[lal12]

1. Version of this module you are using.

mysql@2.17.1

2. Version of Node.js you are using. v10.15.3
3. The JavaScript source code you are using that produces an error.

   var mysql      = require('mysql');
   var connection = mysql.createConnection({
   user: 'root',
   socketPath: '/tmp/mysqld.sock',
   database: 'wutdb',
   debug: true
   });
   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();

4. The error you are getting back, including the stack trace.

   
   /data/userfiles/test.js:17
   if (error) throw error;
   ^

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client unterst�ützt das vom Server erwartete Authentifizierungsprotokoll nicht. Bitte aktualisie ren Sie Ihren MariaDB-Client at Handshake.Sequence._packetToError (/data/userfiles/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Handshake.ErrorPacket (/data/userfiles/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18) at Protocol._parsePacket (/data/userfiles/node_modules/mysql/lib/protocol/Protocol.js:291:23) at Parser._parsePacket (/data/userfiles/node_modules/mysql/lib/protocol/Parser.js:433:10) at Parser.write (/data/userfiles/node_modules/mysql/lib/protocol/Parser.js:43:10) at Protocol.write (/data/userfiles/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/data/userfiles/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/data/userfiles/node_modules/mysql/lib/Connection.js:525:10) at Socket.emit (events.js:189:13) at addChunk (_stream_readable.js:284:12)

at Protocol._enqueue (/data/userfiles/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/data/userfiles/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/data/userfiles/node_modules/mysql/lib/Connection.js:119:18)
at Object.<anonymous> (/data/userfiles/test.js:14:12)
at Module._compile (internal/modules/cjs/loader.js:701:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:712:10)
at Module.load (internal/modules/cjs/loader.js:600:32)
at tryModuleLoad (internal/modules/cjs/loader.js:539:12)
at Function.Module._load (internal/modules/cjs/loader.js:531:3)
at Function.Module.runMain (internal/modules/cjs/loader.js:754:12)

>     5. Add `debug: true` to your connection and run again and include the complete output here as well.

<-- HandshakeInitializationPacket { protocolVersion: 10, serverVersion: '5.5.5-10.1.38-MariaDB', threadId: 1593, scrambleBuff1: <Buffer 23 3f 55 6f 4c 70 6d 55>, filler1: <Buffer 00>, serverCapabilities1: 63487, serverLanguage: 33, serverStatus: 2, serverCapabilities2: 41023, scrambleLength: 21, filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>, scrambleBuff2: <Buffer 40 22 27 3d 3d 5b 34 30 31 46 4c 2b>, filler3: <Buffer 00>, pluginData: 'mysql_native_password', protocol41: true }

--> (1593) ClientAuthenticationPacket { clientFlags: 455631, maxPacketSize: 0, charsetNumber: 33, filler: undefined, user: 'root', scrambleBuff: , database: 'wutdb', protocol41: true }

<-- (1593) ErrorPacket { fieldCount: 255, errno: 1251, sqlStateMarker: '#', sqlState: '08004', message: 'Client unterst�ützt das vom Server erwartete Authentifizierungsprotokoll nicht. Bitte aktualisieren Sie Ihren MariaDB-Client' }

Below here the error output is printed (s. 4.) 

I wondered about the `mysql_native_password` part in the packet, too. But I don't see any other way for `mysql -u root` to connect (and auth) than via unix_socket. The result of `SELECT Host,User,plugin FROM mysql.user WHERE User = 'root'`:
```tsv
 "Host" "User"  "plugin"
"%" "root"  ""
"127.0.0.1" "root"  "unix_socket"
"localhost" "root"  "unix_socket"

Checking with strace -e socket mysql -u root shows that mysql -u root correctly connects via UNIX_SOCKET:

--- SIGILL {si_signo=SIGILL, si_code=ILL_ILLOPC, si_addr=0x76ba87a8} ---
--- SIGILL {si_signo=SIGILL, si_code=ILL_ILLOPC, si_addr=0x76ba8790} ---
--- SIGILL {si_signo=SIGILL, si_code=ILL_ILLOPC, si_addr=0x76ba8798} ---
--- SIGILL {si_signo=SIGILL, si_code=ILL_ILLOPC, si_addr=0x76ba87a0} ---
--- SIGILL {si_signo=SIGILL, si_code=ILL_ILLOPC, si_addr=0x76ba8788} ---
socket(AF_UNIX, SOCK_STREAM, 0)         = 3
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1840
Server version: 10.1.38-MariaDB MariaDB Server

[lal12]

Ok, I checked again why it worked on my desktop. And I was wrong there since I accendtily enabled socket_auth for another user than I used to connect with. Sorry for the "false alarm".

So I guess the answer is pretty straight forward then and it just needs to be implemented. I can do a PR. However it would be nice if you could give me a hint on how to do that or where to start. I guess the implementation should be straight forward since mysql -u root does not really seem to be aware of unix_socket auth and just sends an empty password. Because how should it know whether it just is a user without pw or one which authenticates via socket. So I guess the real difference is just some wrongly set flag 🤔.

[lal12]

After some debugging I found the solution. You actually only need to set the flag PLUGIN_AUTH, which is documented as an additional settable flag. I actually tried that before opening this issue. However this flag will never be used, since it was specifically excluded in https://github.com/mysqljs/mysql/blob/master/lib/ConnectionConfig.js#L109. So if I remove this line and add flags: ["PLUGIN_AUTH"] to my config, the socket authentification works.

[lal12]

@dougwilson I could do a PR (if it's even worth this minor change). However the question is what to do about it. The easy one would obviously be to just remove PLUGIN_AUTH from the disabled flags. Alternatively another flag prefix * could be added, which will override even the disabled flags.

[dougwilson]

Hi @la12, sorry I have been away for a bit. I haven't fully read what is above just yet, but at least for your message at-ing me: the reason why some flags are disabled are the ones that require the client to actually support it (vs others that just change MySQL server behavior). In order to even just allow turning it on, all that is required to support said flag needs to be implemented into this module first. I hope that makes sense why there are flags that are disabled and cannot be turned on. Another example of this is compression.

And just because turning it on in your specific case is not evidence to it actually working correctly :) Just like you can force on the compression flag too, and it will "work" as long as the server does not decide to actually send a compressed packet to the client :)

So really the gist is that if this doesn't work without that flag, the only realistic way to land a change to even be able to enable the flag is to also implement the support into this module that that flag is signaling to the server this module is capable of doing (that's what the client-capabilities flags like PLUGIN_AUTH do). I hope that helps.

[dougwilson]

Most likely the reason turning the flag on works for your case is that the server is never actually sending the new style of auth handshake to the client at all; instead it just refuses to interact with any client that does not support the new auth system, even if it doesn't need to use it.

[LA12]

Hi @dougwilson you probably wanted to mention @lal12 . Good luck with the issue :)

[lal12]

Most likely the reason turning the flag on works for your case is that the server is never actually sending the new style of auth handshake to the client at all; instead it just refuses to interact with any client that does not support the new auth system, even if it doesn't need to use it.

The fact that I can connect to users with a password, would contradict that. I guess it makes sense, that MariaDB wants to have the PLUGIN_AUTH flag to look into its auth plugins at all, because it does not know whether the plugin specifies a special packet, which in the case of socket_auth is not needed. But I will look into the MariaDB sources to find out.

Also it probably would not be a problem if activated since it would fail at a later point, when an unsupported packet arrives. So if it has to be especially activated by the user I don't see why this should be a problem. Even if my case should turn out to be a special case, why not allow the user to deal with it as he wishes? Obviously it would be marked as unsupported and untested...

Even the README says:

There are other flags available. They may or may not function, but are still available to specify.

And lists PLUGIN_AUTH below it.

[lal12]

As I suspected https://github.com/atcurtis/mariadb/blob/master/sql/sql_acl.cc#L8321 will ignore all plugins even if they don't require special packets. I guess you couldn't surely support all auth plugins anyway, since an auth plugin can have a server and a client lib. However the client lib obviously wouldn't work with mysqljs anyway.

So as far as I tried and understood the mariadb code, if the client sends PLUGIN_AUTH flag, the appropiate plugin will be called. This then can decide whether to authenticate on already sent data or sends some other packet. This packet then will result in an exception in mysqljs, complaining about an unsupported packet. Which as I see it is an ok behaviour, if a user manually specifies PLUGIN_AUTH

[dougwilson]

I know this is a segway, but any reason why you are not using the MariaDB client to connect to your MariaDb server? https://www.npmjs.com/package/mariadb

[dougwilson]

The reason I ask is that MariaDB extends the mysql protocol and so and of their special commands and special error messages don't work properly with this module, either.

[lal12]

I know this is a segway, but any reason why you are not using the MariaDB client to connect to your MariaDb server? https://www.npmjs.com/package/mariadb

I actually wasn't aware there is one and had a good experience with (the api of) mysql and mysql2, so not a reason to explicitly look for it.

As far as I am aware the code regarding this situation is originally from mysql, I still think this problem could occur on MySQL to, unless they had big changes there. But I will take a look at the mariadb module for my project.