Networking Issue With Large Queries

[shermo]

When running large queries, SQLyog will get stuck in running mode, and is unresponsive, even after pressing the ‘stop execution’ button. When I monitor what’s happening on the server, the query has successfully completed, but SQLyog is presenting that it is still running. I think this may be networking related.

 

As such, I thought this might be fixed through modifying the Session Idle Timeout and Keep-Alive Interval settings when making a new connection, but I’ve tried a few different settings and none of them fix them problem. With some settings I get a “error 1094” error, whereas with other settings there is no error, but it still becomes unresponsive.

 

I’ve also raised the ‘max_allowed_packet’ size on the server to 512MB so that SHOW GLOBAL VARIABLES LIKE ‘max_allowed_packet’ returns 536870912. However, I think this is an issue with the client, as it has only started happening since upgrading SQLyog versions.

 

I am using Community v12.09 (64 bit), but I had the same problem with the 32 bit version. I can try it on our paid version if that will make any difference.

 

Thanks

[Abhishek Kumar Pandey]

Hi,

 

Community version and paid version are same in this context so changing to paid version will not make any difference. 

When you use stop button of SQLyog it kills the current thread and create a new connection which is copy of killed connection. Now as you are getting error 1094 it means that the thread which SQLyog is trying to kill, no longer exists. So I am curious to know whether there are any other process/user which kills the thread on server? May be a deadlock detector kills the thread if thread is involved in dead lock.

There haven’t been any change in this basic functionality of SQLyog for long time. So please also tell what was the previous version of SQLyog in which this issue wasn’t appearing. But I really doubt that issue is of client.

[peterlaursen]

It is not unusual with (cheap) shared hosting that they will kill queries runnng for longer than a specific time. This is in order to protect other users on the same server against a de facto ‘denial of service’ caused by poorly written queries by one user (examples of this: 1) call to a routine entering an infinite loop 2) full cartesian joins (explicit or implict) of huge tables as it may happen for instance when using a subquery on MySQL versions that have poor subquery optimization (what all servers older than MySQ 5.6 have).).

[shermo]

The thread successfully runs on the server, so it’s not killed, but it does stop (since it’s finished). Perhaps that would present similar symptoms? It’s a local server, not a hosted one, so if it’s a server config problem I would be able to fix it, perhaps.

 

I’ve rolled back to 8.6. Will update if problems persist.

 

Thanks, Dave

[shermo]

No problems with v8.6. I’ve got the Session Idle Timeout set to 28800 for both. Keep-Alive Interval is blank in newer version, and older version doesn’t have that option.

[Abhishek Kumar Pandey]

 I am not able to reproduce it. Please tell us, how long the query executes. Also tell us your server version (execute select version()).

[Author]

Posts