heat-manage purge_deleted broken

[Steven Hardy]

I'm seeing this, reverting the fix for bug #1506079 appears to fix it - I'm seeing this on CentOS7 and Fedora 21 mariadb.

2015-12-09 15:19:36.682 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.InternalError) (1093, u"Table 'raw_template' is specified twice, both as a target for 'DELETE' and as a separate source for data") [SQL: u'DELETE FROM raw_template WHERE raw_template.id IN (SELECT raw_template.id \nFROM raw_template LEFT OUTER JOIN stack ON stack.prev_raw_template_id = raw_template.id OR stack.raw_template_id = raw_template.id \nWHERE stack.id IS NULL)']
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters context)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 146, in execute
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters result = self._query(query)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 296, in _query
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters conn.query(q)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 781, in query
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 942, in _read_query_result
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters result.read()
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1138, in read
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 906, in _read_packet
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters packet.check_error()
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 367, in check_error
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters _check_mysql_exception(errinfo)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 115, in _check_mysql_exception
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters raise InternalError(errno, errorvalue)
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters InternalError: (1093, u"Table 'raw_template' is specified twice, both as a target for 'DELETE' and as a separate source for data")
2015-12-09 15:19:36.682 TRACE oslo_db.sqlalchemy.exc_filters
ERROR: (pymysql.err.InternalError) (1093, u"Table 'raw_template' is specified twice, both as a target for 'DELETE' and as a separate source for data") [SQL: u'DELETE FROM raw_template WHERE raw_template.id IN (SELECT raw_template.id \nFROM raw_template LEFT OUTER JOIN stack ON stack.prev_raw_template_id = raw_template.id OR stack.raw_template_id = raw_template.id \nWHERE stack.id IS NULL)']

[Steven Hardy (shardy)]

It doesn't look like we've got an integration test for this, I guess adding one would be a good idea, as this has ended up broken a few times now.

[OpenStack Infra (hudson-openstack)]

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

[Steve Baker (steve-stevebaker)]

since bug 1506079 was backported to liberty I assume this needs to be too.

[OpenStack Infra (hudson-openstack)]

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

commit 6cece5bb7e9e10dfb33596c32319e7ad9b81fbff
Author: Thomas Herve <email address hidden>
Date: Wed Dec 9 16:45:30 2015 +0100

    Fix database purge query

    Fix raw_template purge query on MySQL, and handle stack tags before
    removing stacks. This also removes a bunch of race conditions where we
    deleted incorrect data.

    Change-Id: I7b7a1d94acefbaeeed86f1833c979819361c8988
    Closes-Bug: #1524387

[Thierry Carrez (ttx)]

This issue was fixed in the openstack/heat 6.0.0.0b2 development milestone.

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/heat 6.0.0.0b2 development milestone.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/liberty
Review: https://review.openstack.org/278463

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/278463
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=77a36ec519e2eff9814ed0fb01050b582b9fd021
Submitter: Jenkins
Branch: stable/liberty

commit 77a36ec519e2eff9814ed0fb01050b582b9fd021
Author: Thomas Herve <email address hidden>
Date: Wed Dec 9 16:45:30 2015 +0100

    Fix database purge query

    Fix raw_template purge query on MySQL, and handle stack tags before
    removing stacks. This also removes a bunch of race conditions where we
    deleted incorrect data.

    Change-Id: I7b7a1d94acefbaeeed86f1833c979819361c8988
    Closes-Bug: #1524387
    (cherry picked from commit 6cece5bb7e9e10dfb33596c32319e7ad9b81fbff)

[OpenStack Infra (hudson-openstack)]

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

commit cb98419d6218f4ce80bfef47f4abe4baf49b2303
Author: Thomas Herve <email address hidden>
Date: Wed Dec 9 16:45:30 2015 +0100

    Fix database purge query

    Fix raw_template purge query on MySQL, and handle stack tags before
    removing stacks. This also removes a bunch of race conditions where we
    deleted incorrect data.

    Change-Id: I7b7a1d94acefbaeeed86f1833c979819361c8988
    Closes-Bug: #1524387
    (cherry picked from commit 6cece5bb7e9e10dfb33596c32319e7ad9b81fbff)

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/heat 2015.1.4 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/heat 5.0.2 release.

[James Page (james-page)]

Hello Steven, or anyone else affected,

Accepted heat into liberty-proposed. The package will build now and be available in the Ubuntu Cloud Archive in a few hours, and then in the -proposed repository.

Please help us by testing this new package. To enable the -proposed repository:

  sudo add-apt-repository cloud-archive:liberty-proposed
  sudo apt-get update

Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-liberty-needed to verification-liberty-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-liberty-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[James Page (james-page)]

The verification of the Stable Release Update for heat has completed successfully and the package has now been released to -updates. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.

[James Page (james-page)]

This bug was fixed in the package heat - 1:5.0.1-0ubuntu3.1~cloud1
---------------

 heat (1:5.0.1-0ubuntu3.1~cloud1) trusty-liberty; urgency=medium
 .
   * d/p/fix-database-purge-query.patch: Cherry picked from upstream
     stable/liberty branch to fix database purge query. (LP: #1524387)
   * d/p/fix-db-unit-tests-in-the-western-hemisphere.patch: Cherry picked
     from upstream stable/liberty branch to fix db unit tests in the
     western hemisphere.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/heat 2015.1.4 release.