Delete Consistency Group Fails with DB Error

[Chuck Fouts]

Internal Tempest tests are failing when deleting a consistency group. The error from the log file is:

2016-06-02 17:31:49.211 INFO cinder.api.contrib.consistencygroups [req-815de577-e1d6-4418-a56e-8e8346257709 tempest-ConsistencyGroupsV2Test-1276397807] Delete consistency group with id: a8cc0153-1a16-4e2c-85ac-ada73a75bcd8
2016-06-02 17:31:49.216 INFO cinder.api.openstack.wsgi [req-7d35bc72-0440-4258-bfc8-8374b4b9b983 tempest-NetAppCDOTQOSTest-1518787507] GET http://127.0.0.1:8776/v2/23bf4a3d35b6411ab80730939884125d/qos-specs/ec5f1f08-31a7-4115-b303-d9ad6dc65776/associate?vol_type_id=083d9ae8-592f-4b98-bf0c-6b9377661ac8
2016-06-02 17:31:49.216 DEBUG cinder.api.openstack.wsgi [req-7d35bc72-0440-4258-bfc8-8374b4b9b983 tempest-NetAppCDOTQOSTest-1518787507] Empty body provided in request get_body /opt/stack/new/cinder/cinder/api/openstack/wsgi.py:717
2016-06-02 17:31:49.216 DEBUG cinder.api.openstack.wsgi [req-7d35bc72-0440-4258-bfc8-8374b4b9b983 tempest-NetAppCDOTQOSTest-1518787507] Calling method '<bound method QoSSpecsController.associate of <cinder.api.contrib.qos_specs_manage.QoSSpecsController object at 0x7fac5a24aad0>>' _process_stack /opt/stack/new/cinder/cinder/api/openstack/wsgi.py:874
2016-06-02 17:31:49.217 DEBUG cinder.api.contrib.qos_specs_manage [req-7d35bc72-0440-4258-bfc8-8374b4b9b983 tempest-NetAppCDOTQOSTest-1518787507] Associate qos_spec: ec5f1f08-31a7-4115-b303-d9ad6dc65776 with type: 083d9ae8-592f-4b98-bf0c-6b9377661ac8 associate /opt/stack/new/cinder/cinder/api/contrib/qos_specs_manage.py:282
2016-06-02 17:31:49.248 ERROR oslo_db.sqlalchemy.exc_filters [req-815de577-e1d6-4418-a56e-8e8346257709 tempest-ConsistencyGroupsV2Test-1276397807] DBAPIError exception wrapped from (pymysql.err.InternalError) (1093, u"You can't specify target table 'consistencygroups' for update in FROM clause") [SQL: u'UPDATE consistencygroups SET updated_at=%(updated_at)s, status=%(status)s WHERE consistencygroups.deleted = false AND NOT (EXISTS (SELECT * \nFROM cgsnapshots \nWHERE cgsnapshots.consistencygroup_id = consistencygroups.id AND cgsnapshots.deleted = 0)) AND NOT (EXISTS (SELECT * \nFROM volumes \nWHERE volumes.consistencygroup_id = consistencygroups.id AND volumes.deleted = 0 AND (volumes.attach_status = %(attach_status_1)s OR (EXISTS (SELECT * \nFROM snapshots \nWHERE volumes.id = snapshots.volume_id AND snapshots.deleted = 0))))) AND NOT (EXISTS (SELECT * \nFROM consistencygroups AS consistencygroups_1 \nWHERE consistencygroups_1.deleted = 0 AND consistencygroups_1.status = %(status_1)s AND consistencygroups_1.source_cgid = %(source_cgid_1)s)) AND consistencygroups.id = %(id_1)s'] [parameters: {'status': 'deleting', u'attach_status_1': 'attached', 'updated_at': datetime.datetime(2016, 6, 2, 17, 31, 49, 247097), u'id_1': 'a8cc0153-1a16-4e2c-85ac-ada73a75bcd8', u'source_cgid_1': 'a8cc0153-1a16-4e2c-85ac-ada73a75bcd8', u'status_1': 'creating'}]
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters context)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 161, in execute
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters result = self._query(query)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 317, in _query
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters conn.query(q)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 835, in query
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1019, in _read_query_result
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters result.read()
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1302, in read
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 981, in _read_packet
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters packet.check_error()
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 393, in check_error
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters _check_mysql_exception(errinfo)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 115, in _check_mysql_exception
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters raise InternalError(errno, errorvalue)
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters InternalError: (1093, u"You can't specify target table 'consistencygroups' for update in FROM clause")
2016-06-02 17:31:49.248 27596 ERROR oslo_db.sqlalchemy.exc_filters

