Lost Connection to MySQL Server During Query

Error Code: 2013. Lost connection to MySQL server during query

New versions of MySQL WorkBench have an option to change specific timeouts.

For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.

MySQL keeps losing connection when trying to make a query

298M rows is a lot to go through. I can definitely see that taking more than 30 seconds, but not much more. First, thing I would do is remove your default disconnection time limit. Personally I always make mine around 300 seconds or 5 min. If you're using mysql workbench that can be done via this method: MySQL Workbench: How to keep the connection alive

Also, I would try and check to see if the trade_time column has an index on it. Having your column that you query often indexed is a good strategy to make queries faster.

SHOW INDEX FROM tablename;

Look to see if trade_time is in the list. If not, you can create an index like so:

CREATE INDEX dateTime ON tablename (trade_time);

Over time losing connection to mysql server during queries

I solved this issue by not having connections open for too long. My program connected to the MySQL server at the beginning of the program and used that same connection all the time. So instead of having one connection, I just opened a connection when I needed to access the database then closed it afterwards. Just make sure you don't have your connection open for too long

Don't do this:

con = mysql.connector.connect(
        host=host,
        user="ImNotThat",
        passwd="Stupid",
        database="toShowMyPasswords"
 )

async def someListenerEvent():
     doThing()
     con.commit()

Do this:

async def someListenerEvent():
    con = mysql.connector.connect(
        host=host,
        user="ImNotThat",
        passwd="Stupid",
        database="toShowMyPasswords"
    )
    doThing()
    con.commit()
    cur.close()
    con.close()
    

---

Related Topics