## 事象
[[deno_mysql]]を使って書いた以下のコードを実行するとAccess denied for userエラーになる。
```ts
import { Client } from "https://deno.land/x/
[email protected]/mod.ts";
const client = await new Client().connect({
hostname: "localhost",
port: 13306,
username: "user",
db: "bruno",
password: "password",
});
const result = await client.execute(`select * from ??`, ["users"]);
console.log(result);
```
```error
error: Uncaught (in promise) Error: Access denied for user 'user'@'192.168.224.1' (using password: YES)
throw new Error(error.message);
^
at PoolConnection.nextPacket (https://deno.land/x/
[email protected]/src/connection.ts:257:13)
at eventLoopTick (ext:core/01_core.js:175:7)
at async PoolConnection._connect (https://deno.land/x/
[email protected]/src/connection.ts:186:23)
at async PoolConnection.connect (https://deno.land/x/
[email protected]/src/connection.ts:220:5)
at async Client.createConnection (https://deno.land/x/
[email protected]/src/client.ts:64:5)
at async DeferredStack.creator (https://deno.land/x/
[email protected]/src/pool.ts:67:20)
at async DeferredStack.pop (https://deno.land/x/
[email protected]/src/deferred.ts:35:16)
at async ConnectionPool.pop (https://deno.land/x/
[email protected]/src/pool.ts:99:14)
at async Client.useConnection (https://deno.land/x/
[email protected]/src/client.ts:122:24)
at async Client.execute (https://deno.land/x/
[email protected]/src/client.ts:113:12)
```
### 前提条件
`docker-compose.yml`
```yaml
version: "3.1"
services:
db:
image: mysql:8
container_name: mysql-sample
restart: always
ports:
- 13306:3306
volumes:
- ./conf.d:/etc/mysql/conf.d
- ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
environment:
MYSQL_ROOT_PASSWORD: example
```
`conf.d/my.cnf`
```ini
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log
local-infile=1
[mysql]
default-character-set=utf8mb4
local-infile=1
```
`docker-entrypoint-initdb.d/init.sql`
```sql
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
CREATE DATABASE IF NOT EXISTS bruno;
USE bruno;
CREATE TABLE users (
id int NOT NULL,
name varchar(255) NOT NULL
) ENGINE=InnoDB;
INSERT INTO users VALUES (1,'one'),(2,'two'),(3,'three');
```
### 再現手順
1. `mysql-sample` コンテナをボリュームごと削除する
2. `mysql-sample` を起動する
3. `deno run --allow-net main.ts` を実行
### 備考
mysqlコマンドや[[DBeaver]]から一度ログインしたユーザーとしてであれば、上記コードでも正常に動作する。
## 原因
バグっぽい。
<div class="link-card">
<div class="link-card-header">
<img src="https://github.githubassets.com/favicons/favicon.svg" class="link-card-site-icon"/>
<span class="link-card-site-name">GitHub</span>
</div>
<div class="link-card-body">
<div class="link-card-content">
<p class="link-card-title">MySQL - access denied until I connect via Workbench or CLI · Issue #171 · denodrivers/mysql</p>
<p class="link-card-description">I cannot connect to MySQL 8.x server via this module until I con ... </p>
</div>
<img src="https://opengraph.githubassets.com/be18e587722ef2dfd94256dc17615555b231183b5f971707943d8350a902a6f3/denodrivers/mysql/issues/171" class="link-card-image" />
</div>
<a href="https://github.com/denodrivers/mysql/issues/171"></a>
</div>
## 回避方法
該当ユーザーの[[Authentication Method (MySQL)|Authentication Method]]を[[mysql_native_password]]にすると回避できるらしい。
`my.cnf`の変更で[[mysql_native_password]]を利用できるようにする。
```diff
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log
local-infile=1
+ mysql_native_password=ON
```
ユーザー追加時に指定。
```diff
- CREATE USER 'user'@'%' IDENTIFIED BY 'password';
+ CREATE USER 'user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password';
```
これで `deno run --allow-net main.ts` を実行すると結果が返却される。