Cant connect to mysql 8

[metallurgical]

Ubuntu
ubuntu LTS 20.04 (Focal Fossa)

Mysql Version
8.0.21

Node Version
v14.20.0

Npm Version
6.14.17

Core Package Version
^5.8.4

Tried both npm package
mysql and mysql2

Error received
{"message":"ER_ACCESS_DENIED_ERROR: Access denied for user 'myuser_a'@'localhost' (using password: YES)"}

Hi guys, above is the details error that i received when trying to connect with my adonisjs application. In my local seems to be working but not to avail in my ubuntu server. This db user somehow can connect using both cli and other PHP(Laravel) project but not working when it comes to nodejs app.

I've created 2 users with different mysql_native_password and another using default authentication sha. Here is my code for creating user with mysql_native_password:

CREATE USER 'myuser_a'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser_a'@'localhost';
FLUSH PRIVILEGES

And this using default:

CREATE USER 'myuser_b'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser_b'@'localhost';
FLUSH PRIVILEGES

Have tried these two config but results also same:

DB_CONNECTION=mysql
MYSQL_CLIENT=mysql2
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=myuser_a # myuser_b
MYSQL_DB_NAME=mydb
MYSQL_PASSWORD=mypassword

DB_CONNECTION=mysql
MYSQL_CLIENT=mysql
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=myuser_a # myuser_b
MYSQL_DB_NAME=mydb
MYSQL_PASSWORD=mypassword

The only different is when i try to connect to mysql using MYSQL_CLIENT=mysql for user myuser_b i will received error message ER_NOT_SUPPORTED_AUTH_MODE, then i change to mysql2 only received error ER_ACCESS_DENIED_ERROR.

Is there anything that i missed for adonisjs config?

These solutions seems not working in my case
#2941

[RomainLanz]

Hey @metallurgical! 👋🏻

You have to use mysql2 to use MySQL 8.

Looking at the error message, it seems you have not defined your environment variable correctly.
Have you tried to connect to your server using any other software with the user you created?

Also, note that if your password has a $, you have to escape it.

[metallurgical]

@RomainLanz Already use mysql2. How can i missed the Escape the $ sign, that was the culprit. Now already can. Thanks a lot @RomainLanz . Never though the solutions is right under my nose 🙏