MySQL Connection not working

Getting error while creating a new credentials with MySQL. username, password, host and port are correct. However, i am getting following error

Access denied for user @ (using password: YES)

When i connect with the same credentials via command line, i am getting successful connection in response.

N8N Information:

  • n8n version:: 0.233.1
  • Database (default: SQLite): Postgres
  • n8n EXECUTIONS_PROCESS setting (default: own, main): own
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker-compose
  • Operating system: Linux

Hey @Kevin_Trivedi,

I suspect the user is not allowed to login from the location you are using it. When you added the user if you did something like create 'user'@'localhost' the user would only be able to access the database from the localhost if you wanted to connect from an external system it would be something like create 'user'@'%' which would alllow the user to log in from any location.

The above is of course just an example and you would need to consult the MySQL documentation for the correct syntax to use when creating the user.

Hello @Jon :

No! the user is allowed to login to the remote host from my local machine. If the issue was related to access level, then login wouldn’t be working from my local machine as well.

We get similar error when user enters wrong password. Can you confirm that N8N doesn’t change password when trying to connect to the MySQL DB?

Hey @Kevin_Trivedi,

n8n shouldn’t change the password. While your machine might be able to access it when you try from a container it is no longer trying directly from your machine so in cases like this if your user was created in the database using @localhost the connection will likely fail.

I would expect the error to be the same as the idea is to not give away what the issue could be, Although it is still entirely possibly that you are using a special character in the password and that is causing an issue.

You are still not understanding correctly, this isn’t a DB on my localhost, this is a 3rd party MySQL DB, which is hosted on remote env. I am successfully connecting the remote host from my local machine.
But if i try with N8N, it’s triggering error.

Hey @Kevin_Trivedi,

Sadly that is not information you provided in the original ticket, I probably shouldn’t have made the assumption that you were running MySQL in a container on the same host.

In that case are you seeing the error when connecting to a remote host I would check for special characters in the password, They should be ok but maybe there is something escaping a value. Do you have n8n running in docker on the same machine you are testing the connection from?

Hello @Jon, I am sorry for not able to explain correctly.

there aren’t any special characters in the password. No, the N8N is hosted on AWS Lightsail. I tried to check logs on docker container when the N8N tries to test the remote DB connection, but it does’t log anything when test endpoint is executed.

and i am able to connect the remote DB successfully on my laptop.

Hey @Kevin_Trivedi,

Are you able to test the connection from Lightsail as well, Your laptop and the Lightsail instance are not going to be the same thing so it could still be a location issue unless you are using a vpn of somekind to connect to the lightsail network.

Which database provider are you using is it also running in AWS?

Database which runs the AWS LightSail N8N is Postgres.

And Yes, i am successfully able to connect MySQL DB from SSH terminal of AWS LightSail instance as well, using the same command line connection string.

The only error appears when running via N8N.

Interesting, What is the full error and what is the database provider for mysql?

This is the full error that i am receiving from the MySQL Server.

Access denied for user “username”@“IPAddress” (using password: YES)

Database provider for the remote MySQL host is AWS. But it doesn’t belong to me.

Wait… what is the username?

Normally that error would be something like Access denied for user 'x'@'y' (using password: YES) which is just saying access is denied for the user and they are using a password.

Yes, the portal, somehow hide the values that were present in “<” and “>”

This is the error message.

Access denied for user “username”@“IPAddress” (using password: YES)

Perfect, When you say the values present in < and > what do you mean? does the username contain one of those characters?

No, it was just to notify, that it contained username and password values in it.

At the moment I am out of ideas, I can’t see or think of anything on our side that would be causing it and it is very odd that the lightsail teminal can connect as I would have expected that to fail unless the lightsail terminal and the n8n instance are using different IPs.

Have you verifed that the user can log in from any location with whoever set the user up?

If user tries to connect from any other location, it’s blocked by firewall from the MySQL DB Server, and receive following error message.

Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Hey @Kevin_Trivedi,

This wouldn’t be a firewall issue, MySQL has an in built access control feature which can result in the same error.

Another thing that it could be… Does the connection require SSL?

Hello @Jon

Just want to ask, the MySQL connection string is build like this right? when we enter credentials in N8N.

mysql -h “hostname” -u “username” -P “port” -p"password"

Hey @Kevin_Trivedi,

We don’t connect using the MySQL command but it will be similar to that under it all.