Repeated loss of connection error on localhost
| User, date | Message |
|---|---|
|
Written by RichardPhillips
7 months ago Category: General 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
7 months ago 3949 posts since Fri, 07 Apr 06 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
7 months ago 3949 posts since Fri, 07 Apr 06 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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 |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
Sorry -I need to edit previous post - should be I am now going to perform further testing! Is there a way to edit our own posts? Thank you. |
|
Written by ansgar
7 months ago 3949 posts since Fri, 07 Apr 06 |
No, you cannot edit poststYou 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' |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
7 months ago 3949 posts since Fri, 07 Apr 06 |
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? |
|
Written by RichardPhillips
7 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
7 months ago 3949 posts since Fri, 07 Apr 06 |
You could examine the "[somename].err" log file on your server installation, located normally in the data directory. Probably you find some hint there. |
|
Written by RichardPhillips
6 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
6 months ago 3949 posts since Fri, 07 Apr 06 |
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 |
|
Written by RichardPhillips
6 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
6 months ago 3949 posts since Fri, 07 Apr 06 |
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! |
|
Written by RichardPhillips
6 months ago 15 posts since Fri, 26 Oct 12 |
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. |
|
Written by ansgar
6 months ago 3949 posts since Fri, 07 Apr 06 |
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. |
|
Written by RichardPhillips
6 months ago 15 posts since Fri, 26 Oct 12 |
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. |