Repeated loss of connection error on localhost

[expired user #6652]'s profile image [expired user #6652] posted 11 years ago in General Permalink
Starting this morning, I have received many loss of connection errors. I am running a localhost server and what I think is a fairly simple database - 21 tables, the smallest ((look-up table) being 1.2 KB - 9 rows) to the largest of 11 GB (36 million rows). I am trying to run a simple query to select distinct data from a single column in the largest table (doing some benchmarking). Below is a copy of the bug report.

I am really new to MySQL (using MariaDB) and it is through HeidiSQL that I have learned much - not yet enough though.

date/time : 2012-10-26, 10:45:44, 234ms
computer name : W****IL7
user name : p***ri
registered owner : Setup
operating system : Windows 7 x64 Service Pack 1 build 7601
system language : English
system up time : 17 minutes 39 seconds
program up time : 40 seconds
processors : 8x Intel(R) Core(TM) i7-2920XM CPU @ 2.50GHz
physical memory : 1614/4046 MB (free/total)
free disk space : (C:) 221.88 GB
display mode : 1680x1050, 32 bit
process id : $18f0
allocated memory : 71.26 MB
executable : heidisql.exe
exec. date/time : 2012-10-26 09:41
version : 7.0.0.4217
compiled with : Delphi XE
madExcept version : 3.0m beta 1
callstack crc : $60a75084, $cfa77d85, $cfa77d85
exception number : 1
exception class : EAccessViolation
exception message : Access violation at address 60A75084 in module 'libmysql.dll'. Read of address 00000088.

main thread ($15a4):
60a75084 libmysql.dll
60a6a31f libmysql.dll mysql_ping
00716ac8 heidisql.exe dbconnection 1446 +2 TMySQLConnection.Ping
00716c8a heidisql.exe dbconnection 1483 +1 TDBConnection.KeepAliveTimerEvent
0053524b heidisql.exe ExtCtrls TTimer.Timer
0053512f heidisql.exe ExtCtrls TTimer.WndProc
004ab6c8 heidisql.exe Classes StdWndProc
75de7885 USER32.dll DispatchMessageW
00575d73 heidisql.exe Forms TApplication.ProcessMessage
00575db6 heidisql.exe Forms TApplication.HandleMessage
005760e1 heidisql.exe Forms TApplication.Run
00802938 heidisql.exe heidisql 70 +21 initialization
75293398 kernel32.dll BaseThreadInitThunk

Any thoughts, or tips would be great.

Thank you.
ansgar's profile image ansgar posted 11 years ago Permalink
Since some recent commit, HeidiSQL automatically sends keep-alive pings each 20 seconds, and that's what is causing the crash here in libmysql.dll, probably because the connection is already lost before the ping is done. Though that should not crash at all... Hm. Is your libmysql.dll an older one? Please check that, you can download the right one here.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Thank you, I could not connect to that site from here (firewall issues) but it should not be that old, the laptop is 2 weeks old, and all software (MariaDB, HeidiSQL, Mysql) was downloaded and installed in the last two weeks. I just realized that this error started this PM, after I upgraded HeidiSQL to 7.0.0.4217. I was using the February Stable build before that. Which is embeded in MariaDB download.

I also just downloaded and installed mysql-connector-c-6.0.2-winx64.msi from the MySQL website since your message in an effort to get a newer libmysql.dll.

Thank you for the thoughts, ideas.
ansgar's profile image ansgar posted 11 years ago Permalink
Can you check with a different client, if you get disconnects as well? I could imagine your server disconnects you due to a short interactive_timeout or wait_timeout variable.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Thank you, I have grabbed MySQL workbench, I am running the same query on both WB, and HeidiSQL, I will let you know what happens, so far (since updating libmysql.dll) no crashes.

[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Ok,
The error does not seem to happen when I work through the MariaDB command line, but does whenever I use an interface (HeidiSQL, or MySQL Workbanch)
Any thoughts?
Thank you.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Just for additional information here is my My.ini file contents:
[mysqld]
datadir=W:/Databases/Data
port=3306
enable-named-pipe
socket = eedsql
slow_query_log
long_query_time = 60

ignore-builtin-innodb
plugin-load=innodb=ha_innodb.dll
innodb_buffer_pool_size = 1G
innodb_write_io_threads = 12
innodb_read_io_threads = 12
innodb_thread_concurrency = 24
thread_cache_size = 12
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G
innodb_lock_wait_timeout = 360
innodb_additional_mem_pool_size = 256M
innodb_file_per_table
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
innodb_additional_mem_pool_size = 16M
innodb_max_dirty_pages_pct = 90
net_read_timeout = 90
net_write_timeout = 120
transaction_isolation =READ-UNCOMMITTED
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 64M
max_prepared_stmt_count =500000
max_allowed_packet = 64M
max_heap_table_size = 528M
join_buffer_size = 16M
query_cache_size = 0M
read_buffer_size = 8M

[mysqldump]
max_allowed_packet = 64M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
#interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[client]
port=3306
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
And here is the create commands for the table that seems to most often be the culprit of the errors:

CREATE TABLE `rel_cmdty_dtl` (
`Rel_Release_ID` BIGINT(20) NOT NULL,
`Rel_Inv_Nbr` CHAR(35) NOT NULL COLLATE 'latin1_general_ci',
`Rel_Page_Nbr` INT(5) NOT NULL,
`Rel_Detail_Nbr` INT(5) NOT NULL,
`Rel_Goods_Unit_Price` BIGINT(11) NULL DEFAULT NULL,
`Rel_Goods_Total_Price` BIGINT(11) NULL DEFAULT NULL,
`Rel_Goods_Tot_Prc_Cur_Cde` CHAR(5) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`Rel_Qty_Gds` BIGINT(13) NULL DEFAULT NULL,
`Rel_Qty_Gds_Uom` CHAR(4) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`Rel_Ctry_Of_Orig_Cde` CHAR(5) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`Rel_Orig_State_cde` CHAR(5) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`Rel_HScode` BIGINT(11) NULL DEFAULT NULL,
`Rel_Goods_Com_Desc` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`ComID` BIGINT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ComID`),
UNIQUE INDEX `Rel_Release_ID_Rel_Inv_Nbr_Rel_Page_Nbr_Rel_Detail_Nbr` (`Rel_Release_ID`, `Rel_Inv_Nbr`, `Rel_Page_Nbr`, `Rel_Detail_Nbr`),
INDEX `Rel_HScode` (`Rel_HScode`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=43135359;

I understand that there is an issue with using CHAR as part of the unique key - alas the data coming to me is outside of my control.
Thank you.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Quick update - I am not going to perform further testing, but I tried two more things this morning, and so far the loss of connection seems to have stopped.
1) I downloaded and am using the portable version of Heidi,
2) I went through my My.ini and reduced some of the values. I recall see somewhere that if you alot to much memory to MySql you may have issues.

I will keep working with the two changes as above for awhile to see if the error has stopped, and if so switch back to the installed HeidiSQl to see if that was the cause.

Thank you.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Sorry -I need to edit previous post - should be I am now going to perform further testing!
smile
Is there a way to edit our own posts?
Thank you.
ansgar's profile image ansgar posted 11 years ago Permalink
No, you cannot edit poststsmileYou are forced to be blamed when you write rubbish, hehe :)

I am still thinking of a short interactive_timeout variable. Please check it:
SHOW VARIABLES LIKE 'interactive_timeout'
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Thanks Anse,
The result on the interactive_timout is 28800, assuming this is seconds than we are looking at 8 hours.
I did have issues in Work Bench with the 600 second limit, but I corrected that and still was loosing the connections.
Still - so far - seems to be working with the 2 changes I mentioned earlier.
Thank you.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Anse,
I ran some queries overnight and can confirm the following.
1) Using the same queries for both 7.0.04053 and 7.0.04217
The queries on 4217 will cause the connection loss, running the same query on 4053 will not.
I tried moving the libmysql.dll from the 4053 folder to the 4217 and re-ran the same queries, again same connection loss.
Therefore, I have removed from the equation, MySQl, the My.ini, the library.
I hope this is helpful to you, as HeidiSQL is great, and I enjoy using it.
Here is the latest bug report from the last crash:
date/time : 2012-10-31, 09:00:01, 923ms
computer name : W***7
user name : p***i
registered owner : Setup
operating system : Windows 7 x64 Service Pack 1 build 7601
system language : English
system up time : 1 day 21 hours
program up time : 3 minutes 9 seconds
processors : 8x Intel(R) Core(TM) i7-2920XM CPU @ 2.50GHz
physical memory : 1477/4046 MB (free/total)
free disk space : (C:) 203.71 GB
display mode : 1680x1050, 32 bit
process id : $11ec
allocated memory : 54.50 MB
executable : heidisql.exe
exec. date/time : 2012-10-26 09:41
version : 7.0.0.4217
compiled with : Delphi XE
madExcept version : 3.0m beta 1
callstack crc : $5de45084, $71f66ce9, $71f66ce9
exception number : 1
exception class : EAccessViolation
exception message : Access violation at address 5DE45084 in module 'libmysql.dll'. Read of address 00000088.

main thread ($1134):
5de45084 libmysql.dll
5de3a31f libmysql.dll mysql_ping
00716ac8 heidisql.exe dbconnection 1446 +2 TMySQLConnection.Ping
00716c8a heidisql.exe dbconnection 1483 +1 TDBConnection.KeepAliveTimerEvent
0053524b heidisql.exe ExtCtrls TTimer.Timer
0053512f heidisql.exe ExtCtrls TTimer.WndProc
004ab6c8 heidisql.exe Classes StdWndProc
760d7885 USER32.dll DispatchMessageW
00575d73 heidisql.exe Forms TApplication.ProcessMessage
00575db6 heidisql.exe Forms TApplication.HandleMessage
005760e1 heidisql.exe Forms TApplication.Run
00802938 heidisql.exe heidisql 70 +21 initialization
75ba3398 kernel32.dll BaseThreadInitThunk


Thank you.


ansgar's profile image ansgar posted 11 years ago Permalink
I'm still thinking your libmysql is from an older version, as there was some other user reporting exactly the same, when it turned out he had a superold libmsql. But it could also be a server issue. Do you have the connection problem on other servers as well?
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
The libmysql I am using now (that works) is the one that comes in the portable version of HSQL. File version 5.2.10.0 - digital signature - Monty Programs AB, Signing Time: 06/12/2011, 8:17:27 ( I do not know if that helps).
The libmysql that was with the version of HSQL has the same version number and signature date.
As mentioned this is a local server only - I do not access any others, so no idea on the server side of things.

Is there any thing else I could try for you? Or other information?

Thanks.
ansgar's profile image ansgar posted 11 years ago Permalink
You could examine the "[somename].err" log file on your server installation, located normally in the data directory. Probably you find some hint there.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Anse,
I have ran some more tests - again using the portable version of HeidiSQL everything works. I tried using the installed version (newer) and I get the same error. From my .err file here is the log:
121107 10:45:04 [Warning] Aborted connection 2 to db: 'dbname' user: 'username' host: 'localhost' (Unknown error)

Incidentally I checked in the system log files and this is the same information.

I set up a general query log, and ran the same query again to cause the error (it seems any query will do it). And no entry appears in the general log.

To me, it really seems like there is something different between the portable version, and the installed version of HeidiSQL that is causing the Aborted Connection, from my tests this is the only item that is not common between the two connection methods.
Same DB (local)
Same INI file (local)
Same libmysql.dll

Anything that I can do to help track it down please let me know.

Thank you.

ansgar's profile image ansgar posted 11 years ago Permalink
Please compare again portable with non-portable, this time like this:
- go to the directory of your portable installation, which makes no problem
- exit all running heidisql.exe instances
- rename "portable_settings.txt" to "portable_settings.txt_whatever"
- start heidisql.exe
- watch out for errors and report back here
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Ok,
First, I was running HeidSql, from my desktop - I know not the best way to do it. With no portabl_settings.txt at all.
So I extracted the portable version again to a folder, ran it, and ran a query no problem. Changed the name of the settings file, closed, and re-opened HeidiSQL and ran the same query.
No problems at all.

I then tried the installed version - same crash.
Thanks.
ansgar's profile image ansgar posted 11 years ago Permalink
You are running the "installed" version from the desktop?? That means you are running heidisql.exe outside of the installed directory! In that case HeidiSQL loads the first libmysql.dll it finds *somewhere* on your harddrive, but not from the install directory. Likely it finds an old one in your Windows directory. D'oh!
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Sorry I am not being clear, I was running the portable version from my desktop, the only items there were on the desktop were the heidisql.exe and libmysql.dll. When I realized that there was no portable_settings.txt file on the desktop either, I extracted the portable files fresh into a folder.
Ran you test - closed, and re-opened the portable HeidiSQL (now in a folder - with a portable_settings.txt) ran a query
Perfect - no problems.
I then changed the name of the portable_settings.txt to portable_settings.txt.whatever (I know you were joking about the name - the idea is so that the program cannot find it right?)
Re-opened the portabl HeidiSQL and ran the same query again, and again perfect.

Closed the portable HeidiSQL and opened the "installed" version. Opened the same query, and boom, same error message as always.

Is there anything at all I can provide you, that might help figure this out?
Thank you.
ansgar's profile image ansgar posted 11 years ago Permalink
Your installed version does not have the right libmysql.dll. This is is so obvious, as the non-portable version is nothing else than the same files without the portable_settings.txt. You could compare your installed version with the portable one using BeyondCompare.
[expired user #6652]'s profile image [expired user #6652] posted 11 years ago Permalink
Anse,
I un-installed HeidiSQL completely (including the version installed with MariaDB)
I then re-installed 7.0.0.4053
I ran queries perfectly, several, with no issues at all.
I then updated to the nightly build (714?) restarted HeidiSQL and tried to run a query.
Bang same error.
I think this would eliminate the libmysql.dll file would it not?
As the 4053 version works, I will wait for the next stable build and try it, see if there are any issues then.

Unless there is something I can do / or information I can give to help with debugging for you. (I am not a programmer and tend to learn by breaking things).

Thanks.

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