python - MySQL Has gone away during executemany on after moving to VPS

Question

I'm trying to move some services to my VPS. It's multiple python scripts that is scraping from an API and feeding data to an MySQL database. Most of it is working flawlessly after the move, but this one script has a single insert that fails on the new MySQL-installation.

It's trying to insert 100 lines in a 2.5gb table. It's returning with error 2006, 'MySQL server has gone away'. It's not a big insert, Python says its about 90kB. I can't believe that it's timing out cause I get the error within 10-15seconds of launching the script, which spends most of that time doing http-requests before attempting the insert. Heres what I've done:

1. Verified that both MySQL-databases are set up with the same timeouts and allowed packet size. I've also tried increasing both on the new VPS.
2. The same exact script works on my old database.
3. I've tried dividing the insert into smaller parts, and it's still shutting down the connection.
4. I've tried running the script from my old server, so the Python env. is identical. It's connecting fine and doing other inserts/updates.
5. I can log into the mysql with ssh and have tried a simple 1-line insert into the table and that works.

It's working well updating 100 lines in a different table, which is smaller (600MB). This is why I suspect that my VPS somehow cant handle the 2.5GB table. The VPS has less RAM than my previous server. But it shouldn't really open the entire table to INSERT, should it? The VPS has 4gb while my old server had 8gb. I'm never above 25% memory usage on my old server.

Here's the function after I've tried making smaller parts of it:

def updateComplete(completeupdate, LIST_LENGTH=10):
    logging.debug(f"updateComplete has been called for {completeupdate}")
    START_INDEX = 0 
    cursor = db.cursor()
    while completeupdate[START_INDEX:START_INDEX+LIST_LENGTH]:
        cursor.executemany("""INSERT INTO userscomplete
                            (timer, userid, username, userlevel, userrank, battlestats,
                            forumposts, karma, age, role, statu, description, jobposition, jobposition2,
                            companyid, factionposition, factionid, factiondays, inactivedays,
                            networth, propertyvalue, attackswon, attackslost, moneymugged, drugsused,
                            maxlife, defendslost, defendswon, undefeated, awards) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
                            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
                            %s, %s)""", completeupdate)
        START_INDEX+=LIST_LENGTH
    db.commit()

wait_timeout = 28800 max_allowed_packet = 104857600

EDIT: Forgot to note that the old server is MySQL v. 8.0.20 and the new is 8.0.23.

question from:https://stackoverflow.com/questions/65849947/mysql-has-gone-away-during-executemany-on-after-moving-to-vps

Answer 1

Waitting for answers