## 事象 [[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` を実行すると結果が返却される。