Streamlit app runs into an error after some time when deployed as Databricks app

[alxy]

I have deployed the Streamlit sample app as Databricks app to my workspace. The app was working fine for a couple of days. However, after some time I got the following error when interacting with delta tables (I slightly plaxed with the layout of that app, but the code is taken directly from the sample):

I wasn't able to find the root cause of the error, but I had to re-deploy my app (again, using exactly the same code) and it started to work again as expected. My assumption is (I dont know how I can proof it) that is may be related to connection resource caching - as other people have also faced issues with long-open sql connections: databricks/databricks-sql-python#23 (comment)

As an additional info: I'm using a serverless SQL warehouse with auto-shutdown of 10 minutes. Not sure if this plays will with a longer idle time on the app and the connection caching.

Code in question is here:

databricks-apps-cookbook/streamlit/views/tables_read.py

Lines 23 to 29 in b09cfb3

	@st.cache_resource
	def get_connection(http_path):
	return sql.connect(
	server_hostname=cfg.host,
	http_path=http_path,
	credentials_provider=lambda: cfg.authenticate,
	)

[Vincent-Caspers]

I am experiencing similar issues. For my code, I also used the sample code from the cookbook of Databricks https://apps-cookbook.dev/docs/streamlit/tables/tables_read, but after some idle time of the app, I get hit with a similar error message.

Running an SQL warehouse without auto-shutdown does not change the situation in any way.
After redeploying it, everything works fine again.

`@st.cache_resource  # Cache the database connection
def get_connection():
    return sql.connect(
        server_hostname=cfg.host,
        http_path=HTTP_PATH,
        credentials_provider=lambda: cfg.authenticate,
    )

def read_table(table_name, conn):
    with conn.cursor() as cursor:
        query = f"SELECT * FROM {table_name}"
        cursor.execute(query)
        return cursor.fetchall_arrow().to_pandas()`

[pbv0]

Thanks for reporting this.

I agree this may be related to the connection resource caching. I can't reproduce this in my testing, but you may want to add a TTL to the caching (see Streamlit docs https://docs.streamlit.io/develop/api-reference/caching-and-state/st.cache_resource) or catch the error and establish a new connection (or invalidate the cache).

Let me know if those would be viable options.

[alxy]

@pbv0 It is not related to the auto-shutdown (after warehouse stops and starts again it doesnt immediately happen) and really only happens after a couple of days (or minimum one night) for me. I recommend to deploy the sample app and really not touch it for some days to see if it replicates.

[pbv0]

To properly investigate this, I need more detailed logs than the error above. I have had several instances of the app running for longer periods of time and have never seen this error. Could you instrument your instance of the cookbook with additional logging to find out what is the actual "Error during request to server"?

[Vincent-Caspers]

Hi @pbv0,

Thank you for your response.

This is the part I used the cookbook for:

`cfg = Config()

HTTP_PATH = "/sql/1.0/warehouses/xxxxxxxxxxxxxxx"

TABLE_NODES = "xxx.yyy.zzz"
TABLE_FLOWS = "aaa.bbb.ccc"

@st.cache_resource # Cache the database connection
def get_connection():
return sql.connect(
server_hostname=cfg.host,
http_path=HTTP_PATH,
credentials_provider=lambda: cfg.authenticate,
)

def read_table(table_name, conn):
with conn.cursor() as cursor:
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
return cursor.fetchall_arrow().to_pandas()

conn = get_connection()

nodes_pd = read_table(TABLE_NODES, conn)
flows_pd = read_table(TABLE_FLOWS, conn)`

For your request, I added additional logging, and changed my code to the following:

`cfg = Config()
HTTP_PATH = "/sql/1.0/warehouses/xxxxxxxxxxxxxxx"

TABLE_NODES = "xxx.yyy.zzz"
TABLE_FLOWS = "aaa.bbb.ccc"

@st.cache_resource
def get_connection():
try:
logger.info("Creating DB connection...")
conn = sql.connect(
server_hostname=cfg.host,
http_path=HTTP_PATH,
credentials_provider=lambda: cfg.authenticate,
)
logger.info("DB connection established successfully.")
return conn
except Exception as e:
logger.error(f"Failed to connect to Databricks SQL: {e}", exc_info=True)
st.error(f"Connection Error: {e}")
raise

def read_table(table_name, conn):
try:
logger.info(f"Reading table: {table_name}")
with conn.cursor() as cursor:
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
df = cursor.fetchall_arrow().to_pandas()
logger.info(f"Successfully read {len(df)} rows from {table_name}")
return df
except Exception as e:
logger.error(f"Error reading table {table_name}: {e}", exc_info=True)
st.error(f"Error reading from table {table_name}: {e}")
raise

conn = get_connection()

nodes_pd = read_table(TABLE_NODES, conn)
flows_pd = read_table(TABLE_FLOWS, conn)

logger.info(f"Environment: {os.environ}")`

After having it run idle for some time again, im presented with the following:

Error reading from table xxx.yyy.zzz: Error during request to server

RequestError: Error during request to server
Traceback:

File "/app/python/source_code/.venv/lib/python3.11/site-packages/streamlit/runtime/scriptrunner/exec_code.py", line 88, in exec_func_with_error_handling
result = func()
^^^^^^
File "/app/python/source_code/.venv/lib/python3.11/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 590, in code_to_exec
exec(code, module.dict)
File "/app/python/source_code/app.py", line 62, in
nodes_pd = read_table(TABLE_NODES, conn)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/python/source_code/app.py", line 51, in read_table
cursor.execute(query)
File "/app/python/source_code/.venv/lib/python3.11/site-packages/databricks/sql/client.py", line 784, in execute
execute_response = self.thrift_backend.execute_command(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/python/source_code/.venv/lib/python3.11/site-packages/databricks/sql/thrift_backend.py", line 840, in execute_command
resp = self.make_request(self._client.ExecuteStatement, req)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/python/source_code/.venv/lib/python3.11/site-packages/databricks/sql/thrift_backend.py", line 478, in make_request
self._handle_request_error(error_info, attempt, elapsed)
File "/app/python/source_code/.venv/lib/python3.11/site-packages/databricks/sql/thrift_backend.py", line 308, in _handle_request_error
raise network_request_error

are there any other ways to help you?

[kujaska]

I have the same situation - started with demo data app with modified code below

@st.cache_resource  # Cache the database connection
def get_connection():
    return sql.connect(
        server_hostname=cfg.host,
        http_path=HTTP_PATH,
        credentials_provider=lambda: cfg.authenticate,
    )

@st.cache_data(ttl=300)  # 5 min cache
def RunSQL(query, _conn):
    try:
        with _conn.cursor() as cursor:
            cursor.execute(query)
            return cursor.fetchall_arrow().to_pandas()
    except Exception as e:
        st.error(f"Database query failed: {str(e)}")
        st.error(f"Query text: {query}")
        return pd.DataFrame()  

#some RunSQL calls below

and got "Database query failed: Error during request to server" error after 8-11+ hours of inactivity

[clarknatalia]

Has anyone managed to solve this issue? Happens the same for me after some time, but redeploying isn't working. The app worked a month ago, what has worked in the past is disconnecting and reconnecting the sql warehouse permission. There must be a simpler way?