How to Fix MySQL Lost Connection

In some cases when working with MySQL databases, you may encounter the “Error 2013: Lost connection to MySQL server during query” error. Although this can be frustrating when it happens, there is relatively simple reason why it happens and how you can fix it.

What Causes “Lost Connection to MySQL Server During Query” Error?

This type of error occurs when the connection between a client and the server exceeds the specified timeout value.

For example, if you have a long query that takes a long time to complete the execution, MySQL drops the connection to the server.

Take for example, when exporting a large database, depending on the export method and the data to be exported, it can take a long time for this process to complete. To prevent the issues on the server, MySQL can terminate this connection before the process completion.

Another common cause of this type of error is the complex join operations.

MySQL Show Timeout Value

As mentioned, you can fix this type of error by simply increasing the timeout durations on the server side.

The timeout values are stored in the wait_timeout and interactive_timeout variables. We can fetch the current values as shown in the following queries:

The resulting output is as follows:

The given result shows the default value of the interactive_timeout variable. This value defines the duration, in seconds, that the server waits for an activity on an active session before terminating it.

We can also fetch the value of wait_timeout variable as:

Resulting table:

This variable sets the duration that the MySQL server waits for an activity in a non-interactive session.

Fix MySQL Lost Connection Error by Updating the Timeout Variables

To fix this type of error, we can update the values of the two variables discussed previously. We can run the query as shown in the following:

The previous command should update the value of the interactive_timeout variable to 43200 seconds.

To update the wait_timeout parameter, run the following command:

You are done. You can try re-running your timed query.

Conclusion

In this post, we discussed the cause of connection lost error when working with MySQL and the steps that you can take to fix them. However, be mindful of your queries or segment them into efficient blocks.

Thanks for reading and happy coding!