2013 - Lost connection to MySQL server during query

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

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?

I used both "stable 6" and "stable 6 + updates" - I'll try again with your suggestion...

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!

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".

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!

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?

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!

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!

Email sent!

Anse.
Did you receive my email with the data?

Ah sorry for the delay, yes got it. Gimme one or two days to get some time for it.

No problem. Take the time you need! Of course!

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!

Have you tried to run the stored procedure?

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?

You should execute the queries in your procedure one after the other, to track down where the connection breaks.

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

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.

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!

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...

Yes, only in heidiSQL

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!

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.

Got it. libmysql.dll v5.5.8 fixes that crash.

Perfect! What do we have to do to get this lib?

for example, download mariadb server package from here; install somewhere, get libmysql.dll from lib/ subdirectory, deinstall again.

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.

That's sound really good!
@Anse: I think your plan will be fine!

How can i know the version of libmysql.dll?

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".

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.

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. */

I still se version 5.2.10 of libmysql after updating!

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!

Adding to my last post.

this seems only to happen if multiple queries are performed. If only one query is executed it works fine

In my case, and this the 5.5.28 version of libmysql, i hava no problem with more than one query!

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.

Great!!

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.

See also issue #3262.

I had to exchange libmysql again now in r4531, to fix a "malformed packet" error. Please shout if you get connection problems again now.