Currently cannot support auth method mismatch!

[dong-lufei]

deno 1.19.2
MySQL Community Server 8.0.28
https://deno.land/x/mysql@v2.10.2
https://deno.land/x/oak@v10.5.1/mod.ts
跑起来报错：
error: Uncaught (in promise) Error: Currently cannot support auth method mismatch!
throw new Error("Currently cannot support auth method mismatch!");
^
at PoolConnection._connect (https://deno.land/x/mysql@v2.10.2/src/connection.ts:104:17)
at async PoolConnection.connect (https://deno.land/x/mysql@v2.10.2/src/connection.ts:148:5)
at async Client.createConnection (https://deno.land/x/mysql@v2.10.2/src/client.ts:47:5)
at async DeferredStack.creator (https://deno.land/x/mysql@v2.10.2/src/pool.ts:61:20)
at async DeferredStack.pop (https://deno.land/x/mysql@v2.10.2/src/deferred.ts:35:16)
at async ConnectionPool.pop (https://deno.land/x/mysql@v2.10.2/src/pool.ts:93:14)
at async Client.useConnection (https://deno.land/x/mysql@v2.10.2/src/client.ts:105:24)
at async Client.execute (https://deno.land/x/mysql@v2.10.2/src/client.ts:96:12)

[sant123]

Try running in your mysql instance this query:

ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'your-root-password';

Basically there are two authentication strategies and this library supports the latest one.

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

Perhaps while this gets fixed you could try using https://github.com/sail-sail/mysql2 that supports both.

This is the one that is throwing the exception:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your-root-password';

[shiyuhang0]

I search the code and get the following result

export default function auth(
  authPluginName: string,
  password: string,
  seed: Uint8Array,
) {
  switch (authPluginName) {
    case "mysql_native_password":
      return mysqlNativePassword(password, seed);

    case "caching_sha2_password":
      return cachingSha2Password(password, seed);
    default:
      throw new Error("Not supported");
  }
}

it seems the driver can support mysql_native_password? @sant123

[sant123]

Interesting, I tested again and now is working:

import { Client } from "https://deno.land/x/mysql@v2.10.2/mod.ts";

const client = await new Client().connect({
  hostname: "127.0.0.1",
  username: "root",
  db: "mydb",
  password: "set-your-password",
});

const users = await client.query(`select * from users`);

console.log(users);

create schema mydb;

use mydb;

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nombre` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `mydb`.`users` (`nombre`) VALUES ('foo');
INSERT INTO `mydb`.`users` (`nombre`) VALUES ('bar');
INSERT INTO `mydb`.`users` (`nombre`) VALUES ('baz');

docker run -e "MYSQL_ROOT_PASSWORD=set-your-password" -p 3306:3306 -d mysql:8.0.28-oracle

[sant123]

deno 1.28.1 (release, x86_64-unknown-linux-gnu)
v8 10.9.194.1
typescript 4.8.3

[mrl5]

using mysql 8, for me works with mysql_native_password and fails with caching_sha2_password.

this is actually aligned with:

• MySQL 8 incompatibilities mysqljs/mysql#1959 -> Fix MySQL 8.0.x incompatibilities mysqljs/mysql#1962
• https://stackoverflow.com/questions/50373427/node-js-cant-authenticate-to-mysql-8-0
• Host '172.18.0.1' is not allowed to connect to this MySQL server docker-library/mysql#275 (comment)

steps to reproduce

1. setup mysql 8

docker run --name mysql -d \
    -p 3306:3306  \
   -e MYSQL_ROOT_PASSWORD=change-me --restart unless-stopped \
    mysql:8

2. log in to mysql server

docker exec -it mysql bash
mysql -u root -p

3. set caching_sha2_password password

ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'change-me';

4. use this script

import { Client } from "https://deno.land/x/mysql@v2.11.0/mod.ts";


export async function main() {
    const mysql_conn = {
      host: '0.0.0.0',
      port: 3306,
      user: 'root',
      password: 'change-me',
      database: 'mysql'
    };
    mysql_conn.db = mysql_conn.database;
    mysql_conn.hostname = mysql_conn.host;
    mysql_conn.username = mysql_conn.user;

    const client = await new Client().connect(
        mysql_conn,
    );

    return (await client.execute('SHOW TABLES'));
}

console.log(await main())

actual result

INFO connecting 0.0.0.0:3306
INFO close connection
Uncaught Error: Access denied for user 'root'@'172.17.0.1' (using password: YES)
    at PoolConnection.nextPacket (https://deno.land/x/mysql@v2.11.0/src/connection.ts:216:13)
    at async PoolConnection._connect (https://deno.land/x/mysql@v2.11.0/src/connection.ts:145:23)
    at async PoolConnection.connect (https://deno.land/x/mysql@v2.11.0/src/connection.ts:179:5)
    at async Client.createConnection (https://deno.land/x/mysql@v2.11.0/src/client.ts:47:5)
    at async DeferredStack.creator (https://deno.land/x/mysql@v2.11.0/src/pool.ts:67:20)
    at async DeferredStack.pop (https://deno.land/x/mysql@v2.11.0/src/deferred.ts:35:16)
    at async ConnectionPool.pop (https://deno.land/x/mysql@v2.11.0/src/pool.ts:99:14)
    at async Client.useConnection (https://deno.land/x/mysql@v2.11.0/src/client.ts:105:24)
    at async Client.execute (https://deno.land/x/mysql@v2.11.0/src/client.ts:96:12)
    at async main (<anonymous>:15:12)

expected

it runs

workaround

in mysql docker run

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'change-me';

and then re-run the script, it will not throw that error again

[mrl5]

I actually just realized that lack of support for caching_sha2_password is mentioned in todo list in https://deno-mysql.netlify.app/

Support caching_sha2_password auth plugin (mysql8 default)

@manyuanrong are there any plans in nearest future to support it?

[lideming]

@mrl5 The docs site is outdated. It was supported. I guess recent changes broke it😢. Trying fixing...

[shiyuhang0]

@mrl5 The docs site is outdated. It was supported. I guess recent changes broke it😢. Trying fixing...

It seems you have fixed it #142.
I am curious about the reason for the bug, is it because the encrypt is wrong?