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:
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):
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_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
Looking fine to me 
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'
services:
n8n:
image: n8nio/n8n:0.201.0
networks:
- traefik-public
deploy:
labels:
- "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"
volumes:
- upload:/data
environment:
- 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
- N8N_BASIC_AUTH_PASSWORD=bar
# 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_FUNCTION_ALLOW_EXTERNAL=uuid
- 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?