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

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"

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.

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.

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:

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

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

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

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

      - mysql
    image: n8nio/n8n:0.181.2
    restart: unless-stopped
      - N8N_PORT=5678
      - DB_TYPE=mysqldb
      - DB_MYSQLDB_HOST=mysql
      - DB_MYSQLDB_USER=n8n
      - N8N_LOG_LEVEL=debug
      - 5678:5678

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

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?

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_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
n8n start

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?

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.

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

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?

I’m experiencing this as well… if I run with 0.167.0 or less, it’s not an issue, but if I try to run anything higher, I get this error. I have verified that the database is completely table-less before starting the container. I’m using Docker Compose… here’s my template:

version: '3.8'

    image: n8nio/n8n:0.201.0
      - traefik-public
        - "traefik.enable=true"
        - "traefik.constraint-label=traefik-public"
        - "traefik.http.routers.{{ inst_name }}-n8n.rule=(Host(`{{inst_name}}.cerulean.bywatersolutions.tools`) && PathPrefix(`/process`))"
        - "traefik.http.routers.{{ inst_name }}-n8n.entrypoints=http"
        - "traefik.http.routers.{{ inst_name }}-n8n.service={{inst_name}}-n8n"
        - "traefik.http.routers.{{ inst_name }}-n8n.middlewares=process-strip"
        - "traefik.http.services.{{ inst_name }}-n8n.loadbalancer.server.port=5678"
        - "traefik.http.middlewares.process-strip.stripprefix.prefixes=/process"
      - upload:/data
      - DB_TYPE=mysqldb
      - DB_MYSQLDB_HOST={{ db_host }}
      - DB_MYSQLDB_PORT={{ db_port }}
      - DB_MYSQLDB_DATABASE={{ inst_name}}_n8n
      - DB_MYSQLDB_USER={{ db_user }}
      - DB_MYSQLDB_PASSWORD={{ db_pass }}
      # Basic auth credentials
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=foo
      # Other settings
      - N8N_HOST={{ inst_name }}.{{ domain }}
      - N8N_PROTOCOL=https
      - N8N_PATH=/process/
      - WEBHOOK_TUNNEL_URL=https://{{ inst_name }}.{{ domain }}/process
      - N8N_ENCRYPTION_KEY={{ inst_encryption_key }}
      - GENERIC_TIMEZONE={{ inst_tz }}
      - N8N_METRICS=true
      # Configurations for NodeJS; includes modules needed
      - NODE_ENV=production
      - NODE_OPTIONS='--max_old_space_size=1024'

I’m doing this in DigitalOcean, and I’m reading here that this is problematic: I can't start n8n on a fresh database. @Kamara_Fidele , is that where you’re working, too?