Deletion of deep trees of objects can fail with MySQL

[Andras Kovi]

The MySQL reference states[1]:

"If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the same cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep."

As a result, deep execution hierarchies are not possible to be deleted from the root and fail with an error like:

(pymysql.err.InternalError) (1296, u"Got error 193 '`mistraldb`.`task_executions_v2`, CONSTRAINT `task_executions_v2_ibfk_1` FOREIGN KEY (`workflow_execution_id`) REFERENCES `workflow_executions_v2` (`id`) ON DELETE CASCADE' from InnoDB")
[SQL: DELETE FROM workflow_executions_v2 WHERE (workflow_executions_v2.project_id = %(project_id_1)s OR workflow_executions_v2.scope = %(scope_1)s) AND workflow_executions_v2.id = %(id_1)s]
[parameters: {u'project_id_1': '<default-project>', u'id_1': u'b1f88f4b-7911-4760-aded-5b1f93d5ebe5', u'scope_1': 'public'}]
(Background on this error at: http://sqlalche.me/e/2j85)

Same error can happen on PGsql. But there the modification of stack size may mitigate the issue.

[1] https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.5/en/ansi-diff-foreign-keys.html

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/mistral 9.0.0.0b1 development milestone.