fwaas_v2 - unable to associate port with firewall (PXC strict mode)

[James Page]

[Impact]
Unable to associate ports with a firewall under FWaaS v2

[Test Case]
Deploy OpenStack (stein or Later) using Charms
Create firewall policy, apply to router - failure as unable to associate port with policy in underlying DB

[Regression Potential]
Medium; the proposed fix has not been accepted upstream as yet (discussion ongoing due to change of database migrations).

[Original Bug Report]
Impacts both Stein and Rocky (although rocky does not enable v2 just yet).

542 a9761fa9124740028d0c1d70ff7aa542] DBAPIError exception wrapped from (pymysql.err.InternalError) (1105, 'Percona-XtraDB-Cluster prohibits use of DML command on a table (neutron.firewall_group_port_associations_v2) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER') [SQL: 'DELETE FROM firewall_group_port_associations_v2 WHERE firewall_group_port_associations_v2.firewall_group_id = %(firewall_group_id_1)s'] [parameters: {'firewall_group_id_1': '85a277d0-ebaf-4a5d-9d45-6a74b8f54372'}] (Background on this error at: http://sqlalche.me/e/2j85): pymysql.err.InternalError: (1105, 'Percona-XtraDB-Cluster prohibits use of DML command on a table (neutron.firewall_group_port_associations_v2) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER')
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters context)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 509, in do_execute
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 165, in execute
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters result = self._query(query)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 321, in _query
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters conn.query(q)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 860, in query
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1061, in _read_query_result
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters result.read()
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1349, in read
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1018, in _read_packet
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters packet.check_error()
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 384, in check_error
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters raise errorclass(errno, errval)
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters pymysql.err.InternalError: (1105, 'Percona-XtraDB-Cluster prohibits use of DML command on a table (neutron.firewall_group_port_associations_v2) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER')
2019-06-07 10:07:50.937 30837 ERROR oslo_db.sqlalchemy.exc_filters

ProblemType: Bug
DistroRelease: Ubuntu 18.04
Package: neutron-server 2:14.0.0-0ubuntu1.1~cloud0 [origin: Canonical]
ProcVersionSignature: Ubuntu 4.15.0-51.55-generic 4.15.18
Uname: Linux 4.15.0-51-generic x86_64
ApportVersion: 2.20.9-0ubuntu7.6
Architecture: amd64
CrashDB:
 {
                "impl": "launchpad",
                "project": "cloud-archive",
                "bug_pattern_url": "http://people.canonical.com/~ubuntu-archive/bugpatterns/bugpatterns.xml",
             }
Date: Fri Jun 7 10:22:18 2019
Ec2AMI: ami-000005f0
Ec2AMIManifest: FIXME
Ec2AvailabilityZone: nova
Ec2InstanceType: m1.blue
Ec2Kernel: unavailable
Ec2Ramdisk: unavailable
PackageArchitecture: all
ProcEnviron:
 TERM=screen-256color
 PATH=(custom, no user)
 XDG_RUNTIME_DIR=<set>
 LANG=C.UTF-8
 SHELL=/bin/bash
SourcePackage: neutron
UpgradeStatus: No upgrade log present (probably fresh install)
mtime.conffile..etc.default.neutron-server: 2019-06-07T09:50:57.855385

[Sahid Orentino (sahid-ferdjaoui)]

Missing primary keys for firewall_group_port_associations_v2 and firewall_policy_rule_associations_v2. The workaround is to change the mode used [0].

  juju config percona-cluster pxc-strict-mode=PERMISSIVE

[0] https://bugs.launchpad.net/ubuntu/+source/octavia/+bug/1826875/comments/3

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/663931

[James Page (james-page)]

As cosmic EOL's today not targeting for this fix.

[Launchpad Janitor (janitor)]

This bug was fixed in the package neutron-fwaas - 1:15.0.0~rc1-0ubuntu3

