Migration failed while upgrade from 233.1 to 236.1

[vincentcsm]

Hello, everyone!
I’m trying upgrading from version 0.233.1 to 0.236.1 received error:

Loading config overwrites [ '/n8n-config/config.json', '/n8n-secret/secret.json' ]
2023-07-21T08:30:36.527Z | warn     | License manager not initialized "{ file: 'License.js', function: 'isFeatureEnabled' }"
2023-07-21T08:30:36.534Z | error    | Last session crashed "{ file: 'CrashJournal.js', function: 'init' }"
2023-07-21T08:30:46.960Z | debug    | Lazy Loading credentials and nodes from n8n-nodes-base "{\n  credentials: 320,\n  nodes: 428,\n  file: 'DirectoryLoader.js',\n  function: 'loadAll'\n}"
n8n ready on 0.0.0.0, port 5678
2023-07-21T08:30:47.376Z | warn     | Migrations in progress, please do NOT stop the process. "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
2023-07-21T08:30:47.377Z | debug    | Starting migration MigrateIntegerKeysToString1690000000000 "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
query failed: CREATE UNIQUE INDEX "pk_workflow_entity_id" ON workflow_entity ("id");
error: error: relation "pk_workflow_entity_id" already exists

anyway to resolve it? It keep crashing

n8n setup

• n8n version: 0.233.1 → 0.236.1
• Database (default: SQLite): Postgres
• Running n8n via (Docker, npm, n8n cloud, desktop app): k8s

[EmeraldHerald]

Hi @vincentcsm Sorry you’re running into this.

The migration is part of the [email protected] release. This error might have occurred because the migration didn’t complete on an initial run when it was first executed.

• Can you restore a backup of his old db before updating?
• Can you try again and make sure the migration runs through the first time?

[vincentcsm]

Hi @EmeraldHerald
Thanks your prompt reply, the last DB backup is quite a long times ago, so it maybe the last option for me, Yes, I think it is auto upgrade and filed since I’m using the latest image tab in my deployment.
after I realize the n8n was down then I change tag back to 0.233.1 and the system is able to come back and up, however, I can’t create new workflow and all the current workflow can’t execute.

I have just tried again the upgrade to 0.234.0 but the DB log error message is the same.
any suggestions?
Thanks a lot.

[EmeraldHerald]

Ah, sorry to hear that @vincentcsm Unfortunately that suggestion would have come from someone on our development team, so that would be the advice I have for you

The reason you can’t downgrade past 0.234.0 is due to this being an irreversible migration - after the database has begun the migration process, you won’t be able to go back to older versions past this point.

[vincentcsm]

Hi @EmeraldHerald
so the issue is during the migration, there was an unexpected issue that interrupted the process,
anyway, I can trigger again the migration or resolve the table that is mentioned in the error logs?
Sorry for troubling you.

[EmeraldHerald]

I checked in trying to get a second opinion here, and I’m afraid there really isn’t another option than restoring a database backup and ensuring the migration completes.

I’ll give @krynble a ping here too, just in case we’ve all missed something.

[krynble]

Hey @vincentcsm

Thanks for pinging me @EmeraldHerald - I hope the below steps can help.

Given that restoring a backup might be a very bad option, you can try to actually run the migration manually.

This takes into account that you have full database access to perform queries and are familiar with what migrations mean.

Also make sure that n8n is not running while attempting this.

The migration is nothing more than a bunch of queries executed one at a time, in sequence, as described in this file for Postgres.

It seems the migration is failing at around line 19 of the above file. You could check if the other steps were performed and if not, then reproduce them manually.

Once you are done with all steps, all that is left to do is manually insert a single row to the migrations table reflecting the manually executed migration. Your table show look somewhat like the one in this table (the last row is the one that you must manually add)

I hope this helps!

[vincentcsm]

Hi @krynble
Thanks your suggestion, after run the querys and insert the row,
below is what my migration looks like now.

however when I use the image tab 0.236.1 I got another error

2023-07-21T17:03:27.722Z | warn     | Migrations in progress, please do NOT stop the process. "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
2023-07-21T17:03:27.723Z | debug    | Starting migration SeparateExecutionData1690000000020 "{ file: 'migrationHelpers.js', function: 'logMigrationStart' }"
query failed: INSERT INTO "execution_data" (
				"executionId",
				"workflowData",
				"data")
				SELECT "id", "workflowData", "data" FROM "execution_entity"
error: error: missing chunk number 0 for toast value 1838704 in pg_toast_16417

[vincentcsm]

Dear @krynble @EmeraldHerald
Thanks, both for your great help, the latest issue has been resolved by truncating the execute_entity table.
Now I can start with 0.236.1,
Let me do some functionality tests.
Thanks a lot.

[vincentcsm]

Hi @krynble
just want to make sure
is this foreign key setting in execution_entity I recreate correct?

I didn’t confirm the detail before removing the original one.
Sorry.

[krynble]

@vincentcsm

missing chunk number 0 for toast value 1838704 in pg_toast_16417

I’ve never seen this error before, but maybe these steps can help you.

About the foreign key, yes, that seems the case as of line 178 here.