N8N not starting after new docker pull

[cloudz]

Describe the issue/error/question

N8N isn’t starting anymore. Stuck in migration if I look at the logs. It finds a duplicate column.

What is the error message (if any)?

Migration “CreateLdapEntities1674509946020” failed, error: Duplicate column name ‘disabled’

Information on your n8n setup

• n8n version: docker:latest
• Database you’re using (default: SQLite): mariadb 10.6
• Running n8n with the execution process [own(default), main]:
• Running n8n via [Docker, npm, n8n.cloud, desktop app]: docker

[edit]
I fixed it by adding a line to the ‘migrations’ table in the database, specifying that specific migration with a timestamp of now() …
It pushed through after that - but I don’t know if it broke something.
The migration itself kept adding the “disabled” column to the user table.

[MutedJam]

@cloudz

CreateLdapEntities1674509946020

Hi @cloudz, welcome to the community and sorry for the trouble.

It seems this particular issue was caused by an earlier image sent to a few users allowing them to test our LDAP functionality.

Perhaps @RicardoE105 or @netroy can confirm whether anything else is needed to fix the migration for good?

[netroy]

Yeah. CreateLdapEntities1674509946020 was part of the LDAP test image, and is not included in any of the released images.

Generally when testing custom images, and then reverting back to a stable image, I highly recommend manually reverting any migrations using n8n db:revert to make sure that there are no invalid migrations in place.

[cloudz]

I simply did an docker:latest - so that was probably at the wrong time.
I’ve since moved away from MariaDB and moved everything over to Postgres. It’s running fine now.

[kimtiago]

I’m facing the same problem. I’m using docker:latest and using mariadb too…

You from n8n team must check docker image… this image is coming with the ‘CreateLdapEntities1674509946020’ migration.

The problem is this migration query:

CREATE TABLE IF NOT EXISTS `auth_identity` (
	`userId` VARCHAR(36) REFERENCES `user`(id),
	`providerId` VARCHAR(64) NOT NULL,
	`providerType` VARCHAR(32) NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`providerId`, `providerType`)
) ENGINE='InnoDB';

The creation of this table is showing me the error

Error in foreign key constraint of table n8n.auth_identity:
Create table n8n.auth_identity with foreign key (userId) constraint failed. Field type or character set for column ‘userId’ does not match referenced column ‘id’

The DDL of my table user is

CREATE TABLE `user` (
  `id` varchar(36) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `firstName` varchar(32) DEFAULT NULL,
  `lastName` varchar(32) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `resetPasswordToken` varchar(255) DEFAULT NULL,
  `resetPasswordTokenExpiration` int(11) DEFAULT NULL,
  `personalizationAnswers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedAt` datetime NOT NULL DEFAULT current_timestamp(),
  `globalRoleId` int(11) NOT NULL,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `apiKey` varchar(255) DEFAULT NULL,
  `disabled` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_e12875dfb3b1d92d7d7c5377e2` (`email`),
  UNIQUE KEY `UQ_ie0zomxves9w3p774drfrkxtj5` (`apiKey`),
  KEY `FK_f0609be844f9200ff4365b1bb3d` (`globalRoleId`),
  CONSTRAINT `FK_f0609be844f9200ff4365b1bb3d` FOREIGN KEY (`globalRoleId`) REFERENCES `role` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

To fix this, I executed manually the query with this additional parameter

CREATE TABLE IF NOT EXISTS `auth_identity` (
	`userId` VARCHAR(36) CHARACTER SET utf8mb3 REFERENCES `user` (id),
	`providerId` VARCHAR(64) NOT NULL,
	`providerType` VARCHAR(32) NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`providerId`, `providerType`)
) ENGINE='InnoDB'; 

I can’t remember if I changed charset of this table in the past, but my mariadb instance uses the default charset utf8mb4

I don’t know if this is a bug from n8n with mariadb…

[cafuego]

I’m seeing this exact same error on the current :latest and :next tags. I am running with a MariaDB (on RDS) backend.

The work-around provided above won’t work, as the my user table actually contains the id field as an integer, not a varchar. So I’m a bit stuck.

If the migration CreateLdapEntities1674509946020 should not be present in the docker image, why is it still trying to run on startup? Can I get rid of it?

[Jon]

Hey @cafuego,

Welcome to the community

The migration should be there and should be fixed now but it would be worth noting that MySQL / MariaDB support will be dropped soon so it may be worth thinking about migrating to Postgres.

[cafuego]

Hey Jon - I ended up sorting my issue the same way @kimtiago did; setting consistent charsets and collations on all tables.

Is there a way to migrate existing data from a MySQL/Maria instance to Postgres?

[kimtiago]

@Jon , can you give a deadline for this mariadb/MySQL support drop? Is this intention documented somewhere?

[Mulen]

Many people use MariaDB or MySQL. I don’t know what is the thing that you want to migrate away from these databases. They both support column store engines, JSON data types etc. Also the data that n8n stores are not having complex data types as i can see.

[Jon]

A few replies to do

@cafuego we don’t have anything documented yet but one thing you could do is run the cli commands to export all the workflows and credentials then update the config and do the first run to create your owner account and import them all.

@kimtiago the docs PR needs to be reviewed but the plan is to drop support in v1.0 which could be released next month. Current installs will work as they do now so there is no rush unless you really need a feature that is available in 1.0, There will be other breaking changes as well and I am sure we will make a post soon about it.

@Mulen Our data suggests that MariaDB / MySQL is used by a very small % of our users but it costs a fair amount of time in dev resources to keep it supported and tested. By dropping support we can focus more on adding new features, improving existing features and seeing what we can do to improve overall performance. Of course not everyone will be happy with this, I know I wasn’t as I did some installations before I started here for some clients of mine and now I have to spend some time doing migrations but I think generally the change will be good.

[Mulen]

Thanks for the explanation.

Would you provide a guide to migrate the existing data from MariaDB to Postgresql?

Let’s say i can move the existing data as is to postgresql. If i point afterwards the db connection from n8n to the new database, would n8n start normally?

[Jon]

Hey @Mulen,

I am thinking a detailed guide might be worth doing although the migration steps would be export all workflows and credentials using the cli tool then update the config and do the first run and run the cli again to import them.

I believe there are tools out there that can export data from MySQL / MariaDB and import them into Postgres but I have never tried using one with n8n, In theory it could work and I think there was someone recently on here or on Discord that gave it a go and managed to get it working.