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?