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.
In the top main menu of MySQL Workbench, click on Edit – Preferences.
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.
Leave a Reply