MySQL Bugs: #87630: Error 2013 lost connection to mysql server during query When Remotely Connected

Bug #87630	Error 2013 lost connection to mysql server during query When Remotely Connected
Submitted:	31 Aug 2017 14:48	Modified:	11 Sep 2017 21:10
Reporter:	Marc Long	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.6.36	OS:	Windows (Windows Server 2012 R2, 2016 Datacenter)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
We have an interesting problem, whereby certain queries when executed via remote connection through p2p tunnel to the database are immediately getting "Error 2013 lost connection to MySQL server during query". The statement can be executed from MySQL Workbench or Visual Studio and the problem will surface. When we encounter the issue, we can run a simple SELECT against one of the tables in the problem query, then run the problem query and it works. If I then close my connection to MySQL, reopen the connection, re-run the problem query I have the problem again. ALSO INTERESTINGLY-if we RDP to the server, login as the same user and run the exact same query from the server, we have no problems. Additionally, if we execute the problem query via remote connection on the same domain NOT using ptp tunnel, we do not encounter the issue. I have researched and tested this as far as I am able, trying various things such as:
-changing several my.ini settings like max_allowed_packet, interactive_timeout, wait_timeout, disabling sql_mode, and others
-Confirmed resource utilization is not an issue during problem
-Tested with clients "on the wire" vs wireless
-Looked at Windows Firewall settings
-Adjusted timeout settings in MySQL workbench
-Rebuilt the tables involved with a null ALTER
-Confirmed no network disruption when problem is occurring by observing continuous ping of the server
-Noted that the counter for  SHOW GLOBAL STATUS LIKE 'Aborted_connects';  does not increase after an incident
-Followed the advice of numerous articles/blogs on the subject

ALL to no avail.  The problem was reported to me 4 days ago, but could have been happening prior and just not been reported. We have not had a recent update to MySQL Server, Workbench, no recent change to network infrastructure.  

I will be happy to share my.ini settings if needed, whether from the client side or the server side.

How to repeat:
#Execute the following via remote connection through p2p tunnel to the database:

USE our_schema;

SELECT DISTINCT GROUP_CONCAT(' ', (SELECT name FROM tbl1 WHERE id = sco_id) 
ORDER BY sco ASC) AS combos, 
GROUP_CONCAT(CAST(sco_id AS CHAR) 
ORDER BY sco ASC) AS 
combos2 FROM tbl2 
WHERE sco IN (101, 102, 103, 106, 109, 110) GROUP BY cust_id ORDER BY combos;

#Will immediately get "Error 2013 lost connection to MySQL server during query"
#Now run one or both of the following:
SELECT * FROM tbl1;
SELECT * FROM tbl2;

#Re-run the problem query and it will successfully return results
#Close the connection to MySQL Server, reconnect, re-run the problem query
#and we get the lost connection error.

#Run the problem directly on the server via RDP, no issues
#Run the problem query from a different client on the same domain as the server,
#no issues.

Suggested fix:
No clue.

Hi,

What p2p tunnel are you using? 

This type of errors is common on "bad network" or for example if you are behind "double nat", client will lose connection to server and you will get this error. Repeating the query (like you do) will work as newly established connection is up and running. This happens because connection is partially lost due to inactivity (and if you run a long running command you will have this inactivity). The "partial" part of the loss is that you still managed to get the reply from the server but you can't send new data.. You can workaround this problem with double nat with ssh for e.g. by introducing keepalive packets. Now for windows and p2p tunnel, you have to tell me which one you are using so that I can see if this is the same issue, but in any way the solution needs to be on the tunnel side, nothing mysql can do about it.

all best
Bogdan

Hi,

The P2P tunnel is between a WatchGuard firewall and Cisco ASA firewall. This tunnel is kept alive and the issue occurs when the tunnel is in full use and everything else has full connectivity. I am not sure what you mean by which tunnel we are using. This VPN has nothing to do with Windows as it is on the network level and is OS independent. This is not a VPN client connection and there is no double NAT.
The connection is not "partially lost due to inactivity" as you said; this happens immediately upon connecting to the server.  Also, we are not repeating the query to get it to work...that doesn't work at all. We are running a simpler SELECT statement against one of the tables in the problem query, and that seems to have a temporary positive effect, until we reconnect to the server and the problem immediately starts over again.

Thanks,
Marc

Hi,

> This tunnel is kept alive and the issue occurs when the tunnel is in full use 
> and everything else has full connectivity.

So this happens "only then" (when tunnel is loaded) or it happens "always" ?

> I am not sure what you mean by which tunnel we are using.

Well see, I am using mysql trough openvpn and it works perfectly; I use mysql trough pptp and it works perfectly; I use mysql trough pvpn and it works perfectly. I am asking what tunnel are you using so I can try to reproduce problem you are experiencing that we can then debug and figure out if it has to do with MySQL or not. As you can see MySQL connector works normally trough a number of vpn's.

> This VPN has nothing to do with Windows as it is on the network 
> level and is OS independent. This is not a VPN client connection 
> and there is no double NAT.

What you are saying then is 
 - when you connect to your MySQL server trough one part of your network infrastructure, it works ok
 - when you try to connect from other part of your infrastructure it does not work properly

