Foreman-rake db:migrate PG::DuplicateTable: ERROR: relation "discovery_rules" already exists

foreman-rake db:migrate fails.
I am not sure when I started to see this. It could have started with 1.19 or 1.20.0. I was running 1.20.1 and am trying to get to 1.21.2 but I need to get to 1.20.2 first.

I was also playing with /etc/foreman-installer/custom-hiera.yaml to try and make security happy with turning off TLSv1 and TLSv1.1

# Apache
apache::mod::ssl::ssl_protocol: [ 'ALL' ,  '-SSLv2' , '-SSLv3' ,  '-TLSv1' ,  '-TLSv1.1' , '+TLSv1.2' ]

I would like foreman-rake db:migrate to run and return 0

# foreman-rake db:migrate  --trace
** Invoke db:migrate (first_time)
** Invoke db:load_config (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:load_config
** Invoke plugin:refresh_migrations (first_time)
** Invoke environment 
** Execute plugin:refresh_migrations
** Execute db:migrate
== 20141107091416 CreateDiscoveryRules: migrating =============================
-- adapter_name()
   -> 0.0000s
-- adapter_name()
   -> 0.0000s
-- adapter_name()
   -> 0.0000s
-- create_table(:discovery_rules, {:id=>:integer})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateTable: ERROR:  relation "discovery_rules" already exists
: CREATE TABLE "discovery_rules" ("id" serial NOT NULL PRIMARY KEY, "name" character varying, "search" character varying, "hostgroup_id" integer NOT NULL, "hostname" character varying DEFAULT '', "max_count" integer DEFAULT 0, "priority" integer DEFAULT 0, "enabled" boolean DEFAULT TRUE NOT NULL, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
/opt/theforeman/tfm-ror52/root/usr/share/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:75:in `async_exec'
/opt/theforeman/tfm-ror52/root/usr/share/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:75:in `block (2 levels) in execute'

The installer seems to run okay and it seems to be working okay.

# foreman-installer --foreman-configure-epel-repo false
Installing             Done                                               [100%] [........................................]

# rpm -qa|grep foreman

I tried to run foreman-rake db:migrate and it still doesn’t like the discovery_rules table.

I decided to try and look at discovery_rules and it looks like it is empty.

foreman=# select * from discovery_rules;
 id | name | search | hostgroup_id | hostname | max_count | priority | enabled | created_at | updated_at 
(0 rows)

Maybe I could just remove it?

I dumped the data base and removed the table and it just gives me another error.

PG::DuplicateColumn: ERROR:  column "discovery_rule_id" of relation "hosts" already exists

I tried to remove that as well, since i have a backup.

foreman=# ALTER TABLE hosts
foreman-# DROP COLUMN discovery_rule_id;
foreman=# \q

Now it complains:

PG::DuplicateTable: ERROR:  relation "discovery_attribute_sets" already exists

I ended up restoring the database because it was like I picked up some changes and I wasn’t sure how to get those back.

Once I restored the database I removed the column and the 2 tables and it ran and returned 0.

foreman=# alter table hosts
foreman-# drop column discovery_rule_id;
foreman=# drop table discovery_rules, discovery_attribute_sets;
foreman=# \q

Does this seem okay? Is there a better way to deal with this?

I am not a dba.

Thanks for any help!

Not sure why, but for some reason the discovery plugin’s migration tried to run twice. In any case if the migration ran properly after dropping the tables and column it’s fine. You can verify by checking that they were created again after you ran the migration.

This server has been update many times so it is really hard to say when this problem happened.
I did just check and the tables are there. I think I am in good shape now.
Thank you for your help!

public | discovery_attribute_sets | table | foreman
public | discovery_rules | table | foreman