Introduction
This is another article about NodeJS application. The focus of this article is just to show how to solve the problem exist upon executing a NodeJS application. The problem is rising an error message as exist in the title of the article. The error message is R_DBACCESS_DENIED_ERROR: Access denied for user ‘db_user’@’%’ to database ‘db_master’. The error message has an additional information on it. So, it can vary on another depends on the environment. The variation exist in the name of the user, host source access and also the database name. The following is the full error message upon executing a NodeJS application :
[admin@10 db]$ node app.js
Error: ER_DBACCESS_DENIED_ERROR: Access denied for user 'db_user'@'%' to database 'db_master'
at Handshake.Sequence._packetToError (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Handshake.ErrorPacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
at Protocol._parsePacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket. (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:88:28)
at Socket. (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (internal/streams/readable.js:309:12)
--------------------
at Protocol._enqueue (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:116:18)
at /home/admin/nodejs/db/app.js:24:7
at Layer.handle [as handle_request] (/home/admin/nodejs/db/node_modules/express/lib/router/layer.js:95:5)
at next (/home/admin/nodejs/db/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/home/admin/nodejs/db/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/home/admin/nodejs/db/node_modules/express/lib/router/layer.js:95:5)
at /home/admin/nodejs/db/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/home/admin/nodejs/db/node_modules/express/lib/router/index.js:335:12) {
code: 'ER_DBACCESS_DENIED_ERROR',
errno: 1044,
sqlMessage: "Access denied for user 'db_user'@'%' to database 'db_master'",
sqlState: '42000',
fatal: true
}
/home/admin/nodejs/db/app.js:32
throw err;
^
Error: Cannot enqueue Query after fatal error.
at Protocol._validateEnqueue (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:212:16)
at Protocol._enqueue (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:138:13)
at Connection.query (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:198:25)
at Handshake. (/home/admin/nodejs/db/app.js:30:8)
at Handshake. (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:526:10)
at Handshake._callback (/home/admin/nodejs/db/node_modules/mysql/lib/Connection.js:488:16)
at Handshake.Sequence.end (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at Handshake.ErrorPacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/sequences/Handshake.js:125:8)
at Protocol._parsePacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/home/admin/nodejs/db/node_modules/mysql/lib/protocol/Parser.js:433:10) {
code: 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR',
fatal: false
}
[admin@10 db]$
The following is the content of the file with the name of ‘app.js’ :
const express = require("express");
const mysql = require("mysql");
const app = express();
app.get("/",(req,res) => res.send("Hello World !"));
app.listen(3001);
const con = mysql.createConnection({
host: "10.0.2.2",
user : "db_user",
password : "password",
database : "db_master"
});
app.post("/db",(req, res) => {
try {
con.connect(function(err){
if(err)
console.debug(err);
else
console.log("Connected !");
});
} catch(err) {
console.error(err.message);
res.status(500).send("Server Error !");
}
});
The above content file is actually a script for connecting to a host machine with the IP Address of ‘10.0.2.2’. After activating the NodeJS application, simulating the HTTP Post request using Postman application to access the address of ‘localhost:3001/db from the host machine. Accesing port 3001 from the host machine to the guest machine is possible. Check the article with the title of ‘How to Add Port Forwarding Rule to access NodeJS Service of Guest Machine running in VirtualBox Manager from Host Machine’ in this link. So, the execution of the NodeJS script actually exist in a guest machine or a virtual server. The guest machine is running in a VirtualBox application.
Solving the Problem
According to the description in the introduction, the problem is the privilege access. The reason is because the error message is showing it clearly. Clearly, the error message is : Access denied for user ‘db_user’@’%’ to database ‘db_master’. So, the problem exist in the absence of the privilege for ‘db_user’ to access the ‘db_master’ database. The following are the steps to solve it :
1. Check directly using MySQL client available in the guest machine or the virtual server where the execution of the NodeJS application occur. Try to connect to the MySQL database exist in the host machine as follows :
[admin@10 db]$ mysql -udb_user -p -h 10.0.2.2 db_master Enter password: ERROR 1044 (42000): Access denied for user 'db_user'@'%' to database 'db_master' [admin@10 db]$
2. Apparently, as exist in the above output command execution, it fails to connect. So, access MySQL Database Server in the host machine using any access available as follows :
[admin@10 db]$ mysql -uroot -p -h 10.0.2.2 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 160 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
3. Next, after successfully logging in to MySQL Database Server, execute a query. The query execution in the MySQL Command Prompt is for granting access as follows :
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> grant all on db_master.* to 'db_user'@'%'; Query OK, 0 rows affected (0.38 sec) mysql> flush privileges; Query OK, 0 rows affected (0.14 sec) mysql>
4. After granting access in the previous step, just try again to access MySQL Database Server as follows :
[admin@10 db]$ mysql -udb_user -p -h 10.0.2.2 db_master Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 162 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
5. Finally, since the access directly from MySQL client is a success, try to execute the NodeJS application once more as follows :
[admin@10 db]$ node app.js Connected !