I am using pymysql client to connect to mysql in my flask API, everything works fine for some days(around 1-2 days) after that suddenly it starts to throw this error
Traceback (most recent call last):
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1039, in _write_bytes
self._sock.sendall(data)
TimeoutError: [Errno 110] Connection timed out
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "Main.py", line 194, in post
result={'resultCode':100,'resultDescription':'SUCCESS','result':self.getStudentATData(studentId,args['chapterId'])}
File "Main.py", line 176, in getStudentATData
cur.execute("my query")
File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 855, in query
self._execute_command(COMMAND.COM_QUERY, sql)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1092, in _execute_command
self._write_bytes(packet)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1044, in _write_bytes
"MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (TimeoutError(110, 'Connection timed out'))")
And if restart the application it again works fine, i have tried everything but cant seem to get away with this, can anyone help? As suggested, i am implemented a retry kind of mechanism but that didn't solve the problem
def connect(self):
#db connect here
def cursor(self):
try:
cursor = self.conn.cursor()
except Exception as e:
print(e)
self.connect()
cursor = self.conn.cursor()
return cursor
And consuming it like DB().cursor()
As I see, you have two options:
Create new connection for every query, then close it. Like this:
def db_execute(query):
conn = MySQLdb.connect(*)
cur = conn.cursor()
cur.execute(query)
res = cur.fetchall()
cur.close()
conn.close()
return res