Foreman web nor Foreman-rake is unable to see duplicate host

Problem:

We encounter this error whenever we move some of the hosts to a hostgroup - “Name has already been taken”

I don’t see any duplicates for the host/s even when I switch to “Any Organization”.

I tried checking the database to see if there is a duplicate entry for some of the problematic host.
Querying the “hosts” table in the database I found 2 IDs pointing to the same host (all the problematic hosts has this behavior)

 id  |     name      | hostgroup_id |         created_at         |         updated_at         |     last_report
-----+---------------+--------------+----------------------------+----------------------------+---------------------
 301 | aikengall1    |              | 2021-02-08 00:06:58.701721 | 2021-02-08 00:07:02.755265 |
 319 | aikengall1    |              | 2021-02-08 00:36:14.74654  | 2021-02-10 00:45:47.595442 | 2021-02-10 00:43:06
(2 rows)

I was hoping to find the host via the foreman-rake console so I could delete it but it does not seem to see the resource.

$> foreman-rake console
Loading production environment (Rails 6.0.3.1)
irb(main):001:0> Host.find_by_id(301)
=> nil

It would seem like the only way to fix this is by deleting the duplicate host from the database. I’m tempted to just delete the host as was done in this old post Removing Duplicated Host but would like to know
is there a proper/clean way of removing these duplicate hosts from the DB?

Thanks in advance for the help.

Expected outcome:
The duplicate ID, related facts, reports, etc. are removed and Host can be added to hostgroup.

Foreman and Proxy versions:
Katello 3.17
Foreman 2.2.1
Foreman-Proxy 2.2.1

Foreman and Proxy plugin versions:

Distribution and version:

Other relevant data:

Is it possible there is a hanging discovered host with the same hostname/MAC?

Hi lzap, Sorry for the delayed reply.

I dont know about hanging discovered hosts as we are simply installing the puppet agent to existing servers and configuring them to report to our puppetmaster server.

I took 10 sample nodes and checked for duplicate mac addresses in the “nics” table and did not find any.

I tried doing the queries below to get the nics related to their hostnames.

Here’s a query that came up for a host with 2 NICs
foreman=# SELECT hosts.id, hosts.name, nics.host_id, nics.mac, nics.ip FROM hosts INNER JOIN nics ON hosts.id = nics.host_id WHERE hosts.name = '[redacted1]';

 id  |      name       | host_id |        mac        |      ip
-----+-----------------+---------+-------------------+--------------
 132 |     [redacted1] |     132 | 00:01:05:32:bf:b0 | 192.168.1.15
 132 |     [redacted1] |     132 | 00:01:05:32:bf:b1 | 192.168.1.14
  78 |     [redacted1] |      78 | 00:01:05:32:bf:b0 | 192.168.1.15
  78 |     [redacted1] |      78 | 00:01:05:32:bf:b1 | 192.168.1.14

Here’s a query for a host with a single nic

foreman=# SELECT hosts.id, hosts.name, nics.host_id, nics.mac, nics.ip FROM hosts INNER JOIN nics ON hosts.id = nics.host_id WHERE hosts.name = '[redacted2]';

 id  |     name      | host_id |        mac        |      ip
-----+---------------+---------+-------------------+---------------
 134 |    [redacted2] |     134 | 00:01:05:53:2d:e0 | 10.56.132.192
  92 |    [redacted2] |      92 | 00:01:05:53:2d:e0 | 10.56.132.192

It seems like the one that’s constantly reporting is the node with the most recent ID as shown below.

 id  |      name      | hostgroup_id |         created_at         |         updated_at         |     last_report
-----+----------------+--------------+----------------------------+----------------------------+---------------------
 132 | [redacted1]    |              | 2021-02-02 23:34:50.771017 | 2021-02-19 04:23:34.784675 | 2021-02-19 04:22:31
  78 | [redacted1]    |         9318 | 2021-02-02 23:05:04.414603 | 2021-02-02 23:09:09.732829 |
(2 rows)


 id  |     name      | hostgroup_id |         created_at         |         updated_at         |     last_report
-----+---------------+--------------+----------------------------+----------------------------+---------------------
 134 | [redacted2]   |              | 2021-02-02 23:35:17.56774  | 2021-02-19 04:21:36.869682 | 2021-02-19 04:19:25
  92 | [redacted2]   |              | 2021-02-02 23:05:33.261629 | 2021-02-02 23:05:41.107796 |

Which is why I’m thinking of just deleting the older ID and all the associated facts, etc.

Any input would be appreciated.

Yes, most of our code does not use SQL transactions and occasionally you can run into similar problems.

Hello I have same problem. 1/3 of our hosts have duplicate. All hosts have only one nic. How to solve it?
foreman 3.0