Hello, Sorry I could not work on this for a while. To fix this issue I just added one request to my previous message. I will write down my entire procedure: use nova; update instance_id_mappings set deleted = id where uuid in (select uuid from instances where deleted != 0); exit nova-manage db archive_deleted_rows --all-cells --until-complete delete from nova.instance_id_mappings where uuid not in (select uuid from nova.instances); delete from nova_api.consumers where nova_api.consumers.uuid not in (select nova_api.instance_mappings.instance_uuid from nova_api.instance_mappings); Thus new request: delete from placement.consumers where placement.consumers.uuid not in (select nova_api.instance_mappings.instance_uuid from nova_api.instance_mappings); I already executed the db migrate script so I have to clear placement tables. If you still have negative values I think there are many cases. I faced these: - All instances in a deleted project are still present in placement/nova_api consumers. I removed them from nova_api.instance_mappings before nova-manage db archive_deleted_rows - Last one is weird: A very old shelved instance which appeared after running placement-manage db online_data_migrations Best regards, Romain On Wed, 2020-11-04 at 09:08 -0800, melanie witt wrote:
On 11/4/20 08:54, Seth Tunstall wrote:
Hello,
In case it helps anyone else searching for this in future: Melanie's suggestion to clean out the orphaned consumers worked perfectly in my situation.
The last two I had were apparently left over from the original build of this environment. I brute-force cleaned them out of the DB manually:
DELETE FROM nova_cell0.block_device_mapping WHERE nova_cell0.block_device_mapping.instance_uuid IN (SELECT uuid FROM nova_api.consumers WHERE nova_api.consumers.uuid NOT IN (SELECT nova_api.allocations.consumer_id FROM nova_api.allocations));
<snip>
Caveat: I am not intimately familiar with how the ORM handles these DB tables, I may have done something stupid here.
Hm, sorry, this isn't what I was suggesting you do ... I was making a guess that you might have instances with 'deleted' != 0 in your nova_cell0 database and that if so, they needed to be archived using 'nova-manage db archive_deleted_rows' and then that might take care of removing their corresponding nova_api.instance_mappings which would make the manual cleanup find more rows (the rows that were being complained about).
What you did is "OK" (not harmful) if the nova_cell0.instances records associated with those records were 'deleted' column != 0. But there's likely more cruft rows left behind that will never be removed. nova-manage db archive_deleted_rows should be used whenever possible because it knows how to remove all the things.
I tried to run:
nova-manage db archive_deleted_rows --verbose --until-complete -- all-cells
but nova-db-manage complained that it didn't recognise --no-cells
This is with the train code? --all-cells was added in train [1]. If you are running with code prior to train, you have to pass a nova config file to the nova-manage command that has its [api_database]connection set to the nova_api database connection url and the [database]connection set to the nova_cell0 database. Example:
nova-manage --config-file <nova.conf pointing at nova_cell0> db archive_deleted_rows ...
Cheers, -melanie
[1] https://docs.openstack.org/nova/train/cli/nova-manage.html#nova-database