Unable to start n8n due to error: There was an error initializing DB: "Table 'role' already exists"

Questions
#1

I am using docker and MySQL database to initialise my n8n but I keep getting the error There was an error initializing DB: "Table ‘role’ already exists"

#2

Hi @Kamara_Fidele, welcome to the community!

I am sorry to hear you’re having trouble. This sounds like it might be an error caused by an interrupted previous database migration, similar to what has happened here: Latest Update causing issues "SQLITE_ERROR: table "temporary user" already exists" - #2 by MutedJam

My suggestion would be to follow the steps described in the post above (switching databases would require updating the db credentials when using MySQL rather than moving a file like in the post).

You could also try deleting the role table manually. As with all manual database operations, you might want to take a backup beforehand.

#3

Hello @MutedJam , thanks for the reply.
Let me try the steps mentioned previously in Latest Update causing issues "SQLITE_ERROR: table "temporary user" already exists"

But it’s for the first time I am starting n8n so there was no any migrations before.

#4

Oh, that’s good to know. Are you perhaps using a non-empty database then which already came with a role table? In that case you might want to consider simply creating a fresh database for your n8n instance as n8n would create these tables on first start:

image
image935×582 33.9 KB

#5

At the first, I thought the problem was that. Then I created a new fresh database.
But nothing changed.

#6

Woah, that’s weird. I just started a fresh n8n instance using the below docker-compose.yml file:

services:
  mysql:
    image: mysql:8
    restart: unless-stopped
    environment:
      - MYSQL_ROOT_PASSWORD=MysqlPassword1234
      - MYSQL_DATABASE=n8n
      - MYSQL_USER=n8n
      - MYSQL_PASSWORD=n8n
    ports:
      - 3306:3306

  adminer:
    image: adminer:latest
    restart: unless-stopped
    ports:
      - 8080:8080

  n8n:
    depends_on:
      - mysql
    image: n8nio/n8n:0.181.2
    restart: unless-stopped
    environment:
      - N8N_PORT=5678
      - DB_TYPE=mysqldb
      - DB_MYSQLDB_HOST=mysql
      - DB_MYSQLDB_USER=n8n
      - DB_MYSQLDB_PASSWORD=n8n
      - N8N_LOG_LEVEL=debug
      - N8N_DIAGNOSTICS_ENABLED=false
      - N8N_VERSION_NOTIFICATIONS_ENABLED=false
      - N8N_PERSONALIZATION_ENABLED=false
      - N8N_HIRING_BANNER_ENABLED=false
      # - N8N_USER_MANAGEMENT_DISABLED=true
    ports:
      - 5678:5678

Launched just fine (don’t use it for production use cases though, this config does not keep your data on container recreation):

image
image1914×384 163 KB

Can you confirm how exactly you’re starting n8n and MySQL? Are you also running into trouble when setting the DB_TABLE_PREFIX environment variable?

#7

I am currently using the latest docker image.
I am using this command to start the container:

docker run -d
–restart unless-stopped
–name n8n
-p 80:5678
-e DB_TYPE=mysqldb
-e DB_MYSQLDB_DATABASE=db_name
-e DB_MYSQLDB_HOST=db_host
-e DB_MYSQLDB_PORT=db_port
-e DB_MYSQLDB_USER=db_user
-e DB_MYSQLDB_PASSWORD=db_password
-v ~/.n8n:/home/node/.n8n
n8nio/n8n
n8n start

#8

Looking fine to me :smiley:

How do you create your database with the role table already present? Did the DB_TABLE_PREFIX variable change anything for you?

#9

There is no role table present when I create the database.
It is getting created after starting the container.

But I wasn’t setting the DB_TABLE_PREFIX variable.
Let me set it and see if anything change.

#10

Nothing changes even when I set the DB_TABLE_PREFIX variable.
The error “There was an error initializing DB: "Table ‘role’ already exists” keeps showing

#11

Unfortunately I am still unable to reproduce the problem on my end. Any chance you can provide a docker-compose.yml file including your database setup using which the problem can be reproduced?