Lost connection to MySQL server during query

The mysql docs have a whole page dedicated to this error: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

of note are

• You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.5.2.10, “Packet too large”.

• You can get more information about the lost connections by starting mysqld with the --log-warnings=2 option. This logs some of the disconnected errors in the hostname.err file

Make sure you close cursor before connection. I've resolved my problem with this:

if cur and con:                        
    cur.close() 
    con.close() 

You need to increase the timeout on your connection. If you can't or don't want to do that for some reason, you could try calling:

data = db.query(sql).store_result()

This will fetch all the results immediately, then your connection won't time out halfway through iterating over them.

I encountered similar problems too. In my case it was solved by getting the cursor in this way:

cursor = self.conn.cursor(buffered=True)

The same as @imxylz, but I had to use mycursor.execute('set GLOBAL max_allowed_packet=67108864') as I got a read-only error without using the GLOBAL parameter.

mysql.connector.__version__ 

8.0.16

I was getting this error with a "broken pipe" when I tried to do bulk inserts with millions of records. I ended up solving this by chunking my data into smaller batch sizes and then running an executemany command with the mysql cursor for each of the inserts I needed to do. This solved the problem and didn't seem to affect the performance in any noticeable way.

eg.

def chunks(data):
    for i in range(0, len(data), CHUNK_SIZE):
        yield data[i:i + CHUNK_SIZE]


def bulk_import(update_list):
    new_list = list(chunks(update_list))
    for batch in new_list:
         cursor.execute(#SQL STATEMENT HERE)

You can see my answer followed for similar problem:

https://stackoverflow.com/a/69610550/16647254

use lock to solve this problem

lock.acquire()
mysqlhelper.getconn()
result_db_num = mysqlhelper.update(sql, [businessid, md5_id])
mysqlhelper.end()
mysqlhelper.dispose()
lock.release()

I had the same problem and wrestled for many hours, experimenting with LOTS of different solutions. What finally worked for me was this. The code opens a 10 connection pool initially, then mysql.connector serves up connections from the pool with get_connection().

class DB:
    connection = None

    def __init__(self):
        self.conn()

    def conn(self):
        try:
            if not self.connection:
                self.connection = mysql.connector.pooling.MySQLConnectionPool(user='web', password='mypasswd',
                                                                              host='prod', database='myelection',
                                                                              autocommit=True, pool_size=10,
                                                                              buffered=True)
            return self.connection.get_connection()

        except mysql.connector.errors.InterfaceError as err:
            print("can't connect to mysql ", err)

        except mysql.connector.DatabaseError as err:
            print("database error: ", err)

        except Exception as err:
            print("unknown db exception: ", err)

        print("exiting from conn() with error.")
        exit()

    # Make sure your class methods open, then automatically 
    # close the connections and the cursors.
    def new_polling_place(self, pp_name):
        #  cur = self.conn().cursor(dictionary=True)
        with self.conn() as con:
            with con.cursor() as cur:
                cur.execute("INSERT INTO pollingplace (pp_name) VALUES (%s)", [pp_name])
                return cur.lastrowid

This very same situation happened to me while working with mariadb , sqlalchemy and pandas and just like @iamapotatoe above I also created a function to break up the dataframe into chunks and port them over to the sql database bit by bit. This can be utilized especially if changing the max_allowed_packet in the mysql config option doesn't work for you .

def load_large_df(table_name,df_to_load,batch_size,engine):
    df_to_load = df_to_load.dropna(how='all')
    with engine.connect() as conn:
        conn.execute(f"DROP TABLE IF EXISTS {table_name}")
        rows = df_to_load.shape[0]
        batch = int(rows/batch_size)
        

        strt = 0
        while strt < rows:
            df = df_to_load[strt:].head(batch)
            df.to_sql(table_name,con=conn,if_exists='append')
            strt += batch

very simple to solve, go to the control panel of you phpadmin and click on config/then edit the .ini file you see. look for port 3306 if that's not the port you are using for your connection change 3306 to the port you are using. on your login screen just put localhost for your server, your port if its not the default or if you did not change the file name my.ini in sql configuration leavit as is. then put your username:root or the one you created then the password:1234 or the one you assigned. if you are connecting localy, do not check the url option. then type the name of the database you want to edit. note: once you are connected you will see the list of databases you have on your server or the server you are connecting to.