python mysql.connector Mysql Connection not available error

Question

I am using mysql.connector for interacting with mysql db. I create a db connection at the beginning of myscript and reuse the same connection for all database activity like select/insert/update etc. Randomly I get following exception,

MySQL Connection not available.

Traceback (most recent call last):
  File "database.py", line 46, in query
    cursor = self.connection.cursor(buffered=True, dictionary=True)
  File "lib/python3.4/site-packages/mysql/connector/connection.py", line 807, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.
2017-12-08 13:16:03,845 ERROR util.py 1247 MySQL Connection not available.

what could be the cause for above error?

    import mysql.connector


class Database:
    """
        Simple Database class
    """

    def __init__(self, db_conf, logger):
        self.logger = logger
        try:
            self.connection = mysql.connector.connect(host=db_conf['host'], user=db_conf['user'], password=db_conf['password'],
                                                      database=db_conf['db'], port=db_conf['port'])
        except Exception as e:
            self.logger.exception(e)
            raise

    def insert_or_update(self, query, type):
        """
            Transactional query
        """
        try:
            cursor = self.connection.cursor(buffered=True)
            cursor.execute(query)
            self.connection.commit()

            if type == 'insert':
                last_row_id = cursor.lastrowid
                cursor.close()
                return last_row_id
            elif type == 'update':
                row_count = cursor.rowcount
                cursor.close()
                return row_count
        except Exception as e:
            self.connection.rollback()
            self.logger.info(query)
            self.logger.exception(e)
            raise

    def query(self, query):
        """
            Non transactional query
        """
        try:
            cursor = self.connection.cursor(buffered=True, dictionary=True)
            cursor.execute(query)
            self.connection.commit()
            result = cursor.fetchall()
            cursor.close()
            return result
        except Exception as e:
            self.logger.info(query)
            self.logger.exception(e)
            raise

show your code in the post. – Umar Asghar Dec 13 '17 at 13:10 — Umar Asghar, Dec 13 '17 at 13:10
code updated with the question – Jenish Dec 13 '17 at 13:40 — Jenish, Dec 13 '17 at 13:40

score 1 · Answer 1 · answered Dec 13 '17 at 13:12

Some more context might be useful in determining the cause of this error, but usually this error is either:

Due to a network error or
Due to a session timeout

If it is a network error. Then wrap your code into an except and try again. If this is due to a session timeout you should think about managing your sessions better and closing and reopening sessions for tasks.

score 0 · Answer 2 · answered Apr 28 '23 at 07:23

raise errors.OperationalError("MySQL Connection not available.") as of this statement in Error it means that connection, while you are trying to get data, is not available, which possibly due to long wait_timeout to catch that error you can try

from django.db.utils import OperationalError
...
except  OperationalError as err:
                    print("[Connection Error]:\n"+str(err))

as of solution I'm also looking for one yet. that answer will give you brief explaination

python mysql.connector Mysql Connection not available error

2 Answers2