MySQL connection issue with remote SQL

Questions
1

MySQL connection issue with remote SQL

I’ve set up a n8n instance on my raspberrypi but due to lock issues with sqlite I decided to connect to a mysql server on a remote server. The strangest thing is that connecting to this remote SQL works within workflows in n8n but not when I configure the exact same address in the docker compose file. I receive this error upon building:

Stopping n8n...,
Initializing n8n process,
There was an error initializing DB: "Access denied for user 'dsp_n8n'@'XX-XXX-XXX-XX.fixed.kpn.net' (using password: YES)",
,

Where XX-XXX-XXX-XX is my own ip address and kpn.net is my ISP.
This error occurs when I enter a server IP or Domainname in the DB_MYSQLDB_HOST variable.

version: "3.1"

services:
  n8n:
    container_name: n8n
    image: n8nio/n8n
    restart: unless-stopped
    environment:
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=${BASIC_USERNAME}
      - N8N_BASIC_AUTH_PASSWORD=${BASIC_PASSWORD}
      - DB_TYPE=mariadb
      - DB_MYSQLDB_DATABASE=${DB_MYSQLDB_DATABASE}
      - DB_MYSQLDB_HOST=${DB_MYSQLDB_HOST}
      - DB_MYSQLDB_PORT=${DB_MYSQLDB_PORT}
      - DB_MYSQLDB_USER=${DB_MYSQLDB_USER}
      - DB_MYSQLDB_PASSWORD=${MYSQLDB_PASSWORD}
    ports:
      - "5678:5678"
    volumes:
      - /config/n8n/.n8n:/home/node/.n8n
      - /config/n8n/files:/files

Information on my n8n setup

  • n8n version: Latest
  • Database: MariaDB
  • Running n8n with the execution process: ?
  • Running n8n via: Docker

Any idea’s on how I can resolve these issues?

2

Hey @rowan,

That error is one I have seen a lot over the years and it tends to mean the username is right but the password is wrong or the user is not allowed to login from that location.

I would double check the password or check the MySQL logs to see if that shows anything else.

3

Thanks for looking into it, but sadly in this case the host is incorrect. I used a different host, for example AAA.COM however the error contains a totally different host. When I use AAA.COM in my workflow it does connect.

4

The host in the error should be the machine connecting, when you make the credentials you normally do user@localhost or similar. Sometimes you would use user@% would allow the user to connect from any host.

The downside is the error is coming back from MySQL and is well documented so you will need to look into the MySQL logs to see what it is unhappy about.

It could be worth setting the password in the compose file just to see if that changes anything maybe the wrong password is being sent.

5

Thanks for your help but sorry, you’re absolutely wrong as this looks like a bug. While I understand the error, as I said. I can use the database connection within a workflow, I can also connect from any host. but when setting them from a docker compose they fail.

(ps. I don’t mean to be rude, English isn’t my native language.)

image
image1442×1031 65.6 KB

6

found it, the password contained a $ and a ^ which caused the connection to fail. Absolutely a bug.

7

Hey @rowan,

That would potentially do it I will give it a go today to see if I can reproduce it.

When it comes to troubleshooting we have to rely on the message we get back from the server to start with and in this case the server was saying the password was wrong, by setting the password in the compose file it would have led us to see if it was something with the .env file as they don’t always like special characters without being escaped (Variables containing special characters coming from env file are not preserved · Issue #8607 · docker/compose · GitHub)

When you set the password did you do something like…

MYSQLDB_PASSWORD=myPassword

Or

MYSQLDB_PASSWORD='myPassword'

1 Like
8

At first I entered them with double quotes

MYSQLDB_PASSWORD="myPassword$^"

they didn’t work, then I added them within a variable within double quotes

myPasswordVar = myPassword$^
MYSQLDB_PASSWORD="${myPasswordVar}"

followed by without as described in my inital post

myPasswordVar = myPassword$^
MYSQLDB_PASSWORD=${myPasswordVar}

I use portainer to launch my containers with their env variable functionality.

image
image2368×1309 208 KB

edit: I accidently put a & instead of the $ in the password of the screenshot.

9

Hey @rowan,

As portainer runs on top of Docker it could be the same issue, I will do some testing and see what I can find out.

10

Just managed to reproduce the issue and it does look to be an issue with the way Docker handles .env options. To get a password with a $ working I had to use myPassword$$^ I did notice that when trying to create the container in Portainer it did give me an error at that point to tell me there was a problem.

To test it a bit more I then made a Stack using the below options.

version: '3.8'
services:
  n8n:
    image: n8nio/n8n:latest
    environment:
      - DB_TYPE=mysqldb
      - DB_MYSQLDB_HOST=172.17.0.1
      - DB_MYSQLDB_PORT=3306
      - DB_MYSQLDB_DATABASE=n8n
      - DB_MYSQLDB_USER=n8n
      - DB_MYSQLDB_PASSWORD=${MYSQLPASS}
      - NODE_ENV=production
    ports:
      - 5555:5678

I have then set the one env option below and everything appears to be working with no issues.

image
image1506×180 19.1 KB

Could this be down to the version of Portainer and / or Docker that you have installed? I am running Portainer 2.11.0.

1 Like
11

thank you very much, that’s a very good find!

1 Like