distal-attribute
distal-attribute
distal-attribute
distal-attribute

2013 - Lost connection to MySQL server during query

User, date Message
Written by swaiba
3 years ago
Category: General
13 posts since Fri, 14 Jan 11
I am getting this all the time at the moment while trying to import a database... I've free'd up tons of memory and do nothing else with the machine... but at some point it just stops with this error.

I have exported using heidisql and am inporting to an empty database locally
Written by ansgar
3 years ago
5023 posts since Fri, 07 Apr 06
Probably increase the maximum allowed packet size right before you import that file:

SET @@global.max_allowed_packet = 1048576*10;


What revision of HeidiSQL was used to create the export file?
Written by swaiba
3 years ago
13 posts since Fri, 14 Jan 11
I used both "stable 6" and "stable 6 + updates" - I'll try again with your suggestion...
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
I am receiving the same error when doing a long query, aprox after 113 seconds!

I have a TCP-IP connection to a localhost!

It doesn't matter if it's a query or if I run a procedure or function that exceed 2 minutes!

I have SET @@global.max_allowed_packet = 1048576*10 but the problem remains.

I have seen other solutions and i download the lastest libmysql.dll, but the warning still remains.

Can someone tell me how should i configure HeidiSql in order to allow long querys without lossing connection?

If i did the query on a MySql Command Line, i have no problem! neither with MySQL WorkBench!

Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
This thread is one year old, btw. Well, however.

I highly guess your connection is killed due to a short "interactive_timeout" variable? Look it up in HeidiSQL under "Host" > "Variables".
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
After SHOW VARIABLES LIKE '%_timeout', my values are:

"Variable_name" "Value"
"connect_timeout" "10"
"delayed_insert_timeout" "300"
"innodb_lock_wait_timeout" "50"
"innodb_rollback_on_timeout" "OFF"
"interactive_timeout" "28800"
"lock_wait_timeout" "31536000"
"net_read_timeout" "30"
"net_write_timeout" "60"
"slave_net_timeout" "3600"
"wait_timeout" "28800"

I hope this help!
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Ok, interactive_timeout is what I was looking for, this is 28800s = 8 hours. So, this cannot be the problem.

I had recently fixed an error which causes exactly your error, when executing multiple queries in one packet. Which revision of HeidiSQL are you running?
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Good morning!
I have got the error in all recent versions, because i have the autoupdated activated! Actual version is r4219, but i have been suffering this problem in previous versions.

I can provide you a link with a database, in order to test yourself, if you don't have a big database!

Thank you very much for your help, and for your software!
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
A link will be fine. That way I can also check if the server causes the problem, if not HeidiSQL. But do not post your credentials here in the forum. Send me an email to the email mentioned here in the webpage footer. Thanks!
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Email sent!
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Anse.
Did you receive my email with the data?
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Ah sorry for the delay, yes got it. Gimme one or two days to get some time for it.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
No problem. Take the time you need! Of course! smile
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Just imported that file here. Having no problems with some disconnect so far, while browsing through the table data, switching to another one, opening the proc, again selecting some thousand data rows and so on.

You said you would send me a link/credentials to your server, so I can see what's up with the server. I don't know if that's ok for you, but if you want I would take a look. Again, just for safety reasons: do not post credentials here in the forum. Thanks!
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Have you tried to run the stored procedure?
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Not yet, until a minute ago. Takes some time untils it returns with an "EExternalException" in the libmysql function mysql_next_result(). Call stack says nothing helpful here. I don't guess you meant that exception, you talked about "Lost connection". But that could also be the same cause, with a different version of libmysql.dll on your side. However, that procedure does something very weird. Did you check all code in it?
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
You should execute the queries in your procedure one after the other, to track down where the connection breaks.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Yes, i've cheked the SP code and it does what it should: take a lot of data and calculate agregates!

In the 4th answer of this topic i write that the error raise after 113 seconds, aprox. Not inmidiatly!

I think you got the error, the "EExternalException" in the libmysql, but now it's time to ask why or what is giving the error.

I don't know the functions on libmysql, so i cannot help you more than i can.
david
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Well, I know what mysql_next_result() does, but I have no clue why it breaks on your result. That's why I asked to track down the error to one of the queries in that procedures. It's very likely that only one of these queries is causing that, not just all or the procedure itself. Can you do that? Just extract the queries as simple queries, and execute them one after the other in a query tab.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
If you run the code on MySQL WorkBench, you can get the SQL sentences. Here you have them:

DELETE FROM test.datahorario;
DELETE FROM test.datadiario;
DELETE FROM test.datamensual;
INSERT INTO test.datahorario SELECT DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00') AS fecha, dc.dia, dc.anno, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo, AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado=0 GROUP BY dc.idinstalacioncontador, dc.idparametro, DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00') ORDER BY fecha;
INSERT INTO test.datadiario SELECT dc.dia, dc.anno, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo , AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado = 0 AND dc.valor > 0 GROUP BY dc.idinstalacioncontador, dc.dia, dc.idparametro ORDER BY dia;
INSERT INTO test.datamensual SELECT STR_TO_DATE(CONCAT(dc.anno, '-',month(dc.dia),'-01'),'%Y-%m-%d') as dia, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma,
MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo , AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado=0 AND dc.valor > 0
GROUP BY dc.idinstalacioncontador, STR_TO_DATE(CONCAT(dc.anno, '-',month(dc.dia),'-01'),'%Y-%m-%d'), dc.idparametro ORDER BY dia ASC;


