Foreman 3.0 database migration on upgrade fails with undefined method

JakeGroves · July 19, 2023, 5:47pm

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

ekohl · July 19, 2023, 5:54pm

Reading the code it’s this line:

github.com

theforeman/foreman/blob/1af33cf60548a83e44f263045e776df5c843202f/app/models/filter.rb#L236


      
                errors.add(:location_ids, _('You can\'t assign locations to this resource'))
              end
            end
          
          
  def enforce_override_flag
              self.override = false unless resource_taxable?
              true
            end
          
          
  def role_not_locked
              errors.add(:role_id, _('is locked for user modifications.')) if role.locked? && !role.modify_locked
              errors.empty?
            end
          end

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.

Ed_Murphy · July 19, 2023, 7:23pm

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

ekohl · July 19, 2023, 7:37pm

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?

Ed_Murphy · July 20, 2023, 10:50am

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)

ekohl · July 20, 2023, 12:27pm

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

github.com

theforeman/foreman/blob/b657d269a726e3bd3f7ebc8e67c7fc1d894d9176/app/models/filter.rb#L58


      
          scoped_search :relation => :role, :on => :id, :rename => :role_id, :complete_enabled => false, :only_explicit => true, :validator => ScopedSearch::Validators::INTEGER
          scoped_search :relation => :role, :on => :name, :rename => :role
          scoped_search :relation => :permissions, :on => :resource_type, :rename => :resource
          scoped_search :relation => :permissions, :on => :name,          :rename => :permission
          
          
before_validation :build_taxonomy_search, :nilify_empty_searches, :enforce_override_flag
          before_save :enforce_inherited_taxonomies, :nilify_empty_searches
          
          
validates :search, :presence => true, :unless => proc { |o| o.search.nil? }
          validates_with ScopedSearchValidator
          validates :role, :presence => true
          
          
validate :role_not_locked
          before_destroy :role_not_locked
          
          
validate :same_resource_type_permissions, :not_empty_permissions, :allowed_taxonomies
          
          
def self.allows_taxonomy_filtering?(_taxonomy)
            false
          end

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

JakeGroves · July 20, 2023, 2:49pm

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