py311 unit test failures - more sqlalchemy migration issues

[Goutham Pacha Ravi]

This bug may be very similar to https://bugs.launchpad.net/manila/+bug/2035236 that we fixed up earlier in the Caracal release cycle. In a py311 environment, the database migration snake walk test case fails to process the init migration:

https://paste.openstack.org/show/bEuQE9UKdjJmaqWNLRij/

Prior to switching the charset of a table, we do seek that the foreign key constraint checking be disabled, so we can switch the charset and re-enable the constraint checks: https://opendev.org/openstack/manila/src/commit/a7ffbf7bd0b62fea1c94eaaa6cc518298704ad85/manila/db/migrations/alembic/versions/162a3e673105_manila_init.py#L412-L420

However, this seems to be broken.

The tox console log is attached. Relevant imports, and package versions are:

SQLAlchemy-1.4.51
SQLAlchemy-Utils-0.41.1
alembic-1.9.4

[Vida Haririan (vhariria)]

See additional triage information at https://meetings.opendev.org/meetings/manila/2024/manila.2024-02-15-15.00.log.html

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/manila/+/908738
Committed: https://opendev.org/openstack/manila/commit/c8ed5b05e9e8644d7418f8c5c46bee64a9835500
Submitter: "Zuul (22348)"
Branch: master

commit c8ed5b05e9e8644d7418f8c5c46bee64a9835500
Author: Takashi Kajinami <email address hidden>
Date: Sun Feb 11 16:50:31 2024 +0900

    db: Fix charset updates in migrations

    We are failing py311 jobs with our migration checks;
    and the reason is that we have some 'implied' sql defaults
    that might be inconsistent with the server versions - one
    of them is the charset. We recently [1] fixed a bunch of
    un-annotated tables with explicit charset declarations.
    This is a continuation of that attempt.

    Unfortunately, we can't change history. On existing
    deployments, changes to these migrations will be ignored,
    and must be done manually if there are any inconsistencies
    to be fixed.

    While we're at it, this commit also cleans up separate
    steps to set the charset and engine to pre-created
    tables.

    [1] I7cd6fa0cc8e054af112493746e753fef2024000f
    Closes-Bug: #2053211
    Change-Id: I16345f63a279549e81cbde7b60fed29521d48a02
    Signed-off-by: Goutham Pacha Ravi <email address hidden>

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/2023.2
Review: https://review.opendev.org/c/openstack/manila/+/909555

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/manila 18.0.0.0rc1 release candidate.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/manila/+/909555
Committed: https://opendev.org/openstack/manila/commit/695df5993e76b12bbb50bb84c4dbf8db234d3054
Submitter: "Zuul (22348)"
Branch: stable/2023.2

commit 695df5993e76b12bbb50bb84c4dbf8db234d3054
Author: Takashi Kajinami <email address hidden>
Date: Sun Feb 11 16:50:31 2024 +0900

    db: Fix charset updates in migrations

    We are failing py311 jobs with our migration checks;
    and the reason is that we have some 'implied' sql defaults
    that might be inconsistent with the server versions - one
    of them is the charset. We recently [1] fixed a bunch of
    un-annotated tables with explicit charset declarations.
    This is a continuation of that attempt.

    Unfortunately, we can't change history. On existing
    deployments, changes to these migrations will be ignored,
    and must be done manually if there are any inconsistencies
    to be fixed.

    While we're at it, this commit also cleans up separate
    steps to set the charset and engine to pre-created
    tables.

    [1] I7cd6fa0cc8e054af112493746e753fef2024000f
    Closes-Bug: #2053211
    Change-Id: I16345f63a279549e81cbde7b60fed29521d48a02
    Signed-off-by: Goutham Pacha Ravi <email address hidden>
    (cherry picked from commit c8ed5b05e9e8644d7418f8c5c46bee64a9835500)