MySQL Connection not available after sometime

[assinchu]

How do i avoid such connection issue. What soultion i have other than restarting my flask app ?

Error
MySQL Connection not available.

After few minutes, i get above error. Connection gets lost.

[zainhoda]

@assinchu could you show the code that you're using to connect to MySQL (please exclude the password and other private information)

[andreped]

@assinchu could you show the code that you're using to connect to MySQL (please exclude the password and other private information)

I think this happens because the SQL DB connection might timeout. What we did in a project is to have a wrapper around this connection, such that if one is unable to connect to the DB, for instance due to a timeout, it tries to setup the SQL DB connection again (up to X times). This was rather easy to setup when we used Flask (not Streamlit) to handle this backend logic with retries communicating with the SQL DB.

[assinchu]

Thanks @zainhoda and @andreped
I also think its DB timeout issue and I am using vanna-flask app. Let me add re tries for mysql DB connection

[assinchu]

If someone is having similar issue: They can use below snippet to re connect to MySQL when there is disconnect

vn = MyVanna(config={'api_key': '', 'model': 'gpt-3.5-turbo'})

conn_details = {
'user': '',
'password': '',
'host': '',
'database': ''
}
import mysql.connector

def connect_to_mysql(conn_details):
try:
# Establish a connection
conn = mysql.connector.connect(**conn_details)
if conn.is_connected():
print('Connected to MySQL database!')
# Print the connection details
print('Connection details:', conn_details)
return conn
except mysql.connector.Error as e:
print(f'Error connecting to MySQL database: {e}')
return None

Connect to MySQL

connection = connect_to_mysql(conn_details)

You define a function that takes in a SQL query as a string and returns a pandas dataframe

def run_sql_my(sql: str) -> pd.DataFrame:
print("run_sql_my def")
global connection # Declare connection as global

# Check if connection is closed or None
if connection is None or not connection.is_connected():
    connection = connect_to_mysql(conn_details)

# Proceed if connection is established
if connection:
    try:
        df = pd.read_sql_query(sql, connection)
        return df
    except Exception as e:
        print(f"Error executing SQL query: {e}")
        return pd.DataFrame()  # Return empty DataFrame in case of error
else:
    print("MySQL connection is not established.")
    return pd.DataFrame()  # Return empty DataFrame if connection is not established

This gives the package a function that it can use to run the SQL

vn.run_sql = run_sql_my
vn.run_sql_is_set = True