Solve the problem caused by Node.js mysql client not supporting authentication protocol
Preface
The mysql module (project address is https://github.com/mysqljs/mysql) is an open source, JavaScript-written MySQL driver that can operate MySQL in Node.js applications. However, in the process of use, there is a "ER_NOT_SUPPORTED_AUTH_MODE" problem.
This paper introduces the causes and solutions of the problem.
Error message
When I try to connect to MySQL 8 using the mysql module, the following error message appears:
D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ index.js:17throw error; ^ Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server Consider upgrading MySQL clientat Handshake.Sequence._packetToError (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ lib\ protocol\ sequences\ Sequence.js:47:14) at Handshake.ErrorPacket (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ protocol\ sequences\ Handshake.js:123:18) at Protocol._parsePacket (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib Protocol\ Protocol.js:291:23) at Parser._parsePacket (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ protocol\ Parser.js:433:10) at Parser.write (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ protocol\ Parser.js:43:10) at Protocol.write (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ Lib\ protocol\ Protocol.js:38:16) at Socket. (d:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ Connection.js:91:28) at Socket. (d:\ workspaceGithub\ nodejs-book-samples\ samples\ node_modules\ mysql\ lib\ Connection.js:525:10) at Socket.emit (events.js:196:13) at addChunk (_ stream_readable.js:290:12)-at Protocol._enqueue (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ protocol\ Protocol.js:144: 48) at Protocol.handshake (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ protocol\ Protocol.js:51:23) at Connection.connect (D:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ node_modules\ mysql\ lib\ Connection.js:119:18) at Object. (d:\ workspaceGithub\ nodejs-book-samples\ samples\ mysql-demo\ index.js:12:12) at Module._compile (internal/modules/cjs/loader.js:759:30) at Object.Module._extensions..js (internal/modules/cjs/loader.js:770:10) at Module.load (internal/modules/cjs/loader.js:628:32) at Function.Module._load (internal/modules/cjs/loader.js:555:12) at Function.Module.runMain (internal) / modules/cjs/loader.js:826:10) at internal/main/run_main_module.js:17:11
Cause of error
The reason for this error is that currently, the latest mysql module does not fully support MySQL 8's "caching_sha2_password" encryption, while "caching_sha2_password" is the default encryption in MySQL 8. Therefore, the following command is that "caching_sha2_password" encryption has been used by default, and the account and password cannot be used in the mysql module.
Mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456 query OK, 0 rows affected (0.12 sec)
Solution method
The solution is to change the password of user root and specify the encryption method that the mysql module can support:
Mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' 123456 query OK, 0 rows affected (0.12 sec)
The above statement shows that the encryption method using "mysql_native_password" is specified. This approach is supported in the mysql module.
If you run the application here, you can see the following console output information:
$node index.jsThe result is: RowDataPacket {user_id: 1, username: 'Lao Wei'}
Where "RowDataPacket {user_id: 1, username: 'Lao Wei'}" is the result of the database query.
Source code
Examples of this section can be found in https://github.com/waylau/nodejs-book-samples 's "mysql-demo" application.
The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.
