ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column categories.search_priority does not exist

[DeanMarkTaylor (Dean Taylor)]

Currently trying to rebuild and seeing the following error:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column categories.search_priority does not exist

Detail

precompile target: desktop Default
rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column categories.search_priority does not exist
LINE 1: ..."categories"."navigate_to_first_post_after_read", "categorie...
                                                             ^
: SELECT "categories"."id", "categories"."name", "categories"."color", "categories"."topic_id", "categories"."topic_count", "categories"."created_at", "categories"."updated_at", "categories"."user_id", "categories"."topics_year", "categories"."topics_month", "categories"."topics_week", "categories"."slug", "categories"."description", "categories"."text_color", "categories"."read_restricted", "categories"."auto_close_hours", "categories"."post_count", "categories"."latest_post_id", "categories"."latest_topic_id", "categories"."position", "categories"."parent_category_id", "categories"."posts_year", "categories"."posts_month", "categories"."posts_week", "categories"."email_in", "categories"."email_in_allow_strangers", "categories"."topics_day", "categories"."posts_day", "categories"."allow_badges", "categories"."name_lower", "categories"."auto_close_based_on_last_post", "categories"."topic_template", "categories"."contains_messages", "categories"."sort_order", "categories"."sort_ascending", "categories"."uploaded_logo_id", "categories"."uploaded_background_id", "categories"."topic_featured_link_allowed", "categories"."all_topics_wiki", "categories"."show_subcategory_list", "categories"."num_featured_topics", "categories"."default_view", "categories"."subcategory_list_style", "categories"."default_top_period", "categories"."mailinglist_mirror", "categories"."suppress_from_latest", "categories"."minimum_required_tags", "categories"."navigate_to_first_post_after_read", "categories"."search_priority" FROM "categories" WHERE (uploaded_background_id IS NOT NULL)

/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:69:in `async_exec_params'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:69:in `exec_params'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:611:in `block (2 levels) in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:610:in `block in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:609:in `exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:596:in `execute_and_clear'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:81:in `exec_query'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:470:in `select'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:62:in `select_all'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/querying.rb:41:in `find_by_sql'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:560:in `block in exec_queries'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:584:in `skip_query_cache_if_necessary'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:547:in `exec_queries'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:422:in `load'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:200:in `records'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation/delegation.rb:71:in `each'
/var/www/discourse/lib/stylesheet/importer.rb:70:in `block in <class:Importer>'
/var/www/discourse/lib/stylesheet/importer.rb:157:in `instance_eval'
/var/www/discourse/lib/stylesheet/importer.rb:157:in `imports'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/import_handler.rb:29:in `block in import_function'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/engine.rb:40:in `compile_data_context'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/engine.rb:40:in `render'
/var/www/discourse/lib/stylesheet/compiler.rb:38:in `compile'
/var/www/discourse/lib/stylesheet/compiler.rb:20:in `compile_asset'
/var/www/discourse/lib/stylesheet/manager.rb:162:in `compile'
/var/www/discourse/lib/stylesheet/manager.rb:96:in `block (2 levels) in precompile_css'
/var/www/discourse/lib/stylesheet/manager.rb:89:in `each'
/var/www/discourse/lib/stylesheet/manager.rb:89:in `block in precompile_css'
/var/www/discourse/lib/stylesheet/manager.rb:88:in `each'
/var/www/discourse/lib/stylesheet/manager.rb:88:in `precompile_css'
/var/www/discourse/lib/tasks/assets.rake:60:in `block (2 levels) in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:268:in `block in each_connection'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:266:in `each'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:266:in `each_connection'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:71:in `each_connection'
/var/www/discourse/lib/tasks/assets.rake:53:in `block in <top (required)>'
/var/www/discourse/lib/tasks/assets.rake:235:in `block in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rake-12.3.2/exe/rake:27:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'


