Hey!

I was wondering if it's possible to postpone the merge of the requirements bump for SQLalchemy at least till Milestone 1?

Basically why I'm asking, as we're trying to revive the Freezer project now, and it's broken badly in many places, including SQLalchemy compatibility. While there is an outstanding patch [1] which fixes the "current" state of CI, there's nobody around to merge that. And patch for changing the project leadership model to DPL (which allows us to get access to land patches to the project) is currently under TC review [2].

So it would be pretty much appreciated if we could be given some more time before breaking the project even further.

While I fully understand that we already waited for too long and agree that it needs to be done early in the cycle, I still kindly ask for some minor delay until we sort out access at the very least...

[1] https://review.opendev.org/c/openstack/freezer-api/+/896350 [2] https://review.opendev.org/c/openstack/governance/+/914911

пт, 12 апр. 2024 г. в 12:33, Stephen Finucane stephenfin@redhat.com:

o/

A whole 2 years and 8 months to the day after I first communicated about it [1], it would appear the requirements bump to SQLAlchemy 2.0 [2] is finally ready to go. I suspect most if not all service projects are familiar with SQLAlchemy 2.0 at this point, having needed to fix whatever compatibility issues were seen. For those that aren't, I suggest reading my previous emails on the matter.

So what happens next? Hopefully for the vast majority of projects the answer should be "nothing": many projects now have sqlalchemy-tips jobs that are designed to run unit and/or integration tests with the latest versions of SQLAlchemy, Alembic and oslo.db so the switchover should be seamless. I foresee three situations:

• For projects that continue working as-is post-bump, you may wish to delete the sqlalchemy-tips jobs now. The jobs were designed as a stop gap measure to prevent projects regressing on SQLAlchemy 2.0 support while we got other projects over the line. While there is some limited value in keeping these jobs around, they do consume resources and - to the best of my knowledge - the SQLAlchemy community is not planning to release a 3.0 version of SQLAlchemy (or 2.0 version of Alembic) any time soon :) The requirements jobs gates on many projects and should identify compatibility issues with these libraries going forward, so it's probably best to drop them from the projects. I'll push a couple of patches shortly to do just this. Feel free to accept or reject them as you see fit.

• For projects that are supposed to be SQLAlchemy 2.0 Ready (TM) but start seeing issues that the sqlalchemy-tips jobs didn't catch (because, say, the job was running unit tests rather than functional tests and the unit tests are missing coverage), I'd encourage you to look through what is now a fairly extensive repository of patches [3] that we've built up while addressing SQLAlchemy 2.0 compatibility issues. These are mostly all tagged with the sqlalchemy-20 topic and there's a very good chance that a patch addressing your specific issue has already been submitted to another project.

• For projects that were not in the gating queue for requirements bumps, have not prepped for SQLAlchemy, and start failing catastrophically post-bump: have you not been listening to me?! :) You've got some work to do but fortunately that work should be relatively simple (if a bit tedious). As noted above, there are a *lot* of patches that you can look through to identify solutions to your compatibility issues. My preferred strategy - which is the one also advocated for by the SQLAlchemy 2.x docs [4] - has been to configure the warnings filter to raise an error for all SQLAlchemy deprecation warnings then filter out those that we actually hit and iteratively work to remove these filters. That won't work now that SQLAlchemy 2.0 is in upper-constraints so you're going to have to fix it all in one go. You may wish to adopt the standard approach locally though, before squashing all the commits upon review.

This has already consumed far (far!) too much of my time so I won't offer to undertake the migration of any outstanding projects. I am, however, happy to field questions and point to existing patches that resolve a given issue. Hopefully none of it will be necessary though.

Cheers, Stephen

[1] https://lists.openstack.org/pipermail/openstack-discuss/2021-August/024122.h... [2] https://review.opendev.org/c/openstack/requirements/+/879743 [3] https://review.opendev.org/q/topic:sqlalchemy-20 [4] https://docs.sqlalchemy.org/en/20/changelog/migration_20.html

I want to extend my deepest thanks to Stephen who has been working on this for at least a year if not more; while I was glad to help here and there, he really did the vast majority of work on this, and truly saved me immense amounts of effort.

