MySQL connection pooling in Python2.7 doesn't work as expected. Facing issue with close() function of connection pool

Question

I have taken the python sample code for MySQL Connection pool and tried to execute it with django framework. I'm able to execute the queries and retrieve the data if I don't close the cursor and connection object. When I call close() function connection object it's failing with following error, Please help me to solve this.

ProgrammingError at /settings 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Method: GET

Request URL: http://sample.app.com/settings

Django Version: 1.11.5

Exception Type: ProgrammingError

Exception Value: 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Exception Location: venv/local/lib/python2.7/site-packages/mysql/connector/connection.py in _auth_switch_request, line 256 Python Executable: venv/bin/python2

Python Version: 2.7.17

The code which I'm using, Coding in bold are creating the issue. If don't close connection, its working fine. when i close it doesn't work and throws an error.

db_pool.py

import mysql.connector.pooling

dbconfig = {
    "host":"127.0.0.1",
    "port":"3306",
    "user":"root",
    "password":"root",
    "database":"test",
}


class MySQLPool(object):
    """
    create a pool when connect mysql.
    """
    def __init__(self, host="127.0.0.1", port="3306", user="root",
                 password="root", database="test", pool_name="mypool",
                 pool_size=3):
        res = {}
        self._host = host
        self._port = port
        self._user = user
        self._password = password
        self._database = database

        res["host"] = self._host
        res["port"] = self._port
        res["user"] = self._user
        res["password"] = self._password
        res["database"] = self._database
        self.dbconfig = res
        self.pool = self.create_pool(pool_name=pool_name, pool_size=pool_size)

    def create_pool(self, pool_name="mypool", pool_size=3):
        """
        Create a connection pool
        """
        pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            pool_reset_session=True,
            **self.dbconfig)
        return pool

    > > def close(self, conn, cursor):
    > > """ Here I'm facing issue, close is causing the issue. this close must release the connection object and add it to the connection pool. """
    > >   cursor.close()
    > >   conn.close()

    def execute(self, sql, args=None, commit=False):
        """
        Execute a sql
        """
        # get connection form connection pool.
        conn = self.pool.get_connection()
        cursor = conn.cursor()
        if args:
            cursor.execute(sql, args)
        else:
            cursor.execute(sql)
        if commit is True:
            conn.commit()
            self.close(conn, cursor)
            return None
        else:
            res = cursor.fetchall()

            self.close(conn, cursor)
            return res

Using above code in another file db_operation.py

from db_pool import MySQLPool

def testing(request, bid, *args, **kwargs):

    mysql_pool = MySQLPool()
    query = "select * from test.table1;"
    result = mysql_pool.execute(query)
    print 'RESULT : ', result

Please help me to solve this or share if any best examples to use connection pool properly. Thanks in advance.

Answer 1

I have solved this issue. By default pool_reset_session is set to True and modified this to False. Now, it's working fine.

When pool_reset_session is true, it's resetting the session with default config's for host, user and no password. As per my current DB connection, it will not allow without password. So, pool_reset_session is caused an issue and solved it.

Thanks.