Caused by:
PG::UndefinedColumn: ERROR:  column categories.search_priority does not exist
LINE 1: ..."categories"."navigate_to_first_post_after_read", "categorie...
                                                             ^
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:69:in `async_exec_params'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:69:in `exec_params'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:611:in `block (2 levels) in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:610:in `block in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activesupport-5.2.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:609:in `exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:596:in `execute_and_clear'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:81:in `exec_query'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:470:in `select'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:62:in `select_all'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/querying.rb:41:in `find_by_sql'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:560:in `block in exec_queries'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:584:in `skip_query_cache_if_necessary'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:547:in `exec_queries'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:422:in `load'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation.rb:200:in `records'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.2.1/lib/active_record/relation/delegation.rb:71:in `each'
/var/www/discourse/lib/stylesheet/importer.rb:70:in `block in <class:Importer>'
/var/www/discourse/lib/stylesheet/importer.rb:157:in `instance_eval'
/var/www/discourse/lib/stylesheet/importer.rb:157:in `imports'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/import_handler.rb:29:in `block in import_function'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/engine.rb:40:in `compile_data_context'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/sassc-1.11.4/lib/sassc/engine.rb:40:in `render'
/var/www/discourse/lib/stylesheet/compiler.rb:38:in `compile'
/var/www/discourse/lib/stylesheet/compiler.rb:20:in `compile_asset'
/var/www/discourse/lib/stylesheet/manager.rb:162:in `compile'
/var/www/discourse/lib/stylesheet/manager.rb:96:in `block (2 levels) in precompile_css'
/var/www/discourse/lib/stylesheet/manager.rb:89:in `each'
/var/www/discourse/lib/stylesheet/manager.rb:89:in `block in precompile_css'
/var/www/discourse/lib/stylesheet/manager.rb:88:in `each'
/var/www/discourse/lib/stylesheet/manager.rb:88:in `precompile_css'
/var/www/discourse/lib/tasks/assets.rake:60:in `block (2 levels) in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:268:in `block in each_connection'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:266:in `each'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:266:in `each_connection'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rails_multisite-2.0.6/lib/rails_multisite/connection_management.rb:71:in `each_connection'
/var/www/discourse/lib/tasks/assets.rake:53:in `block in <top (required)>'
/var/www/discourse/lib/tasks/assets.rake:235:in `block in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rake-12.3.2/exe/rake:27:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Tasks: TOP => assets:precompile:css

I am running a multi-site install.

I noted the following relevant commit:
https://github.com/discourse/discourse/commit/5e410dc5e0eff133d0b2e396d022fa1bf2ef85e4

And the following DB migrate:
https://github.com/discourse/discourse/blob/5e410dc5e0eff133d0b2e396d022fa1bf2ef85e4/db/migrate/20190314082018_add_search_priority_to_categories.rb

I’m unsure how to confirm the DB migrate has run successfully.

Thanks in advance for any help.

[Falco]

@DeanMarkTaylor

I am running a multi-site install.

Do you have code to run migrations on all sites in your app.yml ?

[DeanMarkTaylor (Dean Taylor)]

I’m pretty sure I do - as it’s worked since ~ AprilMay 2017.

The last time I had this kind of issue I worked around it, seen here:
https://meta.discourse.org/t/multisite-builds-are-currently-broken-under-new-native-theme-changes/60865

And then undid my change after Sam’s fix.

I’ll test these changes again.

[DeanMarkTaylor (Dean Taylor)]

Thanks @Falco for reminding be of the migrations the yml config - this pointed me in the right direction.

I can confirm that the following change “fixed” this issue for me:
https://meta.discourse.org/t/multisite-builds-are-currently-broken-under-new-native-theme-changes/60865/10?u=deanmarktaylor

However previously Sam made a change because of this happening twice:
https://meta.discourse.org/t/multisite-builds-are-currently-broken-under-new-native-theme-changes/60865/13?u=deanmarktaylor

This might need visiting again.

[Falco]

@sam changes where addressing the specific cases that you reported, but the way it’s setup it will always blow when a migrations adds a column.

@riking adresses that on the last post there:

https://meta.discourse.org/t/multisite-builds-are-currently-broken-under-new-native-theme-changes/60865/15?u=falco

Not sure if his suggestion is the more appropriate one, or if we should add another hook for a multisite template to use.

[pfaffman (Jay Pfaffman)]

Are you saying that your multisite instances get migrated a different way and that a multisite upgrade will fail right now?

[Falco]

Using rake db:migrate runs the migrations for the default site. For multisite you need to use rake multisite:migrate.

[pfaffman (Jay Pfaffman)]

Got it. But I thought that I always see a bootstrap do a multisite migrate, right?

[DeanMarkTaylor (Dean Taylor)]

My understanding is that this depends on your specific multisite configuration.

