Sqlyog On Wine And Connection Timeouts

[jtarbox]

I've been using SQLYog for quite some time and we recently upgraded to an enterprise site license. At that time I jumped from an early version of 8.x community to 9.1 commercial. As I'm doing development, testing, and administration on many mysql servers there is often times where I will have a connection open within sqlyog for hours before getting back to it to use it for something else. In these cases, I've noticed the application think like it's running a query while it isn't (confirmed from the process list on the mysql server). The application is stuck per se, it thinks it's doing a query and I cannot stop it's execution. Nor can I even close the application, as it gives me a pop-up saying that it's busy running a query. Sometimes, it will come back and finally finish the query, but the reconnect mechanism is almost 10-20 minutes for a simple query on any size database (I've tested this on small ]<1mb] or large dbs [>12tb])

I never had this problem with the older community version, but I've even updated to 9.2 and it's still occurring. I do not know if it's the version, or wine, or what. It seems I can reproduce this just by letting a connection sit idle over the connection timeout period for the server. (and altering the connection timeout setting on the server is not an option here.)

System Specs:

Fedora 15 (x86_64) – updated daily

Wine 1.3.24-1.fc15 (i686) – wine 64bit has issues with some apps so I keep this at 32bit

SQLYog 9.20 – downloaded today

Thoughts?

[peterlaursen]

Please tell:

1) what is the 'wait_timeout' setting on this server (execute “SHOW GLOBAL VARIABLES LIKE 'wait_timeout'”)

2 do you use (SSH or HTTP) tunnelling?

“I've noticed the application think like it's running a query”. Please tell how do you reach this conclusion? Because “it gives me a pop-up saying that it's busy running a query”? I think it is simply trying to reconnect (and that could then be an imperfection with the error message). Please read this Blog: http://www.webyog.co…ession-timeout/ . Are you pefectly sure that settings have not been changed on the server(s) affected?

Could you try to set the 'session timeout' to at least 28800 (=8 hours) in the SQLyog connection manager for affected connection(s) if it is not already?

[ashwin]

@jtarbox,

Are you connected to MySQL server through a VPN connection?

[jtarbox]

@ash/peter – This office is connecting through a vpn tunnel via hefty routing hardware. We have 6-9 folks in the office at any one time so it is always connected and active. If it was a bandwidth issue, I'd see it in other applications I use throughout the day. This office configuration has been this way for ages any did not occur with either community or enterprise versions of sqlyog in the 8.x revisions.

@peter – The wait_timeout is the default for mysql and cannot be changed. This is an enterprise level environment and the allowed DB changes are extremely limited. Also, yes, when the application is taking forever to do the query (aka, taking forever to reconnect before it does the query) I have attempted to just close the app and relaunch it, but when attempting to close the application it says it cannot due to a query running.

If I kill the process Id for sqlyog I can then relaunch the application and connect immediately and begin working. So it's not an issue of the connection to/from the sql server, it's a problem with the timeout/reconnect in v9.x

[peterlaursen]

“The wait_timeout is the default for mysql and cannot be changed”. Of coruse it cna be changed. Any client can SET SESSION wait_timeout = … (provided that server is 5.0.x or higher).

But we have a few similar reports wiht VPN connections. I am afraid we cannot do much about it. It is not an issue wiht our code, but with the VPN and/or the MySQL client code (VPN does not realize that a connection will need to be kept open even if ther is no activivty for hours and the mysql client does not detect that reconnection is required).

Anyway let us discuss ..

[jtarbox]

'peterlaursen' wrote:

“The wait_timeout is the default for mysql and cannot be changed”. Of coruse it cna be changed. Any client can SET SESSION wait_timeout = … (provided that server is 5.0.x or higher).

But we have a few similar reports wiht VPN connections. I am afraid we cannot do much about it. It is not an issue wiht our code, but with the VPN and/or the MySQL client code (VPN does not realize that a connection will need to be kept open even if ther is no activivty for hours and the mysql client does not detect that reconnection is required).

