Update fails: 1.43.0

Kool_Baudrillard · May 27, 2024, 8:49pm

Describe the problem/error/question

Trying to update to 1.43.0.

Running n8n locally installed with npm and Postgres:

User settings loaded from: /Users/MyUser/.n8n/config
0|n8n | Initializing n8n process
0|n8n | n8n ready on 0.0.0.0, port 5678
0|n8n | Migrations in progress, please do NOT stop the process.
0|n8n | Starting migration CreateProject1714133768519
0|n8n | Migration “CreateProject1714133768519” failed, error: relation “user” does not exist
0|n8n | Error: There was an error running database migrations
0|n8n | QueryFailedError: relation “user” does not exist

It seems to be stuck on the database migration due to given error above

What is the error message (if any)?

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

**n8n version:1.43.0
**Database (default: SQLite):Postgres
n8n EXECUTIONS_PROCESS setting (default: own, main):
**Running n8n via (Docker, npm, n8n cloud, desktop app):npm
**Operating system:macOS

despairblue · May 28, 2024, 7:32am

Can you start n8n with these environment variables set DB_LOGGING_ENABLED=true DB_LOGGING_OPTIONS=all and see if you get more output.

Be aware that this could also print out data from you database so either redact the data before posting or send me a private message with the logs.

That may help me to find out where exactly the migration is failing.

Kool_Baudrillard · May 28, 2024, 8:44am

Done, I´ll send you via DM the logs.

barn4k · May 28, 2024, 9:16am

hello @Kool_Baudrillard

From what version are you trying to upgrade?

Seems you have an n8n version when there was no master user (and no auth implemented, e.g. before the v1.0). Just a suggestion

Kool_Baudrillard · May 28, 2024, 9:53am

Hi Barn, from Version 1.42.1

Wahl I can see in the logs:

0|n8n | query: CREATE EXTENSION IF NOT EXISTS “uuid-ossp”
0|n8n | At least one of the entities has uuid column, but the ‘uuid-ossp’ extension cannot be installed automatically. Please install it manually using superuser rights, or select another uuid extension.

Kool_Baudrillard · May 28, 2024, 9:54am

0|n8n | User settings loaded from: /root/.n8n/config
0|n8n | Initializing n8n process
0|n8n | query: SELECT * FROM current_schema()
0|n8n | query: CREATE EXTENSION IF NOT EXISTS “uuid-ossp”
0|n8n | At least one of the entities has uuid column, but the ‘uuid-ossp’ extension cannot be installed automatically. Please install it manually using superuser rights, or select another uuid extension.
0|n8n | query: SELECT version();
0|n8n | query: CREATE SCHEMA IF NOT EXISTS n8n
0|n8n | query: SET search_path TO n8n,public;
0|n8n | n8n ready on 0.0.0.0, port 5678

Kool_Baudrillard · May 28, 2024, 9:55am

0|n8n | query: CREATE INDEX “IDX_61448d56d61802b5dfde5cdb00” ON “n8n”.“project_relation” (“projectId”)
0|n8n | query: CREATE INDEX “IDX_5f0643f6717905a05164090dde” ON “n8n”.“project_relation” (“userId”)
0|n8n | query: SELECT id, “firstName”, “lastName”, email FROM “user” LIMIT 100 OFFSET 0
0|n8n | query failed: SELECT id, “firstName”, “lastName”, email FROM “user” LIMIT 100 OFFSET 0
0|n8n | error: error: relation “user” does not exist
0|n8n | Migration “CreateProject1714133768519” failed, error: relation “user” does not exist
0|n8n | query: ROLLBACK
0|n8n | Error: There was an error running database migrations
0|n8n | QueryFailedError: relation “user” does not exist

despairblue · May 28, 2024, 10:21am

Thanks, as @barn4k mentioned it seems the user table is missing.

Can you connect to your postgres instance and run these meta commands for me?

\l
\c [db name]
\d

The first lists all the databases in your postgres’ instance. The second connects to the n8n db and the last one shows all tables that exist.

For reference this is what it looks like for me on a new db:

n8n=# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 n8n       | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(4 rows)

n8n=# \c n8n
You are now connected to database "n8n" as user "postgres".
n8n=# \d
                        List of relations
 Schema |               Name                |   Type   |  Owner   
