ããã¯ããªã«ãããããŠæžãããã®ïŒ
MySQL 8.0ã«ãªã£ãŠãããã©ã«ãã®èªèšŒæ¹åŒãcaching_sha2_password
ããmysql_native_password
ã«å€æŽãããŸããã
For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)
Node.jsã§MySQLã«æ¥ç¶ããã«ã¯mysqlãæåã ãšæãã®ã§ãããmysqlã§ã¯caching_sha2_password
ãèªèšŒæ¹åŒã«ãªã£ãŠããå Žåã¯
æ¥ç¶ã§ããŸããã
mysql2ã§ããã°å€§äžå€«ãªã®ã§ãããä»åã¡ãããšèŠãŠããããšã«ããŸããã
- GitHub - mysqljs/mysql: A pure node.js JavaScript Client implementing the MySQL protocol.
- GitHub - sidorares/node-mysql2: :zap: fast mysqljs/mysql compatible mysql driver for node.js
MySQL 8.0ã®ããã©ã«ãã®èªèšŒæ¹åŒãšNode.jsã®MySQLãã©ã€ããŒ
MySQL 8.0.4ã®ãªãªãŒã¹ã®ãšãããããã©ã«ãã®èªèšŒæ¹åŒãcaching_sha2_password
ããmysql_native_password
ã«å€æŽãããŸããã
For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)
ããã¯ãèªèšŒãã©ã°ã€ã³ã®è©±ã«ãªããŸããã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1 認証プラグイン
å
·äœçã«ã¯ãMySQLã®ãµãŒããŒã·ã¹ãã å€æ°default_authentication_plugin
ã®ããã©ã«ãå€ãcaching_sha2_password
ã«ãªã£ããšãã
å€æŽã§ãã
ãµãŒããŒã·ã¹ãã å€æ° / default_authentication_plugin
caching_sha2_password
èªäœããMySQL 8.0ã§è¿œå ããããã®ã§ããã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.2 SHA-2 プラガブル認証のキャッシュ
以åã¯ãã¡ãïŒmysql_native_password
ïŒã«ãªããŸãã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.1 ネイティブプラガブル認証
ãã®ãããcaching_sha2_password
ã«å¯Ÿå¿ããŠããªãã¯ã©ã€ã¢ã³ãã䜿çšããå Žåã¯ããã©ã«ãã®èªèšŒæ¹åŒãmysql_native_password
ãšããã
default_authentication_plugin = mysql_native_password
ãŠãŒã¶ãŒäœææã«mysql_native_password
ãæå®ããŸãã
mysql> create user [username] identified with mysql_native_password by '[password]';
ã§ãNode.jsããæ¥ç¶ããéã«ãã䜿ãmysqlã¯ã©ããªã£ãŠããããšãããšãcaching_sha2_password
ã«ã¯å¯Ÿå¿ããŠããªãããäžèšã®ããããã®
察å¿ãè¡ããmysql_native_password
ã«åãæ¿ããå¿
èŠããããŸãã
GitHub - mysqljs/mysql: A pure node.js JavaScript Client implementing the MySQL protocol.
issueããªãŒãã³ã®ãŸãŸã§ãã
MySQL 8 incompatibilities · Issue #1959 · mysqljs/mysql · GitHub
以åã«ãè¿ãããšããã¡ãã®ãšã³ããªãŒå ã§æžããããšããããŸãã
Promise-mysqlで、Node.jsからMySQLにアクセスする - CLOVER🍀
mysql2ã¯ã©ãããšãããšãcaching_sha2_password
ã«å¯Ÿå¿ããŠããŸãã
GitHub - sidorares/node-mysql2: :zap: fast mysqljs/mysql compatible mysql driver for node.js
ããã§å ¥ã£ãã¿ããã§ããã
Mysql 8 fixes by sidorares · Pull Request #1021 · sidorares/node-mysql2 · GitHub
ããããmysql2èªäœãmysqlã®éçºããŒã ãšå
±åã§éçºããŠããããšãšãmysqlãšäž»èŠæ©èœãªäºææ§ã¯ãããããªã®ã§ä»åŸã¯mysql2ã䜿ãã¹ã
ãªã®ã§ãããã
MySQL2 team is working together with mysqljs/mysql team to factor out shared code and move it under mysqljs organisation.
MySQL2 is mostly API compatible with mysqljs and supports majority of features.
ã§ã¯ãã¡ãã£ãšç¢ºèªããŠã¿ãããšæããŸãã
ç°å¢
ä»åã®ç°å¢ã¯ããã¡ãã
$ node --version v16.16.0 $ npm --version 8.11.0
MySQLã¯ãã¡ãã172.17.0.2ã§åäœããŠãããã®ãšããŸãã
$ mysql --version mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
MySQLãµãŒããŒã®èªèšŒæ¹åŒã¯ãããã©ã«ãã®caching_sha2_password
ãšããŸãã
mysql> show variables where variable_name = 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.02 sec)
æºåãšãé¡
MySQLã«å¯ŸããŠã以äžã®ããã«ããŒã¿ããŒã¹ãš2çš®é¡ã®ãŠãŒã¶ãŒãäœæããŸãã
-- ããŒã¿ããŒã¹äœæ mysql> create database example; -- MySQL 8ã®ããã©ã«ãã®èªèšŒæ¹åŒïŒcaching_sha2_passwordïŒã®ãŠãŒã¶ãŒ mysql> create user user_sha2_auth@localhost identified by 'password'; mysql> create user user_sha2_auth@'%' identified by 'password'; mysql> grant all privileges on example.* to user_sha2_auth@localhost; mysql> grant all privileges on example.* to user_sha2_auth@'%'; -- MySQL 8以åã®èªèšŒæ¹åŒïŒmysql_native_passwordïŒã®ãŠãŒã¶ãŒ mysql> create user user_native_auth@localhost identified with mysql_native_password by 'password'; mysql> create user user_native_auth@'%' identified with mysql_native_password by 'password'; mysql> grant all privileges on example.* to user_native_auth@localhost; mysql> grant all privileges on example.* to user_native_auth@'%';
ãŠãŒã¶ãŒã¯ãã²ãšã€ã¯ããã©ã«ãã®èªèšŒæ¹åŒïŒcaching_sha2_password
ïŒãããã²ãšã€ã¯mysql_native_password
ã䜿ã£ããã®ã§ããã
ãããã®ãŠãŒã¶ãŒã«å¯ŸããŠãmysqlããã³mysql2ããæ¥ç¶ããŠã¿ãããšæããŸãã
確èªã¯ãã¹ãã³ãŒãã§è¡ããTypeScriptã§æžãããšã«ããŸãã
Node.jsïŒTypeScriptã®ãããžã§ã¯ããäœæ
ãŸãã¯Node.jsãããžã§ã¯ããäœæããŸãã
$ npm init -y $ npm i -D typescript $ npm i -D prettier $ npm i -D @types/node@v16 $ npm i -D jest @types/jest $ npm i -D esbuild esbuild-jest $ mkdir src test
ãã®æç¹ã§ã®äŸåé¢ä¿ã¯ããããªæãã
"devDependencies": { "@types/jest": "^28.1.6", "@types/node": "^16.11.48", "esbuild": "^0.15.2", "esbuild-jest": "^0.5.0", "jest": "^28.1.3", "prettier": "^2.7.1", "typescript": "^4.7.4" },
scripts
ã¯ãããªæãã§çšæããŸããã
"scripts": { "build": "tsc --project .", "build:watch": "tsc --project . --watch", "typecheck": "tsc --project ./tsconfig.typecheck.json", "typecheck:watch": "tsc --project ./tsconfig.typecheck.json --watch", "test": "jest", "format": "prettier --write src test" },
åçš®èšå®ãã¡ã€ã«ã
tsconfig.json
{ "compilerOptions": { "target": "esnext", "module": "commonjs", "lib": ["esnext"], "baseUrl": "./src", "outDir": "dist", "strict": true, "forceConsistentCasingInFileNames": true, "noFallthroughCasesInSwitch": true, "noImplicitOverride": true, "noImplicitReturns": true, "noPropertyAccessFromIndexSignature": true, "esModuleInterop": true }, "include": [ "src" ] }
tsconfig.typecheck.json
{ "extends": "./tsconfig", "compilerOptions": { "baseUrl": "./", "noEmit": true }, "include": [ "src", "test" ] }
.prettierrc.json
{ "singleQuote": true }
jest.config.js
module.exports = { testEnvironment: 'node', transform: { "^.+\\.tsx?$": "esbuild-jest" } };
mysqlãã€ã³ã¹ããŒã«ããŸããasync
ãawait
ã䜿ãããã£ãã®ã§ãPromise-mysqlãã€ã³ã¹ããŒã«ããŠãããŸãã
$ npm i mysql promise-mysql
mysql2ãã€ã³ã¹ããŒã«ã
$ npm i mysql2
åå®çŸ©ãã€ã³ã¹ããŒã«ã
$ npm i -D @types/mysql
æçµçã«ãäŸåé¢ä¿ã¯ãããªããŸããã
"devDependencies": { "@types/jest": "^28.1.6", "@types/mysql": "^2.15.21", "@types/node": "^16.11.48", "esbuild": "^0.15.2", "esbuild-jest": "^0.5.0", "jest": "^28.1.3", "prettier": "^2.7.1", "typescript": "^4.7.4" }, "dependencies": { "mysql": "^2.18.1", "mysql2": "^2.3.3", "promise-mysql": "^5.2.0" }
ãã¹ãã³ãŒããæžããŠç¢ºèªãã
ããšã¯ããã¹ãã³ãŒããæžããŠç¢ºèªããã ãã§ããã
mysqlã
test/mysql-auth.test.ts
import mysql from 'promise-mysql'; test('connect, caching_sha2_password authentication user, failure', async () => { try { await mysql.createConnection({ host: '172.17.0.2', port: 3306, database: 'example', user: 'user_sha2_auth', password: 'password', }); } catch (e) { const error = e as Error; expect(error.message).toBe( 'ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client' ); } }); test('connect, mysql_native_password authentication user', async () => { const connection = await mysql.createConnection({ host: '172.17.0.2', port: 3306, database: 'example', user: 'user_native_auth', password: 'password', }); try { const [rows, field] = await connection.query('select 1 as result'); expect(rows).toEqual({ result: 1 }); } finally { await connection.end(); } });
mysqlã®å Žåãcaching_sha2_password
ãèªèšŒæ¹åŒïŒãšãããããã©ã«ãïŒã«ããŠãããŠãŒã¶ãŒã«ã¯æ¥ç¶ã§ããŠããŸããã
èªèšŒæ¹åŒãmysql_native_password
ãšããŠãããŠãŒã¶ãŒãžã¯æ¥ç¶ã§ããŠããŸãã
mysql2ã
test/mysql2-auth.test.ts
import mysql2 from 'mysql2/promise'; test('connect, user_sha2_auth authentication user', async () => { const connection = await mysql2.createConnection({ host: '172.17.0.2', port: 3306, database: 'example', user: 'user_sha2_auth', password: 'password', }); try { const [rows, fields] = await connection.execute('select 1 as result'); expect(rows).toEqual([{ result: 1 }]); } finally { connection.end(); } }); test('connect, mysql_native_password authentication user', async () => { const connection = await mysql2.createConnection({ host: '172.17.0.2', port: 3306, database: 'example', user: 'user_native_auth', password: 'password', }); try { const [rows, fields] = await connection.execute('select 1 as result'); expect(rows).toEqual([{ result: 1 }]); } finally { connection.end(); } });
mysql2ã®å Žåã¯ãèªèšŒæ¹åŒãcaching_sha2_password
ã§ãã£ãŠãmysql_native_password
ã§ãã£ãŠãæ¥ç¶ã§ããŸãã
ããã§ãåäœç¢ºèªã§ããŸããããšã
ãŸãšã
mysqlãšmysql2ã®2ã€ã§ãMySQL 8.0ã®ããã©ã«ãã®èªèšŒæ¹åŒã§ããcaching_sha2_password
ã«å¯Ÿå¿ããŠãããèŠãŠã¿ãŸããã
察å¿ããŠããã®ã¯mysql2ã®ã¿ã§ãä»åŸã®ããšãèãããšmysqlãããmysql2ã䜿ã£ãŠãã£ãæ¹ãããããã§ããã