Migration failing QueryFailedError: syntax error at or near ":"

[xav]

Describe the problem/error/question

I’m trying to update n8n to the latest stable on docker. I did stop + rm the exiting n8n docker and starting it again with:

docker run -d --hostname n8n.local
–name n8n
–net n8n --ip 10.9.0.2
-m 12g
–memory-swap 2g
–cpus=1
-p 5678:5678
-e WEBHOOK_URL={https://example.orgl}
-e DB_TYPE=postgresdb
-e DB_POSTGRESDB_DATABASE=n8n
-e DB_POSTGRESDB_HOST=postgresql
-e DB_POSTGRESDB_USER=myuser
-e DB_POSTGRESDB_PASSWORD=mypassword
-e N8N_BASIC_AUTH_ACTIVE=true
-e N8N_BASIC_AUTH_USER=admin
-e N8N_BASIC_AUTH_PASSWORD=my password
-e NODE_FUNCTION_ALLOW_BUILTIN=*
-e GENERIC_TIMEZONE=“Europe/Berlin”
-e EXECUTIONS_PROCESS=main
-v /srv/n8n:/data
–restart unless-stopped
-d docker.n8n.io/n8nio/n8n

What is the error message (if any)?

docker logs

n8n ready on 0.0.0.0, port 5678
Migrations in progress, please do NOT stop the process.
QueryFailedError: syntax error at or near ":"
QueryFailedError: syntax error at or near ":"
...
QueryFailedError: syntax error at or near ":"
QueryFailedError: syntax error at or near ":"
Error: There was an error running database migrations
QueryFailedError: current transaction is aborted, commands ignored until end of transaction block
Last session crashed

Is there a way to run the migration manually or have more details of what is not working?

Information on your n8n setup

• n8n version: not sure docker.n8n.io/n8nio/n8n
• Database (default: SQLite): postgress
• n8n EXECUTIONS_PROCESS main:
• Running n8n via Docker
• Operating system: ubuntu

[Jon]

Hey @xav,

Welcome to the community

Which version of n8n are you upgrading from? You could try setting DB_LOGGING_ENABLED to true and DB_LOGGING_OPTIONS to all to see what is happening. I would also remove the N8N_BASIC_X env options as they have not been used since the release of v1.

[Alvaro_Hernandez]

Hello, I have de same problem. In my case i run on kubernetes and after helm upgrade from 0.175.1 version to last stable version.

here is the db_logging:

Loading config overwrites [ '/n8n-config/config.json', '/n8n-secret/secret.json' ]
Last session crashed
query: SELECT * FROM current_schema()
query: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
query: SELECT version();
query: SET search_path TO public;
n8n ready on 0.0.0.0, port 5678
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'migrations'
query: SELECT * FROM "public"."migrations" "migrations" ORDER BY "id" DESC
17 migrations are already loaded in the database.
47 migrations were found in the source code.
IntroducePinData1654090467022 is the last executed migration. It was executed on Wed Jun 01 2022 13:34:27 GMT+0000 (Coordinated Universal Time).
30 migrations are new migrations must be executed.
query: START TRANSACTION
Migrations in progress, please do NOT stop the process.
query: SELECT id, nodes FROM "workflow_entity" LIMIT 100 OFFSET 0
query: UPDATE "workflow_entity" SET nodes = $2 WHERE id = :id -- PARAMETERS: [1,"[{\"parameters\":{},\"name\":\"Start\",\"type\":\"n8n-nodes-base.start\",\"typeVersion\":1,\"position\":[240,300],\"id\":\"dfcc56b7-a5fc-4d39-8080-8ce747cdb1>
query: UPDATE "workflow_entity" SET nodes = $2 WHERE id = :id -- PARAMETERS: [2,"[{\"parameters\":{},\"name\":\"Start\",\"type\":\"n8n-nodes-base.start\",\"typeVersion\":1,\"position\":[240,300],\"id\":\"72409b8e-1edb-4ce3-8013-f112267efc>
query: INSERT INTO "public"."migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1658932090381,"AddNodeIds1658932090381"]
query failed: UPDATE "workflow_entity" SET nodes = $2 WHERE id = :id -- PARAMETERS: [1,"[{\"parameters\":{},\"name\":\"Start\",\"type\":\"n8n-nodes-base.start\",\"typeVersion\":1,\"position\":[240,300],\"id\":\"dfcc56b7-a5fc-4d39-8080-8ce>
error: error: syntax error at or near ":"
QueryFailedError: syntax error at or near ":"
query failed: UPDATE "workflow_entity" SET nodes = $2 WHERE id = :id -- PARAMETERS: [2,"[{\"parameters\":{},\"name\":\"Start\",\"type\":\"n8n-nodes-base.start\",\"typeVersion\":1,\"position\":[240,300],\"id\":\"72409b8e-1edb-4ce3-8013-f11>
error: error: syntax error at or near ":"
QueryFailedError: syntax error at or near ":"
query failed: INSERT INTO "public"."migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1658932090381,"AddNodeIds1658932090381"]
error: error: current transaction is aborted, commands ignored until end of transaction block
query: ROLLBACK
Error: There was an error running database migrations
QueryFailedError: current transaction is aborted, commands ignored until end of transaction block
Loading config overwrites [ '/n8n-config/config.json', '/n8n-secret/secret.json' ]
Last session crashed

Here is the content of the migrations table:

Thanks for the answer

[Jon]

Hey @Alvaro_Hernandez,

Welcome to the community

There does appear to be an issue when upgrading from old versions to the latest, Assuming you have a backup of your database and n8n it would be worth reverting for now.

Would you also be able to try upgrading to 1.1.1 then going to the latest?

[xav]

If I were to run:

docker pull docker.n8n.io/n8nio/n8n:1.1.1

and docker run … -d docker.n8n.io/n8nio/n8n it should work?

[Jon]

Hey @xav,

It would depend on what version you are upgrading from but I was just taking a quick look and I believe that will indeed work.