Certainly the “recommended” way back in 2017 was to include this:

after_bundle_exec:
    - exec: cd /var/www/discourse && sudo -E -u discourse bundle exec rake multisite:migrate

Which when I ran a rebuild it failed to build the multisite a bit earlier today.

Noting when the CSS is compiled early for theme updates etc before the DB migration…
… because of this it makes use of SQL queries that can’t complete.

Hence moving an execution of bundle exec rake db:migrate before bundle exec rake multisite:migrate in the before_bundle_exec section works around this.

[sam (Sam Saffron)]

Basically in a multisite you have to run bundle exec rake multisite:migrate prior to precompile, in this case you clearly did not.

The problem is that we were m missing some hooks here which make this a nightmare to fix cleanly.

I added some per:

https://github.com/discourse/discourse_docker/commit/5716d28f3c6d2a20a9fe2491fce6c131c9951f5c

I you use:

after_db_migrate:
    - exec: cd /var/www/discourse && sudo -E -u discourse bundle exec rake multisite:migrate

You should be all good.

[pfaffman (Jay Pfaffman)]

Well, now I’m really confused.

I don’t have that after_db_migrate stanza and have had this multisite instance running for well over a year. I thought that the web template was doing the multisite migrate and haven’t noticed any problems.

Should I add that after_db_migrate stanza?

[david (David Taylor)]

@pfaffman

I thought that the web template was doing the multisite migrate

No, there’s no reference to multisite:migrate in the whole discourse_docker repository.

I guess you must have bundle exec rake multisite:migrate somewhere in your config file? It’s probably run towards the end, which can potentially cause the issues described in this topic.

The new after_db_migrate hook Sam added means you can run multisite:migrate right next to the regular db:migrate, which is the best place for it.

I guess the best thing would be for us to add a “multisite” template to discourse_docker, which people can add to their templates (including on our own infrastructure). What do you think @sam?

[pfaffman (Jay Pfaffman)]

I swear that I see “multisite” in every rebuild (ooooh, perhaps that’s just a test for multisite). And I don’t see a reference to it in my yml file.

I’m very confused how either these databases have been getting migrated or how they are working if they haven’t. And how your multisite databases get migrated.

[david (David Taylor)]

Adding the multisite:migrate line is part of the howto for multisite setups

Multisite configuration with Docker

While multisite is supported in the Discourse application, this is an advanced sysadmin setup. If you don’t know what you’re doing, do not set up multisite. The Discourse team is unable to provide multisite configuration support. If you wish to host multiple domains on a singled Docker setup, you’ll need a multisite configuration. Here are the basic building blocks for one. Understand hooks Multisite is a fairly advanced topic. Before attempting a multisite build, spend some time…

@pfaffman

how your multisite databases get migrated

Using the multisite:migrate command as described in the howto

@pfaffman

I’m very confused how either these databases have been getting migrated

This is certainly surprising. I think you would be noticing issues if you hadn’t migrated for a whole year. If it was only a few weeks, then it might be possible to continue running on an old schema without too many errors. Have you modified one of the other templates to include multisite:migrate?

[pfaffman (Jay Pfaffman)]

I just looked again and don’t see anything like that in the yml. I’m sure I didn’t modify any templates. I’ll have a closer look in a few days and see what happens when I do a bootstrap.

[sam (Sam Saffron)]

@david

I guess the best thing would be for us to add a “multisite” template to discourse_docker, which people can add to their templates (including on our own infrastructure). What do you think @sam?

Maybe… if you feel strongly about it you can add a trivial web.multisite.yml so people can mix that in which is less confusing than typing the hook.

[riking (Kane York)]

The point of the linked post of mine above was that I was fairly sure that changing the default action – the content of what’s now hook: db_migrate – from db:migrate to multisite:migrate (i.e. a 1-line PR) was safe, but with a “I cannot verify this statement” disclaimer.

[supermathie (Michael Brown)]

@riking

from db:migrate to multisite:migrate

Is there EVER a case where we want to db:migrate but not multisite:migrate?

[pfaffman (Jay Pfaffman)]

EDIT: Well, I think that what happened was that my multi.yml got truncated somehow. And the one that I’d been using had been doing migrations as expected, but I’d forgotten, and when I grepped the file, I didn’t see the migration.

Thankfully the backup that I had contained the whole file. I modified it to use the new hook and all is well.