MySQL Connector/Python not closing connection explicitly

Question

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.

score 3 · Accepted Answer · answered Jan 08 '17 at 20:54

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.

Thank you for your comments. It clarified that I was incorrectly never calling `cursor.close()` and also need to explicitly call `my_cxn.close()`. Is there a good way to incorporate mysql.connector connections into a python class such that one is not creating an unnecessary number of connections to the server and also responsibly closing connections without needing to be explicit? Is using `__enter__` and `__exit__` a good way to think about implementing mysql.connector in a Class and ensuring proper cleanup when used with `with`? — Vishal, Jan 09 '17 at 03:30
@Vishal I don't think there's a single, best solution to this. It depends on how your class is to be used. The rule of thumb is to create as few connections as you can but to always close them when you're done. So how your class is used will determine the best way to do it. And, there are exceptions to that rule of thumb. — Oliver Dain, Jan 09 '17 at 17:34

score 0 · Answer 2 · answered Jan 09 '17 at 04:02

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.

MySQL Connector/Python not closing connection explicitly

2 Answers2