and you believe bug is in MySQL?

> The connection is not "partially lost due to inactivity" as you said; this happens
> immediately upon connecting to the server.  Also, we are not repeating the query
> to get it to work...that doesn't work at all.

I apologize for not understand your thoughts properly but I can only work with info I have.
With info I have I cannot reproduce the problem, and with experience I have I can say this is 99% not MySQL bug but a problem with your infrastructure. What type of problem you have to see with your network engineer. Monitoring traffic to see how come the disconnection happens is attm the way to go. The only other thing is to check the MySQL logs and see if there's anything in the logs that can give info to why that connection was dropped.

all best
Bogdan

Bogden, please see my responses below:

So this happens "only then" (when tunnel is loaded) or it happens "always"?

Yes, it happens always as the tunnel is always on.

Well see, I am using mysql trough openvpn and it works perfectly; I use mysql trough pptp and it works perfectly; I use mysql trough pvpn and it works perfectly. I am asking what tunnel are you using so I can try to reproduce problem you are experiencing that we can then debug and figure out if it has to do with MySQL or not. As you can see MySQL connector works normally trough a number of vpn's.

My network mgr answered this one: All of those types are client-based and not network based. Our tunnel is between 2 firewalls (hence P2P) and just seen by the client as a network just as if it were an ethernet connection to another subnet. Windows does not know it is a VPN.

What you are saying then is
- when you connect to your MySQL server trough one part of your network infrastructure, it works ok.

When we connect from client to server within the domain, the problem query works fine.

- when you try to connect from other part of your infrastructure it does not work properly.

When we use p2p tunnel across domains, the problem query does not work, but other queries do work.

and you believe bug is in MySQL?

Yes; it doesn’t make sense from a network perspective why a specific query, ‘query A’ would immediately fail over p2p, then we run ‘query B’ and ‘query C’ which successfully complete, then we re-run ‘query A’ and it now successfully completes. Then we reconnect to MYSQL server and ‘query A’ immediately begins failing again, and will consistently continue to fail until we repeat the steps previously mentioned. NOTE- simply rerunning ‘query A’ does not work; we have to run ‘query B ’ and ‘query C’ after ‘query A’ fails to get ‘query A’ to work the next time. I spelled this all out in my instructions on how to recreate the issue.

I apologize for not understand your thoughts properly but I can only work with info I have.
With info I have I cannot reproduce the problem, and with experience I have I can say this is 99% not MySQL bug but a problem with your infrastructure. What type of problem you have to see with your network engineer. Monitoring traffic to see how come the disconnection happens is attm the way to go. The only other thing is to check the MySQL logs and see if there's anything in the logs that can give info to why that connection was dropped.

So far my network mgr is unable to find anything with our infrastructure that would be a potential cause. I am not seeing any sign anywhere of dropped connections. I will be happy to share any logs with you; just let me know what you need.

Hi,

interesting issue for sure, but I'm still pretty sure it's unrelated to the MySQL and has something to do with network.

> All of those types are client-based and not network based.

Yes, but I have also many network-network tunnels here, I even run cluster tests in 2 locations linked with a router to router vpn and till recently every connection I had from home was going trough such tunnel (the way adsl is routed from adsl provider to internet provider for e.g.) .. never a problem. 

>  ‘query A’ would immediately fail over p2p

So you are saying "only this one query fails", all other queries work ok?

how about lowering the MTU on both client and server, just for a test, should be easy and non intrusive test?

> I will be happy to share any logs with you; just let me know what you need.

That will need a network, not a mysql expert to solve. sniffing mysql traffic when this happens and then investigating those logs should explain how the connection fails; also, I asked but I missed the answer, is there anything visible in mysql log when this "Disconnect" happens?

all best
Bogdan

Bogdan,

Can we please set up a conference call with you and my network engineer to discuss this?

Thanks,
Marc Long

Hi,

That is not part of the bug processing system. We can certainly help you solve your problem but as a part of a support effort, not trough bug report. Contacting our Support team is certainly the proper way to go about it (and then you can have audio conference, shared shell and all the chabang needed to fix the issue)

As I said
 - you connect to mysql trough network using "path X" and it works ok
 - you connect to mysql trough network using "path Y" and it does not work

It is a problem in path Y, not in MySql. *especially* if in path Y you have "invisible" network changes, so there's nothing mysql client/server should know / do about it, something is wrong with network... is it MTU issue, or high layer routing is messing with packets, or smart firewall or.. in any case I don't see how can mysql know "whats in the path".. 

all best
Bogdan

Hi Bogdan,

With all due respect, I feel like we are going in circles and just need to have a real time conversation between MySQL Support, me and my Network mgr. We still have Enterprise support for a license we purchased, but are not using for this issue; should I contact them, or is there some other support line you can direct me to?

Thanks,
Marc

Hi Marc,

> We still have Enterprise support for a license we purchased, 

> should I contact them,

Yes, please! With support you can have a conf. call, webmeeting... The bugs database is not a support channel.

Thanks
Bogdan

Ok, I will open a case with Enterprise support.

Thanks