---------------
neutron-fwaas (1:15.0.0~rc1-0ubuntu3) eoan; urgency=medium

  * d/p/add-missing-pk-firewall-group-associations-v2.patch: Cherry pick
    fix to resolve issue with missing primary key on
    firewall_group_associations_v2 table (LP: #1831986).

 -- James Page <email address hidden> Fri, 11 Oct 2019 14:19:24 +0100

[James Page (james-page)]

This bug was fixed in the package neutron-fwaas - 1:15.0.0~rc1-0ubuntu3~cloud0
---------------

 neutron-fwaas (1:15.0.0~rc1-0ubuntu3~cloud0) bionic-train; urgency=medium
 .
   * New update for the Ubuntu Cloud Archive.
 .
 neutron-fwaas (1:15.0.0~rc1-0ubuntu3) eoan; urgency=medium
 .
   * d/p/add-missing-pk-firewall-group-associations-v2.patch: Cherry pick
     fix to resolve issue with missing primary key on
     firewall_group_associations_v2 table (LP: #1831986).

[Timo Aaltonen (tjaalton)]

Hello James, or anyone else affected,

Accepted neutron-fwaas into disco-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/neutron-fwaas/1:14.0.0-0ubuntu1.2 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation on how to enable and use -proposed. 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-needed-disco to verification-done-disco. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed-disco. In either case, without details of your testing we will not be able to proceed.

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

N.B. The updated package will be released to -updates after the bug(s) fixed by this package have been verified and the package has been in -proposed for a minimum of 7 days.

[Corey Bryant (corey.bryant)]

Hello James, or anyone else affected,

Accepted neutron-fwaas into stein-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:stein-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-stein-needed to verification-stein-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-stein-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!

[Corey Bryant (corey.bryant)]

Since disco is EOL this month, we can work this through the stein cloud archive.

[OpenStack Infra (hudson-openstack)]

Change abandoned by Slawek Kaplonski (<email address hidden>) on branch: master
Review: https://review.opendev.org/663931
Reason: As we are going to deprecate master branch in this project this patch is not needed anymore.

[Corey Bryant (corey.bryant)]

This bug was fixed in the package neutron-fwaas - 1:16.0.0+git2022060611.b7be99c14-0ubuntu1~cloud0
---------------

 neutron-fwaas (1:16.0.0+git2022060611.b7be99c14-0ubuntu1~cloud0) jammy-zed; urgency=medium
 .
   * New package for the Ubuntu Cloud Archive.
 .
 neutron-fwaas (1:16.0.0+git2022060611.b7be99c14-0ubuntu1) kinetic; urgency=medium
 .
   * New upstream snapshot for OpenStack Zed.
   * d/control: Update standards version to 4.6.1.
 .
 neutron-fwaas (1:16.0.0+git2022033113.de8a4d4cf-0ubuntu1) jammy; urgency=medium
 .
   * New upstream snapshot for OpenStack Yoga.
 .
 neutron-fwaas (1:16.0.0+git2022030710.a9f26b81e-0ubuntu1) jammy; urgency=medium
 .
   * d/control: Update VCS paths for move to lp:~ubuntu-openstack-dev.
   * New upstream snapshot for OpenStack Yoga.
   * d/control: Align (Build-)Depends with upstream.
   * d/control: Switch to debhelper-compat 13.
   * d/p/add-missing-pk-firewall-group-associations-v2.patch: Rebased.
 .
 neutron-fwaas (1:16.0.0-0ubuntu1) groovy; urgency=medium
 .
   * d/watch: Scope to 16.x series and get tarballs from opendev.org.
   * New upstream release for OpenStack Ussuri (LP: #1877642).
 .
 neutron-fwaas (1:16.0.0~b3~git2020032402.5e6c04885-0ubuntu1) focal; urgency=medium
 .
   * New upstream snapshot for OpenStack Ussuri.
 .
 neutron-fwaas (1:16.0.0~b2~git2020020713.d9ac7173f-0ubuntu1) focal; urgency=medium
 .
   * New upstream snapshot for OpenStack Ussuri.
   * d/p/add-missing-pk-firewall-group-associations-v2.patch: Refreshed.
 .
 neutron-fwaas (1:16.0.0~b1~git2019121712.330e233a4-0ubuntu1) focal; urgency=medium
 .
   * New upstream snapshot for OpenStack Ussuri.
   * d/control: Align (Build-)Depends with upstream.
   * d/control, d/rules, d/compat: Switch to debhelper compat 12 and pybuild.
   * d/rules: Update PBR_VERSION to handle ~git versioning.
 .
 neutron-fwaas (1:15.0.0-0ubuntu1) eoan; urgency=medium
 .
   * New upstream release for OpenStack Train.
 .
 neutron-fwaas (1:15.0.0~rc1-0ubuntu3) eoan; urgency=medium
 .
   * d/p/add-missing-pk-firewall-group-associations-v2.patch: Cherry pick
     fix to resolve issue with missing primary key on
     firewall_group_associations_v2 table (LP: #1831986).
 .
 neutron-fwaas (1:15.0.0~rc1-0ubuntu2) eoan; urgency=medium
 .
   * d/p/disable-check-constraint-for-public-columns.patch: Disable
     creation of check constraints for 'public' columns under MySQL
     (LP: #1846606) resolving compatibility with MySQL >= 8.
   * d/tests/*: Add autopkgtest to validate database migrations under
     MySQL.
 .
 neutron-fwaas (1:15.0.0~rc1-0ubuntu1) eoan; urgency=medium
 .
   [ Sahid Orentino Ferdjaoui ]
   * New upstream snapshot for OpenStack Train.
 .
   [ Corey Bryant ]
   * d/watch: Monitor 15.x series.
   * New upstream release for OpenStack Train.
   * d/control: Align (Build-)Depends with upstream.
   * d/p/drop-sphinxcontrib-apidoc.patch: Dropped. No longer needed.
 .
 neutron-fwaas (1:15.0.0~b2~git2019073114.243a7ef57-0ubuntu1) eoan; urgency=medium
 .
   * New upstream snapshot for OpenStack Train.
   * d/control: Align (Build-)Depends with upstream.
   * d/p/netfilter_log-Correct-decode-binary-types.patch: Dropped.
     Fixed in snapshot.
 .
 neu...