Lost Connection to MySQL Server During Query – Fixing a SQL Error Message

SQL Commands on ASPIsFun

It’s probably happened to every SQL developer at least one time during their coding projects. They are running queries. Suddenly they get the error message – Lost connection to MySQL server during query. That’s a pretty generic error. What does it mean, and how do you fix it?

Most of the time, this error has to do with timeout values. That is, let’s say you’re working with a table with hundreds of thousands of rows. Your MySQL database is going to take some time to get you the results. MySQL has a ‘safety net’ set up so queries can’t just run for hours and hours and clog up your database processing. Most of the time, queries can’t run for more than 30 seconds or 60 seconds before they are cut off.

That’s probably what’s going on here.

So how do you fix that?

First off, log into MySQL Workbench and take a look at your server variables. You see those by clicking on ‘Administration’ in the lower left hand side. Then click on Status and System Variables in the left-hand listing. When the two-tab set of data shows up in the right-hand window, click on the System Variables tab. You’re looking for the Networking/Timeout settings.

Specifically, you want to look at the net_read_timeout and the net_write_timeout values. Those are given in seconds. If those are only in the 30 second or 60 second range, you’re going to want to increase those values.

In the top main menu of MySQL Workbench, click on Edit – Preferences.

In the preferences window, go into SQL Editor on the left.

You should see the read and timeout values there, in seconds. Update those to be longer. Click on OK to save.

SHUT DOWN MYSQL WORKBENCH!!

You must shut down and then restart MySQL Workbench for this to take effect.

Once you restart MySQL Workbench, go back into the preferences area to make sure the new values are in place.

This should hopefully now take care of your issue!

Now, of course, I will mention that it is always a good idea to make sure you have crafted your SQL query as efficiently as possible. Make sure you’re using key values, keep your tables well maintained, and so on. But sometimes you are simply working with such large volumes of data that you need to let your query run for a little while to finish.

Be the first to comment

Leave a Reply

Your email address will not be published.


*