purge_deleted gives SQL integrety error

[Sam Morrison]

heat-manage purge_deleted -g days 2

2015-04-02 17:09:16.867 1733 ERROR oslo.db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`heat`.`stack`, CONSTRAINT `stack_ibfk_2` FOREIGN KEY (`user_creds_id`) REFERENCES `user_creds` (`id`))') 'DELETE FROM user_creds WHERE user_creds.id = %s' (8029L,)
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters Traceback (most recent call last):
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters context)
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters self.errorhandler(self, exc, value)
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters raise errorclass, errorvalue
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`heat`.`stack`, CONSTRAINT `stack_ibfk_2` FOREIGN KEY (`user_creds_id`) REFERENCES `user_creds` (`id`))')
2015-04-02 17:09:16.867 1733 TRACE oslo.db.sqlalchemy.exc_filters
ERROR: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`heat`.`stack`, CONSTRAINT `stack_ibfk_2` FOREIGN KEY (`user_creds_id`) REFERENCES `user_creds` (`id`))') 'DELETE FROM user_creds WHERE user_creds.id = %s' (8029L,)

[Angus Salkeld (asalkeld)]

I haven't been able to reproduce this on the latest devstack.
Can you provide some steps to reproduce?

[Sam Morrison (sorrison)]

This is using stable/juno. Looks like it's trying to delete a row in the user_creds table when there is a row in the stacks table that references it.

Sorry this is in a production install, the heat DB has lots of sensitive info so I don't think I can simply give you a dump.

[Sam Morrison (sorrison)]

By running this multiple times it ends up working. This happens when 2 stacks have the same raw_template_id but are deleted on different days.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/201384
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=d3def2acee7e836263319bd64471816cf1de2540
Submitter: Jenkins
Branch: master

commit d3def2acee7e836263319bd64471816cf1de2540
Author: Randall Burt <email address hidden>
Date: Mon Jul 13 18:35:09 2015 -0500

    Refactor and correct purge_deleted

    This method had not been updated in a while
    to account for changes in the db schema. Also,
    it was refactored to use bulk delete statements
    rather than iteration as the iteration approach
    ran quite a long time and consumed quite a bit
    of resources.

    Change-Id: Ia49e801d3c244a1fabb8441e7eaa22b88e5fcb56
    Closes-Bug: #1439518

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/kilo
Review: https://review.openstack.org/224960

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/224960
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=d76094c9d663fff479e7d959920420fb23cfd0d2
Submitter: Jenkins
Branch: stable/kilo

commit d76094c9d663fff479e7d959920420fb23cfd0d2
Author: Randall Burt <email address hidden>
Date: Mon Jul 13 18:35:09 2015 -0500

    Refactor and correct purge_deleted

    This method had not been updated in a while
    to account for changes in the db schema. Also,
    it was refactored to use bulk delete statements
    rather than iteration as the iteration approach
    ran quite a long time and consumed quite a bit
    of resources.

    Change-Id: Ia49e801d3c244a1fabb8441e7eaa22b88e5fcb56
    Closes-Bug: #1439518
    (cherry picked from commit d3def2acee7e836263319bd64471816cf1de2540)