Error 2013 lost connection to mysql server during query sql statement

Is your database restore stuck with MySQL Error 2013 (hy000)?

Often queries or modifications in large databases result in MySQL errors due to server timeout limits.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix MySQL Error 2013 (hy000) for our customers.

Why this MySQL Error 2013 (hy000) happens?

While dealing with MySQL, we may encounter some errors. Today, we are going to discuss one such error.

This MySQL 2013 error occurs during a restore of databases via mysqldump, in MySQL replication, etc.

This error appears when the connection between MySQL client and database server times out.

In general, this happens in databases with large tables. As a result, it takes too much time for the query to return data and the connection drops with an error.

Other reasons for the error include a large number of aborted connections, insufficient server memory, server restrictions, etc.

How do we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a lot on the triggering reason. Let’s now see how our MySQL Engineers help customers solve it.

1. Changing MySQL limits

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to connect with MySQL server.

So, our Engineers checked in detail and found that the connect_timeout value was set to only a few seconds. So, we increased it to 10 in the MySQL configuration file. For that, we followed the steps below:

Firstly, we opened the MySQL configuration file at /etc/mysql/my.cnf

Then, we searched for connect_timeout and set it as:

connect_timeout=10

Then we tried connecting with MySQL server and we were successful.

Additionally, it requires the proper setting of the variable max_allowed_packet in the MySQL configuration file too. While trying to restore the dump file in GB sizes, we increase the value to a higher one.

2. Disable Access restrictions

Similarly, this error also appears when the host has access restrictions. In such cases, we fix this by adding the client’s IP in /etc/hosts.allow or allow it in the server firewall.

Also, the error can happen due to the unavailability of the server. Recently, in a similar instance, the problem was not related to MySQL server or MySQL settings. We did a deep dig and found that high network traffic is causing the problem.

When we checked we found that a weird process running by the Apache user. So, we killed that and this fixed the error.

3. Increasing Server Memory

Last and not least, MySQL memory allocation also becomes a key factor for the error. Here, the server logs will have related entries showing the insufficient memory limit.

Therefore, our Dedicated Engineers reduce the innodb_buffer_pool size. This reduces the memory allocation on the server and fixes the error.

[Need assistance with MySQL errors – We can help you fix it]

Conclusion

In short, we discussed in detail on the causes for MySQL Error 2013 (hy000) and saw how our Support Engineers fix this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

The misstep depicted in the title of this article is entirely outstanding. A couple of attempts on handling the issue exist in various articles on the web. However, in error 2013 you lost connection to MySQL server during a query, concerning this article, there is a specific condition that is exceptionally novel so in the end, it causes the error to occur. The error occurs with the specific error message. That error message is in the going with yield message:

• root@hostname ~# mysql – uroot – p – h 127.0.0.1 – P 4406
• Enter secret key:
• Error 2013 (HY000): Lost relationship with MySQL server at ‘examining initial correspondence bundle’, system error: 0
• root@hostname ~#

The above error message is a result of partner with a MySQL Database Server. It is a standard MySQL Database Server running on a machine. However, the real connection is a substitute one. The connection exists using Worker holder running collaboration. Coming up next is the reliable running course of that Worker holder:

• root@hostname ~# netstat – tulpn | grep 4406
• tcp6 0:4406: * LISTEN 31814/worker-go-between
• root@hostname ~#

There are at this point lots of articles analyze about this mix-up. For a model in this association in the stack overflow or this association and one more in this association, besides in this association. The general issue is truly something almost identical. There is something misguided in the running arrangement of the regular MySQL Database Server.

Why this happens

This misstep appears when the relationship between your MySQL client and database server times out. Essentially, it took unreasonably long for the request to return data so the connection gets dropped.

By far most of my work incorporates content migrations. These activities for the most part incorporate running complex MySQL requests that burn through a huge lump of the day to wrap up. I’ve found the WordPress wp_postmeta table especially hazardous considering the way that a site with countless posts can without a very remarkable stretch have two or three hundred thousand post meta sections. Joins of enormous datasets from such tables can be especially genuine.

Avoid the issue by sanitizing your requests

Generally speaking, you can avoid the issue absolutely by refining your SQL questions. For example, instead of joining all of the substance of two especially immense tables, have a go at filtering through the records you needn’t waste time with. Where possible, have a go at reducing the amount of partakes in a singular inquiry. This should have the extra benefit of simplifying your inquiry to examine. For my inspirations, I’ve found that denormalizing content into working tables can deal with the read execution. This avoids breaks.

Re-making the inquiries isn’t, by and large, another option so you can effort the going with server-side and client-side workarounds.

A server-side course of action

If you’re ahead for your MySQL server, make a pass at changing a couple of characteristics. The MySQL documentation proposes extending the net_read_timeout or connect timeout values on the server.

The client-side course of action

You can extend your MySQL client’s sever regards on the possibility that you don’t have exclusive induction to the MySQL server.

