2013 - Lost connection to MySQL server during query

User, date Message
Written by swaiba
2 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
2 years ago
3958 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
2 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
6 months ago
29 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
6 months ago
3958 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
6 months ago
29 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
6 months ago
3958 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
6 months ago
29 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
6 months ago
3958 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
6 months ago
29 posts since Thu, 08 Nov 12 		Email sent!
Written by combuilder
6 months ago
29 posts since Thu, 08 Nov 12 		Anse.
Did you receive my email with the data?
Written by ansgar
6 months ago
3958 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
6 months ago
29 posts since Thu, 08 Nov 12 		No problem. Take the time you need! Of course! smile
Written by ansgar
6 months ago
3958 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
6 months ago
29 posts since Thu, 08 Nov 12 		Have you tried to run the stored procedure?
Written by ansgar
6 months ago
3958 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
6 months ago
3958 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
6 months ago
29 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
6 months ago
3958 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
6 months ago
29 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
6 months ago
3958 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
6 months ago
29 posts since Thu, 08 Nov 12 		Yes, only in heidiSQL
Written by combuilder
6 months ago
29 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
6 months ago
3958 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
6 months ago
3958 posts since Fri, 07 Apr 06 		Got it. libmysql.dll v5.5.8 fixes that crash.
Written by combuilder
6 months ago
29 posts since Thu, 08 Nov 12 		Perfect! What do we have to do to get this lib?
Written by jfalch
6 months ago
223 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
6 months ago
3958 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
6 months ago
29 posts since Thu, 08 Nov 12 		That's sound really good!
@Anse: I think your plan will be fine!
Written by combuilder
6 months ago
29 posts since Thu, 08 Nov 12 		How can i know the version of libmysql.dll?
Written by ansgar
6 months ago
3958 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
6 months ago
3958 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
6 months ago
13 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
6 months ago
29 posts since Thu, 08 Nov 12 		I still se version 5.2.10 of libmysql after updating!
Written by combuilder
6 months ago
29 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
6 months ago
13 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
6 months ago
29 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
5 months ago
3958 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
5 months ago
29 posts since Thu, 08 Nov 12 		Great!!
 

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