search

n8n-io / n8n

Free and source-available fair-code licensed workflow automation tool. Easily automate tasks across different services.
https://n8n.io
Other
46.65k stars 6.75k forks source link

Failed to upgraded to 1.43.0 #9965

Closed juherr closed 2 months ago

juherr commented 3 months ago

Bug Description

When upgrading from 1.42.1 to 1.43.0, the upgrade fails due to a migration error:

> n8n start
Initializing n8n process
n8n ready on 0.0.0.0, port 8080
Migrations in progress, please do NOT stop the process.
Starting migration CreateProject1714133768519
Migration "CreateProject1714133768519" failed, error: Cannot add foreign key constraint
Error: There was an error running database migrations
QueryFailedError: Cannot add foreign key constraint

To Reproduce

  1. Having a working 1.42.1 instance
  2. Upgrade to 1.43.0

Expected behavior

The migration should work

Operating System

Linux

n8n Version

1.43.0

Node.js Version

20.15.0

Database

MySQL

Execution mode

main (default)

juherr commented 3 months ago

The issue is linked to the migration file from #8922

Joffcom commented 3 months ago

Hey @juherr,

Can you try updating to 1.48.3 and see if you have the same issue?

Quick Edit: Looks like we have not changed that migration since the release so updating may not help. Internal dev ticket is PAY-1722

despairblue commented 3 months ago

@juherr could you try running version 1.43.0 again with db logging enabled:

env DB_LOGGING_ENABLED=true DB_LOGGING_OPTIONS=all n8n start

That should produce more output. The logs could contain data from the db, so you could either clean them before posting them or email them to me (danny@n8n.io). That will help getting to the bottom of this.

Thanks 🙏🏾

juherr commented 3 months ago

@despairblue 

> n8n start
Initializing n8n process
query: SELECT VERSION() AS `version`
query: SELECT `ExecutionEntity`.`id` AS `ExecutionEntity_id`, `ExecutionEntity`.`waitTill` AS `ExecutionEntity_waitTill` FROM `execution_entity` `ExecutionEntity` WHERE ( ((`ExecutionEntity`.`waitTill` <= ?) AND (`ExecutionEntity`.`status` != ?)) ) AND ( `ExecutionEntity`.`deletedAt` IS NULL ) ORDER BY `ExecutionEntity_waitTill` ASC -- PARAMETERS: ["2024-07-08T12:59:40.986Z","crashed"]
n8n ready on 0.0.0.0, port 8080
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'migrations'
Application start successful
No cron to setup
query: SELECT * FROM `XXXX`.`migrations` `migrations` ORDER BY `id` DESC
56 migrations are already loaded in the database.
58 migrations were found in the source code.
RemoveNodesAccess1712044305787 is the last executed migration. It was executed on Tue Apr 02 2024 07:51:45 GMT+0000 (Coordinated Universal Time).
2 migrations are new migrations must be executed.
query: START TRANSACTION
Migrations in progress, please do NOT stop the process.
Starting migration CreateProject1714133768519
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project'
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project_relation'
query: CREATE TABLE `project_relation` (`projectId` varchar(36) NOT NULL, `userId` varchar(36) NOT NULL, `role` varchar(255) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) REFERENCES `project` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE, PRIMARY KEY (`projectId`, `userId`)) ENGINE=InnoDB
query failed: CREATE TABLE `project_relation` (`projectId` varchar(36) NOT NULL, `userId` varchar(36) NOT NULL, `role` varchar(255) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) REFERENCES `project` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE, PRIMARY KEY (`projectId`, `userId`)) ENGINE=InnoDB
error: Error: Cannot add foreign key constraint
Migration "CreateProject1714133768519" failed, error: Cannot add foreign key constraint
query: ROLLBACK
Error: There was an error running database migrations
QueryFailedError: Cannot add foreign key constraint
despairblue commented 3 months ago

Thanks, that's already helpful.

From the logs I can see that it checks if the table project exists and then if the table project_relation exists:

query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project'
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project_relation'

Then it proceeds to try to create the project_relation table.

That is unexpected, because it means that there is already a project table in your schema.

I don't think it's from executing the migration previously as it runs in a transaction. Do you have any idea what that table is and where it comes from?

For reference this is what n8n outputs for me (it first checks if the project table exists, then creates it, then checks for the project_relation table and then creates that one):