This SQLAlchemy 2.0 work caps off the major tasks I set out to do with Openstack nearly ten years ago, which were 1. modernize oslo.db enginefacade 2. move all projects off sqlalchemy-migrate onto alembic, both of which have been pushed over the finish line in large part thanks to Stephen.

Upstream we are working on SQLAlchemy 2.1 at a modest pace. It's not expected to have any major behavioral or API changes compared to 2.0, mostly performance and pep-484 types of improvements. I still continue to be employed full time for Openstack related tasks and I am curious to see how 2.0 performs within Openstack on a day to day basis.

Thanks all for undertaking this major project ! glad to see it so close to done.

- mike

On Fri, Apr 12, 2024, at 6:24 AM, Stephen Finucane wrote:

o/

A whole 2 years and 8 months to the day after I first communicated about it [1], it would appear the requirements bump to SQLAlchemy 2.0 [2] is finally ready to go. I suspect most if not all service projects are familiar with SQLAlchemy 2.0 at this point, having needed to fix whatever compatibility issues were seen. For those that aren't, I suggest reading my previous emails on the matter.

So what happens next? Hopefully for the vast majority of projects the answer should be "nothing": many projects now have sqlalchemy-tips jobs that are designed to run unit and/or integration tests with the latest versions of SQLAlchemy, Alembic and oslo.db so the switchover should be seamless. I foresee three situations:

• For projects that continue working as-is post-bump, you may wish to delete the sqlalchemy-tips jobs now. The jobs were designed as a stop gap measure to prevent projects regressing on SQLAlchemy 2.0 support while we got other projects over the line. While there is some limited value in keeping these jobs around, they do consume resources and - to the best of my knowledge - the SQLAlchemy community is not planning to release a 3.0 version of SQLAlchemy (or 2.0 version of Alembic) any time soon :) The requirements jobs gates on many projects and should identify compatibility issues with these libraries going forward, so it's probably best to drop them from the projects. I'll push a couple of patches shortly to do just this. Feel free to accept or reject them as you see fit.

• For projects that are supposed to be SQLAlchemy 2.0 Ready (TM) but start seeing issues that the sqlalchemy-tips jobs didn't catch (because, say, the job was running unit tests rather than functional tests and the unit tests are missing coverage), I'd encourage you to look through what is now a fairly extensive repository of patches [3] that we've built up while addressing SQLAlchemy 2.0 compatibility issues. These are mostly all tagged with the sqlalchemy-20 topic and there's a very good chance that a patch addressing your specific issue has already been submitted to another project.

• For projects that were not in the gating queue for requirements bumps, have not prepped for SQLAlchemy, and start failing catastrophically post-bump: have you not been listening to me?! :) You've got some work to do but fortunately that work should be relatively simple (if a bit tedious). As noted above, there are a *lot* of patches that you can look through to identify solutions to your compatibility issues. My preferred strategy - which is the one also advocated for by the SQLAlchemy 2.x docs [4] - has been to configure the warnings filter to raise an error for all SQLAlchemy deprecation warnings then filter out those that we actually hit and iteratively work to remove these filters. That won't work now that SQLAlchemy 2.0 is in upper-constraints so you're going to have to fix it all in one go. You may wish to adopt the standard approach locally though, before squashing all the commits upon review.

This has already consumed far (far!) too much of my time so I won't offer to undertake the migration of any outstanding projects. I am, however, happy to field questions and point to existing patches that resolve a given issue. Hopefully none of it will be necessary though.

Cheers, Stephen

[1] https://lists.openstack.org/pipermail/openstack-discuss/2021-August/024122.h... [2] https://review.opendev.org/c/openstack/requirements/+/879743 [3] https://review.opendev.org/q/topic:sqlalchemy-20 [4] https://docs.sqlalchemy.org/en/20/changelog/migration_20.html

Thank you, Stephen, for your very hard work on this topic ! This has been very challenging for years and we could not arrive at the current state without you.

I went through the patches to remove sqla 2.0 job which effectively pulls sqlalchemy 2.0 (and also submitted changes for some projects not yet covered) and found several problems. It'd be nice if the relevant project team can be looked into the problems now.

1. Ironic The incompatible usage is caught in CI[1]. I've submitted a fix[2] and am checking if this can be enough to unblock ironic[3] and ironic-inspector[4] CI.

