[mysql8] Unknown column 'public' in 'firewall_rules_v2'

[Albert Damen]

I installed a fresh openstack test cluster in eoan today (October 3).

Neutron database initialization with the command:
sudo su -s /bin/sh -c "neutron-db-manage
  --config-file /etc/neutron/neutron.conf
  --config-file /etc/neutron/plugins/ml2/ml2_conf.ini
   upgrade head" neutron

failed with error message:
oslo_db.exception.DBError: (pymysql.err.InternalError) (1054, "Unknown column 'public' in 'firewall_rules_v2'") [SQL: 'ALTER TABLE firewall_rules_v2 CHANGE public shared BOOL NULL'] (Background on this error at: http://sqlalche.me/e/2j85)

In mysql the table and the column exist, with a constraint on the column:
CONSTRAINT `firewall_rules_v2_chk_1` CHECK ((`public` in (0,1))),

manually updating the column in mysql failed with the same error message.
mysql> ALTER TABLE firewall_rules_v2 CHANGE public shared BOOL NULL;
ERROR 1054 (42S22): Unknown column 'public' in 'check constraint firewall_rules_v2_chk_1 expression'

I guessed the constraint did not like it if the name of the column was changed.
I removed the column 'private' and created it again, without the constraint. The the alter table command worked fine.
After doing the same for the private columns in tables firewall_groups_v2 and firewall_policies_v2 Neutron could initialize the database and all was fine (I could create a network and start an instance).

neutron 2:15.0.0~rc1-0ubuntu1
mysql-server 8.0.16-0ubuntu3

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[James Page (james-page)]

Worth noting that mysql-8 is the first mysql release that supports check constraints and they behave a little differently to pgsql ones.

[James Page (james-page)]

I think that if you rename a column the check constraint would need to be dropped first and then re-created afterwards.

[James Page (james-page)]

I think the check constraint is automatically created by sqlalchemy to enforce the Boolean type definition.

[James Page (james-page)]

Related - https://github.com/sqlalchemy/alembic/issues/605

[Bernard Cafarelli (bcafarel)]

Not tested directly, but marking confirmed, James are you working on it on neutron-fwaas side?

[James Page (james-page)]

I can next week - however the solution is at best a workaround as alembic/sqla don't have an immediate fix for dropping and re-creating the constraint that gets auto-generated.

The fix is to use create_constraint=False when creating sa.Boolean column types so they don't get generated in the first place - that could be limited to MySQL thus maintaining the pre MySQL 8 behaviour.

[OpenStack Infra (hudson-openstack)]

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

[Launchpad Janitor (janitor)]

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

---------------
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.

 -- James Page <email address hidden> Mon, 07 Oct 2019 09:31:20 +0100

[OpenStack Infra (hudson-openstack)]

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