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

2013 - Lost connection to MySQL server during query

swaiba posted 4 years ago in General
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
ansgar posted 4 years ago
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?
swaiba posted 4 years ago
I used both "stable 6" and "stable 6 + updates" - I'll try again with your suggestion...
combuilder posted 2 years ago
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!

ansgar posted 2 years ago
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".
combuilder posted 2 years ago
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!
ansgar posted 2 years ago
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?
combuilder posted 2 years ago
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!
ansgar posted 2 years ago
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!
combuilder posted 2 years ago
Email sent!

combuilder posted 2 years ago
Anse.
Did you receive my email with the data?
ansgar posted 2 years ago
Ah sorry for the delay, yes got it. Gimme one or two days to get some time for it.
combuilder posted 2 years ago
No problem. Take the time you need! Of course! smile
ansgar posted 2 years ago
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!
combuilder posted 2 years ago
Have you tried to run the stored procedure?
ansgar posted 2 years ago
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?
ansgar posted 2 years ago
You should execute the queries in your procedure one after the other, to track down where the connection breaks.
combuilder posted 2 years ago
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
ansgar posted 2 years ago
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.
combuilder posted 2 years ago
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!

ansgar posted 2 years ago

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...
combuilder posted 2 years ago
Yes, only in heidiSQL
combuilder posted 2 years ago
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!
ansgar posted 2 years ago
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.
ansgar posted 2 years ago
Got it. libmysql.dll v5.5.8 fixes that crash.
combuilder posted 2 years ago
Perfect! What do we have to do to get this lib?
jfalch posted 2 years ago
for example, download mariadb server package from here; install somewhere, get libmysql.dll from lib/ subdirectory, deinstall again.
ansgar posted 2 years ago
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.
combuilder posted 2 years ago
That's sound really good!
@Anse: I think your plan will be fine!
combuilder posted 2 years ago
How can i know the version of libmysql.dll?

ansgar posted 2 years ago
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".
ansgar posted 2 years ago
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.
maeeh posted 2 years ago
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. */



combuilder posted 2 years ago
I still se version 5.2.10 of libmysql after updating!
combuilder posted 2 years ago
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!
maeeh posted 2 years ago
Adding to my last post.

this seems only to happen if multiple queries are performed. If only one query is executed it works fine
combuilder posted 2 years ago
In my case, and this the 5.5.28 version of libmysql, i hava no problem with more than one query!
ansgar posted 2 years ago
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.
combuilder posted 2 years ago
Great!!
ansgar posted 1 year ago
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.

ansgar posted 1 year ago
See also issue #3262.
ansgar posted 1 year ago
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.