search

influitive / apartment

Database multi-tenancy for Rack (and Rails) applications
2.67k stars 463 forks source link

Rails 5.2.2 + Apartment Gem 2.2.0 - Error: 'One of the following schema(s) is invalid' #581

Closed tim-lynn-clark closed 5 years ago

tim-lynn-clark commented 5 years ago

Steps to reproduce

rake db:drop rake db:create rake db:migrate

Expected behavior

Public schema should be created and migrations run Tenant schemas should be created and migrations run

Actual behavior

Public schema is created and all migrations successfully run Tenant scheams are NOT created and migrations are NOT run on tenant schemas

Error: Migrating zenith tenant One of the following schema(s) is invalid: "zenith" "public" Migrating myotherapy tenant One of the following schema(s) is invalid: "myotherapy" "public"

It appears as though the Apartment gem tries to recreate the "public" schema each time it tries to create a tenant schema is failing because the "public" schema already exists. That is my guess at least, no idea what would cause this to happen.

System configuration

Gem Versions: pg 1.1.4 or 0.21.0 (tried both)

Application Config: Subdomain config.middleware.use Apartment::Elevators::Subdomain

tim-lynn-clark commented 5 years ago

There are a few of these same issues unanswered on Stackoverflow, just so you are aware.

https://stackoverflow.com/questions/43719312/apartment-gem-one-of-the-following-schemas-is-invalid

https://stackoverflow.com/questions/54341212/how-to-use-ngrok-with-multi-tenant-rails-application

https://stackoverflow.com/questions/44876650/one-of-the-following-schemas-is-invalid-test-public

https://stackoverflow.com/questions/39494139/rails-postgres-schema-issue-one-of-the-following-schemas-is-invalid-test

seanabrahams commented 5 years ago

Make sure you carefully follow the README regarding Persistent Schemas https://github.com/influitive/apartment#persistent-schemas including adding schema_search_path: "public,shared_extensions" to your database.yml.

I ran into a similar issue but resolved it after carefully reviewing the README. Also, if apartment is configured to pull tenant_names from somewhere that includes names that you haven't created a tenant for, Apartment::Tenant.create('tenant_name'), you could run into issues. Make sure you've created Tenants for your tenant_names.

howtwizer commented 5 years ago

After updating PostgreSQL to version 11.2, I started to get same error - "public" schema already exists.

After some investigating - I noticed that I can create tenant if switching config.use_sql to false. This is not acceptable in my current application, so I continue to dig.

Application on Heroku with PostgreSQL 10.7 working, so I decide to downgrade my local PostgreSQL to @10.6 - and it starts to work with use_sql = true

So I think that the reason of problem is incompatibility with PostgreSQL 11.
Similar issue was in past https://github.com/influitive/apartment/issues/532

ancorcruz commented 5 years ago

@howtwizer The issue I have found with Heroku is that no matter what postgresql version you are running, the tooling (pg_dump) gets upgraded to the latest version breaking the tenant creation.

Our application runs with postgres 10.7. However, pg_dump is on 11.x and we can not create new tenants right now. Heroku support told me there is no way to use old pg_dump versions once updated.

Every major postgresql update has been a pain with apartment so far. (9 -> 10, 10 -> 11)

howtwizer commented 5 years ago

@ancorcruz Yes, I agree with you. My heroku deployment stops to dump dbs after a week from my last comment - and version of pg was the same. I had solve this issue with @artemave solution.

mikecmpbll commented 5 years ago

does anyone understand specifically what's causing this? happy to try and work on any appropriate resolution but i'm not in a position to deep-dive into PG issues unfortunately.

ancorcruz commented 5 years ago

@mikecmpbll as my understanding of the issues with PG updates; there is no easy way to avoid them. Issues are related to pg_dump output changes, like adding the schema name to every single statement. This breaks the way apartment uses the dump file to create new tenants and has happened for the latest two major releases.

archonic commented 5 years ago

I just ran into this myself. Does anyone know the last version of pg_dump to work with apartment? Are the versions in lockstep with postgres?

Update: My production env where everything is working has pg_dump (PostgreSQL) 10.7 (Debian 10.7-1.pgdg90+1) and my local where everything is borked has pg_dump (PostgreSQL) 11.3. I'll see if downgrading locally fixes it.

archonic commented 5 years ago

Alpine makes it difficult to install particular versions of some packages. Alpine 3.9 for example has postgresql-client pinned to 11.3. You have to downgrade to Alpine 3.8 to get postgresql-client 10.x. That said downgrading to 10.x works. pg_dump 10.8 works while 11.x does not.

mikecmpbll commented 5 years ago

we can add in workarounds as we have been doing, if we understand what change is causing the issues. the parsing of the dump is pretty nasty, and i'd hope that we can evolve to a nicer solution, but for now there's no better ideas :)

artemave commented 5 years ago

Have you guys tried this pr https://github.com/influitive/apartment/pull/586 ?

archonic commented 5 years ago

Can anyone verify that master now works with pg_dump 11?