Error querying database after uninstallation + reinstallation: Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost'

user_13773368 0
Viewed 164

I get an error when querying my database after deleting my old version of MySQL and reinstalling the newest one.

I am using: MySQL Ver 8.0.23 for macos10.15

My app is in Next.js and it was working fine before this. I'm running it on localhost. It uses serverless-mysql to connect:

const mysql = require('serverless-mysql')

const db = mysql({
  config: {
    host: process.env.MYSQL_HOST,
    database: process.env.MYSQL_DATABASE,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
  }
})

The test connection works perfectly fine in MySQL Workbench.

Things I've tried:

  • Resetting the password.
  • Uninstalling MySQL again and using the Legacy Password Encryption. This makes the plugin for the root user "mysql_native_password".
  • Creating a new user, granting them all permissions, and trying with their credentials

This might be overkill but here is what the user looks like after all of this:

COMMAND:

use mysql;
select * from user where User="root";

RESULT:

[
    {
        "Host" : "localhost",
        "User" : "root",
        "Select_priv" : "Y",
        "Insert_priv" : "Y",
        "Update_priv" : "Y",
        "Delete_priv" : "Y",
        "Create_priv" : "Y",
        "Drop_priv" : "Y",
        "Reload_priv" : "Y",
        "Shutdown_priv" : "Y",
        "Process_priv" : "Y",
        "File_priv" : "Y",
        "Grant_priv" : "Y",
        "References_priv" : "Y",
        "Index_priv" : "Y",
        "Alter_priv" : "Y",
        "Show_db_priv" : "Y",
        "Super_priv" : "Y",
        "Create_tmp_table_priv" : "Y",
        "Lock_tables_priv" : "Y",
        "Execute_priv" : "Y",
        "Repl_slave_priv" : "Y",
        "Repl_client_priv" : "Y",
        "Create_view_priv" : "Y",
        "Show_view_priv" : "Y",
        "Create_routine_priv" : "Y",
        "Alter_routine_priv" : "Y",
        "Create_user_priv" : "Y",
        "Event_priv" : "Y",
        "Trigger_priv" : "Y",
        "Create_tablespace_priv" : "Y",
        "ssl_type" : "",
        "ssl_cipher" : ?,
        "x509_issuer" : ?,
        "x509_subject" : ?,
        "max_questions" : 0,
        "max_updates" : 0,
        "max_connections" : 0,
        "max_user_connections" : 0,
        "plugin" : "mysql_native_password",
        "authentication_string" : "*E7CF5215B32CCDA122352F3A92E9F73131E28662",
        "password_expired" : "N",
        "password_last_changed" : "2021-02-24 18:54:52",
        "password_lifetime" : null,
        "account_locked" : "N",
        "Create_role_priv" : "Y",
        "Drop_role_priv" : "Y",
        "Password_reuse_history" : null,
        "Password_reuse_time" : null,
        "Password_require_current" : null,
        "User_attributes" : null
    }
]

Would really appreciate any help anyone can offer, this bug is destroying my sanity. Let me know if I can provide any more details.

next.js access-denied mysql database
1 Answers

The way I solved this problem was I manually entered my password in my code instead of the ENV file. It worked. When I console logged the ENV file to see why that wasn't working, it was because I was putting it in a string, for ex: MYSQL_PASSWORD = "password". It SHOULD be: MYSQL_PASSWORD = password

So if anyone is struggling, check that the ENV variables are set up correctly, and manually put them in to rule out any ENV issues.

user_137733680
Related