MySQL Worktable

You can adjust the SQL Editor tendencies in MySQL Work Table:

• In the application menu, select Edit > Preferences > SQL Editor.
• Quest for the MySQL Session portion and augmentation the DBMS connection read break regard.
• Save the settings, very MySQL Work Table, and return the connection.

Step for handling the issue

There are a couple of stages for handling the issue above. There are two segments for handling the issue. The underlying portion is for perceiving the principal driver of the issue. Later on, the ensuing part is the genuine plan taken for tending to the fundamental driver of that issue. Thusly, going with the region which is the underlying portion will focus on power to search for the justification behind the issue.

Glancing through the justification behind the issue

Because of this article, coming up next is the means for settling the mix-up

1. Check whether the MySQL Database Server measure is truly running. Effect it as follows using any request plan open in the working for truly taking a gander at a running connection. Concerning this article, it is ‘systemctl status MySQL. Thusly, coming up next is a model for the execution of the request plan:

• root@hostname ~# systemctl status MySQL
• service – MySQL Community Server
• Stacked: stacked (/lib/systemd/structure/mysql.service; horrible; vendor preset: engaged)
• Dynamic: dynamic (running) since Mon 2019-09-16 13:16:12; 40s back
• Cycle: 14867 ExecStart=/usr/sbin/mysqld – demonize – pid-file=/run/mysqld/mysqld. Pid (code=exited, status=0/SUCCESS)
• Connection: 14804 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
• Guideline PID: 14869 (mysqld)
• Tasks: 31 (limit: 4915)
• Group:/system. Slice/mysql.service
• └─14869/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• root@hostname ~#

1. Preceding partner with MySQL Database Server using a substitute port focusing on any moving toward requesting where it is a worker compartment measure dealing with, basically test the regular connection. By the day’s end, the partner using the normal port tuning in the machine for any moving toward a relationship with MySQL Database Server. Normally, it exists in port ‘3306’. Do it as follow:

• root@hostname ~# mysql – uroot
• Goof 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
• root@hostname ~#

The above screw-up message is where the genuine root issue is. Check for the genuine report which is tending to the connection record for MySQL daemon measure as follows:

• root@hostname ~# disc/var/run/mysqld/
• root@hostname ~# ls
• Pid MySQL. sock MySQL. sock. Lock
• root@hostname ~#

As shown by the above yield, the report doesn’t exist. That is the explanation the relationship with MySQL Database Server is continually failed. Even though the connection cooperation is done through the default port of ‘3306’.

1. The effort to restart the cooperation and trust that it will handle the issue.

• root@hostname ~# systemctl stop MySQL
• root@hostname ~# systemctl start MySQL
• root@hostname ~# mysql – uroot
• Slip-up 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
• root@hostname ~#

1. Unfortunately, the above cycle moreover wraps up in disappointment. Progress forward the movement for handling the issue, just check the MySQL Database arrangement record. In the wake of really investigating the report course of action, it doesn’t fit in any way shape, or form. Eventually, going through hours for changing the arrangement records, nothing happens.

For the reason happens above, check the right game plan before to see which MySQL Database Server arrangement is used by the running MySQL Database Server.

How might we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a ton upon the setting off reason. We should now see how our MySQL Engineers help customers with settling it.

1. Changing MySQL limits

Lately, one of our customers pushed toward us saying that he is getting a mix-up as the one showed underneath while he is efforting to interface with the MySQL server.

Along these lines, our Engineers checked thoroughly and found that the connect timeout regard was set to two or three minutes. Thusly, we extended it to 10 in the MySQL arrangement record. For that, we followed the means underneath:

First thing, we opened the MySQL plan archive at, etc/MySQL/my.cnf

Then, we searched for connect timeout and set it as:

• connect timeout=10

Then, we had a go at partner with MySQL server and we were viable.

Additionally, it requires the genuine setting of the variable max_allowed_packet in the MySQL arrangement record also. While efforting to restore the landfill record in GB sizes, we increase the value to a higher one.

2. Disabled person Access limits

This slip-up in like manner appears when the host approaches impediments. In such cases, we fix this by adding the client’s IP in, etc/hosts. Allow or license it in the server firewall.

Similarly, the error can happen as a result of the detachment of the server. Lately, in a similar case, the issue was not related to MySQL server or MySQL settings. We did a significant tunnel and found that high association traffic is causing the issue.

Exactly when we checked we found that an unconventional communication running by the Apache customer. Thusly, we killed that, and this good misstep.

3. Growing Server Memory

Last and not least, MySQL memory apportioning furthermore transforms into a basic factor for the slip-up. Here, the server logs will have related segments showing the lacking memory limit.

Subsequently, our Dedicated Engineers decline the innodb_buffer_pool size. This reduces the memory segment on the server and fixes the slip-up.

Checking the MySQL Database Server configuration used by the running MySQL Database Server

