19

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
Improve this question
4
  • Not sure if this will be of use, but take a look at this ReconnectingConnectionPool recipe for Twisted gist.github.com/powdahound/174056 Commented Mar 27, 2014 at 22:31
  • 1
    I've implemented it - gist.github.com/opensourcegeek/9822127 Commented Mar 28, 2014 at 0:14
  • Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/… Commented Aug 19, 2016 at 19:43
  • I've found a solution using the 'ping' method of 'connection' on PyMySQL Documentation and I've put an example here Commented Apr 26, 2019 at 12:27

6 Answers 6

Reset to default
23

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This should be the preferred solution, of course.
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.
@RichAndrews are you saying that MySQL Variables I was setting earlier are wiped out and I need to reset them?
@Volatil3 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.
15

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
Improve this answer

Comments

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.

Improve this answer

1 Comment

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.
1

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
Improve this answer

1 Comment

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.
1

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.

Improve this answer

Comments

0

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")
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.