CODEWITHSUNDEEP
pythonmysql
Vishal
Vishal

Reputation: 2336

MySQL Connector/Python not closing connection explicitly

I have the following:

class FooData(object):
    def __init__(self):
        ...
        try:
            self.my_cnf = os.environ['HOME'] + '/.my.cnf'
            self.my_cxn = mysql.connector.connect(option_files=self.my_cnf)
            self.cursor = self.my_cxn.cursor(dictionary=True)
        except mysql.connector.Error as err:
            if err.errno == 2003:
                self.my_cnf = None
                self.my_cxn = None
                self.cursor = None

I am able to use my_cxn and cursor without any obvious failure. I never explicitly terminate the connection, and have observed the following messages in my mysql error log though...

2017-01-08T15:16:09.355190Z 132 [Note] Aborted connection 132 to db:
    'mydatabase' user: 'myusername' host: 'localhost'
    (Got an error reading communication packets)

Am I going about this the wrong way? Would it be more efficient for me to initialize my connector and cursor every time I need to run a query?

What do I need to look for on the mysql config to avoid these aborted connection?

Separately, I also observe these messages in my error logs frequently:

2017-01-06T15:28:45.203067Z 0 [Warning] Changed limits: max_open_files: 1024
    (requested 5000)
2017-01-06T15:28:45.205191Z 0 [Warning] Changed limits: table_open_cache: 431
    (requested 2000)

Is it related to the above? What does it mean and how can I resolve it?

I tried various solutions involving /lib/systemd/system/mysql.service.d/limits.conf and other configuration settings but couldn't get any of them to work.

Upvotes: 3

Views: 11120

Answers (2)

Vishal
Vishal

Reputation: 2336

I rewrote my class above to look like this...

class FooData(object):
    def __init__(self):
        self.myconfig = {
            'option_files': os.environ['HOME'] + '/.my.cnf',
            'database': 'nsdata'
        }
        self.mysqlcxn = None

    def __enter__(self):
        try:
            self.mysqlcxn = mysql.connector.connect(**self.myconfig)
        except mysql.connector.Error as err:
            if err.errno == 2003:
                self.mysqlcxn = None
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        if self.mysqlcxn is not None and self.mysqlcxn.is_connected():
            self.mysqlcxn.close()

    def etl(self)
        ...

I can then use with ... as and ensure that I am cleaning up properly.

with FooData() as obj:
    obj.etl()

The Aborted connection messages are thus properly eliminated.

Oliver Dain's response set me on the right path and Explaining Python's '__enter__' and '__exit__' was very helpful in understanding the right way to implement my Class.

Upvotes: 0

Oliver Dain
Oliver Dain

Reputation: 9953

It's not a config issue. When you are done with a connection you should close it by explicitly calling close. It is generally a best practice to maintain the connection for a long time as creating one takes time. It's not possible to tell from your code snippet where would be the best place to close it - it's whenever you're "done" with it; perhaps at the end of your __main__ method. Similarly, you should close the cursor explicitly when your done with it. Typically that happens after each query.

So, maybe something like:

class FooData(object):
    def __init__(self):
        ...
        try:
            self.my_cnf = os.environ['HOME'] + '/.my.cnf'
            self.my_cxn = mysql.connector.connect(option_files=self.my_cnf)

     def execute_some_query(self, query_info):
        """Runs a single query. Thus it creates a cursor to run the
           query and closes it when it's done."""

        # Note that cursor is not a member variable as it's only for the
        # life of this one query    
        cursor = self.my_cxn.cursor(dictionary=True)
        cursor.execute(...)

        # All done, close the cursor
        cursor.close()

    def close():
        """Users of this class should **always** call close when they are
           done with this class so it can clean up the DB connection."""
        self.my_cxn.close()

You might also look into the Python with statement for a nice way to ensure everything is always cleaned up.

Upvotes: 3

Related Questions