In the past fragment or part, there is a need to search for the real plan report used by the running MySQL Database Server. It is essential to guarantee that the plan record used is the right one. Thusly, every change can invite the right impact on dealing with the mix-up issue. Coming up next is the movement for searching for it:

1. Truly check out the once-over of the assistance first by suggesting the running framework. In the past part, the running framework is the ‘MySQL one. Execute the going with request guide to list the open running cycle:

• systemctl list-unit-reports | grep MySQL

The yield of the above request plan for a model is in the going with one:

• user hostname: ~$ systemctl list-unit-archives | grep MySQL
• service horrendous
• Service horrendous
• user hostname: ~$

1. Then, at that point, truly investigate the substance of the help by executing the going with the request. Pick the right help, in this particular circumstance, it is ‘MySQL. service’:

• user hostname: ~$ systemctl cat myself. service
• #/lib/system/structure/Mysql.service
• # MySQL systemd organization record
• [Unit]
• Description=MySQL Community Server
• After=network. Target
• [Install]
• Wanted by=multi-user. Target
• [Service]
• Type=forking
• User=mysql
• Group=mysql
• PIDFile=/run/mysqld/mysqld. Pid
• PermissionsStartOnly=true
• ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
• ExecStart=/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• Timeouts=600
• Restart=on-dissatisfaction
• Runtime Directory=mysqld
• RuntimeDirectoryMode=755
• LimitNOFILE=5000
• user hostname: ~$

1. The record obligated for starting the help is in the archive ‘/usr/share/MySQL/MySQL-systems-start’ according to the yield message above. Coming up next is the substance of that record which is only fundamental for it:

• if [! – r, etc/MySQL/my.cnf]; then,
• resonation “MySQL arrangement not found at, etc/MySQL/my.in. Assuming no one minds, make one.”
• leave 1
• fi
• …..

1. Resulting in truly taking a gander at the substance of the report ‘/, etc/MySQL/my.on, obviously, it isn’t the right record. Accordingly, to be more exact, there are other ways to deal with find the planned archive used by the running MySQL Database Server. The reference or the information exists in this association. Hence, according to the information in that association, basically perform the going with request guide to get the right one. It is forgetting the cycle ID and the right MySQL Database Server running collaboration:

• root@hostname ~# netstat – tulpn | grep 3306
• tcp6 0:3306: * LISTEN 21192/mysqld
• root@hostname ~# ps aux | grep 21192
• root@hostname ~# ps aux | grep 21192
• mysql 21192 0.2 0.1 3031128 22664? Sl Sep16 1:39/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• root 25442 0.0 23960 1068 pts/20 S+ 01:41 0:00 grep 21192
• root@hostname ~#
• Ensuing to getting the right running cycle, do the going with the request of ‘trace file_name_process’:
• root@hostname ~# album/usr/bin/
• root@hostname ~# strace. /mysqld
• Coming up next is fundamental for the yield of the request:
• detail, etc/my.cnf”, 0x7fff2e917880) = – 1 ENOENT (No such archive or vault)
• detail, etc/mysql/my.cnf”, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/my.cnf”, O_RDONLY) = 3
• fstat (3, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
• brk(0x35f6000) = 0x35f6000
• read (3, “#n# The MySQL data base server co”…, 4096) = 839
• openat (AT_FDCWD, “/, etc/mysql/conf. d/”, O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4
• fstat (4, {st_mode=S_IFDIR|0755, st_size=4096, …}) = 0
• get dents (4, /* 4 entries */, 32768) = 120
• get dents (4, /* 0 entries */, 32768) = 0
• close (4) = 0
• detail, etc/mysql/conf.d/mysql.cnf”, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/conf.d/mysql.cnf”, O_RDONLY) = 4
• fstat (4, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
• read (4, “[mysqld]nn# Connection and Three”…, 4096) = 629
• read (4, “”, 4096) = 0
• close (4) = 0
• detail, etc/mysql/conf.d/mysqldump.cnf”, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/conf.d/mysqldump.cnf”, O_RDONLY) = 4
• fstat (4, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
• read (4, “[MySQL dump] nquicknquote-namesnma”…, 4096) = 55
• read (4, “”, 4096) = 0
• close (4) = 0
• read (3, “”, 4096) = 0
• close (3) = 0
• detail (“/root/.my. cnf”, 0x7fff2e917880) = – 1 ENOENT (No such record or list)

The right one is finally in ‘/, etc/MySQL/conf.d/mysql.cf. Resulting in truly investigating the substance of the record, it is an empty archive. This is its essential driver. There has been some update and inverse present type of the MySQL Database Server, it making some disaster area the MySQL Database Server. The plan is essentially to fill that empty plan record with the right arrangement. The reference for the right arrangement of MySQL Database Server exists in this association. Restart the MySQL Server again, the above error issue will be tended to.

November 4, 2017 | Posted in SQL

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.

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.

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!

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.