Foreman 3.0 database migration on upgrade fails with undefined method

Problem:
I am trying to upgrade from 2.4 to 2.5 or 3.0.
If I go to either version I get the following when running foreman-rake db:migrate

foreman-rake db:migrate --trace
** Invoke db:migrate (first_time)
** Invoke db:load_config (first_time)
** Invoke environment (first_time)
** Execute environment
API controllers newer than Apipie cache! Run apipie:cache rake task to regenerate cache.
At least one field decryption failed, check ENCRYPTION_KEY
** Execute db:load_config
** Invoke plugin:refresh_migrations (first_time)
** Invoke environment
** Execute plugin:refresh_migrations
** Execute db:migrate
== 20210502113529 DropViewBookmarksPermission: migrating ======================
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

undefined method `locked?' for nil:NilClass
/usr/share/foreman/app/models/filter.rb:236:in `role_not_locked'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:428:in `block in make_lambda'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:200:in `block (2 levels) in halting'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:605:in `block (2 levels) in default_terminator'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:604:in `catch'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:604:in `block in default_terminator'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:201:in `block in halting'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `block in invoke_before'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `invoke_before'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:134:in `run_callbacks'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:825:in `_run_destroy_callbacks'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/callbacks.rb:309:in `destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `block in destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:375:in `block in with_transaction_returning_status'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:278:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:366:in `with_transaction_returning_status'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `destroy_all'
/usr/share/foreman/db/migrate/20210502113529_drop_view_bookmarks_permission.rb:5:in `up'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:831:in `exec_migration'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:812:in `block (2 levels) in migrate'
/usr/lib/ruby/2.5.0/benchmark.rb:293:in `measure'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:811:in `block in migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:471:in `with_connection'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:810:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1002:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1310:in `block in execute_migration_in_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1361:in `block in ddl_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `block in transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:280:in `block in within_new_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:278:in `within_new_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1361:in `ddl_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1309:in `execute_migration_in_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1281:in `block in migrate_without_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1280:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1280:in `migrate_without_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1229:in `block in migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1229:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1061:in `up'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1036:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:86:in `block (3 levels) in <top (required)>'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:84:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:84:in `block (2 levels) in <top (required)>'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `block in execute'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `execute'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:219:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:199:in `invoke_with_call_chain'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:188:in `invoke'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:160:in `invoke_task'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `block (2 levels) in top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `block in top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:125:in `run_with_threads'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:110:in `top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:83:in `block in run'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:186:in `standard_exception_handling'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:80:in `run'
/usr/bin/rake:27:in `<main>'

Caused by:
NoMethodError: undefined method `locked?' for nil:NilClass
/usr/share/foreman/app/models/filter.rb:236:in `role_not_locked'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:428:in `block in make_lambda'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:200:in `block (2 levels) in halting'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:605:in `block (2 levels) in default_terminator'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:604:in `catch'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:604:in `block in default_terminator'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:201:in `block in halting'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `block in invoke_before'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:513:in `invoke_before'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:134:in `run_callbacks'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:825:in `_run_destroy_callbacks'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/callbacks.rb:309:in `destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `block in destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:375:in `block in with_transaction_returning_status'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:278:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:366:in `with_transaction_returning_status'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `destroy'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `destroy_all'
/usr/share/foreman/db/migrate/20210502113529_drop_view_bookmarks_permission.rb:5:in `up'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:831:in `exec_migration'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:812:in `block (2 levels) in migrate'
/usr/lib/ruby/2.5.0/benchmark.rb:293:in `measure'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:811:in `block in migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:471:in `with_connection'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:810:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1002:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1310:in `block in execute_migration_in_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1361:in `block in ddl_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `block in transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:280:in `block in within_new_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:278:in `within_new_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1361:in `ddl_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1309:in `execute_migration_in_transaction'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1281:in `block in migrate_without_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1280:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1280:in `migrate_without_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1229:in `block in migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1229:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1061:in `up'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/migration.rb:1036:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:86:in `block (3 levels) in <top (required)>'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:84:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/activerecord-6.0.3.7/lib/active_record/railties/databases.rake:84:in `block (2 levels) in <top (required)>'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `block in execute'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:281:in `execute'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:219:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:199:in `invoke_with_call_chain'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/task.rb:188:in `invoke'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:160:in `invoke_task'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `block (2 levels) in top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `each'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:116:in `block in top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:125:in `run_with_threads'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:110:in `top_level'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:83:in `block in run'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:186:in `standard_exception_handling'
/usr/share/foreman/vendor/ruby/2.5.0/gems/rake-13.0.6/lib/rake/application.rb:80:in `run'
/usr/bin/rake:27:in `<main>'
Tasks: TOP => db:migrate

Expected outcome:
Database seed works fine

Foreman and Proxy versions:
Foreman 2.4 upgrading to 2.5 or 3.0

Foreman and Proxy plugin versions:
Foreman 2.4 upgrading to 2.5 or 3.0

Distribution and version:
Debian buster 10

Other relevant data:

f I comment out the code in the up function in /usr/share/foreman/db/migrate/20210502113529_drop_view_bookmarks_permission.rb
it then fails with the same issue on 20210901081438_drop_view_tasks_permission

Any ideas on what I need to do to migrate the DB cleanly?
The two migration scripts:

class DropViewBookmarksPermission < ActiveRecord::Migration[6.0]
  def up
    Permission.where(name: 'view_bookmarks').destroy_all
    # clean up any empty filters left behind
    Filter.where.not(id: Filtering.distinct.select(:filter_id)).destroy_all
  end

  def down
    # Will be recreated automatically by seeds
  end
end

class DropViewTasksPermission < ActiveRecord::Migration[6.0]
  def up
    Permission.where(name: 'view_tasks').destroy_all
    # clean up any empty filters left behind
    Filter.where.not(id: Filtering.distinct.select(:filter_id)).destroy_all
  end

  def down
    # The permission will get recreated by seeds
  end
end

Reading the code it’s this line:

That suggests a filter exists without a role attached. You can find that with a DB query:

SELECT * FROM filters WHERE role_id IS NULL;

I don’t have any of those in my DB and the code suggests it’s invalid. You should be able to delete them too:

DELETE FROM filters WHERE role_id IS NULL;

Then you can retry the migration.

Hi,

I have just tried that DELETE, however it looks like there’s some constraints on that table.

foreman=# DELETE FROM filters WHERE role_id IS NULL;
ERROR: update or delete on table “filters” violates foreign key constraint “filterings_filters_id_fk” on table “filterings”
DETAIL: Key (id)=(159) is still referenced from table “filterings”.

Would appreciate any advice on how to continue

Can you share the table definition. You can open the DB console using sudo -u foreman psql foreman and then use \d filters.

For my Foreman 3.5 instance that’s:

foreman=> \d filters
                                           Table "public.filters"
     Column      |            Type             | Collation | Nullable |               Default               
-----------------+-----------------------------+-----------+----------+-------------------------------------
 id              | integer                     |           | not null | nextval('filters_id_seq'::regclass)
 search          | text                        |           |          | 
 role_id         | integer                     |           |          | 
 created_at      | timestamp without time zone |           |          | 
 updated_at      | timestamp without time zone |           |          | 
 taxonomy_search | text                        |           |          | 
 override        | boolean                     |           | not null | false
Indexes:
    "filters_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "filters_roles_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)
Referenced by:
    TABLE "filterings" CONSTRAINT "filterings_filters_id_fk" FOREIGN KEY (filter_id) REFERENCES filters(id)

NOte how it has a foreign key constraint on roles, so it shouldn’t be possible to have the data you have.

Can you also share which entries it found?

Looks like we have the same table definition:

foreman=# \d filters
    Column      |            Type             | Collation | Nullable |               Default
-----------------+-----------------------------+-----------+----------+-------------------------------------
 id              | integer                     |           | not null | nextval('filters_id_seq'::regclass)
 search          | text                        |           |          |
 role_id         | integer                     |           |          |
 created_at      | timestamp without time zone |           | not null |
 updated_at      | timestamp without time zone |           | not null |
 taxonomy_search | text                        |           |          |
 override        | boolean                     |           | not null | false

Indexes:
    "filters_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "filters_roles_id_fk" FOREIGN KEY (role_id) REFERENCES roles(id)
Referenced by:
    TABLE "filterings" CONSTRAINT "filterings_filters_id_fk" FOREIGN KEY (filter_id) REFERENCES filters(id)

This is what the table looks like:

 id  | search | role_id |         created_at         |         updated_at         | taxonomy_search | override
-----+--------+---------+----------------------------+----------------------------+-----------------+----------
 159 |        |         | 2018-10-02 16:52:30.345315 | 2018-10-02 16:52:30.345315 |                 | f
 160 |        |         | 2018-10-02 16:52:30.574525 | 2018-10-02 16:52:30.574525 |                 | f
 161 |        |         | 2018-10-02 16:52:30.660855 | 2018-10-02 16:52:30.660855 |                 | f
 162 |        |         | 2018-10-02 16:52:30.700956 | 2018-10-02 16:52:30.700956 |                 | f
...
 170 |        |         | 2018-10-02 16:52:31.189818 | 2018-10-02 16:52:31.189818 |                 | f
 207 |        |         | 2018-10-02 16:52:33.009782 | 2018-10-02 16:52:33.009782 |                 | f
 242 |        |         | 2018-10-02 16:52:34.276466 | 2018-10-02 16:52:34.276466 |                 | f
(133 rows)

Ok, so the column is nullable but the code doesn’t agree with that. If I interpret this correctly then it should be present:

And that line dates back to the very first addition of that file in 2014.

I think it should be safe to remove the filters. Of course the usual notes like keeping a backup apply.

DELETE FROM filterings WHERE filter_id IN (SELECT id FROM filters WHERE role_id IS NULL);
DELETE FROM filters WHERE role_id IS NULL;

And to add a constraint I’ve opened Bug #36607: Database doesn't enforce filters.role_id is NOT NULL - Foreman

1 Like

Thanks @ekohl ! That has done the trick :slight_smile: we are now smooth smailing on our way upwards to 3.7
Our Foreman database is pretty old :sweat: we have been using your platform for around 8-9 years now … so not surprised we got some artefacts

1 Like