Query Timeout in MySQL Workbench | Error Code: 2013. Lost connection to MySQL server during query

This is kind of a silly and duplicative post, but I spent too much time searching for the right answer, so maybe this will help the right course of action bubble to the top faster in the future.

The Issue

I was trying to run a query on my local SQL install (whatever MAMP manages and provisions) using MySQL Workbench 6.3 for Mac but kept getting a timeout error.

error code 2013: lost connection to MySQL server during query

The query itself wasn’t overly complex, but I was using aggregate functions, group by, and a join to consolidate a dataset. I’m working with distance education reporting data for all U.S. colleges and universities from 2012-2015, so this join involved a 7K row table and another with 25K rows, so not inconsequential but also not BIG data level.

SELECT
STABBR as State,
EFDELEV as Level , 
SUM(EFDETOT) as Total_Distance,
SUM(EFDEEXC) as Exclusive_Distance,
SUM(EFDESOM) as Some_Distance,
SUM(EFDENON) as None_Distance

FROM hd2012 LEFT JOIN ef2012a_dist_rv
ON hd2012.UNITID = ef2012a_dist_rv.UNITID
GROUP BY State,  Level;

I did some initial googling on the error code, but it is a pretty general error code, so it was difficult to be sure whether this was a limitation of SQL or the Workbench DBMS. I read a few posts that suggested manipulating some of the .conf files for the underlying MySQL install, and I went too long down this road before trying something in Workbench itself.

It turns out there are timeout settings for the DBMS that you extend to make sure that it waits a sufficient amount of time for your query to return data. Thanks to this specific answer on StackOverflow, but the description of “how-to” it links to is no longer valid, hence this blog post.

The Fix

There is a quick setting in Preferences that helped me. As you might expect, the DBMS has settings to manage its connection to the SQL server. In my case, those were just too short for my long running queries.

I changed the 30 second defaults to 180, and returned the data I needed. However, I’d imagine that some things would call for a much higher timeout, especially if you wanted to do a lot of transactions.

preferences settings menu my sql workbench

Another Fix

As of 08/27/2018, I did some additional noodling around with the queries that produced this slow result and realized some simple indexing reduced the query time from ~50 seconds to .227 seconds. You can find a more detailed post about that here.

If you are looking for a way to stop the timeout error, now you have two options. However, now I realize that most of my issue had nothing to do with MySQL Workbench and everything to do with the way I constructed the underlying database : ) However, options are always good, so good luck!

I'm working on building the most comprehensive course available on building workflows with Google Apps Script.

Join the Course Waiting List for a Huge Discount!

* indicates required

8 thoughts on “Query Timeout in MySQL Workbench | Error Code: 2013. Lost connection to MySQL server during query”

  1. Yogesh says:

    Nice !

  2. Sumit says:

    It’s not working in my case.

    1. BrownBearWhatDoYouSee says:

      Not sure how big your data set is, how complex the query is, or how powerful your machine is, but have you tried increasing the timeout limit higher than 180 secs?

    2. BrownBearWhatDoYouSee says:

      I did some more work on the project that created the slow queries and indexing was the ticket. Maybe that would help in your case: https://jeffreyeverhart.com/2018/08/27/using-the-right-tools-indexing-in-mysql/

  3. jexx says:

    its not working , i put 6000 seconds 🙁

    1. BrownBearWhatDoYouSee says:

      Wow, does it run for the whole 6K seconds and then timeout? What type of dataset are you working with and what type of machine are you using? If it’s that big of a data set it might be time to look into come cloud options that would let you buy a massive machine for a few hours to crunch your numbers. Also possible to write a script of some kind to run the query.

      Since the timeout is more a function of MySQL Workbench than the server, that should churn until the results finish. Let me know how it goes. -JE

    2. BrownBearWhatDoYouSee says:

      I did some more work on the project that created the slow queries and indexing was the ticket. Maybe that would help in your case: https://jeffreyeverhart.com/2018/08/27/using-the-right-tools-indexing-in-mysql/

Leave a Reply

Your email address will not be published. Required fields are marked *