Python Peewee MySQL server has gone away && Lost connection to MySQL server during query

MySQL server has gone away

##### peewee
from peewee import *
from peewee import __exception_wrapper__

class RetryOperationalError(object):

     def execute_sql(self, sql, params=None, commit=True):
         try:
             cursor = super(RetryOperationalError, self).execute_sql(sql, params, commit)
         except OperationalError:
             if not self.is_closed():
                 self. close()
             with __exception_wrapper__:
                 cursor = self. cursor()
                 cursor. execute(sql, params or ())
                 if commit and not self.in_transaction():
                     self. commit()
         return cursor

class RetryMySQLDatabase(RetryOperationalError, MySQLDatabase):
     def sequence_exists(self, seq):
         pass

database = RetryMySQLDatabase(...) # create connection object

# Code usage: determine whether the connection is closed before each execution of SQL
def post(self):
if database.is_closed():
database. connect()
...


#####pymysql
def get_conn():
global conn
global cur
If conn is not None:
try:
conn.ping(True)
return conn
except Exception as e:
logging. exception(e)
try:
conn = pymysql.Connect(host='hostname', user='username', password='password', database='database', port=3306, charset='utf8mb4', )
return conn
except Exception as e:
logging. exception(e)



# Query data
querystr="SELECT * FROM t_user"
get_conn()
cur. execute(querystr)
conn.commit()


### reason
1. Connection timeout: When a connection has not initiated a new database operation for a long time and reaches the wait_timeout of MySQL, it will be forcibly closed by the server side. After the connection is closed and then used for query, an error message will appear, which is the first One day the service can be accessed normally, but the next day cannot be accessed.
Execute the statement: show global variables like '%timeout'; the value of wait_timeout is disconnected after xxx seconds of no operation on the connection
2. MySQL downtime: Execute the statement, show global status like 'uptime'; check the running time of MySQL, the larger the value of uptime, the longer the running time of MySQL.

3. The query result set is too large: Execute the statement, show global variables like 'max_allowed_packet'; check the maximum size limit of the result set, it will be accompanied by an error of 'Your SQL statement was too large.', you need to optimize the SQL statement, or modify this parameter value. set global max_allowed_packet=1024*1024*16;
4. There is another situation where this error may be reported if the SQL statement is too long. I don’t know why~~~

### solve
1. It is not recommended to extend the connection time of the database and modify the value of wait_timeout.
2. Modification in the code: before each execution of the SQL statement, first determine whether the connection is valid, and if it is invalid, reconnect

pymysql.err.InterfaceError: (0, '')

# Reason 1: The mysql connection is closed by itself, and any db-related operations on any closed conn, including ping(), will cause this error. If the error of problem 1 occurs but is not resolved in time, and the service is still accessed, this error will occur because a closed connection is used.
# Reason 2: The database operation object instance has not been logged out, but the held database connection has expired, resulting in the failure of subsequent database operations
# Reason three: The database connection code is outside the function
# Reason 4: After connecting to MySQL, there is no operation to close the connection. There will be no problems in a short period of time. If you keep this connection for a long time, connection confusion will occur.

# The following code will directly throw an exception and cannot execute the except code segment
try:
db.ping(reconnect=True)
except:
db = pymysql. connection(..)
findlly:
cursor = db. cursor()
cursor.execute(sql)

# reproduced
class DataSource(object):

def __init__(self):
self.conn = self.to_connect()

def __del__(self):
self.conn.close()

def to_connect(self):
return pymysql.connections.Connection(params)

def is_connected(self):
"""Check if the server is alive"""
try:
self.conn.ping(reconnect=True)
print("db is connecting")
except:
traceback.print_exc()
self.conn = self.to_connect()
print("db reconnect")

pymysql.err.OperationalError: (2013, Lost connection to MySQL server during query')

# Execute before each database operation, to be supplemented
def reConnect(self):
     try:
         db. ping()
     except:
         db = pymysql. connect(...)