How to reconnect to mysql after receiving mqtt message in python?
Try:
cur = db.cursor()
try:
cur.execute(query, params)
except MySQLdb.Error:
db.ping(True)
cur.execute(query, params)
db.ping(True)
says to reconnect to DB is the connection was lost. You can also call db.ping(True)
right after MySQLdb.connect
. But to be on the safe side I'd better wrap execute()
into try
and call db.ping(True)
in except
block.
Reconnecting MySQL on timeout
You have to catch the exception and based on which error, reconnect or to do something else. Whether it is a connection time out, or a network problem or the MySQL had to be restarted.
The below (pseudoish) code shows how you could do that, but there is more to it. You'll want to try a few times and then bail out, or maybe try every 2 minutes or so.
while True:
try:
# do your database stuff
except peewee.OperationalError as exc:
# Oops! We have to try to reconnect
Does not really matter whether you use an ORM or not. However, an ORM might offer this functionality.
Python mysql (using pymysql) auto reconnect
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
How can I change the default Mysql connection timeout when connecting through python?
Do:
con.query('SET GLOBAL connect_timeout=28800')
con.query('SET GLOBAL interactive_timeout=28800')
con.query('SET GLOBAL wait_timeout=28800')
Parameter meaning (taken from MySQL Workbench in Navigator: Instance > Options File > Tab "Networking" > Section "Timeout Settings")
- connect_timeout: Number of seconds the mysqld server waits for a connect packet before responding with 'Bad handshake'
- interactive_timeout Number of seconds the server waits for activity on an interactive connection before closing it
- wait_timeout Number of seconds the server waits for activity on a connection before closing it
BTW: 28800 seconds are 8 hours, so for a 10 hour execution time these values should be actually higher.
Related Topics
Sample Each Group After Pandas Groupby
How Does Python Find a Module File If the Import Statement Only Contains the Filename
Cannot Import Qtwebkitwidgets in Pyqt5
Finding Occurrences of a Word in a String in Python 3
Kivy Not Working (Error: Unable to Find Any Valuable Window Provider.)
Convert Pandas Datetimeindex to Unix Time
Selenium Python Error: Element Could Not Be Scrolled into View
How to Get a List of All Indices of Repeated Elements in a Numpy Array
Print List of Lists in Separate Lines
Tkinter: Mouse Drag a Window Without Borders, Eg. Overridedirect(1)
How to Send an Xml Body Using Requests Library
Making a Python User-Defined Class Sortable, Hashable
Printing a List of Objects of User Defined Class
Concat Dataframe Reindexing Only Valid with Uniquely Valued Index Objects
Overriding Properties in Python
What Is the Purpose of Subclassing the Class "Object" in Python