Vscode-sqltools: cannot connect to local mysql v8

[ntt2k]

Issue Type

• [*] Bug
• [ ] Enhancement
• [*] Feature Request
• [ ] Question
• [ ] Other

Prerequisites (For bugfixes)

• [*] Are you running the latest version?
• [*] Did you check the logs?
• [*] Did you check the Setup?

Versions

• OS: MacOS latest

• SGDB: MySQL v8

  "sqltools.connections": [
  {
  "name": "testdb",
  "server": "127.0.0.1",
  "dialect": "MySQL",
  "port": 3306,
  "database": "testdb",
  "username": "testuser",
  "askForPassword": false,
  "password": "testpassword",
  "connectionTimeout": 25
  }
  ]

Debugger listening on ws://127.0.0.1:6010/15bca7bb-6909-492c-a1ea-b2bc356343bd
For help see https://nodejs.org/en/docs/inspector
Defining data for /api/status
Defining data for /api/create-connection
SQLTools http server is listening on port 62326
Request came: GET /
Response: 200 index.html
Request came: GET /js/vendors.js
Request came: GET /js/app.js
Request came: GET /css/app.css
Response: 200 /js/vendors.js
Response: 200 /js/app.js
Response: 200 /css/app.css
Request came: POST /api/create-connection
Query error: { Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Packet.asError (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packets/packet.js:713:13)
at ClientHandshake.Command.execute (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/commands/command.js:28:22)
at Connection.handlePacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:515:28)
at PacketParser.onPacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket. (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlState: '08004',
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client' }
Error while preparing columns completions { Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Packet.asError (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packets/packet.js:713:13)
at ClientHandshake.Command.execute (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/commands/command.js:28:22)
at Connection.handlePacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:515:28)
at PacketParser.onPacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket. (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlState: '08004',
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client' }
Error while preparing columns completions { Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Packet.asError (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packets/packet.js:713:13)
at ClientHandshake.Command.execute (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/commands/command.js:28:22)
at Connection.handlePacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:515:28)
at PacketParser.onPacket (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket. (/Users/ntt2k/.vscode/extensions/mtxr.sqltools-0.15.0/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlState: '08004',
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client' }

[mtxr]

Actually I can't fix this issue. Both mysql node libs (https://github.com/mysqljs/mysql/pull/1962) are not supporting this authentication.

I suggest you to try this SO answer for now:
https://stackoverflow.com/a/51918364

Let me know if it worked for you, but my hands are tied til' they merge this PR.

[ntt2k]

The temporary fix suggested on StackOverflow works!

I just make a donation to the project as well.

Thanks.

[OzzyTheGiant]

I would like to point out that if you are using PHP's PDO or mysqli_connect function and you're on PHP version 7.1, those utilities are already using the new caching_sha2_password plugin, which means that if you use the same username for php scripts and for the SQLTools extension, you're gonna have a bad time trying to connect to your site via localhost. According to this Russian post on Medium, you should have a user account for accessing mysql via workbench, cli, or through extensions like this one, and a separate account for PHP 7.1 scripts that use PDO or any other kinds of scripts that use the new plugin exclusively.

[mtxr]

@ntt2k I'll be closing this for now since I can't work on it till the 3rd party libs fixes this issue.

Feel free to reopen it whenever you want, ok?

Thanks!

Also, thank you @OzzyTheGiant for this well explained comment. Sure it will help :clap::clap: