ConnectionResetError: [Errno 104] Connection reset by peer

[juntoku9]

Bug Description

I am using cloud sql connector in my fastapi server, it connects to cloud sql, I followed the example from tutorial, yet every few hours it gets [ConnectionResetError: [Errno 104] Connection reset by peer] error.

Example code (or command)

# helper function to return SQLAlchemy connection pool
def init_connection_pool(connector: Connector) -> Engine:
    # Python Connector database connection function
    def getconn():
        conn = connector.connect(
            "my-project",
            "pg8000", # Add None here for driver
            user=db_user,  
            password=db_pass,
            db=db_name, 
            ip_type= IPTypes.PUBLIC  # IPTypes.PRIVATE for private IP
        )
        return conn

    SQLALCHEMY_DATABASE_URL = "postgresql+pg8000://"

    engine = create_engine(
        SQLALCHEMY_DATABASE_URL , creator=getconn
    )
    return engine

# initialize Cloud SQL Python Connector
connector = Connector()
engine = init_connection_pool(connector)
# create SQLAlchemy ORM session
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base: DeclarativeMeta = declarative_base()

# Dependency to get the database session
def get_db() -> Generator:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Stacktrace

ConnectionResetError: [Errno 104] Connection reset by peer

at .send ( /usr/local/lib/python3.9/ssl.py:1174 )
at .write ( /usr/local/lib/python3.9/socket.py:722 )
at ._flush ( /usr/local/lib/python3.9/site-packages/pg8000/core.py:144 )
sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) network error


at ._flush ( /usr/local/lib/python3.9/site-packages/pg8000/core.py:146 )
at .execute_simple ( /usr/local/lib/python3.9/site-packages/pg8000/core.py:677 )
at .execute ( /usr/local/lib/python3.9/site-packages/pg8000/dbapi.py:465 )
at .do_execute ( /usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py:732 )
at ._execute_context ( /usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1819 )
at .raise_ ( /usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py:208 )
at ._handle_dbapi_exception ( /usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:2043 )

Steps to reproduce?

cloud run fast api server in container cloud sql connector for connecting to the cloud sql after all setup, in 10+hrs the connectoon would break

Environment

1. OS type and version: FROM python:3.9-buster(docker)
2. Python version:3.9
3. Cloud SQL Python Connector version:cloud-sql-python-connector==1.6.0

Additional Details

No response

[enocom]

@juntoku9 I assume you're not running your Cloud Run services with CPU always allocated?

[enocom]

Potentially related to https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/issues/805.

[jackwotherspoon]

Similar issue on SO: https://stackoverflow.com/questions/78052352/intermittent-pg8000-exceptions-interfaceerror-in-cloud-run-to-cloud-sql-postgres

[enocom]

@juntoku9 friendly ping on this.

[jackwotherspoon]

This should now be fixed by #1093

Starting the Connector with refresh_strategy="lazy" in serverless environments is now recommended.

from google.cloud.sql.connector import Connector

with Connector(refresh_strategy="lazy") as connector:
    # ... use connector in lazy refresh mode