Database Connection Pool Settings

[fenss]

Describe the bug I use MySQL as the storage by setting environment variables:

export LANGFLOW_DATABASE_URL=mysql+pymysql://{mysql_user}:{mysql_pwd}@{mysql_host}:{mysql_port}/langflow

But I can not find any pool settings in the database service code, so dead session comes out sometime because the networks setting.

Can you offer some options to set the SQLAlchemy pool? Thanks.

[dosubot[bot]]

To mitigate issues with dead sessions in MySQL due to network settings, you can configure the SQLAlchemy connection pool with specific parameters. Here are some options you can use:

1. Pool Recycle: Ensures that connections are recycled after a specified number of seconds to avoid idle connections being closed by MySQL.
2. Pool Pre-Ping: Enables a "ping" operation to test connections before they are used, replacing invalid connections with new ones.
3. Pool Timeout: Specifies the number of seconds to wait before giving up on getting a connection from the pool.

Here is how you can configure these options in your create_engine call within the DatabaseService class:

from sqlalchemy import create_engine

def _create_engine(self) -> "Engine":
    """Create the engine for the database."""
    settings_service = get_settings_service()
    if settings_service.settings.DATABASE_URL and settings_service.settings.DATABASE_URL.startswith("sqlite"):
        connect_args = {"check_same_thread": False}
    else:
        connect_args = {}

    return create_engine(
        self.database_url,
        connect_args=connect_args,
        pool_recycle=3600,  # Recycle connections after 1 hour
        pool_pre_ping=True,  # Enable pre-ping to test connections
        pool_timeout=30,  # Wait up to 30 seconds for a connection
        max_overflow=-1
    )

In this example:

• pool_recycle=3600 ensures that connections are recycled every hour.
• pool_pre_ping=True enables the pre-ping feature to test connections before they are used.
• pool_timeout=30 sets the timeout for getting a connection from the pool to 30 seconds.

These settings should help mitigate issues with dead sessions in MySQL due to network settings.

You can find the relevant code in the service.py file of the langflow repository. Here is the link to the file for reference: src/backend/langflow/services/database/service.py.

#### Details

**Sources** - [src/backend/langflow/services/database/service.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/langflow/services/database/service.py) - [src/backend/base/langflow/services/database/service.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/services/database/service.py) - [src/backend/base/langflow/services/settings/manager.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/services/settings/manager.py) - [src/backend/base/langflow/services/settings/service.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/services/settings/service.py) - [src/backend/base/langflow/services/settings/service.py](https://github.com/langflow-ai/langflow/blob/dev/src/backend/base/langflow/services/settings/service.py) - [src/backend/base/langflow/components/experimental/SQLExecutor.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/components/experimental/SQLExecutor.py) - [src/backend/base/langflow/services/monitor/utils.py](https://github.com/langflow-ai/langflow/blob/dev/src/backend/base/langflow/services/monitor/utils.py) - [src/backend/base/langflow/services/monitor/utils.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/services/monitor/utils.py) - [src/backend/base/langflow/services/monitor/service.py](https://github.com/logspace-ai/langflow/blob/dev/src/backend/base/langflow/services/monitor/service.py)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.