Python mysql (using pymysql) auto reconnect

Question

I'm not sure if this is possible, but I'm looking for a way to reconnect to mysql database when the connection is lost. All the connections are held in a gevent queue but that shouldn't matter I think. I'm sure if I put some time in, I can come up with a way to reconnect to the database. However I was glancing pymysql code and I saw that there is a 'ping' method in Connection class, which I'm not sure exactly how to use.

The method looks like it will reconnect first time but after that it switched the reconnect flag to False again? Can I use this method, or is there a different way to establish connection if it is lost? Even if it is not pymysql how do people tackle, database servers going down and having to re-establish connection to mysql server?

def ping(self, reconnect=True):
    ''' Check if the server is alive '''
    if self.socket is None:
        if reconnect:
            self._connect()
            reconnect = False
        else:
            raise Error("Already closed")
    try:
        self._execute_command(COM_PING, "")
        return self._read_ok_packet()
    except Exception:
        if reconnect:
            self._connect()
            return self.ping(False)
        else:
            raise

Liquefacient

Not sure if this will be of use, but take a look at this ReconnectingConnectionPool recipe for Twisted gist.github.com/powdahound/174056 – Engage

I've implemented it - gist.github.com/opensourcegeek/9822127 – Liquefacient

Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/… – Transponder

I've found a solution using the 'ping' method of 'connection' on PyMySQL Documentation and I've put an example here – Nata

Answer 1

Finally got a working solution, might help someone.

from gevent import monkey
monkey.patch_socket()
import logging

import gevent
from gevent.queue import Queue
import pymysql as db

logging.basicConfig(level=logging.DEBUG)
LOGGER = logging.getLogger("connection_pool")


class ConnectionPool:
    def __init__(self, db_config, time_to_sleep=30, test_run=False):
        self.username = db_config.get('user')
        self.password = db_config.get('password')
        self.host = db_config.get('host')
        self.port = int(db_config.get('port'))
        self.max_pool_size = 20
        self.test_run = test_run
        self.pool = None
        self.time_to_sleep = time_to_sleep
        self._initialize_pool()

    def get_initialized_connection_pool(self):
        return self.pool

    def _initialize_pool(self):
        self.pool = Queue(maxsize=self.max_pool_size)
        current_pool_size = self.pool.qsize()
        if current_pool_size < self.max_pool_size:  # this is a redundant check, can be removed
            for _ in xrange(0, self.max_pool_size - current_pool_size):
                try:
                    conn = db.connect(host=self.host,
                                      user=self.username,
                                      passwd=self.password,
                                      port=self.port)
                    self.pool.put_nowait(conn)

                except db.OperationalError, e:
                    LOGGER.error("Cannot initialize connection pool - retrying in {} seconds".format(self.time_to_sleep))
                    LOGGER.exception(e)
                    break
        self._check_for_connection_loss()

    def _re_initialize_pool(self):
        gevent.sleep(self.time_to_sleep)
        self._initialize_pool()

    def _check_for_connection_loss(self):
        while True:
            conn = None
            if self.pool.qsize() > 0:
                conn = self.pool.get()

            if not self._ping(conn):
                if self.test_run:
                    self.port = 3306

                self._re_initialize_pool()

            else:
                self.pool.put_nowait(conn)

            if self.test_run:
                break
            gevent.sleep(self.time_to_sleep)

    def _ping(self, conn):
        try:
            if conn is None:
                conn = db.connect(host=self.host,
                                  user=self.username,
                                  passwd=self.password,
                                  port=self.port)
            cursor = conn.cursor()
            cursor.execute('select 1;')
            LOGGER.debug(cursor.fetchall())
            return True

        except db.OperationalError, e:
            LOGGER.warn('Cannot connect to mysql - retrying in {} seconds'.format(self.time_to_sleep))
            LOGGER.exception(e)
            return False

# test (pytest compatible) -------------------------------------------------------------------------------------------
import logging

from src.py.ConnectionPool import ConnectionPool

logging.basicConfig(level=logging.DEBUG)
LOGGER = logging.getLogger("test_connection_pool")


def test_get_initialized_connection_pool():
    config = {
        'user': 'root',
        'password': '',
        'host': '127.0.0.1',
        'port': 3305
    }
    conn_pool = ConnectionPool(config, time_to_sleep=5, test_run=True)
    pool = conn_pool.get_initialized_connection_pool()
    # when in test run the port will be switched back to 3306
    # so the queue size should be 20 - will be nice to work 
    # around this rather than test_run hack
    assert pool.qsize() == 20

Liquefacient

Answer 2

Well, I've got the same problem in my application and I found a method on the PyMySQL documentation that pings to the server and check if the connection was closed or not, if it was closed, then it reconnects again.

from pymysql import connect
from pymysql.cursors import DictCursor

# create the connection
connection = connect(host='host', port='port', user='user', 
                     password='password', db='db', 
                     cursorclass=DictCursor)

