[SRU] barbican-manage db upgrade fails with MySQL8

[David Ames]

[Impact]

New installations of OpenStack Barbican with Mysql8 fail with a traceback when trying to initialize the database

[Test Case]

Use the OpenStack charms to deploy Barbican on Ubuntu Focal. Using Focal is expected to have a traceback and with this change is expected to setup successfully.

[Regression Potential]

The regression potential for this is minimal as the package currently does not work at all with mysql8 which ships with Ubuntu Focal.

The proposed change is a cherry-pick from upstream that resolves this issue by rebasing the old migrations into a single larger migration. This
only affects new deploys that would normally step through all of the old
migrations so shouldn't affect running deploys.

-----------------------------------------------------

Running

barbican-manage db upgrade fails with the following traceback when the DB is mysql8:

2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters self.dialect.do_execute(
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 581, in do_execute
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters result = self._query(query)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters conn.query(q)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters result.read()
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters packet.check_error()
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters err.raise_mysql_exception(self._data)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters raise errorclass(errno, errval)
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters pymysql.err.InternalError: (3959, "Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.")
2020-10-08 22:31:32.028 28131 ERROR oslo_db.sqlalchemy.exc_filters
ERROR: (pymysql.err.InternalError) (3959, "Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.")
[SQL: ALTER TABLE secret_acls CHANGE creator_only project_access BOOL NULL]

Seems this is a known issue with alembic [0]

[1] https://github.com/sqlalchemy/alembic/issues/699

[Corey Bryant (corey.bryant)]

It looks like this is fixed upstream so we may just need to get this into the focal package. https://storyboard.openstack.org/#!/story/2007732

[Chris MacNaughton (chris.macnaughton)]

This fix should be in the version of Barbican that I just uploaded to Groovy

[Chris MacNaughton (chris.macnaughton)]

A fix for this is proposed to Barbican's stable/ussuri branch at https://review.opendev.org/#/c/757819

[David Ames (thedac)]

Attempting to validate the package change ran into another failure mode:

# apt-cache policy barbican-common
barbican-common:
  Installed: 1:10.0.0-0ubuntu0.20.04.2~ubuntu20.04.1~ppa202010131146
  Candidate: 1:10.0.0-0ubuntu0.20.04.2~ubuntu20.04.1~ppa202010131146
  Version table:
 *** 1:10.0.0-0ubuntu0.20.04.2~ubuntu20.04.1~ppa202010131146 500
        500 http://ppa.launchpad.net/chris.macnaughton/focal-ussuri/ubuntu focal/main amd64 Packages
        100 /var/lib/dpkg/status
     1:10.0.0-0ubuntu0.20.04.1 500
        500 http://nova.clouds.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
     1:10.0.0~b2~git2020020508.7b14d983-0ubuntu3 500
        500 http://nova.clouds.archive.ubuntu.com/ubuntu focal/main amd64 Packages

# sudo -u barbican barbican-manage db upgrade
/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
  result = self._query(query)
2020-10-13 15:45:16.211 28298 WARNING oslo_db.sqlalchemy.engines [-] MySQL SQL mode is 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION', consider enabling TRADITIONAL or STRICT_ALL_TABLES
2020-10-13 15:45:16.217 28298 INFO alembic.runtime.migration [-] Context impl MySQLImpl.
2020-10-13 15:45:16.217 28298 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.
2020-10-13 15:45:16.232 28298 INFO alembic.runtime.migration [-] Running upgrade 1bc885808c76 -> 161f8aceb687, fill project_id to secrets where missing
2020-10-13 15:45:16.399 28298 WARNING oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped.: pymysql.err.InternalError: (3098, 'The table does not comply with the requirements by an external plugin.')
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters self.dialect.do_execute(
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 581, in do_execute
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters result = self._query(query)
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters conn.query(q)
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
2020-10-13 15:45:16.399 28298 ERROR oslo_db.sqlalchemy.exc_fi...

[Chris MacNaughton (chris.macnaughton)]

https://review.opendev.org/#/c/760218/ has landed in stable-ussuri upstream to resolve this

[Chris MacNaughton (chris.macnaughton)]

I've uploaded the upstream fix for this to the focal-unnapproved queue (https://launchpad.net/ubuntu/focal/+queue?queue_state=1&queue_text=barbican).

[Brian Murray (brian-murray)]

Hello David, or anyone else affected,

Accepted barbican into focal-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/barbican/1:10.0.0-0ubuntu0.20.04.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, what testing has been performed on the package and change the tag from verification-needed-focal to verification-done-focal. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed-focal. 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.

[Ubuntu SRU Bot (ubuntu-sru-bot)]

All autopkgtests for the newly accepted barbican (1:10.0.0-0ubuntu0.20.04.2) for focal have finished running.
The following regressions have been reported in tests triggered by the package:

barbican/1:10.0.0-0ubuntu0.20.04.2 (ppc64el, arm64, amd64, s390x)

Please visit the excuses page listed below and investigate the failures, proceeding afterwards as per the StableReleaseUpdates policy regarding autopkgtest regressions [1].

https://people.canonical.com/~ubuntu-archive/proposed-migration/focal/update_excuses.html#barbican

[1] https://wiki.ubuntu.com/StableReleaseUpdates#Autopkgtest_Regressions

Thank you!

[David Ames (thedac)]

On Tue, Feb 16, 2021 at 12:00 PM Brian Murray
<email address hidden> wrote:
>
> Hello David, or anyone else affected,
>
> Accepted barbican into focal-proposed. The package will build now and be
> available at
> https://launchpad.net/ubuntu/+source/barbican/1:10.0.0-0ubuntu0.20.04.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, what testing has been
> performed on the package and change the tag from verification-needed-
> focal to verification-done-focal. If it does not fix the bug for you,
> please add a comment stating that, and change the tag to verification-
> failed-focal. In either case, without details of your testing we will
> not be able to proceed.

Unfortunately, we seem to still have a problem.

apt-cache policy python3-barbican
python3-barbican:
  Installed: 1:10.0.0-0ubuntu0.20.04.2
  Candidate: 1:10.0.0-0ubuntu0.20.04.2
  Version table:
 *** 1:10.0.0-0ubuntu0.20.04.2 500
        500 http://archive.ubuntu.com/ubuntu focal-proposed/main amd64 Packages
        100 /var/lib/dpkg/status
     1:10.0.0-0ubuntu0.20.04.1 500
        500 http://nova.clouds.archive.ubuntu.com/ubuntu
focal-updates/main amd64 Packages
     1:10.0.0~b2~git2020020508.7b14d983-0ubuntu3 500
        500 http://nova.clouds.archive.ubuntu.com/ubuntu focal/main
amd64 Packages

# sudo -u barbican barbican-manage db upgrade
/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (3719,
"'utf8' is currently an alias for the character set UTF8MB3, but will
be an alias for UTF8MB4 in a future release. Please consider using
UTF8MB4 in order to be unambiguous.")
  result = self._query(query)
2021-02-17 17:12:26.700 232723 WARNING oslo_db.sqlalchemy.engines [-]
MySQL SQL mode is
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',
consider enabling TRADITIONAL or STRICT_ALL_TABLES
2021-02-17 17:12:26.728 232723 INFO alembic.runtime.migration [-]
Context impl MySQLImpl.
2021-02-17 17:12:26.729 232723 INFO alembic.runtime.migration [-] Will
assume non-transactional DDL.
/usr/lib/python3/dist-packages/alembic/script/revision.py:152:
UserWarning: Revision 39cf2e645cba referenced from 39cf2e645cba ->
0f8c192a061f (head), Add Secret Consumers table is not present
  util.warn(
ERROR: '39cf2e645cba'

--
David Ames

[Chris MacNaughton (chris.macnaughton)]

I've uploaded a new version of this package for Focal that should resolve the above bug. Due to packaging confusion, the initial patch removed all of the old migrations, but failed to include the new migration. I've tested the new package to ensure that it does include the new migration, and that the `barbican-manage db upgrade` command works correctly.

[Łukasz Zemczak (sil2100)]

Hello David, or anyone else affected,

Accepted barbican into focal-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/barbican/1:10.0.0-0ubuntu0.20.04.3 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, what testing has been performed on the package and change the tag from verification-needed-focal to verification-done-focal. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed-focal. 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.

[David Ames (thedac)]

Łukasz,

I have verified the 10.0.0-0ubuntu0.20.04.3 version of barbican on Focal. Please complete the SRU.

[Launchpad Janitor (janitor)]

This bug was fixed in the package barbican - 1:10.0.0-0ubuntu0.20.04.3

---------------
barbican (1:10.0.0-0ubuntu0.20.04.3) focal; urgency=medium

  * d/p/resolve-alembic-migration-issue-mysql8.patch: Update .egg-info/SOURCES.txt
    to include new migration and remove old migration.

barbican (1:10.0.0-0ubuntu0.20.04.2) focal; urgency=medium

  * d/control: Update VCS paths for move to lp:~ubuntu-openstack-dev.
  * d/p/resolve-alembic-migration-issue-mysql8.patch: Cherry-pick upstream fix
    to allow migrations to work with mysql8 (LP: #1899104).

 -- Chris MacNaughton <email address hidden> Fri, 12 Mar 2021 10:13:23 +0000

[Łukasz Zemczak (sil2100)]

The verification of the Stable Release Update for barbican has completed successfully and the package is now being released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.

[Chris MacNaughton (chris.macnaughton)]

Hello David, or anyone else affected,

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

[David Ames (thedac)]

Verification on bionic-ussuri is successful:

# sudo -u barbican barbican-manage db upgrade
2021-03-30 16:20:55.622 28161 WARNING oslo_db.sqlalchemy.engines [-] MySQL SQL mode is 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION', consider enabling TRADITIONAL or STRICT_ALL_TABLES
2021-03-30 16:20:55.640 28161 INFO alembic.runtime.migration [-] Context impl MySQLImpl.
2021-03-30 16:20:55.641 28161 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.

# dpkg -l |grep barbican
ii barbican-api 1:10.0.0-0ubuntu0.20.04.3~cloud0 all OpenStack Key Management Service - API Server
ii barbican-common 1:10.0.0-0ubuntu0.20.04.3~cloud0 all OpenStack Key Management Service - common files
ii barbican-worker 1:10.0.0-0ubuntu0.20.04.3~cloud0 all OpenStack Key Management Service - Worker Node
ii python3-barbican 1:10.0.0-0ubuntu0.20.04.3~cloud0 all OpenStack Key Management Service - Python 3 files
ii python3-barbicanclient 4.10.0-0ubuntu1~cloud0 all OpenStack Key Management API client - Python 3.x

# apt-cache policy python3-barbican
python3-barbican:
  Installed: 1:10.0.0-0ubuntu0.20.04.3~cloud0
  Candidate: 1:10.0.0-0ubuntu0.20.04.3~cloud0
  Version table:
 *** 1:10.0.0-0ubuntu0.20.04.3~cloud0 500
        500 http://ubuntu-cloud.archive.canonical.com/ubuntu bionic-proposed/ussuri/main amd64 Packages
        100 /var/lib/dpkg/status

[Chris MacNaughton (chris.macnaughton)]

The verification of the Stable Release Update for barbican has completed successfully and the package is now being released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.