ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO)

I had a similar problem, all my connections were working but where I got the error 'ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO)' my call was made inside middleware at app.js level, so the .env values were not yet reachable, for this case (it could help those who have the same problem as me) I had to add:

require('dotenv').config();

Inside the connexion file like that :

const mysql = require('mysql')
require('dotenv').config();
const connectionLog = mysql.createPool({
    connectionLimit : 10,
    host: process.env.DB_HOST_LOG,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    port:process.env.DB_PORT
})

For me it was the resolution.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

then right after doing that command flushing the privileges by executing this command:

flush privileges;

You had to add the new user with an IP of the allowed host section not of the name of "localhost"

RENAME USER 'myuser'@'localhost' TO 'myuser'@'127.0.0.1';

Solved it by using % in place of localhost:

CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON db.* TO 'user'@'%';

instead of

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost';

Check once if you've imported environment variables file before importing the db.js file or not.

Eg. -

Incorrect:

require("db.js");

const dotenv = require('dotenv');  
dotenv.config({ path: 'env.default' });

Correct:

const dotenv = require('dotenv');  
dotenv.config({ path: 'env.default' });

require("db.js");

So, first import env file, then db.js file.

Sometimes you face this error when you move your variables to .env file. In that case

1. Install dotenv using npm or yarn add dotenv
2. import .env using require('dotenv').config();

After this you'd be able to read your 'user' environment variable

In my case, adding a port number solves the problem ->

host:'localhost',
port:3406,
database:'demo_db',
user:'u_name',
password:'u_pass'

if you are using environment variables and dotenv library make sure that you add this require('dotenv').config();....... just check it first...

const { createPool } = require('mysql') require('dotenv').config();

const pool = createPool({
    port: process.env.DB_PORT,
    host: process.env.DB_HOST,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
}).on("error", (err) => {
    console.log("Failed to connect to Database - ", err);
});


module.exports = pool;

First, you need to install env module and then create a root file .env mentioning the details of environment details:-

DB_NAME=yourdb_name
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=""
PORT=8080 #port_no in which your code is executing

You can do the following : mysql -u root -p Enter password:

Create A User here and database here , After that use these details in your code. i.e make a user by logging as root.