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
– Umar Asghar

2017-12-13 13:10:48 +00:00
Commented Dec 13, 2017 at 13:10 — Umar Asghar
– Umar Asghar, Commented Dec 13, 2017 at 13:10
code updated with the question

Jenish
– Jenish

2017-12-13 13:40:02 +00:00
Commented Dec 13, 2017 at 13:40 — Jenish
– Jenish, Commented Dec 13, 2017 at 13:40

cchristelis · Accepted Answer · 2017-12-13 13:12:52Z

1

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.

answered Dec 13, 2017 at 13:12

cchristelis

2,0351 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Abdullah Iqbal · Accepted Answer · 2023-04-28 07:23:35Z

0

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

answered Apr 28, 2023 at 7:23

M.Abdullah Iqbal

2192 silver badges20 bronze badges

Collectives™ on Stack Overflow

python mysql.connector Mysql Connection not available error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related