On my Render dashboard, I have a MySQL instance deployed and a Web Service instance (Starter tier). I confirmed with Render support that the services are setup correctly for me to be able to run a node.js app on the Web Service instance that connects to the MySQL instance.
During the deploy of the node.js app, I get the following error:
Error: Access denied for user ‘mysql’@‘%’ to database ‘mydb’
I got the username ‘mysql’ from the environment tab of the MySQL instance and that is also where I got the password that I’m using. I triple checked the password. I tried switching to ‘root’ for the username and using the root password but get the same error on the root user.
Does anyone have any idea what I might be doing wrong?
Here is the JavaScript code I’m using to establish the database connection. The error is kicking on the dbcon.query line. Note: I removed the password in the code below.
//MySQL Database Setup
var dbcon = mysql.createConnection({
host: “mysql-8mkf”,
user: “mysql”,
password: “”, //password removed for this forum post. actual code has pw here
});
//Create Database
dbcon.connect(function(err) {
if (err) throw err;
dbcon.query("CREATE DATABASE IF NOT EXISTS mydb", function (err, result) {
if (err) throw err;
console.log("Database created (if it didn't already exist)");
});
});
Most likely the user you are connecting to the database with does not have permission to create a new database and is probably read only. If this is the problem, you can use the database service’s Dashboard shell (or SSH) to create or modify a user to have CREATE (and any other necessary) permissions, which should solve the problem.
Hi Mike, thanks for the reply! That makes sense to me for the ‘mysql’ user but I also tried the root user with the root password from the Environments tab. Wouldn’t the root user have access to everything by default?
UPDATE: as I dig into this and see that privileges are granted based on username AND hostname, I am guessing that root only has all privileges locally, when logged into the CLI.
Yes, the root user should have access to everything, but it’s typically not advisable to use the root account in your code. Instead you should have one or more users to have limited permissions to do specific operations as a safety measure. You can still log into the server as root via the shell or SSH to manage administration of the database (such as user accounts and permissions).
Here’s a quote from the mySQL website:
root is a superuser account for administration of the MySQL server which should not be used for general operations.