manila-mange fails to change column 'id'

[Guy Weatherburn]

OS Rocky 9.3
Openstack Bobcat

Installing manila. Running manila-mange db sync. Fails: CRITICAL manila [-] Unhandled error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1833, "Cannot change column 'id': used in a foreign key constraint 'volume_type_extra_specs_ibfk_1' of table 'manila.volume_type_extra_specs'")

Full log:

su -s /bin/bash manila -c "manila-manage db sync"
2024-01-26 14:36:40.868 17225 INFO alembic.runtime.migration [-] Context impl MySQLImpl.
2024-01-26 14:36:40.870 17225 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.
2024-01-26 14:36:41.006 17225 INFO alembic.runtime.migration [-] Running upgrade -> 162a3e673105, manila_init
2024-01-26 14:36:43.187 17225 CRITICAL manila [-] Unhandled error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1833, "Cannot change column 'id': used in a foreign key constraint 'volume_type_extra_specs_ibfk_1' of table 'manila.volume_type_extra_specs'")
[SQL: ALTER TABLE volume_types CONVERT TO CHARACTER SET utf8]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2024-01-26 14:36:43.187 17225 ERROR manila Traceback (most recent call last):
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
2024-01-26 14:36:43.187 17225 ERROR manila self.dialect.do_execute(
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
2024-01-26 14:36:43.187 17225 ERROR manila cursor.execute(statement, parameters)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 163, in execute
2024-01-26 14:36:43.187 17225 ERROR manila result = self._query(query)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 321, in _query
2024-01-26 14:36:43.187 17225 ERROR manila conn.query(q)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 505, in query
2024-01-26 14:36:43.187 17225 ERROR manila self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 724, in _read_query_result
2024-01-26 14:36:43.187 17225 ERROR manila result.read()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 1069, in read
2024-01-26 14:36:43.187 17225 ERROR manila first_packet = self.connection._read_packet()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet
2024-01-26 14:36:43.187 17225 ERROR manila packet.raise_for_error()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error
2024-01-26 14:36:43.187 17225 ERROR manila err.raise_mysql_exception(self._data)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
2024-01-26 14:36:43.187 17225 ERROR manila raise errorclass(errno, errval)
2024-01-26 14:36:43.187 17225 ERROR manila pymysql.err.OperationalError: (1833, "Cannot change column 'id': used in a foreign key constraint 'volume_type_extra_specs_ibfk_1' of table 'manila.volume_type_extra_specs'")
2024-01-26 14:36:43.187 17225 ERROR manila
2024-01-26 14:36:43.187 17225 ERROR manila The above exception was the direct cause of the following exception:
2024-01-26 14:36:43.187 17225 ERROR manila
2024-01-26 14:36:43.187 17225 ERROR manila Traceback (most recent call last):
2024-01-26 14:36:43.187 17225 ERROR manila File "/bin/manila-manage", line 8, in <module>
2024-01-26 14:36:43.187 17225 ERROR manila sys.exit(main())
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/cmd/manage.py", line 544, in main
2024-01-26 14:36:43.187 17225 ERROR manila fn(*fn_args)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/cmd/manage.py", line 205, in sync
2024-01-26 14:36:43.187 17225 ERROR manila return migration.upgrade(version)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/db/migration.py", line 28, in upgrade
2024-01-26 14:36:43.187 17225 ERROR manila return IMPL.upgrade(version)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/db/migrations/alembic/migration.py", line 51, in upgrade
2024-01-26 14:36:43.187 17225 ERROR manila return alembic.command.upgrade(_alembic_config(), revision or 'head')
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/command.py", line 322, in upgrade
2024-01-26 14:36:43.187 17225 ERROR manila script.run_env()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
2024-01-26 14:36:43.187 17225 ERROR manila util.load_python_file(self.dir, "env.py")
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
2024-01-26 14:36:43.187 17225 ERROR manila module = load_module_py(module_id, path)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
2024-01-26 14:36:43.187 17225 ERROR manila spec.loader.exec_module(module) # type: ignore
2024-01-26 14:36:43.187 17225 ERROR manila File "<frozen importlib._bootstrap_external>", line 850, in exec_module
2024-01-26 14:36:43.187 17225 ERROR manila File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/db/migrations/alembic/env.py", line 40, in <module>
2024-01-26 14:36:43.187 17225 ERROR manila run_migrations_online()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/db/migrations/alembic/env.py", line 35, in run_migrations_online
2024-01-26 14:36:43.187 17225 ERROR manila context.run_migrations()
2024-01-26 14:36:43.187 17225 ERROR manila File "<string>", line 8, in run_migrations
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
2024-01-26 14:36:43.187 17225 ERROR manila self.get_context().run_migrations(**kw)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
2024-01-26 14:36:43.187 17225 ERROR manila step.migration_fn(**kw)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/manila/db/migrations/alembic/versions/162a3e673105_manila_init.py", line 414, in upgrade
2024-01-26 14:36:43.187 17225 ERROR manila migrate_engine.execute(
2024-01-26 14:36:43.187 17225 ERROR manila File "<string>", line 2, in execute
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 402, in warned
2024-01-26 14:36:43.187 17225 ERROR manila return fn(*args, **kwargs)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3257, in execute
2024-01-26 14:36:43.187 17225 ERROR manila return connection.execute(statement, *multiparams, **params)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1365, in execute
2024-01-26 14:36:43.187 17225 ERROR manila return self._exec_driver_sql(
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1669, in _exec_driver_sql
2024-01-26 14:36:43.187 17225 ERROR manila ret = self._execute_context(
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
2024-01-26 14:36:43.187 17225 ERROR manila self._handle_dbapi_exception(
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2122, in _handle_dbapi_exception
2024-01-26 14:36:43.187 17225 ERROR manila util.raise_(newraise, with_traceback=exc_info[2], from_=e)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
2024-01-26 14:36:43.187 17225 ERROR manila raise exception
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
2024-01-26 14:36:43.187 17225 ERROR manila self.dialect.do_execute(
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
2024-01-26 14:36:43.187 17225 ERROR manila cursor.execute(statement, parameters)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 163, in execute
2024-01-26 14:36:43.187 17225 ERROR manila result = self._query(query)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 321, in _query
2024-01-26 14:36:43.187 17225 ERROR manila conn.query(q)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 505, in query
2024-01-26 14:36:43.187 17225 ERROR manila self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 724, in _read_query_result
2024-01-26 14:36:43.187 17225 ERROR manila result.read()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 1069, in read
2024-01-26 14:36:43.187 17225 ERROR manila first_packet = self.connection._read_packet()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 676, in _read_packet
2024-01-26 14:36:43.187 17225 ERROR manila packet.raise_for_error()
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/protocol.py", line 223, in raise_for_error
2024-01-26 14:36:43.187 17225 ERROR manila err.raise_mysql_exception(self._data)
2024-01-26 14:36:43.187 17225 ERROR manila File "/usr/lib/python3.9/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
2024-01-26 14:36:43.187 17225 ERROR manila raise errorclass(errno, errval)
2024-01-26 14:36:43.187 17225 ERROR manila sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1833, "Cannot change column 'id': used in a foreign key constraint 'volume_type_extra_specs_ibfk_1' of table 'manila.volume_type_extra_specs'")
2024-01-26 14:36:43.187 17225 ERROR manila [SQL: ALTER TABLE volume_types CONVERT TO CHARACTER SET utf8]
2024-01-26 14:36:43.187 17225 ERROR manila (Background on this error at: https://sqlalche.me/e/14/e3q8)
2024-01-26 14:36:43.187 17225 ERROR manila

I was following the instructions for installing manila in an Openstack Bobcat installation on ~CentOS 9 @ https://www.server-world.info/en/note?os=CentOS_Stream_9&p=openstack_bobcat&f=2

This issue is stalling the creation of a manila/shared file service. I have trawled here and the internet and found no solution. Any suggestions. I have also seen this on Openstack Yoga on Rocky 9 as well, I had tried an older version because of other issues, now resolved.

None of the 'openstack share commands are working at this stage of the installation. I suspect because of a krook Database.

The DB Syncs for other products i.e. nova, cinder etc all worked.

The network is Neutron with Geneve.

Guy

[Maurice Escher (maurice-escher)]

Probably related to this one: https://jira.mariadb.org/browse/MDEV-31987?jql=text%20~%20%22set_foreign_key_Check%22

[Vida Haririan (vhariria)]

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

[Goutham Pacha Ravi (gouthamr)]

Hi,

Yeah, this seems like a regression in MySQL server; but, we re-wrote that old migration recently: https://review.opendev.org/c/openstack/manila/+/908738

So the fix should be in the Caracal release. It's quite rare to need to do a db migration backport like this; but Bobcat is new enough that we might attempt it:

 https://review.opendev.org/c/openstack/manila/+/909555

[Guy Weatherburn (wot-a-guy)]

Thanks for the update on this. Currently I am providing my share
services on a linux VM but will look at upgrading to Openstack Caracal
as soon as I find a decent process list for doing so.

Guy

On 16/04/2024 18:07, Goutham Pacha Ravi wrote:
> ** Changed in: manila
> Milestone: caracal-3 => dalmation-1
>