After nunning the sentences, you can see that the disconnection occurs during query 4!
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06

combuilder wrote: After nunning the sentences, you can see that the disconnection occurs during query 4!



But only in HeidiSQL. Not in Workbench, or? Just to be sure...
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Yes, only in heidiSQL
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
This is what is happening on HeidiSQL after running the 6 querys i write before:


DELETE FROM test.datahorario;
DELETE FROM test.datadiario;
DELETE FROM test.datamensual;
INSERT INTO test.datahorario SELECT DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00') AS fecha, dc.dia, dc.anno, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo, AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado=0 GROUP BY dc.idinstalacioncontador, dc.idparametro, DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00') ORDER BY fecha;
/* Connection to 127.0.0.1 closed at 2012-11-27 11:29:09 */
/* Connecting to 127.0.0.1 via MySQL (TCP/IP), username root, using password: Yes ... */
/* Connected. Thread-ID: 2 */

SHOW STATUS;
SHOW VARIABLES;
USE `test`;
INSERT INTO test.datadiario SELECT dc.dia, dc.anno, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo , AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado = 0 AND dc.valor > 0 GROUP BY dc.idinstalacioncontador, dc.dia, dc.idparametro ORDER BY dia;
INSERT INTO test.datamensual SELECT STR_TO_DATE(CONCAT(dc.anno, '-',month(dc.dia),'-01'),'%Y-%m-%d') as dia, dc.idparametro, dc.idinstalacioncontador, AVG(dc.valor) AS prom, SUM(dc.valor) AS suma,
MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo , AVG(dc.valor) as valor FROM test.data dc WHERE dc.anulado=0 AND dc.valor > 0
GROUP BY dc.idinstalacioncontador, STR_TO_DATE(CONCAT(dc.anno, '-',month(dc.dia),'-01'),'%Y-%m-%d'), dc.idparametro ORDER BY dia ASC;
/* Affected rows: 166.048 Found rows: 0 Warnings: 0 Duration for 6 queries: 32,499 sec. */

As you can see, thre is a disconnection!
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
I have only 1 disconnect, exactly what you describe:
"SQL Error (2013): Lost connection to MySQL server during query"

Also, I don't get that error in my local MariaDB 5.5.8 server. I guess if I replace the libmysql.dll in the Heidi directory with the one from the 5.5.8 server, I won't get that crash. Going to check that.
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Got it. libmysql.dll v5.5.8 fixes that crash.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Perfect! What do we have to do to get this lib?
Written by jfalchMoney, Euro
2 years ago
407 posts since Sat, 17 Oct 09
for example, download mariadb server package from here; install somewhere, get libmysql.dll from lib/ subdirectory, deinstall again.
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Exactly, that's the best solution for you. I'm also planning to add the newest available stable lib to the HeidiSQL installer for the next release.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
That's sound really good!
@Anse: I think your plan will be fine!
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
How can i know the version of libmysql.dll?
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Hover your mouse in HeidiSQL over the status bar panel, which says "MySQL x.x". A balloon hint will display a handfull of details about the connection, watch out for "Client library".
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
I found a v6.0.2 libmysql on the MySQL site, but it has a file age from august/2009. However, it does not crash on the above scenario, so I'm going to use that for the coming release installer.
Written by maeeh
2 years ago
24 posts since Wed, 01 Dec 10
Hi All,

just installed the actual version and found that i am allways disconnected after 24-26 seconds while querying with a join on larger tables.


select NOW();
select
count(*) as '# of page impressions',
app_name as 'Application',
date_format(userl_last_update,'%Y') as 'year'
from user_logging
left join application using (app_id)
group by userl_user_session,date_format(userl_last_update,'%Y');
/* Connection to dbslave-sct.dhl.com closed at 2012-11-29 10:33:06 */
/* SQL Error (0): Lost connection to MySQL server during query */
/* Affected rows: 0 Found rows: 13 Warnings: 0 Duration for 3 of 4 queries: 0,281 sec. */



Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
I still se version 5.2.10 of libmysql after updating!
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
I have replaced the libmysql with the one installed on MySQL 5.5.28, (libmysql.dll is now version 5.5.28).

Now, everything works! Even the procedure Groupying!!

NOTE: MySQL 5.528 is the version I have installed on my Localhost, and that's where i got the file!
Written by maeeh
2 years ago
24 posts since Wed, 01 Dec 10
Adding to my last post.

this seems only to happen if multiple queries are performed. If only one query is executed it works fine
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
In my case, and this the 5.5.28 version of libmysql, i hava no problem with more than one query!
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
Today I realized that this keep-alive ping was executed during long running queries, which can easily break the whole connection. r4279 changes this keep-alive timer to be left out during queries. I guess even the above mentioned libmysql.dll update is not required any more.
Written by combuilder
2 years ago
30 posts since Thu, 08 Nov 12
Great!!
Written by ansgar
1 year ago
5023 posts since Fri, 07 Apr 06
r4521 updates the libmysql.dll in the HeidiSQL installer from a probably experimental version (ConnectorC 6.0.2) to a MariaDB v5.5.32 version.

It's practically very unlikely, but if the above mentioned disconnects happen again, please shout.
Written by ansgar
1 year ago
5023 posts since Fri, 07 Apr 06
See also issue #3262.
Written by ansgar
12 months ago
5023 posts since Fri, 07 Apr 06
I had to exchange libmysql again now in r4531, to fix a "malformed packet" error. Please shout if you get connection problems again now.
 

Please login to leave a reply, or register at first.