Oslo Database Library

Common errors

• oslo_db.exception.DBConnectionError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

  cause The database connection was idle for too long and was closed by the MySQL server, or a network issue occurred.
  fix Adjust the `[database]pool_recycle` option in your `oslo.db` configuration to a value (in seconds) less than the database's `wait_timeout` setting. For example, `pool_recycle = 3600` if `wait_timeout` is 7200 seconds. Also, ensure `all_tcp = true` in relevant service configurations if using MySQL/Designate.

• ImportError: cannot import name 'enginefacade' from 'oslo_db'

  cause Attempting to import `enginefacade` directly from the top-level `oslo_db` package, or an incorrect `oslo.db` installation.
  fix The correct import path for `enginefacade` is `from oslo_db.sqlalchemy import enginefacade`. Ensure `oslo.db` and its dependencies are correctly installed.

• oslo_db.exception.DBDeadlock: DB exceeded retry limit.

  cause Concurrent database operations led to a deadlock, and oslo.db's retry mechanism was exhausted. This is common in high-concurrency environments, especially during resource-intensive operations like instance creation/deletion in OpenStack services.
  fix Review application logic for potential deadlock scenarios. Consider optimizing database queries, implementing finer-grained locking, or adjusting transaction isolation levels. In OpenStack, this often indicates a need to scale database resources or optimize service configurations.

Warnings

• gotcha oslo.db tightly integrates with SQLAlchemy, and direct manipulation of SQLAlchemy session/engine objects outside of oslo.db's `enginefacade` can lead to unexpected behavior or resource leaks. Always use the provided context managers or decorators.
  Fix: Ensure all database interactions leverage `oslo_db.sqlalchemy.enginefacade.transaction()`, `transaction_context_manager`, or `reader_context_manager`.
• breaking The `[DEFAULT]/sql_connection` and `[DEFAULT]/db_backend` configuration options have been deprecated in favor of `[database]/connection` and `[database]/backend` respectively. Using the old options may result in unapplied configurations or future errors.
  Fix: Update `oslo.db` configuration files (e.g., `oslo.conf`) to use the `[database]` section for connection and backend settings. Refer to the specific release notes for your OpenStack version.
• gotcha When using `psycopg2` (PostgreSQL) or `PyMySQL` (MySQL) as backends, system-level development packages (e.g., `libpq-dev` for PostgreSQL, `libmysqlclient-dev` for MySQL) are often required before `pip install` can succeed for the Python packages.
  Fix: Install the necessary system-level development packages for your chosen database backend before attempting to `pip install` the Python driver (e.g., `sudo apt-get install libpq-dev` for PostgreSQL on Ubuntu).
• gotcha Handling database connection loss (`DBConnectionError`, 'MySQL server has gone away') requires careful configuration of connection pooling, including `pool_recycle` and `wait_timeout` settings, to ensure idle connections are properly reaped and re-established.
  Fix: Configure `[database]pool_recycle` in your `oslo.db` configuration to be less than your database server's `wait_timeout`. Also, ensure optimistic disconnect handling is enabled or properly managed by the backend driver if applicable. For MySQL, using TCP protocol (`all_tcp = true`) can also mitigate issues.

Install

• pip install oslo.db Base installation
• pip install PyMySQL For MySQL/MariaDB backend (example)
• sudo apt-get install libpq-dev && pip install psycopg2 For PostgreSQL backend on Debian/Ubuntu

Imports

• enginefacade

  from oslo_db.sqlalchemy import enginefacade

  Used for managing database sessions and connections.
• models

  from oslo_db.sqlalchemy import models

  Provides base classes for SQLAlchemy models, including TimestampMixin.
• db_api
  wrong:

  from oslo_db.api import DBAPI

  correct:

  from oslo_db import api as db_api

  The `db_api` module is typically imported with an alias and used for database operations.

Quickstart

This quickstart demonstrates how to configure oslo.db with a SQLite backend, define a basic SQLAlchemy model using `oslo_db.sqlalchemy.models.ModelBase` and `TimestampMixin`, and perform basic CRUD operations using `oslo_db.sqlalchemy.enginefacade.transaction_context_manager` and `reader_context_manager`.

import os
from sqlalchemy import Column, Integer, String
from oslo_db.sqlalchemy import enginefacade, models

# Configure a simple SQLite database for demonstration
os.environ['OSLO_DB_CONNECTION'] = 'sqlite:///./test.sqlite'

# Initialize the enginefacade
enginefacade.configure(
    sqlite_synchronous=False # For better performance in SQLite, though less safe
)

# Define a base model for our application
class MyModelBase(models.ModelBase, models.TimestampMixin):
    __abstract__ = True

class User(MyModelBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    email = Column(String(255), unique=True, nullable=False)

    def __repr__(self):
        return f"<User(id='{self.id}', name='{self.name}', email='{self.email}')>"

# Create tables (usually done via migration tools like Alembic in production)
# For quickstart, we'll create directly if not exists
with enginefacade.transaction() as session:
    MyModelBase.metadata.create_all(session.bind)

# Example usage: adding and querying data
class MyContext:
    pass

@enginefacade.transaction_context_manager
def add_user(context, name, email):
    new_user = User(name=name, email=email)
    context.session.add(new_user)
    print(f"Added user: {new_user}")
    return new_user

@enginefacade.reader_context_manager
def get_users(context):
    users = context.session.query(User).all()
    print("All users:")
    for user in users:
        print(f"- {user}")
    return users

if __name__ == '__main__':
    # Use a dummy context object, as oslo_db often expects one
    ctx = MyContext()

    add_user(ctx, "Alice", "alice@example.com")
    add_user(ctx, "Bob", "bob@example.com")

    get_users(ctx)

    # Clean up test.sqlite if it exists
    if os.path.exists('./test.sqlite'):
        os.remove('./test.sqlite')
        print("Cleaned up test.sqlite")