MySQL authentication error with Node and Adonis even after mysql_native_password

Question

I am trying to connect to a MySQL database from a Node JS app running Adonis JS. However, when I try to connect (by loading a page that's routed to a controller that performs a basic SELECT query). I get this error:

"Client does not support authentication protocol requested by server; consider upgrading MySQL client"

I am running MySQL community server 8.0.16, Node JS 10.15.0, Adonis JS 4.1.0, and mysql2 version 1.6.5 (node database driver), all on Windows 10. Also, I am running Adonis in dev mode.

I have tried to use mysql_native_password by running this:

ALTER USER 'web'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;

After running this, I can verify in the MySQL workbench that the query took effect.

I tried loading the page without cache to make sure the error page wasn't cached.

I tried restarting the MySQL server, the Adonis server, and the computer that both were served from.

I tried dropping the user and then adding the user and granting it the necessary privileges:

CREATE USER 'web'@'localhost' identified with mysql_native_password by "password";
GRANT ALL ON db.* to 'web'@'localhost';
flush privileges;

I also verified this inside the workbench.

I researched a solution to this problem, but all of the solutions were to run ALTER USER with mysql_native_password.

My code for connecting to the database inside config/database.js is:

mysql: {
    client: 'mysql2',
    connection: {
      host: Env.get('DB_HOST', 'localhost'),
      port: Env.get('DB_PORT', '3306'),
      user: Env.get('DB_USER', 'web'),
      password: Env.get('DB_PASSWORD', 'password'),
      database: Env.get('DB_DATABASE', 'db')
    }
  },

And my code for querying the database is:

return Database.table("test").select("*");

(Adonis uses knex.js for a query builder)

I expect the query to return the contents of the test table, but instead I receive an authentication error. Any ideas on how I can resolve this issue and get Node/Adonis to connect to MySQL? Thanks!

RESOLVED:

I needed to replace the Env.gets with strings.

MySQL authentication error with Node and Adonis even after mysql_native_password

0 Answers0