Search code examples
mysqlsqlmysql-pythonflask-mysql

Why am I getting MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

I do multiple requests to Mysqlsd and for specific users I get this error

MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

This error occurs on line

cursor.execute('Select * from process WHERE email=%s ORDER BY timestamp DESC LIMIT 20', ("[email protected]",))

But when I do the same query but for a different user, there is no problem.

cursor.execute('Select * from process WHERE email=%s ORDER BY timestamp DESC LIMIT 20', ("[email protected]",))

The page loads correctly.

More details on MySQL is given below

#modules used
from flask_mysqldb import MySQL
import MySQLdb.cursors

#setup
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'myusername'
app.config['MYSQL_PASSWORD'] = 'mypassword'
app.config['MYSQL_DB'] = 'my_db'
mysql = MySQL(app)

#extract of requests made to db
@app.route('/myhome', methods=['GET', 'POST'])
def home_page():
email = "[email protected]"
cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT * FROM mail WHERE email = %s', (email,))
completed = cursor.fetchone()
cursor.execute('SELECT sum(transaction) FROM transactions WHERE email=%s', (email,))
points = cursor.fetchone()
cursor.execute('Select * from process WHERE email=%s ORDER BY timestamp DESC LIMIT 20', (email,))
transactions = cursor.fetchall()

I will post the process table from Mysql here so you can figure out why the issue is happening for red marked user and not for green marked user. MySQL table

Also, this might be an issue of packet size but I haven't had any issue till yesterday (there was more than 11 entries under the user tommy. I deleted 6 rows and it is still not working). Also if you think it is due to packet size, please tell me how can I solve it without increasing packet size because I am on a shared network and the hosting provider is not letting me increase the packet size limit.

Structure table

Solution

  • There are multiple potential reasons for "server has gone away" and only one of them is due to data being too large for your max allowed packet size.

    https://dev.mysql.com/doc/refman/8.0/en/gone-away.html

    If it is caused by the data being too large, keep in mind that each row of result is its own packet. It's not the whole result set that must fit in a packet. If you had 11 rows and you deleted 6 but still get the error, then the row that caused the problem still exists.

    You could:

    • Remove the row that is too large. You might want to change the column data types of your table so that a given row cannot be too large. You showed a screenshot of some data, but I have no idea what data types you use. Hint: use SHOW CREATE TABLE process.

    • Change the max_allowed_packet as a session variable, since you don't have access to change the global variable. Also keep in mind the client must also change its max allowed packet to match. Read https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html