Support new deployments on mysql 8

[fme50]

Try to deploy release/1.18 with Mysql Server version: 8.0.35-27.1 Percona XtraDB Cluster (GPL), Release rel27, we have found the following error:

2024-04-16 11:40:58.009 31 INFO migrate.versioning.api [-] 110 -> 111... �[00m
2024-04-16 11:40:58.015 31 CRITICAL keystone [-] Unhandled error: oslo_db.exception.DBMigrationError: 'NoneType' object has no attribute 'dialect'
2024-04-16 11:40:58.015 31 ERROR keystone Traceback (most recent call last):
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/oslo_db/sqlalchemy/migration.py", line 87, in db_sync
2024-04-16 11:40:58.015 31 ERROR keystone     migration = versioning_api.upgrade(engine, repository, version)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/versioning/api.py", line 186, in upgrade
2024-04-16 11:40:58.015 31 ERROR keystone     return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2024-04-16 11:40:58.015 31 ERROR keystone   File "</usr/lib/python3.6/site-packages/decorator.py:decorator-gen-15>", line 2, in _migrate
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/versioning/util/__init__.py", line 167, in with_engine
2024-04-16 11:40:58.015 31 ERROR keystone     return f(*a, **kw)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/versioning/api.py", line 366, in _migrate
2024-04-16 11:40:58.015 31 ERROR keystone     schema.runchange(ver, change, changeset.step)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/versioning/schema.py", line 93, in runchange
2024-04-16 11:40:58.015 31 ERROR keystone     change.run(self.engine, step)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/versioning/script/py.py", line 154, in run
2024-04-16 11:40:58.015 31 ERROR keystone     script_func(engine)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/keystone/common/sql/migrate_repo/versions/111_add_sndfa_spassword_table.py", line 31, in upgrade
2024-04-16 11:40:58.015 31 ERROR keystone     spassword_table.create_column(sndfa)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/changeset/schema.py", line 489, in create_column
2024-04-16 11:40:58.015 31 ERROR keystone     column.create(table=self, *p, **kw)
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/changeset/schema.py", line 590, in create
2024-04-16 11:40:58.015 31 ERROR keystone     visitorcallable = get_engine_visitor(engine, 'columngenerator')
2024-04-16 11:40:58.015 31 ERROR keystone   File "/usr/lib/python3.6/site-packages/migrate/changeset/databases/visitor.py", line 47, in get_engine_visitor
2024-04-16 11:40:58.015 31 ERROR keystone     return get_dialect_visitor(engine.dialect, name)
2024-04-16 11:40:58.015 31 ERROR keystone AttributeError: 'NoneType' object has no attribute 'dialect'

It is because of these line in the 111_add_sndfa_spassword_table.py file

     spassword_table = utils.get_table(migrate_engine, 'spassword')

We have made the following chages:

## from oslo_db.sqlalchemy import utils
import sqlalchemy as sql

##    spassword_table = utils.get_table(migrate_engine, 'spassword') -->
    spassword_table = sql.Table('spassword', meta, autoload=True)

##    spassword_table = utils.get_table(migrate_engine, 'spassword') -->
    spassword_table = sql.Table('spassword', meta, autoload=True)

The file looks like this, with the changes:

# Copyright 2012 OpenStack Foundation
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may
# not use this file except in compliance with the License. You may obtain
# a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
# License for the specific language governing permissions and limitations
# under the License.

import sqlalchemy as sql


def upgrade(migrate_engine):
    # Upgrade operations go here. Don't create your own engine; bind
    # migrate_engine to your metadata
    meta = sql.MetaData()
    meta.bind = migrate_engine
    spassword_table = sql.Table('spassword', meta, autoload=True)
    sndfa = sql.Column('sndfa', sql.Boolean(), default=False)
    sndfa_last = sql.Column('sndfa_last', sql.DateTime(), default=None)
    sndfa_code = sql.Column('sndfa_code', sql.String(32), default=None)
    sndfa_time_code = sql.Column('sndfa_time_code', sql.DateTime(), default=None)
    sndfa_email = sql.Column('sndfa_email', sql.Boolean(), default=False)
    sndfa_email_code = sql.Column('sndfa_email_code', sql.String(32), default=None)
    spassword_table.create_column(sndfa)
    spassword_table.create_column(sndfa_last)
    spassword_table.create_column(sndfa_code)
    spassword_table.create_column(sndfa_time_code)
    spassword_table.create_column(sndfa_email)
    spassword_table.create_column(sndfa_email_code)


def downgrade(migrate_engine):
    meta = sql.MetaData()
    meta.bind = migrate_engine
    spassword_table = sql.Table('spassword', meta, autoload=True)
    idp_table.drop_column('sndfa')
    idp_table.drop_column('sndfa_last')
    idp_table.drop_column('sndfa_code')
    idp_table.drop_column('sndfa_time_code')
    idp_table.drop_column('sndfa_email')
    idp_table.drop_column('sndfa_email_code')

[AlvaroVega]

The error you reported is about a wrong migration (maybe non following right procedure migration). Which versions are involve ?

[fme50]

The Keystone version is 1.18, there is not a migration process, is just a new deployment! with clean database cluster and platform form zero!

[AlvaroVega]

@fme50 which container and options about mysql 8.0 are you using? It is strange that is working in Mysql5.0 but not in mysql 8.0 but you affirm that is related with the sql schema.
Spassword 1.18.0 now uses keystone xena which uses sqlalchemy version 1.3 (previous versions of keystone were using sqlalchemy 1.1). So this is not related with spassword, maybe just with sqlalchemy.

Could you provide full log (from the start) of keystone spassword 1.18 with mysql 8.0 in a pristine environment? Thanks in advance!