500 on SG deletion: Cannot delete or update a parent row

[Michal Dulko]

In kuryr-kubernetes gate runing NetworkPolicy tests from K8s itself we started to see Neutron returns 500 on SG delete requests. This is found in q-svc logs:

Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource [None req-14b3a3ce-74d3-4b84-a458-57acf8e74c0e service kuryr] delete failed: No details.: oslo_db.exception.DBReferenceError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`neutron`.`securitygroupportbindings`, CONSTRAINT `securitygroupportbindings_ibfk_2` FOREIGN KEY (`security_group_id`) REFERENCES `securitygroups` (`id`))')
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: [SQL: DELETE FROM securitygroups WHERE securitygroups.id = %(id)s]
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: [parameters: {'id': 'f62aa0c2-c513-4794-b48a-003a388d0ba0'}]
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: (Background on this error at: http://sqlalche.me/e/13/gkpj)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource Traceback (most recent call last):
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource cursor, statement, parameters, context
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource cursor.execute(statement, parameters)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 163, in execute
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource result = self._query(query)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 321, in _query
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource conn.query(q)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 505, in query
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource self._affected_rows = self._read_query_result(unbuffered=unbuffered)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 724, in _read_query_result
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource result.read()
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 1069, in read
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource first_packet = self.connection._read_packet()
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 676, in _read_packet
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource packet.raise_for_error()
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/protocol.py", line 223, in raise_for_error
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource err.raise_mysql_exception(self._data)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource File "/usr/local/lib/python3.6/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource raise errorclass(errno, errval)
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource pymysql.err.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`neutron`.`securitygroupportbindings`, CONSTRAINT `securitygroupportbindings_ibfk_2` FOREIGN KEY (`security_group_id`) REFERENCES `securitygroups` (`id`))')
Aug 20 19:43:56.358683 ubuntu-bionic-rax-iad-0019371817 neutron-server[10089]: ERROR neutron.api.v2.resource

Full log: https://508cf8e3a004102dbdaa-fe9c830c883bfe814f1bba253125965d.ssl.cf1.rackcdn.com/747233/2/check/kuryr-kubernetes-e2e-np-containerized-ovn-provider-ovn/c4fdef2/controller/logs/screen-q-svc.txt

It seems like we do not run any POST request for SG rules after that DELETE is called, but honestly I wouldn't be too surprised it may happen as well. Besides that I'm not exactly sure what causes it.

[Rodolfo Alonso (rodolfo-alonso-hernandez)]

Hello:

According to documentation, when a port is still associated to a SG, the error should be 409: https://github.com/openstack/neutron-lib/blob/master/api-ref/source/v2/security-groups.inc

Regards.

[Michal Dulko (michal-dulko-f)]

A little update, this can happen on port operations as well. Full log: https://7494b51f1867d83e2df3-c514cadbc1265d8f1c8d131346aa619b.ssl.cf5.rackcdn.com/748262/3/check/kuryr-kubernetes-e2e-np-containerized-ovn-provider-ovn/1a7daa6/controller/logs/screen-q-svc.txt

Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation Traceback (most recent call last):
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation cursor, statement, parameters, context
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation cursor.execute(statement, parameters)
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 163, in execute
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation result = self._query(query)
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 321, in _query
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation conn.query(q)
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 505, in query
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation self._affected_rows = self._read_query_result(unbuffered=unbuffered)
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 724, in _read_query_result
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation result.read()
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.6/dist-packages/pymysql/connections.py", line 1069, in read
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation first_packet = self.connection._read_packet()
Sep 03 09:17:29.978599 ubuntu-bionic-rax-dfw-0019616233 neutron-server[10691]: ERROR neutron.pecan_wsgi.hooks.translation ...

[Slawek Kaplonski (slaweq)]

Can You try if the same issue will happen with new quota driver: https://review.opendev.org/c/openstack/neutron/+/790060 also?

[Michal Dulko (michal-dulko-f)]

Looks like I still could find it in one of the most recent CI runs we have [1]:

Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation [None req-2317595a-64d5-47a0-9d4a-ba0f86acfbbf service kuryr] PUT failed.: oslo_db.exception.DBReferenceError: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`neutron`.`securitygroupportbindings`, CONSTRAINT `securitygroupportbindings_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE)')
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: [SQL: INSERT INTO securitygroupportbindings (port_id, security_group_id) VALUES (%(port_id)s, %(security_group_id)s)]
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: [parameters: {'port_id': '5ee85680-0d39-4d40-b9c3-56179d1a27eb', 'security_group_id': '2ea191c7-588e-46b5-bd45-50447815cbd0'}]
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: (Background on this error at: https://sqlalche.me/e/14/gkpj)
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation Traceback (most recent call last):
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation self.dialect.do_execute(
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 717, in do_execute
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation cursor.execute(statement, parameters)
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 148, in execute
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation result = self._query(query)
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 310, in _query
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation conn.query(q)
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 548, in query
Aug 25 05:45:12.146325 ubuntu-focal-inap-mtl01-0026083996 neutron-server[106098]: ERROR neutron.pecan_wsgi.hooks.translation self._affected_rows = self._read_query_result(unbuffered=unbu...