# get the cursor
cursor = connection.cursor()

# if the connection was lost, then it reconnects
connection.ping(reconnect=True)      

# execute the query
cursor.execute(query)

I hope it helps.

Nata

This should be the preferred solution, of course. – Robinetta

Worth noting that that ping is a convenience function and reconnect=True does a pymysql.connection.connect() which means any connection setup (mode, autocommit, tx isolation) should be re-configured. – Fraktur

@RichAndrews are you saying that MySQL Variables I was setting earlier are wiped out and I need to reset them? – Hesky

@Hesky I haven't teased it apart, by a reconnect=True in the pymysql client is going to be a new MySQL client connection and would presume that any client session state is wiped out. Would like to hear if there are thoughts otherwise. – Fraktur

Answer 3

The easiest way is to check the connection right before sending a query.

You can do this by creating a small class that contains two methods: connect and query:

import pymysql
import pymysql.cursors

class DB:
    def connect(self):
        self.conn = pymysql.connect(
                             host=hostname,
                             user=username,
                             password=password,
                             db=dbname,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             port=3306)

    def query(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except pymysql.OperationalError:
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return cursor

db = DB()

Now, whenever you send a query using db.query("example SQL") the request is automatically prepared to encounter a connection error and reconnects using self.connect() if it needs to.

Remember: This is a simplified example. Normally, you would want to let PyMySQL help you escape special characters in your queries. To do that, you would have to add a 2nd parameter in the query method and go from there.

Deutero

You never should use "except:" without even restricting to Exception. In this case it should be at least pymssql.StandardError. Usually you do not want to catch SystemExit exception sys.exit(), for example, to prevent process close, because continuing from it your process would be left in a very unstable condition. – Matrilineal

Answer 4

the logic is quite simple, if connection close then try to reconnect for several times in this case I use max tries for 15 times to reconnect or ping.

import pymysql, pymysql.cursors
conn = pymysql.connect(
                         host=hostname,
                         user=username,
                         password=password,
                         db=dbname,
                         charset='utf8mb4',
                         cursorclass=pymysql.cursors.DictCursor,
                         )
cursor = conn.cursor()
# you can do transactions to database and when you need conn later, just make sure the server is still connected
if conn.open is False:
   max_try = 15
   try = 0
   while conn.open is False:
       if try < max_try:
           conn.ping() # autoreconnect is true by default
       try +=1

# check the conn again to make sure it connected
if conn.open:
    # statements when conn is successfully reconnect to the server
else:
    # it must be something wrong : server, network etc

Francoise

that conn.open isn't in PEP 249 and it appears to check to see if there is a socket object (in an ssl context). That check might not guarantee that when the remote server goes away that conn.open will return false if the connection is not going to work when the underlying socket selects/polls. – Fraktur

Answer 5

Old but I encountered a similar problem for accessing hosted db within programs. The solution I ended up using was to create a decorator to automatically reconnect when making a query.

given a connection function:

def connect(self):
    self.conn = mysql.connector.connect(host=self.host, user=self.user, 
    database=self.database, password=self.password)
    self.cursor = self.conn.cursor()
    print("Established connectionn...")

I created

def _reconnect(func):
    @wraps(func)
    def rec(self,*args,**kwargs):
        try:
            result = func(self,*args,**kwargs)
            return result
        except (mysql.connector.Error, mysql.connector.Warning) as e:
            self.connect()
            result = func(self,*args,**kwargs)
            return result
    return rec 

Such that any function using the connection can now be decorated as so

@_reconnect
def check_user_exists(self,user_id):
    self.cursor.execute("SELECT COUNT(*) FROM _ where user_id={};".format(user_id))
    if self.cursor.fetchall()[0][0]==0:
        return False 
    else:
        return True

This decorator will re-establish a connection and rerun any function involving a query to the db.

Swig

Answer 6

You can use a property to keep the connection alive every time you do querying:

import pymysql
import pymysql.cursors
import pandas as pd 

class DB:
    def __init__(self, hostname='1.1.1.1', username='root', password='password',
                 database=None, port=3306, charset="utf8mb4"):
        self.hostname = hostname
        self.database = database
        self.username = username 
        self.password = password
        self.port = port
        self.charset = charset
        self.connect()
    
    @property
    def conn(self): 
        if not self.connection.open:
            print('Going to reconnect')
        self.connection.ping(reconnect=True)
        return self.connection

    def connect(self):
        self.connection = pymysql.connect(
                             host=self.hostname,
                             user=self.username,
                             password=self.password,
                             db=self.database,
                             charset=self.charset,
                             cursorclass=pymysql.cursors.DictCursor,
                             port=self.port)

    def query(self, sql):
        return pd.read_sql_query(sql, con=self.conn)

db = DB(hostname='1.1.1.1', username='root', password='password', database=None, port=3306, charset="utf8mb4")

Gaulin