Anyway let us discuss ..

It's been a bit since I've been able to get back here, but yes, let us continue…

In regards to the timeout setting, I was presuming you were referring to changing the default on the mysql servers. Of course I can do this manually but that would get old fast as I'm working over 3-4 connections at once over several hours.

As to your thoughts on it being VPN, I'm in disagreement. There are 6-9 developers in the office at once, most using some form of windows and sqlyog who are not having this issue. On my fedora box I can easily continuously transfer files to/from our main office looping in a shell and still repeat this issue within sqlyog. The cisco VPN router is not the problem. I almost think that Sqlyog is not properly seeing the mysql server timeout disconnect and is attempting to run the query over a dead connection, and once that times out it then reconnects. Effectively killing 10-20 minutes of my time.

[peterlaursen]

I am not sure I understand everything you write. But ..

Did you try to specify a high 'session timeout' value in the SQLyog connection manager? Please see image [attachment=1640:timeout.jpg] . Using this option will NOT change the global value. If you don't have this option your version is old.

How can you say so categorically that the router (or another network component) is not a problem? We have this old blog http://www.webyog.com/blog/2009/09/02/“mysql-server-has-gone-away”-part-2-session-timeout/ where we as well as users that commented observed this problem with some 'networking routes' and not others. That was why we introduced this setting client side.

[cidr-fk]

I am having an issue that seems similar. I cannot close a connection tab in Sqlyog – when I click the cross on the tab, I get this error pop-up:

Could not close connection. Query(s) are being executed.

The server i am trying to connect to was unresponsive for several hours, but has been 'cleaned up', bad processes killed, all slaves are reconnected to it (after FLUSH HOSTS), and Sqlyog can open new connections to the server as well. Also, this particular sqlyog's connection thread has been manually killed on the mysql server.

BUT Sqlyog won't close the “hung” connection.

It has been hung for over half an hour. The server wait_timeout = 86400, but I believe Sqlyog should be able to detect that it had been disconnected from the server when the user tries to manually terminate its connection.

[peterlaursen]

We know the problem. It is not related to Wine.

Here is a simple test case that illustrates the problem:

Code:

— 1st connection

USE TEST;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 values (1)
LOCK TABLES t1 READ;

— 2nd connection

— a:
— execute from editor
USE TEST;
UPDATE t1 SET id = 2 WHERE id = 1;
— it hangs (waits for LOCK to be released), but I can stop query from the 'X* icon

— b:
— perform same operation from GUI (DATA or RESULT tab)
— still hangs and cannot be stopped
— switching connection tabs hangs SQLyog completely.

Bascially we have one thread in the GUI and (at least) one background thread per connection. In this case the GUI thread is “hijacked by” the irresponsive background thread. That is why 'a' above works and 'b' does not. And to kill the backgorund thread that “hijacks” the GUI a new connection (and thread) is required in order to execute mysql_kill(). And to start such a new GUI thread would be needed (as the existing one cannot be used) but there is only the possibility of one. It goes in circles! 🙁

All this is some kind of old architectural/design flaw that we cannot fix easily. It will require some months work for a skilled developer very well-versed in multithreading. Undoubtedly it is worth the effort (and we have copies of him!) , but we just have too many non-finished projects going on that we need to finish first. So (best guess) it will only be from around March next year that we can find resources for this.

(and my apology to the few people that over the last 5 years have reported this (I think we have around 10 such reports) where I did not reply properly as I did not understand at that time).

[cidr-fk]

Just FYI:

About 15 min after my earlier post today Sqlyog crashed. The screenshots of the two dialog boxes are attached:

– the “Could not close connection” that I have reported,

– the “SQLyog crashed”. Let me know if you need that .dmp file.

[peterlaursen]

Yes .. please let us have the dump file.

[Author]

Posts