SQL connection remains active on the server

[JRMNfrtcm]

Checked other resources

• [X] I added a very descriptive title to this issue.
• [X] I searched the LangChain documentation with the integrated search.
• [X] I used the GitHub search to find a similar question and didn't find it.
• [X] I am sure that this is a bug in LangChain rather than my code.

Example Code

from langchain.sql_database import SQLDatabase
from langchain_experimental.sql import SQLDatabaseChain
from langchain.chat_models import AzureChatOpenAI

DB = SQLDatabase.from_uri(SQLCONNECTION, schema=SQLSCHEMA, include_tables=[SQL_TBL1, SQL_TBL2])

LLM = AzureChatOpenAI(model=OPENAI_MODEL_NAME, temperature=0, openai_api_key=OPENAI_API_KEY, 
                      openai_api_version=OPENAI_DEPLOYMENT_VERSION, azure_endpoint=OPENAI_DEPLOYMENT_ENDPOINT, 
                      deployment_name=OPENAI_DEPLOYMENT_NAME)

db_chain = SQLDatabaseChain(llm=LLM, database=DB, verbose=True)

db_chain.run(USERQUESTION)

Error Message and Stack Trace (if applicable)

N/A

Description

Each time a question is asked, the SQL query is created and remains pending on the server even after the answer is returned to the user. The SQL query is only discarded after killing the console where the script runs.

System Info

langchain==0.1.7 langchain-experimental==0.0.51

Windows 11 Pro

Python 3.11.4

[dosubot[bot]]

🤖

Based on the code snippet you've shared, it seems like you're correctly using the LangChain and LangChain-Experimental libraries. The issue you're experiencing with SQL queries remaining active and pending on the server even after the answer is returned to the user might be due to the connections not being properly closed after the execution of the queries.

In the LangChain library, the connection to the database is established using the with self._engine.begin() as connection: statement in the _execute method of the SQLDatabase class. This is a context manager that should automatically close the connection once the block of code is exited, even if an error occurs within the block. This ensures that the connection is always properly closed after the query is executed.

However, if an exception occurs within the with block, the connection may not be properly closed. To ensure that the connections are always closed, even when an exception occurs, you could use a try/finally block. Here is an example of how you can modify the _execute method to ensure that the connection is always closed:

def _execute(
    self,
    command: Union[str, Executable],
    fetch: Literal["all", "one", "cursor"] = "all",
    *,
    parameters: Optional[Dict[str, Any]] = None,
    execution_options: Optional[Dict[str, Any]] = None,
) -> Union[Sequence[Dict[str, Any]], Result]:
    """
    Executes SQL command through underlying engine.
    If the statement returns no rows, an empty list is returned.
    """
    parameters = parameters or {}
    execution_options = execution_options or {}
    connection = None
    try:
        connection = self._engine.begin()  # type: Connection  # type: ignore[name-defined]
        # ... rest of the code ...
    finally:
        if connection is not None:
            connection.close()

This modification ensures that the connection is always closed, even if an exception occurs within the try block. This should prevent the SQL queries from remaining active and pending on the server after the answer is returned to the user.

Please note that modifying the _execute method in this way might not be the best solution, as it could potentially interfere with other parts of the LangChain library that rely on the existing behavior of the _execute method. It would be best to first try to identify the source of the exceptions that are causing the connections to not be properly closed, and address those issues directly.

If you continue to experience this issue, it would be helpful to have more information about the exceptions that are occurring. This could help in identifying the source of the problem and finding a more targeted solution.

Sources

• libs/community/langchain_community/utilities/sql_database.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. If you want to continue the conversation, start your reply with @dosu-bot.

[keenborder786]

It's very difficult to help without you specifying the SQL engine that you are using?

[JRMNfrtcm]

Hi @keenborder786 !

SQLCONNECTION= "mssql+pyodbc://USER:PASSWORD@SERVER:1433/DATABASENAME?&driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes&TrustServerCertificate=yes&Authentication=SqlPassword"

SQLAlchemy==2.0.27

Thanks!

[JRMNfrtcm]

Any update about this?

[JRMNfrtcm]

Anyone with one update about this topic or any workaround? Thanks

[annavianelli]

any update?