Correct way of keeping mysql connection

Question

I have an application running 24/7 which uses mysql. Different functions of it use mysql. One way to implement it is to create a global mysql instance in the application like this:

self.db = MySQLdb.connect(
            host=self.settings.MYSQL_HOST_LOCAL,
            user=self.settings.MYSQL_USER,
            passwd=self.settings.MYSQL_PASS,
            db=self.settings.MYSQL_DB,
            use_unicode=True,
            charset="utf8",
        )

and execute commands using self.db.execute(...). By doing this, the application uses 1 connection. The other way is to create connection every time I need to execute a transaction.

Approach 1, prevents the application from creating and deleting connections over and over but it will face "mysql gone away" problem if it stays ideal. Approach 2, doesn't have a problem with "mysql gone away", but it has too much I/O.

I am pretty sure neither these approcaches are the right ones, but what is the right approach?

by too much I mean for each select or update, I create a new connection. That doesn't count as "too much"? — AliBZ, Oct 18 '13 at 01:24
Approach 2 is almost always the correct approach. Let connection pooling do it's funky stuff...BTW you appear to be trying to solve a problem before you know you have one. — Mitch Wheat, Oct 18 '13 at 01:25
@MitchWheat thanx, could you please provide me with a reference? — AliBZ, Oct 18 '13 at 17:38

score 1 · Answer 1 · 2013-10-18T02:00:24.930

1

One way to do is to create a connection every time you need to execute. You can also create a function to do it for you. This is how it meant to be. It is not too much I/O..

You can also do the following:

while True:
    try:
        db_session.execute('SELECT * FROM table')
        break
    except SQLAlchemyError:
        db_session.rollback()

If the connection has go away, this will raise an exception, the session will be rollbackd, it'll try again is likely to succeed. (The first solution is much better)

edited Oct 18 '13 at 02:00

answered Oct 18 '13 at 01:31

So should I close my db connection at the end of each transaction? I am using MySQLdb. Lets say I create a new instance at the beginning of my function. If I don't close it, the instance will be deleted, now what happens to the connection? Is it ok now, or should I close it before the function returns? – AliBZ Oct 18 '13 at 01:38

amyangfei · Answer 2 · 2013-10-18T02:03:08.343

In Approach 1, according to the application scenario 'MySQL server has gone away' may happens because

You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

You can add a decorator to any db handler function like this to reconnect MySQL db when exception 'MySQL server has gone away' raised.

class DB:
    """Database interface"""

    def retry(func):
        def call(self, *args, **kwargs):
            lock.acquire()
            try:
                return func(self, *args, **kwargs)
            except MySQLdb.Error, e:
                if 'MySQL server has gone away' in str(e):
                    # reconnect MySQL
                    self.connect_mysql()
                else:
                    # No need to retry for other reasons
                    pass
            finally:
                lock.release()
        return call

    def __init__(self):
        pass

    def connect_mysql(self):
        # do connection

    @retry
    def db_handler_function(self):
        # do something

Thanx, I thought about that, but my main question is which approach is the right one. I have a fix for both of the approaches but neither looks good enough. — AliBZ, Oct 18 '13 at 01:54
some similar posts here: [mysql connections. Should I keep it alive or start a new connection before each transaction?](http://stackoverflow.com/questions/4672713/mysql-connections-should-i-keep-it-alive-or-start-a-new-connection-before-each), [Is it better to hold database connections?](http://stackoverflow.com/questions/16011086/is-it-better-to-hold-database-connections) — amyangfei, Oct 18 '13 at 02:14

Correct way of keeping mysql connection

2 Answers2

Linked