Cleanup compute host resource allocations in placement api left over from Nova bug LP 1756179

cleanup-compute-placement-allocations-lp1756179.sh

#!/bin/bash -ux

# Cleanup compute host resource allocations in placement api left over from Nova bug LP 1756179
# Background: It turns out that when you delete a service the associated resource provider (nova placement api) does not get deleted or updated.

mysql -s -N -h$host -u${user} -p$pw nova

# Get id of deleted compute host
select id from compute_nodes where host="__HOSTNAME_GOES_HERE__" and deleted_at!="NULL";

# First delete any pci_devices records since they have a foreign key constraint on the compute_nodes table
delete from pci_devices where compute_node_id="__ID__";

# Check they are gone
select * from pci_devices where compute_node_id="__ID__";

# Then delete the old compute node entry
delete from compute_nodes where id="__ID__" and deleted_at!="NULL";

# Check it has gone
select * from compute_nodes where id="__ID__";

# Get uuid and fqdn of new compute entry
select uuid, hypervisor_hostname from compute_nodes where host="__HOSTNAME_GOES_HERE__";

# Then update the resource provider entry for that compute host so that it has the uuid of the new compute host
# NOTE: this goes to the nova_api database NOT the nova database
mysql -h$host -u${user} -p$pw nova_api

update resource_providers set uuid="__UUID__" where name="__FQDN__";