I can't start n8n on a fresh database

Describe the issue/error/question

I am attempting to install n8n at digital ocean, so I used the n8n-digital-ocean repo.

I am trying to set up queue mode. I have 4 servers, a master, a worker, redis and MySQL.

I created a fresh database, started the n8n master server, and it fails to start.

What is the error message (if any)?

Initializing n8n process
query is slow: CREATE TABLE migrations (id int NOT NULL AUTO_INCREMENT, timestamp bigint NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB
execution time: 4522
Migration “CreateUserManagement1646992772331” failed, error: Unknown column ‘owner’ in ‘field list’
There was an error initializing DB: “Unknown column ‘owner’ in ‘field list’”

Stopping n8n…


Just for kicks I inserted that migration into the table like so

INSERT INTO migrations (`timestamp`,`name`) VALUES (NOW(), 'CreateUserManagement1646992772331');

It then fails with

Migration "CreateUserManagement1646992772331" failed, error: Unknown column 'owner' in 'field list'
There was an error initializing DB: "Unknown column 'owner' in 'field list'"

I stop and start the server, and it fails with

 ›   Error: There was an error: cannot find configuration param
 ›   'userManagement.isInstanceOwnerSetUp'

I think the latest main of n8n-digital-ocean is broken, or I have magically come up with an incompatible set of env vars.

Please share the workflow

There are no yet

Information on your n8n setup

  • n8n version:
  • Database you’re using (default: SQLite): MYSQL
  • Running n8n with the execution process [own(default), main]: Own
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: docker-compose in n8n-digital-ocean repo

Docker-Compose.yml

version: "3.7"

services:
  caddy:
    container_name: caddy
    image: caddy:latest
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ${DATA_FOLDER}/caddy_data:/data
      - ${DATA_FOLDER}/caddy_config:/config
      - ${DATA_FOLDER}/caddy_config/Caddyfile:/etc/caddy/Caddyfile

  n8n:
    container_name: n8n
    image: n8nio/n8n
    restart: always
    ports:
      - 5678:5678
    environment:
      - EXECUTIONS_PROCESS=own
      - DB_TYPE=mysqldb
      - DB_MYSQLDB_DATABASE=n8ndata
      - DB_MYSQLDB_HOST=private-n8n-db-mysql-8-REDACTED.db.ondigitalocean.com
      - DB_MYSQLDB_PORT=25060
      - DB_MYSQLDB_USER=doadmin
      - DB_MYSQLDB_PASSWORD=REDACTED
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=${N8N_BASIC_AUTH_USER}
      - N8N_BASIC_AUTH_PASSWORD=${N8N_BASIC_AUTH_PASSWORD}
      - N8N_HOST=${SUBDOMAIN}.${DOMAIN_NAME}
      - N8N_PORT=5678
      - N8N_PROTOCOL=https
      - NODE_ENV=production
      - WEBHOOK_URL=https://${SUBDOMAIN}.${DOMAIN_NAME}/
      - GENERIC_TIMEZONE=${GENERIC_TIMEZONE}
      - TZ=${GENERIC_TIMEZONE}
      - N8N_ENCRYPTION_KEY="REDACTED^"
      - N8N_EMAIL_MODE=${N8N_EMAIL_MODE}
      - N8N_SMTP_HOST=${N8N_SMTP_HOST}
      - N8N_SMTP_PORT=${N8N_SMTP_PORT}
      - N8N_SMTP_USER=${N8N_SMTP_USER}
      - N8N_SMPT_PASS=${N8N_SMTP_PASS}
      - N8N_SMTP_SENDER=${N8N_SMTP_SENDER}
      - N8N_SMTP_SSL=false
      - EXECUTIONS_MODE=queue
      - EXECUTIONS_DATA_SAVE_ON_ERROR=all
      - EXECUTIONS_DATA_SAVE_ON_SUCCESS=none
      - EXECUTIONS_DATA_SAVE_ON_PROGRESS=true
      - EXECUTIONS_DATA_SAVE_MANUAL_EXECUTIONS=false
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=731
      - QUEUE_BULL_REDIS_HOST=10.xxx.xxx.x
      - QUEUE_BULL_REDIS_PORT=6379
    volumes:
      - ${DATA_FOLDER}/.n8n:/home/node/.n8n
      - ${DATA_FOLDER}/local_files:/files

volumes:
  caddy_data:
    external: true
  caddy_config:

Hi @phpguru, I am sorry to hear you’re having trouble. @Jon would this by any chance be covered by your recent fix fix: mysql migration using incorrect syntax on insert by Joffcom · Pull Request #4166 · n8n-io/n8n · GitHub?

I shall check shortly, @phpguru for now can you set a version tag in your compose file and use 0.194.0.

1 Like

Hey @phpguru,

Just given this a test on a new database using 195.2 and it is working as expected. Can you drop the tables in the database and make sure the image is 0.195.2 or later and try again.

1 Like

Yeah, it’s really strange. I was wondering if there is a list of steps to “clear and reset n8n completely” - like, should I wipe my ~/.n8n/ dir?

I would like to try the 0.195.2 - but where to put that version number?

  • I stopped the master and the worker
  • I took my docker-compose.yml (pasted above) and moved it out of the directory.
  • I rm -rf the entire n8n-digital-ocean folder
  • I took a fresh clone of n8n-digital-ocean
  • I moved the docker-compose.yml back in
  • I changed image:n8nio/n8n to image: n8nio/n8n:0.195.2
  • I dropped my db and recreated it
  • I deleted my ~/.n8n directory
  • I deleted all docker images
  • I deleted all docker volumes
  • I recreated the caddy_data docker volume

Here is the log after running docker-compose up -d

Creating volume "n8n-digital-ocean_caddy_config" with default driver
Pulling caddy (caddy:latest)...
latest: Pulling from library/caddy
213ec9aee27d: Pull complete
fd0c7d01ba8a: Pull complete
e56da4be4f98: Pull complete
5a4205233f11: Pull complete
1f35320fa0aa: Pull complete
Digest: sha256:faadebac07e5c9daaa97adf528801d228c01d706a6755ab0c082acc4702e25de
Status: Downloaded newer image for caddy:latest
Pulling n8n (n8nio/n8n:0.195.2)...
0.195.2: Pulling from n8nio/n8n
213ec9aee27d: Already exists
864b973d1bf1: Pull complete
80fe61ad56f5: Pull complete
e3887ab559e6: Pull complete
4f4fb700ef54: Pull complete
95328495a398: Pull complete
c2964198180c: Pull complete
45e8c374e50b: Pull complete
7c2546677ec3: Pull complete
f2ba1afa9605: Pull complete
ae281df2b330: Pull complete
Digest: sha256:593eed4a032314576db94fe223aa43ff59f930e50830cce2ff8f118e1e6bd173
Status: Downloaded newer image for n8nio/n8n:0.195.2
Creating caddy ... done
Creating n8n   ... done
[email protected]:~/n8n-digital-ocean# docker logs n8n --follow
Initializing n8n process
query is slow: CREATE TABLE `migrations` (`id` int NOT NULL AUTO_INCREMENT, `timestamp` bigint NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB
execution time: 3933
Migration "CreateUserManagement1646992772331" failed, error: Unknown column 'owner' in 'field list'
There was an error initializing DB: "Unknown column 'owner' in 'field list'"

Stopping n8n...
Initializing n8n process
Migration "CreateUserManagement1646992772331" failed, error: Table 'role' already exists
There was an error initializing DB: "Table 'role' already exists"

Stopping n8n...

There are no keys in redis

Here are the tables created in the database

mysql> SHOW TABLES;
+--------------------+
| Tables_in_n8ndata  |
+--------------------+
| credentials_entity |
| execution_entity   |
| migrations         |
| role               |
| settings           |
| shared_credentials |
| shared_workflow    |
| tag_entity         |
| user               |
| webhook_entity     |
| workflow_entity    |
| workflows_tags     |
+--------------------+
12 rows in set (0.00 sec)

I tried to start the master again

Migration "CreateUserManagement1646992772331" failed, error: Table 'role' already exists
There was an error initializing DB: "Table 'role' already exists"

I ran this SQL on the db directly

INSERT INTO migrations (timestamp, name) VALUES (NOW(), 'CreateUserManagement1646992772331');

I restarted n8n and the output is

Initializing n8n process
query is slow: CREATE TABLE `installed_packages` (`packageName` char(214) NOT NULL,`installedVersion` char(50) NOT NULL,`authorName` char(70) NULL,`authorEmail` char(70) NULL,`createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,`updatedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`packageName`)) ENGINE=InnoDB;
execution time: 3962
Migrations in progress, please do NOT stop the process.
Migration "CreateCredentialsUserRole1660062385367" failed, error: Unknown column 'user' in 'field list'
There was an error initializing DB: "Unknown column 'user' in 'field list'"

Stopping n8n...

I changed the tag to 0.194.0 and dropped the db and recreated it and get

Initializing n8n process
Migration "CreateUserManagement1646992772331" failed, error: Table 'role' already exists
query is slow: ROLLBACK
execution time: 4004
There was an error initializing DB: "Table 'role' already exists"

Stopping n8n...
Initializing n8n process
Migration "CreateUserManagement1646992772331" failed, error: Table 'role' already exists
There was an error initializing DB: "Table 'role' already exists"

Stopping n8n...

Any other ideas folks? Going on 8 hours now just trying to start a cluster.

I even tried this on a brand new droplet with a brand new database:

docker run -it --rm \
	--name n8n \
	-p 5678:5678 \
	-e DB_TYPE=${DB_TYPE} \
	-e DB_MYSQLDB_DATABASE=${MYSQLDB_DATABASE} \
	-e DB_MYSQLDB_HOST=${MYSQLDB_HOST} \
	-e DB_MYSQLDB_PORT=${MYSQLDB_PORT} \
	-e DB_MYSQLDB_USER=${MYSQLDB_USER} \
	-e DB_MYSQLDB_PASSWORD=${MYSQLDB_PASSWORD} \
	-v ~/.n8n:/home/node/.n8n \
	n8nio/n8n \
	n8n start

The result is

Initializing n8n process
query is slow: SELECT * FROM `n8n`.`migrations` `migrations` ORDER BY `id` DESC
execution time: 4530
Migration "CreateUserManagement1646992772331" failed, error: Unknown column 'owner' in 'field list'
There was an error initializing DB: "Unknown column 'owner' in 'field list'"

Stopping n8n...

The only MySQL database option on Digital Ocean is MySQL 8. What version is N8N supposed to work with?

This has to be the problem: Digital Ocean requires SSL connection to MySQL 8 and offers a CA-Certificate.crt to download, but there is no way to configure this in N8N.

Similarly, I tried Postgres 14 at Digital Ocean, same thing, SSL is required for the connection, download ca-certificate.crt and tried to configure that with DB_POSTGRESDB_SSL_CA= and get all kinds of various SSL related errors, such as

There was an error initializing DB: “no pg_hba.conf entry for host “10.108.0.3”, user “doadmin”, database “defaultdb”, no encryption”

I have no idea how it was connecting before to MySQL 8 server, because I never configured SSL.

Hi @phpguru, you can for example specify a tag like 0.195.2 in your docker compose file. So instead of something like

services:
  n8n:
    container_name: n8n
    image: n8nio/n8n

you could use this:

services:
  n8n:
    container_name: n8n
    image: n8nio/n8n:0.195.4

I’ve used 0.195.4 in this example since it’s the latest version of n8n.

2 Likes

Let me give it a quick bash on Digital Ocean, I did my test with a local instance of MySQL although it looks like I have an older version so there could be something there. I suspect though if it was the connection itself it wouldn’t create the tables at all so that is a good sign.

It could be that a previous migration is failing and we are only showing an issue on that one, It does look unrelated to the issue recently fixed though.

I will be back shortly when I have tested on DO.

Alright yeah that is broken… Let me check a v8 mysql container locally and see if I can get a fix out soon for this one.

1 Like

Thanks a lot for trying on Digital Ocean.

I am using (or I was planning to use) their hosted mysql and only last night did I realize that their configuration page after you spin up a db says “ssl: required” — the thing is, I’m able to connect no problem with a regular mysql prompt and some tables got created via n8n startup routine, so I think that ssl required for mysql is just wrong. Kind of shocking but ok great - works in my favor I guess.

I checked and there’s no lower version of mysql to use unless I use a droplet and manage the db myself, which I am open to do but would prefer not to.

The other thing I noticed was for mysql configuration, n8n offers no environment variables for ssl configuration.

I looked into postgres like I mentioned, but when you create a managed db in digital ocean, they only offer the ca-certificate.crt to be downloaded, so I had no clue what to do with the other two ssl config params (the cert and the key).

I would love to be able to set up droplets for master and workers and use their managed service for mysql and Redis, but as you’ve discovered, it’s broken, so I really appreciate you going the extra mile to verify it on digital ocean and mysql 8.

Hey @phpguru,

That is the tricky bit at the moment we just don’t have the support for SSL / TLS with MySQL, I will have quick look and see if we have a PR opened for this that I can do a review on. With Postgres you might not need the certificate it depends on what the digital ocean cert contains, It could be a CA certificate for the connection so it is trusted I would need to clear some time to have a look at that.

Afternoon @phpguru,

Thought I would give you a quick update, I have set up MySQL 8.0.30 which is the latest version available on Dockerhub and on a new database it is working as expected so the good news is I don’t think the issue is directly with MySQL v8 and it is more than likely a config thing.

I will do some more testing over the weekend if I get a chance to see if I can work out what is different so we can get to the bottom of this one.

1 Like

I could try it on four droplets for now - I’m thinking the issue is trying to use Digital Oceans’s managed MySQL, but I don’t know why DO says it requires an SSL connection. I’d have to research how to do it.

If I wanted to try Postgres can you explain how the keys work?

I’m about half way to understanding how to do it on K8s. There’s one GitHub repo (that doesn’t seem to be affiliated with N8N) I saw that has a sample helm chart but I still need to grok parts of it. Seems so much easier. I thought I saw Traefik being used in one of the n8n versions I tried, but caddy is what I got now. Am I crazy? Ultimately that seems like the easier way to run it because of how easy it is to request more pods. How does N8N queue mode worker autoscaling work in K8s?

I can almost confirm that N8N in queue mode works fine when installed on 4 Digital Ocean droplets.

  • Ubuntu 20
  • MySQL 8
  • Redis 6
  • N8N Master
  • N8N Worker

I used the n8n-digital-ocean repo with caddy.

It would be really helpful to have a post or page on the docs about how to test queue mode - or just answer in this thread. In general, I am assuming I would need to create a workflow, then use Postman to fire off some POSTs to trigger the workflow, then watch the worker logs, check Redis for data and such. I’m a sysadmin & solution architect, but not very familiar with N8N. Some type of guide on how to verify that N8N Queue Mode is working properly would be really helpful, because it seems to be, but I’m not sure how to verify it.

Thanks for all your help! Digital Ocean hosted MySQL does NOT work!

Okay from what I can tell all the configs are correct, but N8N cannot use queue mode. Here is what I am getting.

On the Master

To run the master I am just doing

cd ~/n8n-digital-ocean
docker compose up -d

Here is the n8n stanza of docker-compose.yml

  n8n:
    container_name: n8n
    image: n8nio/n8n
    restart: always
    ports:
      - 5678:5678
    environment:
     - EXECUTIONS_PROCESS=${EXECUTIONS_PROCESS}
     - EXECUTIONS_MODE=${EXECUTIONS_MODE}
     - EXECUTIONS_TIMEOUT=${EXECUTIONS_TIMEOUT}
     - EXECUTIONS_DATA_PRUNE=${EXECUTIONS_DATA_PRUNE}
     - EXECUTIONS_DATA_MAX_AGE=${EXECUTIONS_DATA_MAX_AGE}
     - QUEUE_BULL_REDIS_HOST=${QUEUE_BULL_REDIS_HOST}
     - QUEUE_BULL_REDIS_PORT=${QUEUE_BULL_REDIS_PORT}
     - DB_TYPE=${DB_TYPE}
     - 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=${DB_MYSQLDB_PASSWORD}
     - N8N_BASIC_AUTH_ACTIVE=${N8N_BASIC_AUTH_ACTIVE}
     - N8N_BASIC_AUTH_USER=${N8N_BASIC_AUTH_USER}
     - N8N_BASIC_AUTH_PASSWORD=${N8N_BASIC_AUTH_PASSWORD}
     - N8N_HOST=${N8N_HOST}
     - N8N_PORT=${N8N_PORT}
     - N8N_PROTOCOL=${N8N_PROTOCOL}
     - NODE_ENV=${NODE_ENV}
     - WEBHOOK_URL=${WEBHOOK_URL}
     - GENERIC_TIMEZONE=${GENERIC_TIMEZONE}
     - N8N_ENCRYPTION_KEY=${N8N_ENCRYPTION_KEY}
     - N8N_EMAIL_MODE=${N8N_EMAIL_MODE}
     - N8N_SMTP_HOST=${N8N_SMTP_HOST}
     - N8N_SMTP_PORT=${N8N_SMTP_PORT}
     - N8N_SMTP_USER=${N8N_SMTP_USER}
     - N8N_SMPT_PASS=${N8N_SMPT_PASS}
     - N8N_SMTP_SENDER=${N8N_SMTP_SENDER}
     - N8N_SMTP_SSL=${N8N_SMTP_SSL}
    volumes:
      - ${DATA_FOLDER}/.n8n:/home/node/.n8n
      - ${DATA_FOLDER}/local_files:/files```

master logs

[email protected]:~/n8n-digital-ocean# docker logs n8n
Initializing n8n process
n8n ready on 0.0.0.0, port 5678
Version: 0.195.5

Editor is now accessible via:
https://trove.agency:5678/
Owner was set up successfully
User survey updated successfully
Started with job ID: 1 (Execution ID: 2)
Problem with execution 2: Unable to find data of execution "2" in database. Aborting execution.. Aborting.
Started with job ID: 2 (Execution ID: 3)
Problem with execution 3: Unable to find data of execution "3" in database. Aborting execution.. Aborting.
Started with job ID: 3 (Execution ID: 4)
Problem with execution 4: Unable to find data of execution "4" in database. Aborting execution.. Aborting.
Started with job ID: 4 (Execution ID: 5)
Problem with execution 5: Unable to find data of execution "5" in database. Aborting execution.. Aborting.
Started with job ID: 5 (Execution ID: 6)
Problem with execution 6: Unable to find data of execution "6" in database. Aborting execution.. Aborting.
Started with job ID: 6 (Execution ID: 7)
Problem with execution 7: Unable to find data of execution "7" in database. Aborting execution.. Aborting.
Started with job ID: 7 (Execution ID: 8)
Problem with execution 8: Unable to find data of execution "8" in database. Aborting execution.. Aborting.
Started with job ID: 8 (Execution ID: 9)
Problem with execution 9: Unable to find data of execution "9" in database. Aborting execution.. Aborting.
Started with job ID: 9 (Execution ID: 10)
Problem with execution 10: Unable to find data of execution "10" in database. Aborting execution.. Aborting.
Started with job ID: 10 (Execution ID: 11)
Problem with execution 11: Unable to find data of execution "11" in database. Aborting execution.. Aborting.
Started with job ID: 11 (Execution ID: 12)
Problem with execution 12: Unable to find data of execution "12" in database. Aborting execution.. Aborting.
Started with job ID: 12 (Execution ID: 13)
Problem with execution 13: Unable to find data of execution "13" in database. Aborting execution.. Aborting.
Started with job ID: 13 (Execution ID: 14)
Problem with execution 14: Unable to find data of execution "14" in database. Aborting execution.. Aborting.
Initializing n8n process
n8n ready on 0.0.0.0, port 5678
Version: 0.195.5

Editor is now accessible via:
https://trove.agency:5678/
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.
The session "huesukui3tb" is not registered.

.


On the Worker

On the worker I am doing this to run it

docker run \
    --detach \
    --name n8n-queue \
    -e QUEUE_BULL_REDIS_HOST=${QUEUE_BULL_REDIS_HOST} \
    -p 5679:5678 \
    n8nio/n8n n8n worker

worker logs

[email protected]:~# docker logs n8n-queue
Starting n8n worker...
UserSettings were generated and saved to: /home/node/.n8n/config
Migrations in progress, please do NOT stop the process.
query is slow: CREATE TABLE IF NOT EXISTS "credentials_entity" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(128) NOT NULL, "data" text NOT NULL, "type" varchar(128) NOT NULL, "nodesAccess" text NOT NULL, "createdAt" datetime NOT NULL, "updatedAt" datetime NOT NULL)
execution time: 4614

n8n worker is now ready
 * Version: 0.195.5
 * Concurrency: 10

Migrations finished.
Worker failed to find data of execution "2" in database. Cannot continue.
Worker failed to find data of execution "3" in database. Cannot continue.
Worker failed to find data of execution "4" in database. Cannot continue.
Worker failed to find data of execution "5" in database. Cannot continue.
Worker failed to find data of execution "6" in database. Cannot continue.
Worker failed to find data of execution "7" in database. Cannot continue.
Worker failed to find data of execution "8" in database. Cannot continue.
Worker failed to find data of execution "9" in database. Cannot continue.
Worker failed to find data of execution "10" in database. Cannot continue.
Worker failed to find data of execution "11" in database. Cannot continue.
Worker failed to find data of execution "12" in database. Cannot continue.
Worker failed to find data of execution "13" in database. Cannot continue.
Worker failed to find data of execution "14" in database. Cannot continue.

On Redis

[email protected]:~/n8n-digital-ocean# redis-cli -h <INTERNAL IP IN VPC>
<INTERNAL IP IN VPC>:6379> KEYS *
1) "bull:jobs:id"
<INTERNAL IP IN VPC>:6379> GET bull:jobs:id
"13"
<INTERNAL IP IN VPC>:6379> exit

Hey @phpguru,

The worker nodes also need to access the database to be able to load the workflows and save the execution data, It looks like you are only passing in the redis host so it won’t know about any of the otehr bits it is meant to be doing.

After I posted my configs yesterday I just stared at my config and decided that the worker probably needed the db connection too and got it working!

It was glorious.

[SOLVED]

I have more questions but I’ll start a new thread or two. Thanks for all your help.

1 Like