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.
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?
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.
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.
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.
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.
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?