--------+-----------------------------------+----------+----------
 public | auth_identity                     | table    | postgres
 public | auth_provider_sync_history        | table    | postgres
 public | auth_provider_sync_history_id_seq | sequence | postgres
 public | credentials_entity                | table    | postgres
 public | event_destinations                | table    | postgres
 public | execution_data                    | table    | postgres
 public | execution_entity                  | table    | postgres
 public | execution_entity_id_seq           | sequence | postgres
 public | execution_metadata                | table    | postgres
 public | execution_metadata_id_seq         | sequence | postgres
 public | installed_nodes                   | table    | postgres
 public | installed_packages                | table    | postgres
 public | migrations                        | table    | postgres
 public | migrations_id_seq                 | sequence | postgres
 public | project                           | table    | postgres
 public | project_relation                  | table    | postgres
 public | role                              | table    | postgres
 public | role_id_seq                       | sequence | postgres
 public | settings                          | table    | postgres
 public | shared_credentials                | table    | postgres
 public | shared_workflow                   | table    | postgres
 public | tag_entity                        | table    | postgres
 public | user                              | table    | postgres
 public | variables                         | table    | postgres
 public | webhook_entity                    | table    | postgres
 public | workflow_entity                   | table    | postgres
 public | workflow_history                  | table    | postgres
 public | workflow_statistics               | table    | postgres
 public | workflows_tags                    | table    | postgres
(29 rows)

Kool_Baudrillard · May 28, 2024, 10:31am

Ok, that´s weird. I definitely have a user:

                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
------------------+----------+----------+---------+---------+-----------------------
 testDe    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
testUK-Resolve | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 n8n              | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres         | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
 template1        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
(6 rows)

n8n=# \d
Did not find any relations.
n8n=# 

schema is n8n not public

like set in the docs Supported databases and settings | n8n Docs

Kool_Baudrillard · May 28, 2024, 11:03am

n8n=# SET search_path TO n8n, public;
SET
n8n=# \dt
                   List of relations
 Schema |            Name            | Type  |  Owner   
--------+----------------------------+-------+----------
 n8n    | auth_identity              | table | postgres
 n8n    | auth_provider_sync_history | table | postgres
 n8n    | credentials_entity         | table | postgres
 n8n    | event_destinations         | table | postgres
 n8n    | execution_data             | table | postgres
 n8n    | execution_entity           | table | postgres
 n8n    | execution_metadata         | table | postgres
 n8n    | installed_nodes            | table | postgres
 n8n    | installed_packages         | table | postgres
 n8n    | migrations                 | table | postgres
 n8n    | role                       | table | postgres
 n8n    | settings                   | table | postgres
 n8n    | shared_credentials         | table | postgres
 n8n    | shared_workflow            | table | postgres
 n8n    | tag_entity                 | table | postgres
 n8n    | user                       | table | postgres
 n8n    | variables                  | table | postgres
 n8n    | webhook_entity             | table | postgres
 n8n    | workflow_entity            | table | postgres
 n8n    | workflow_history           | table | postgres
 n8n    | workflow_statistics        | table | postgres
 n8n    | workflows_tags             | table | postgres

despairblue · May 28, 2024, 11:24am

I could reproduce this by setting DB_POSTGRESDB_SCHEMA=n8n. What’s interesting is that there are migrations before CreateProject1714133768519 that already fail. The first one to fail when I start n8n from scratch is RemoveResetPasswordColumns1690000000030.

Was the schema non-public from the beginning?

Kool_Baudrillard · May 28, 2024, 11:43am

Hi, yes was non public before.

despairblue · May 28, 2024, 12:25pm

@Kool_Baudrillard it seems there is an issue with migrations and postgres when using a non-public schema. I’ll get back to you once we know more. Thanks for reporting this

Kool_Baudrillard · May 28, 2024, 1:04pm

Glad to help. Keep up the good work

despairblue · May 29, 2024, 11:51am

Keep up the good work

Thank you, we’ll do

As a quick fix, do you think you could run set the search_path for the whole database?

ALTER DATABASE n8n SET search_path TO n8n,public;

It looks like TypeORM only sets this for one connection in the pool, instead of for all of them. So it’s a bit gnarly to fix this. Judging from locally testing it, the above would fix it for you.

Let me know if that works.

Kool_Baudrillard · May 29, 2024, 12:37pm

Hi @despairblue,

I can confirm this works. Updated on local machine and aswell on my test server to the latest version.

Thx!

jan · May 30, 2024, 5:13pm

New version [email protected] got released which includes the GitHub PR 9530.

Kool_Baudrillard · May 30, 2024, 7:04pm

Thx, that was fast!

system · June 6, 2024, 7:04pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.