[DB] Migration to SQLAlchemy 2.0

[Rodolfo Alonso]

This is a container for the efforts to be done in Neutron, neutron-lib and plugins projects to migrate to SQLAlchemy 2.0.

There is currently a patch in neutron-lib to disable the session "__autocommit" flag, that will be optional in SQLAlchemy 1.4 and mandatory in SQLAlchemy 2.0 [1]. We have found problems with how the session transactions are now handled by SQLAlchemy.

In Neutron there are many places where we make a database call running on an implicit transaction, that means we don't explicitly create a reader/writer context. With "autocommit=True", this transaction is discarded immediately; under non-autocommit sessions, the transaction created remains open. That is leading to database errors as seen in the tempest tests.

In [2], as recommended by Mike Bayer (main maintainer and author of SQLAlchemy), we have enabled again the "autocommit" flag and create a log message to track when Neutron tries to execute a command with session with an inactive transaction.

The goal of this bug is to move all Neutron database interactions to be SQLAlchemy 2.0 compliant.

[1]https://review.opendev.org/c/openstack/neutron-lib/+/828738
[2]https://review.opendev.org/c/openstack/neutron-lib/+/833103

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/833247

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/833247
Committed: https://opendev.org/openstack/neutron/commit/eeb918e1b9c27d3b5f0747cb856a4a8483f636ad
Submitter: "Zuul (22348)"
Branch: master

commit eeb918e1b9c27d3b5f0747cb856a4a8483f636ad
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Sat Mar 5 14:34:40 2022 +0000

    Add the corresponding DB context to all SQL transactions

    The goal of this patch is to make the Neutron code compliant
    with SQLAlchemy 2.0.

    All SQL transactions must be executed inside an explicit
    writer/reader context. SQLAlchemy no longer will create an
    implicit transaction if the session has no active transaction.

    A warning message, only available in debug mode, is added. When
    an ORM session calls "do_orm_execute", if there is no active
    transaction, a warning message with a traceback will be logged
    to help to debug the regression introduced.

    Related-Bug: #1964575

    Change-Id: I3da37fee205b8d67d10673075b9130147d9eab5f

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Rodolfo Alonso <email address hidden>" on branch: master
Review: https://review.opendev.org/c/openstack/neutron-lib/+/833103

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/841576

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron-fwaas/+/842254

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/842468

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron-fwaas/+/842254
Committed: https://opendev.org/openstack/neutron-fwaas/commit/ba0a036469947477a1da8d5427413d22a68193b0
Submitter: "Zuul (22348)"
Branch: master

commit ba0a036469947477a1da8d5427413d22a68193b0
Author: zhouhenglc <email address hidden>
Date: Wed May 18 11:01:34 2022 +0800

    Add the corresponding DB context to all SQL transactions

    Neutron code migrated to SQLAlchemy 2.0[1], the goal of this
    patch is to make the fwaas plugin code compliant with
    SQLAlchemy 2.0.

    [1] https://review.opendev.org/c/openstack/neutron/+/833247

    Related-Bug: #1964575

    Change-Id: If3e996740d4b5024e9c798227d0a58ceb09eb1d6

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Rodolfo Alonso <email address hidden>" on branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/841576
Reason: Already implemented.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/842468
Committed: https://opendev.org/openstack/neutron/commit/59973d29c3d9d2defd2003206e138a94090ad4ff
Submitter: "Zuul (22348)"
Branch: master

commit 59973d29c3d9d2defd2003206e138a94090ad4ff
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Fri May 13 14:05:57 2022 +0000

    [sqlalchemy-20] Add missing DB contexts in L3 methods

    The goal of this series of patches is to make the Neutron code
    compliant with SQLAlchemy 2.0.

    This patch adds the missing database contexts in the execution of
    some queries in the L3 code, reported in the following bugs. This
    patch also refactors the ``neutron.db.l3_db`` methods, using the
    method decorators instead of the inline context builders.

    Closes-Bug: #1974144
    Closes-Bug: #1974142
    Related-Bug: #1964575
    Change-Id: I7e18db7f1fa6b8878c13df09895c993704401815

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/852728

[Brian Haley (brian-haley)]

Not sure if we should just close this and track any future issues in their own bugs?

[Rodolfo Alonso (rodolfo-alonso-hernandez)]

Yes, I think we can close this one and consider it as released. So far we didn't find any new error in the CI jobs. Any new bug can be tracked in a new LP bug.

[OpenStack Infra (hudson-openstack)]

Change abandoned by "Rodolfo Alonso <email address hidden>" on branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/852728