...
Starting migration CreateProject1714133768519
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'n8n' AND `TABLE_NAME` = 'project'
query: CREATE TABLE `project` (`id` varchar(36) NOT NULL, `name` varchar(255) NOT NULL, `type` varchar(36) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3
), PRIMARY KEY (`id`)) ENGINE=InnoDB
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'n8n' AND `TABLE_NAME` = 'project_relation'
query: CREATE TABLE `project_relation` (`projectId` varchar(36) NOT NULL, `userId` varchar(36) NOT NULL, `role` varchar(255) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT C
URRENT_TIMESTAMP(3), INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) REFERENCES `project` (`id`) ON DELETE CASCAD
E, CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE, PRIMARY KEY (`projectId`, `userId`)) ENGINE=InnoDB
...
juherr commented 2 months ago

@despairblue Good catch. The table was created on 2024-07-06T00:12.

But:

2024-07-06T00:12:49.748Z > n8n start
2024-07-06T00:12:51.892Z Initializing n8n process
2024-07-06T00:12:52.228Z n8n ready on 0.0.0.0, port 8080
2024-07-06T00:12:52.240Z Migrations in progress, please do NOT stop the process.
2024-07-06T00:12:52.240Z Starting migration CreateProject1714133768519
2024-07-06T00:12:52.314Z Migration "CreateProject1714133768519" failed, error: Cannot add foreign key constraint
2024-07-06T00:12:52.317Z Error: There was an error running database migrations
2024-07-06T00:12:52.317Z QueryFailedError: Cannot add foreign key constraint

I removed the project table, and started again:

Initializing n8n process
query: SELECT VERSION() AS `version`
query: SELECT `ExecutionEntity`.`id` AS `ExecutionEntity_id`, `ExecutionEntity`.`waitTill` AS `ExecutionEntity_waitTill` FROM `execution_entity` `ExecutionEntity` WHERE ( ((`ExecutionEntity`.`waitTill` <= ?) AND (`ExecutionEntity`.`status` != ?)) ) AND ( `ExecutionEntity`.`deletedAt` IS NULL ) ORDER BY `ExecutionEntity_waitTill` ASC -- PARAMETERS: ["2024-07-09T07:16:29.148Z","crashed"]
n8n ready on 0.0.0.0, port 8080
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'migrations'
query: SELECT * FROM `XXXX`.`migrations` `migrations` ORDER BY `id` DESC
56 migrations are already loaded in the database.
58 migrations were found in the source code.
RemoveNodesAccess1712044305787 is the last executed migration. It was executed on Tue Apr 02 2024 07:51:45 GMT+0000 (Coordinated Universal Time).
2 migrations are new migrations must be executed.
query: START TRANSACTION
Migrations in progress, please do NOT stop the process.
Starting migration CreateProject1714133768519
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project'
query: CREATE TABLE `project` (`id` varchar(36) NOT NULL, `name` varchar(255) NOT NULL, `type` varchar(36) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`)) ENGINE=InnoDB
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'XXXX' AND `TABLE_NAME` = 'project_relation'
query: CREATE TABLE `project_relation` (`projectId` varchar(36) NOT NULL, `userId` varchar(36) NOT NULL, `role` varchar(255) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) REFERENCES `project` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE, PRIMARY KEY (`projectId`, `userId`)) ENGINE=InnoDB
query failed: CREATE TABLE `project_relation` (`projectId` varchar(36) NOT NULL, `userId` varchar(36) NOT NULL, `role` varchar(255) NOT NULL, `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) REFERENCES `project` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE, PRIMARY KEY (`projectId`, `userId`)) ENGINE=InnoDB
error: Error: Cannot add foreign key constraint
Migration "CreateProject1714133768519" failed, error: Cannot add foreign key constraint
query: ROLLBACK
Error: There was an error running database migrations
QueryFailedError: Cannot add foreign key constraint

⚠️ Fyi, the rollback didn't remove the project table. That explains why it was existing in my previous log too.

After investigating, the issue comes from the collations that were different between project / project_relation and user. I tried to fix this by hand:

CREATE TABLE `project` (
  `id` varchar(36) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `type` varchar(36) NOT NULL, 
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  PRIMARY KEY (`id`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=InnoDB;

CREATE TABLE `project_relation` (
  `projectId` varchar(36) NOT NULL, 
  `userId` varchar(36) NOT NULL, 
  `role` varchar(255) NOT NULL, 
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), 
  INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), 
  CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) 
    REFERENCES `project` (`id`) 
    ON DELETE CASCADE, 
  CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE,
  PRIMARY KEY (`projectId`, `userId`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=InnoDB;

I did the same with project_relation:

CREATE TABLE `project_relation` (
  `projectId` varchar(36) NOT NULL, 
  `userId` varchar(36) NOT NULL, 
  `role` varchar(255) NOT NULL, 
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  INDEX `IDX_61448d56d61802b5dfde5cdb00` (`projectId`), 
  INDEX `IDX_5f0643f6717905a05164090dde` (`userId`), 
  CONSTRAINT `FK_61448d56d61802b5dfde5cdb002` FOREIGN KEY (`projectId`) 
    REFERENCES `project` (`id`) 
    ON DELETE CASCADE, 
  CONSTRAINT `FK_5f0643f6717905a05164090dde7` FOREIGN KEY (`userId`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE,
  PRIMARY KEY (`projectId`, `userId`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=InnoDB;

But it fails later:

query: ALTER TABLE `shared_credentials` ADD `projectId` varchar(36) NULL DEFAULT NULL
query failed: ALTER TABLE `shared_credentials` ADD `projectId` varchar(36) NULL DEFAULT NULL
error: Error: Duplicate column name 'projectId'
Migration "CreateProject1714133768519" failed, error: Duplicate column name 'projectId'

I don't know why the migration is trying to add a new column because it looks to fetch columns: 

SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'blsmsrco9gtbpje9xqau'
AND `TABLE_NAME` = 'shared_credentials';

The result on my database is: 

def blsmsrco9gtbpje9xqau    shared_credentials  createdAt   1   CURRENT_TIMESTAMP   NO  datetime    NULL    NULL    NULL    NULL    0   NULL    NULL    datetime            select,insert,update,references     
def blsmsrco9gtbpje9xqau    shared_credentials  updatedAt   2   CURRENT_TIMESTAMP   NO  datetime    NULL    NULL    NULL    NULL    0   NULL    NULL    datetime            select,insert,update,references     
def blsmsrco9gtbpje9xqau    shared_credentials  userId  3   NULL    NO  varchar 36  144 NULL    NULL    NULL    utf8mb4 utf8mb4_general_ci  varchar(36) PRI     select,insert,update,references     
def blsmsrco9gtbpje9xqau    shared_credentials  credentialsId   4   NULL    NO  varchar 36  144 NULL    NULL    NULL    utf8mb4 utf8mb4_general_ci  varchar(36) PRI     select,insert,update,references     
def blsmsrco9gtbpje9xqau    shared_credentials  role    5   NULL    NO  text    65535   65535   NULL    NULL    NULL    utf8mb4 utf8mb4_general_ci  text            select,insert,update,references     
def blsmsrco9gtbpje9xqau    shared_credentials  projectId   6   NULL    NO  varchar 36  144 NULL    NULL    NULL    utf8mb4 utf8mb4_general_ci  varchar(36) MUL     select,insert,update,references

If it can help, the MySQL server version is currently: 5.7.42

despairblue commented 2 months ago

TIL that DDL statements create an implicit commit on MySQL.

https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html

Wow. So that means that when the migration ran for the first time it's possible there was a different error and that that error is what would tell us how to fix the migration.

That also means we'd have to manually undo all statements that MySQL implicitly committed. That way we can retry the migration and see the initial error. Then fix it and undo the implicit commits again and retry, hopefully seeing it succeed. Otherwise we have to repeat this until we find the culprit.

This can be time consuming so I'd like to point out the other options we have for completeness sake:

  1. Create a new db from scratch and migrate workflows and credentials there using the CLI (e.g. n8n export:workflow ... && n8n import:workflow --separate ...).
  2. Same as 1. but additionally migrating to PostgresSQL, this has the added benefit of being future proof as we will drop support for MySQL at some point in the future.
  3. What I said above, we'd have to undo the partial migration similar to what you did already, e.g. dropping tables, removing columns, etc.

Let me know how you'd like to continue.

juherr commented 2 months ago

I restored to 1.42.1 which is still working.

Due to the end of MySQL support, I think the best option will be the PG migration.

Thanks for your help.