Troubleshoot Node.js and MySQL connection errors on Windows

Question

First, I work in Window.

I provided the necessary information accurately (host, user, password, port, database) but it gave the error.

code: 'ER_ACCESS_DENIED_ERROR',
    errno: 1045,
    sqlMessage: "Access denied for user 'root'@'localhost' (using password: YES)",
    sqlState: '28000',
    fatal: true

I tried many ways to solve this problem but all failed. I don’t know why reinstalling MySQL temporarily solved it. But when I restarted my computer, the error appeared again. I immediately coded it, played with the version, and tried it out.

My code

const express = require('express');
    const app = express();
    const mysql = require('mysql');
    const port = process.env.PORT || 4000;

    const connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "123456",
    port: "3306",
    database: "study01"
    });
    console.log(connection);
    connection.connect();
    
    app.listen(app.get('port'), () => console.log(`Listening on port ${port}`));

MySQL Server and Module Versions

mysql server : 5.7.38
nodejs : 16.15.1
nodejs mysql : 2.18.1
nodejs express : 4.18.1

I tried these things

• Change password, create another user
• ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY '123456';
• Reinstall Node.js and npm
• Reinstall MySQL

Answer 1

The problem is that your account does not have permission to connect to the database.

Your command is granting permissions, however, you need the account's password hash.

Get the password hash your account is running

USE mysql;
SELECT Password FROM user WHERE User = 'your Username';

This output is then piped into the password hash portion of this command

GRANT ALL PRIVILEGES ON *.* TO `your Username`@`%` IDENTIFIED BY PASSWORD 'your password hash' WITH GRANT OPTION

The

% character is a wildcard, so it allows you to connect to the database from any IP address. For added security, you can restrict it to only your IP address

Hope this helps.