Hello everyone, I have a problem concerning the connection of sqlalchemy with mysqlserver which occurs every morning around 9:00 AM and then it does not reproduce. Knowing that my database exists and all is good, here is the code used in `catalog.yml`:

_mysql : &mysql
  type: pandas.SQLQueryDataSet
  credentials: 
      con: mysql+mysqlconnector://${mysql_connect.username}:${mysql_connect.password}@${mysql_connect.host}:${mysql_connect.port}/${mysql_connect.database}

table_insurers: 
  <<: *mysql
  sql: select * from underwriter_insurers

table_ccns: 
  <<: *mysql
  sql: select * from underwriter_ccns

table_departments: 
  <<: *mysql
  sql: select * from underwriter_departments

and this is the error produced :

2023-07-26 09:33:48 - src.api.tarificateur_compte - ERROR - An error occurred in tarificateur_compte():
Traceback (most recent call last):
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    context = constructor(
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1346, in _init_statement
    self.cursor = self.create_cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1530, in create_cursor
    return self.create_default_cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1533, in create_default_cursor
    return self._dbapi_connection.cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1494, in cursor
    return self.dbapi_connection.cursor(*args, **kwargs)
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 678, in cursor
    raise OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/kedro/io/core.py", line 210, in load
    return self._load()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/kedro_datasets/pandas/sql_dataset.py", line 512, in _load
    return pd.read_sql_query(con=engine, **load_args)
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 467, in read_sql_query
    return pandas_sql.read_query(
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 1736, in read_query
    result = self.execute(sql, params)
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/pandas/io/sql.py", line 1560, in execute
    return self.con.exec_driver_sql(sql, *args)
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1772, in exec_driver_sql
    ret = self._execute_context(
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    context = constructor(
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1346, in _init_statement
    self.cursor = self.create_cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1530, in create_cursor
    return self.create_default_cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 1533, in create_default_cursor
    return self._dbapi_connection.cursor()
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1494, in cursor
    return self.dbapi_connection.cursor(*args, **kwargs)
  File "/home/debian/anaconda3/envs/env_tarificateur/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 678, in cursor
    raise OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.

Can anyone help me fix this problem because I have tried everything I can but I have not managed to solve it, thank you in advance.

hey @Rachid Cherqaoui, let me see if I understand - your code works well usually, but at 09:00 AM it fails and the failure cannot be reproduced at any other time?

hello @Juan Luis, yes, every morning it generates an error of this kind and then it works well for the rest of the day.

my first thought is that I don't think Kedro is the root cause here - could be wrong of course (computers are evil) but I'll suggest trying to set a cron job that attempts a connection with the same database at the same time but without using Kedro at all, maybe do a

SELECT 1

or something like that. if the dummy connection works but the Kedro job fails, we can keep debugging.

bah in fact in another project when I use a python script and I make the connection using sqlalchemy, I got the same error when I use a single connection for all the recovered tables but when I make a connection ( create... connexion and close ) for each table I don't get this error, so I don't know how I could do the same in the yml files.