[Xing Yang (xing-yang)]

Reproduced on my setup. Exception was thrown while doing conditional update:

https://github.com/openstack/cinder/blob/master/cinder/consistencygroup/api.py#L440

[Xing Yang (xing-yang)]

If I remove this filter "~db.cg_creating_from_src(cg_id=group.id)", delete CG will succeed. Looking at the following review again, I don't see why this filter is needed as it is not checked in the original code. Will need to ask Gorka to verify this.

https://github.com/openstack/cinder/blob/master/cinder/consistencygroup/api.py#L437

https://review.openstack.org/#/c/259429/28/cinder/consistencygroup/api.py

[OpenStack Infra (hudson-openstack)]

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

[Xing Yang (xing-yang)]

The purpose of the filter "~db.cg_creating_from_src(cg_id=group.id)" is to prevent a CG from being deleted if it is a source of a cloned CG. However, we should be able to delete a CG that is the source of another CG, just like we can delete a volume that is the source of a cloned volume. So this filter is not needed.

[Michal Dulko (michal-dulko-f)]

The purpose of that filter is to prevent removal of a CG if another CG or CGSnap *is being* created from it. I think you shouldn't be able to delete a CG if it's a source for ongoing creation process.

But it looks like SQL has some restrictions here. Unit tests are probably passing because of SQLite implementation details.

[Xing Yang (xing-yang)]

Assigning it to Gorka as he is looking at how to fix the query.

[OpenStack Infra (hudson-openstack)]

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

[Gorka Eguileor (gorka)]

This is once again MySQL deviating from standard behavior, usually this is fixed using MySQL's non standard update with self left join, but that won't work on any other DB.

There's a way to trick MySQL that will work on all DBs and would look something like this (delete status checks removed for clarity):

UPDATE consistencygroups SET status='deleting'
WHERE consistencygroups.id='1'
AND consistencygroups.status='available'
AND NOT EXISTS (
    SELECT 1 FROM
        (
            SELECT * FROM consistencygroups
            WHERE consistencygroups.status == 'creating'
        ) as cg2
    WHERE consistencygroups.id=cg2.source_cgid
)

[OpenStack Infra (hudson-openstack)]

Change abandoned by xing-yang (<email address hidden>) on branch: master
Review: https://review.openstack.org/325027
Reason: Gorka has submitted a patch which fixes the query. Abandon this one.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/325863
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=6f63347eef802cd593cf63b6dcc130f99c651af8
Submitter: Jenkins
Branch: master

commit 6f63347eef802cd593cf63b6dcc130f99c651af8
Author: Gorka Eguileor <email address hidden>
Date: Mon Jun 6 13:47:48 2016 +0200

    Fix MySQL DB error in Delete CG

    When running a delete CG operation on a MySQL DB engine we'll get an
    error from the DB: "You can't specify target table 'consistencygroups'
    for update in FROM clause".

    This is caused by a non standard behavior only in MySQL, as SQLite and
    PostgreSQL work as expected, on filter `cg_creating_from_src` that
    checks that we are not using the CG we are trying to delete to create
    another CG at this very moment, CGs that were previously created from
    this CG are not considered.

    This patch changes the way we perform the filter using a select subquery
    as a workaround for MySQL unexpected behavior and updates devref to warn
    about this unexpected MySQL behavior.

    Change-Id: Ic10de411ffeceb00f1e8525906995bd8b2f49777
    Closes-Bug: #1588487

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/cinder 9.0.0.0b2 development milestone.