[1] https://bugs.launchpad.net/ironic/+bug/2061345 [2] https://review.opendev.org/c/openstack/ironic/+/915789 [3] https://review.opendev.org/c/openstack/ironic/+/915792 [4] https://review.opendev.org/c/openstack/ironic-inspector/+/915773

2. Glance There are a few usages of Engine.engine, which was removed in SQLAlchemy 2.0. Fortunately these are not actually used so are not caught in tests, but it'd be better to drop these now to avoid future problems/confusions[5].

[5] https://review.opendev.org/c/openstack/glance/+/915791

3. Aodh Functional jobs with MySQL consistently time out. It seems something get stuck during DB migration[6]. I've not yet looked into this further but we likely need a local reproducer for further investigation.

[6] https://review.opendev.org/c/openstack/aodh/+/915570

4. Octavia db migration script is currently broken when sqlalchemy 2.0 is used. I attempted to fix the issue[7][8] detected but another problem was detected[6]. We still have to pay some attention to make sure the whole functionality works with SQLAlchemy 2.0.

[7] https://bugs.launchpad.net/octavia/+bug/2061303 [8] https://review.opendev.org/c/openstack/octavia/+/915772 [9] https://bugs.launchpad.net/octavia/+bug/2061348

Thank you, Takashi

On 4/12/24 19:24, Stephen Finucane wrote:

o/

A whole 2 years and 8 months to the day after I first communicated about it [1], it would appear the requirements bump to SQLAlchemy 2.0 [2] is finally ready to go. I suspect most if not all service projects are familiar with SQLAlchemy 2.0 at this point, having needed to fix whatever compatibility issues were seen. For those that aren't, I suggest reading my previous emails on the matter.

So what happens next? Hopefully for the vast majority of projects the answer should be "nothing": many projects now have sqlalchemy-tips jobs that are designed to run unit and/or integration tests with the latest versions of SQLAlchemy, Alembic and oslo.db so the switchover should be seamless. I foresee three situations:

• For projects that continue working as-is post-bump, you may wish to delete the sqlalchemy-tips jobs now. The jobs were designed as a stop gap measure to prevent projects regressing on SQLAlchemy 2.0 support while we got other projects over the line. While there is some limited value in keeping these jobs around, they do consume resources and - to the best of my knowledge - the SQLAlchemy community is not planning to release a 3.0 version of SQLAlchemy (or 2.0 version of Alembic) any time soon :) The requirements jobs gates on many projects and should identify compatibility issues with these libraries going forward, so it's probably best to drop them from the projects. I'll push a couple of patches shortly to do just this. Feel free to accept or reject them as you see fit.

• For projects that are supposed to be SQLAlchemy 2.0 Ready (TM) but start seeing issues that the sqlalchemy-tips jobs didn't catch (because, say, the job was running unit tests rather than functional tests and the unit tests are missing coverage), I'd encourage you to look through what is now a fairly extensive repository of patches [3] that we've built up while addressing SQLAlchemy 2.0 compatibility issues. These are mostly all tagged with the sqlalchemy-20 topic and there's a very good chance that a patch addressing your specific issue has already been submitted to another project.

• For projects that were not in the gating queue for requirements bumps, have not prepped for SQLAlchemy, and start failing catastrophically post-bump: have you not been listening to me?! :) You've got some work to do but fortunately that work should be relatively simple (if a bit tedious). As noted above, there are a *lot* of patches that you can look through to identify solutions to your compatibility issues. My preferred strategy - which is the one also advocated for by the SQLAlchemy 2.x docs [4] - has been to configure the warnings filter to raise an error for all SQLAlchemy deprecation warnings then filter out those that we actually hit and iteratively work to remove these filters. That won't work now that SQLAlchemy 2.0 is in upper-constraints so you're going to have to fix it all in one go. You may wish to adopt the standard approach locally though, before squashing all the commits upon review.

This has already consumed far (far!) too much of my time so I won't offer to undertake the migration of any outstanding projects. I am, however, happy to field questions and point to existing patches that resolve a given issue. Hopefully none of it will be necessary though.

Cheers, Stephen

[1] https://lists.openstack.org/pipermail/openstack-discuss/2021-August/024122.h... [2] https://review.opendev.org/c/openstack/requirements/+/879743 [3] https://review.opendev.org/q/topic:sqlalchemy-20 [4] https://docs.sqlalchemy.org/en/20/changelog/migration_20.html