nova-manage db archive_deleted_rows does not cleanup table instance_id_mappings

[Krzysztof Pawlowski]

Description
===========
After running nova-manage delete_archive_logs all tables are cleaned up except instance_id_mappings.

Steps to reproduce
==================
* create instance
* delete instance
* nova-manage delete_archive_logs

Expected result
===============
Clean up of instance_id_mappings table

Actual result
=============
+-----------------------------+--------+---------+------------+--------+
| Name | Engine | Version | Row_format | Rows |
+-----------------------------+--------+---------+------------+--------+
| instance_id_mappings | InnoDB | 10 | Compact | 288549 |
| shadow_instance_id_mappings | InnoDB | 10 | Compact | 0 |
+-----------------------------+--------+---------+------------+--------+

Environment
===========
openstack-nova-conductor-17.0.5-1.el7.noarch
openstack-nova-scheduler-17.0.5-1.el7.noarch
openstack-nova-api-17.0.5-1.el7.noarch
openstack-nova-placement-api-17.0.5-1.el7.noarch
openstack-nova-novncproxy-17.0.5-1.el7.noarch
openstack-nova-console-17.0.5-1.el7.noarch
openstack-nova-common-17.0.5-1.el7.noarch

[Matt Riedemann (mriedem)]

What is "delete_archive_logs"? Do you mean "archive_deleted_rows"?

[Matt Riedemann (mriedem)]

Are you using the ec2api for this? As far as I know, instance_id_mappings is only used for ec2 instances.

[Matt Riedemann (mriedem)]

I believe the issue is that when the instances table record is deleted (instances.deleted != 0), we don't also delete the instance_id_mappings table record so the archive command never moves those records to shadow tables because they aren't actually marked as deleted != 0. This is a latent bug as far as I know.

[Matt Riedemann (mriedem)]

I guess you don't have to be using the ec2api, the DB API instance_create() method always creates an instance_id_mappings record but instance_destroy() does not remove it.

[Matt Riedemann (mriedem)]

I've attached a patch for the problem. I'm having some problems uploading changes to gerrit at the moment.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/591558

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/591558
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=8a6b57cf741e17d3de54ed8c85d9fb69c43e7432
Submitter: Zuul
Branch: master

commit 8a6b57cf741e17d3de54ed8c85d9fb69c43e7432
Author: Matt Riedemann <email address hidden>
Date: Tue Aug 14 11:05:06 2018 +0800

    Delete instance_id_mappings record in instance_destroy

    The instance_create DB API creates an instance_id_mappings record
    but instance_destroy was not cleaning it up when the instance is
    deleted; this adds that delete code. Otherwise those records
    never get moved to shadow tables so you can't archive and purge them.

    Change-Id: Idfe52d3c2f987b9aac551f013a0990423d87fad3
    Closes-Bug: #1786298

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/rocky
Review: https://review.openstack.org/604373

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/604373
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=6186996c8093e723bdaa0e06664143685050312d
Submitter: Zuul
Branch: stable/rocky

commit 6186996c8093e723bdaa0e06664143685050312d
Author: Matt Riedemann <email address hidden>
Date: Tue Aug 14 11:05:06 2018 +0800

    Delete instance_id_mappings record in instance_destroy

    The instance_create DB API creates an instance_id_mappings record
    but instance_destroy was not cleaning it up when the instance is
    deleted; this adds that delete code. Otherwise those records
    never get moved to shadow tables so you can't archive and purge them.

    Change-Id: Idfe52d3c2f987b9aac551f013a0990423d87fad3
    Closes-Bug: #1786298
    (cherry picked from commit 8a6b57cf741e17d3de54ed8c85d9fb69c43e7432)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 18.0.2 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 19.0.0.0rc1 release candidate.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/queens
Review: https://review.openstack.org/650309

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/650309
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=1b6f48354b7eacecd7a146fc05a12537b4f22e18
Submitter: Zuul
Branch: stable/queens

commit 1b6f48354b7eacecd7a146fc05a12537b4f22e18
Author: Matt Riedemann <email address hidden>
Date: Tue Aug 14 11:05:06 2018 +0800

    Delete instance_id_mappings record in instance_destroy

    The instance_create DB API creates an instance_id_mappings record
    but instance_destroy was not cleaning it up when the instance is
    deleted; this adds that delete code. Otherwise those records
    never get moved to shadow tables so you can't archive and purge them.

    Change-Id: Idfe52d3c2f987b9aac551f013a0990423d87fad3
    Closes-Bug: #1786298
    (cherry picked from commit 8a6b57cf741e17d3de54ed8c85d9fb69c43e7432)
    (cherry picked from commit 6186996c8093e723bdaa0e06664143685050312d)

[Mohammed Naser (mnaser)]

FYI, if you haven't purged your archived records, you can get away with this:

DELETE FROM instance_id_mappings WHERE uuid IN (SELECT uuid FROM shadow_instances);

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 17.0.11 release.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/pike
Review: https://review.opendev.org/684658

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/684658
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=7aeec0e501816b8b74e5422d81d9628c2e71af16
Submitter: Zuul
Branch: stable/pike

commit 7aeec0e501816b8b74e5422d81d9628c2e71af16
Author: Matt Riedemann <email address hidden>
Date: Tue Aug 14 11:05:06 2018 +0800

    Delete instance_id_mappings record in instance_destroy

    The instance_create DB API creates an instance_id_mappings record
    but instance_destroy was not cleaning it up when the instance is
    deleted; this adds that delete code. Otherwise those records
    never get moved to shadow tables so you can't archive and purge them.

    Depends-On: https://review.opendev.org/684769

    Change-Id: Idfe52d3c2f987b9aac551f013a0990423d87fad3
    Closes-Bug: #1786298
    (cherry picked from commit 8a6b57cf741e17d3de54ed8c85d9fb69c43e7432)
    (cherry picked from commit 6186996c8093e723bdaa0e06664143685050312d)
    (cherry picked from commit 1b6f48354b7eacecd7a146fc05a12537b4f22e18)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova pike-eol release.