Unit tests fails with oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: ml2_geneve_allocations when run with low concurrency or on loaded systems

The issue is noticed in RDO openstack-neutron package build[1], the package builds fails as unit tests fails randomly with below Traceback:-
DEBUG: neutron.tests.unit.services.trunk.test_utils.UtilsTestCase.test_is_driver_compatible_multiple_drivers
DEBUG: -----------------------------------------------------------------------------------------------------
DEBUG: Captured traceback:
DEBUG: ~~~~~~~~~~~~~~~~~~~
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: sqlite3.OperationalError: no such table: ml2_geneve_allocations
DEBUG:
DEBUG: The above exception was the direct cause of the following exception:
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/fixture.py", line 196, in setUp
DEBUG: self._setUp()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/tests/unit/plugins/ml2/test_plugin.py", line 110, in _setUp
DEBUG: self.parent_setup()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/tests/unit/db/test_db_base_plugin_v2.py", line 166, in setUp
DEBUG: self.api = router.APIRouter()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/api/v2/router.py", line 21, in APIRouter
DEBUG: return pecan_app.v2_factory(None, **local_config)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/pecan_wsgi/app.py", line 47, in v2_factory
DEBUG: startup.initialize_all()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/pecan_wsgi/startup.py", line 39, in initialize_all
DEBUG: manager.init()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 301, in init
DEBUG: NeutronManager.get_instance()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 252, in get_instance
DEBUG: cls._create_instance()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_concurrency/lockutils.py", line 414, in inner
DEBUG: return f(*args, **kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 238, in _create_instance
DEBUG: cls._instance = cls()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 126, in __init__
DEBUG: plugin = self._get_plugin_instance(CORE_PLUGINS_NAMESPACE,
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 162, in _get_plugin_instance
DEBUG: plugin_inst = plugin_class()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/quota/resource_registry.py", line 124, in wrapper
DEBUG: return f(*args, **kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/plugin.py", line 282, in __init__
DEBUG: self.type_manager.initialize()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/managers.py", line 205, in initialize
DEBUG: driver.obj.initialize()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_geneve.py", line 47, in initialize
DEBUG: self._initialize(cfg.CONF.ml2_type_geneve.vni_ranges)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_tunnel.py", line 131, in _initialize
DEBUG: self.sync_allocations()
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 139, in wrapped
DEBUG: setattr(e, '_RETRY_EXCEEDED', True)
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 135, in wrapped
DEBUG: return f(*args, **kwargs)
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_db/api.py", line 154, in wrapper
DEBUG: ectxt.value = e.inner_exc
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_db/api.py", line 142, in wrapper
DEBUG: return f(*args, **kwargs)
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 187, in wrapped
DEBUG: context_reference.session.rollback()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 181, in wrapped
DEBUG: return f(*dup_args, **dup_kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_tunnel.py", line 205, in sync_allocations
DEBUG: allocs = ctx.session.query(self.model).all()
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2773, in all
DEBUG: return self._iter().all()
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
DEBUG: result = self.session.execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute
DEBUG: result = conn._execute_20(statement, params or {}, execution_options)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
DEBUG: return meth(self, args_10style, kwargs_10style, execution_options)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
DEBUG: return connection._execute_clauseelement(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
DEBUG: ret = self._execute_context(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
DEBUG: self._handle_dbapi_exception(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2122, in _handle_dbapi_exception
DEBUG: util.raise_(newraise, with_traceback=exc_info[2], from_=e)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
DEBUG: raise exception
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: ml2_geneve_allocations
DEBUG: [SQL: SELECT ml2_geneve_allocations.geneve_vni AS ml2_geneve_allocations_geneve_vni, ml2_geneve_allocations.allocated AS ml2_geneve_allocations_allocated
DEBUG: FROM ml2_geneve_allocations]
DEBUG: (Background on this error at: https://sqlalche.me/e/14/e3q8)
DEBUG: Captured traceback-1:
DEBUG: ~~~~~~~~~~~~~~~~~~~~~
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: sqlite3.OperationalError: no such table: ml2_geneve_allocations
DEBUG:
DEBUG: The above exception was the direct cause of the following exception:
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/fixture.py", line 196, in setUp
DEBUG: self._setUp()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/tests/unit/plugins/ml2/test_plugin.py", line 110, in _setUp
DEBUG: self.parent_setup()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/tests/unit/db/test_db_base_plugin_v2.py", line 166, in setUp
DEBUG: self.api = router.APIRouter()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/api/v2/router.py", line 21, in APIRouter
DEBUG: return pecan_app.v2_factory(None, **local_config)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/pecan_wsgi/app.py", line 47, in v2_factory
DEBUG: startup.initialize_all()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/pecan_wsgi/startup.py", line 39, in initialize_all
DEBUG: manager.init()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 301, in init
DEBUG: NeutronManager.get_instance()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 252, in get_instance
DEBUG: cls._create_instance()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_concurrency/lockutils.py", line 414, in inner
DEBUG: return f(*args, **kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 238, in _create_instance
DEBUG: cls._instance = cls()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 126, in __init__
DEBUG: plugin = self._get_plugin_instance(CORE_PLUGINS_NAMESPACE,
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/manager.py", line 162, in _get_plugin_instance
DEBUG: plugin_inst = plugin_class()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/quota/resource_registry.py", line 124, in wrapper
DEBUG: return f(*args, **kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/plugin.py", line 282, in __init__
DEBUG: self.type_manager.initialize()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/managers.py", line 205, in initialize
DEBUG: driver.obj.initialize()
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_geneve.py", line 47, in initialize
DEBUG: self._initialize(cfg.CONF.ml2_type_geneve.vni_ranges)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_tunnel.py", line 131, in _initialize
DEBUG: self.sync_allocations()
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 139, in wrapped
DEBUG: setattr(e, '_RETRY_EXCEEDED', True)
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 135, in wrapped
DEBUG: return f(*args, **kwargs)
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_db/api.py", line 154, in wrapper
DEBUG: ectxt.value = e.inner_exc
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_db/api.py", line 142, in wrapper
DEBUG: return f(*args, **kwargs)
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 187, in wrapped
DEBUG: context_reference.session.rollback()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 227, in __exit__
DEBUG: self.force_reraise()
DEBUG: File "/usr/lib/python3.9/site-packages/oslo_utils/excutils.py", line 200, in force_reraise
DEBUG: raise self.value
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/db/api.py", line 181, in wrapped
DEBUG: return f(*dup_args, **dup_kwargs)
DEBUG: File "/builddir/build/BUILD/neutron-23.0.0.0b3.dev123/neutron/plugins/ml2/drivers/type_tunnel.py", line 205, in sync_allocations
DEBUG: allocs = ctx.session.query(self.model).all()
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2773, in all
DEBUG: return self._iter().all()
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
DEBUG: result = self.session.execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute
DEBUG: result = conn._execute_20(statement, params or {}, execution_options)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
DEBUG: return meth(self, args_10style, kwargs_10style, execution_options)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
DEBUG: return connection._execute_clauseelement(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
DEBUG: ret = self._execute_context(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
DEBUG: self._handle_dbapi_exception(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2122, in _handle_dbapi_exception
DEBUG: util.raise_(newraise, with_traceback=exc_info[2], from_=e)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
DEBUG: raise exception
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: ml2_geneve_allocations
DEBUG: [SQL: SELECT ml2_geneve_allocations.geneve_vni AS ml2_geneve_allocations_geneve_vni, ml2_geneve_allocations.allocated AS ml2_geneve_allocations_allocated
DEBUG: FROM ml2_geneve_allocations]
DEBUG: (Background on this error at: https://sqlalche.me/e/14/e3q8)
DEBUG:
DEBUG: During handling of the above exception, another exception occurred:
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/fixture.py", line 207, in setUp
DEBUG: raise SetupError(details)
DEBUG: fixtures.fixture.SetupError: {}
DEBUG: Captured traceback-2:
DEBUG: ~~~~~~~~~~~~~~~~~~~~~
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: sqlite3.OperationalError: no such table: qos_fip_policy_bindings
DEBUG:
DEBUG: The above exception was the direct cause of the following exception:
DEBUG: Traceback (most recent call last):
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/fixture.py", line 124, in cleanUp
DEBUG: return self._cleanups(raise_errors=raise_first)
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/callmany.py", line 84, in __call__
DEBUG: raise error[1].with_traceback(error[2])
DEBUG: File "/usr/lib/python3.9/site-packages/fixtures/callmany.py", line 78, in __call__
DEBUG: cleanup(*args, **kwargs)
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/fixture.py", line 134, in <lambda>
DEBUG: self.addCleanup(lambda: self._delete_from_schema(engine))
DEBUG: File "/usr/lib/python3.9/site-packages/neutron_lib/fixture.py", line 119, in _delete_from_schema
DEBUG: conn.execute(table.delete())
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
DEBUG: return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
DEBUG: return connection._execute_clauseelement(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
DEBUG: ret = self._execute_context(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
DEBUG: self._handle_dbapi_exception(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2122, in _handle_dbapi_exception
DEBUG: util.raise_(newraise, with_traceback=exc_info[2], from_=e)
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
DEBUG: raise exception
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
DEBUG: self.dialect.do_execute(
DEBUG: File "/usr/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
DEBUG: cursor.execute(statement, parameters)
DEBUG: oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: qos_fip_policy_bindings
DEBUG: [SQL: DELETE FROM qos_fip_policy_bindings]
DEBUG: (Background on this error at: https://sqlalche.me/e/14/e3q8)

The tests uses sqlite memory db, and during the test runs the db is wiped off due to reconnection(connection_recycle_time=3600)[2]. The issue can be seen when tests take more than 1 hour to finish.

Running full tests suit on slow/loaded systems or running with less concurrency it's easy to hit this 1 hour timeout. The tables are created once and shared across tests[3]. Due to the timeout tables are wiped off so test fails not finding the table, and then later table.delete()(this TRUNCATES the table, i.e delete rows) also fails due to missing tables.

For slow systems workaround can be to run set of tests in one go instead of all tests.
We can also bump the connection_recycle_time for running unit tests to some larger value or provide an option to override it by an env var.
Also we can check and improve total runtime of tests where possible.

[1] https://trunk.rdoproject.org/api-centos9-master/api/report.html?package=openstack-neutron
[2] https://github.com/openstack/oslo.db/blob/master/oslo_db/options.py#L85-L94
[3] https://github.com/openstack/neutron-lib/blob/master/neutron_lib/fixture.py#L161-L169