Out of memory exception while exporting large table to another database (but not when logging is disabled)

brikkelt posted 3 years ago in Import/Export

I'm regularly running into trouble while exporting large tables from one database to another (both MySql).

Sometimes I get a message like 'lost connection to database' (I'm not sure which db, the exporting or the importing one, I'm guessing the importing one). After this, Heidi stops exporting. This happens on a table with only 800 records, but with a lot of data in one of the columns per record (MEDIUMTEXT).

At other times I get an out of memory exception like the one below. This happens on a table with 1.5 million records. However, in this case, when I disable logging the queries (both user fired and internal), the export succeeds (and the process goes quicker as well). So I'm guessing there's one part of the trouble.

I hope you will be able to fix this some time, because in this case I found myself reluctantly reaching back to MySql-Front, which does an error-free and quick job transerring data from one db to another. For the rest, I like Heidi so much better ;-)

Here's the exception that happened on the 1.5 million record export:

date/time          : 2014-07-02, 12:13:41, 453ms
computer name      : XXX
user name          : XXX <admin>
registered owner   : XXX
operating system   : Windows XP Service Pack 3 build 2600 (yeah, I know)
system language    : Dutch
system up time     : 15 days 3 hours
program up time    : 54 minutes 23 seconds
processors         : 2x AMD Turion(tm) 64 X2 Mobile Technology TL-60
physical memory    : 1729/3454 MB (free/total)
free disk space    : (C:) 3,15 GB
display mode       : 1680x1050, 32 bit
process id         : $1ebc
allocated memory   : 381,32 MB
largest free block : 778,10 MB
executable         : heidisql.exe
exec. date/time    : 2014-03-23 08:50
version            :
compiled with      : Delphi XE5
madExcept version  :
callstack crc      : $548518fb, $2615e6b9, $2615e6b9
exception number   : 25
exception class    : EOutOfMemory
exception message  : Out of memory.
main thread ($18b4):
0040a167 heidisql.exe System              549  +0 @NewUnicodeString
0040b43e heidisql.exe System              549  +0 @UStrSetLength
006a895b heidisql.exe SynEditTextBuffer   666 +12 TSynEditStringList.GetSeparatedText
006a8a53 heidisql.exe SynEditTextBuffer   713  +3 TSynEditStringList.GetTextStr
006b9b1c heidisql.exe SynEdit            1878  +1 TCustomSynEdit.SynGetText
0093f96d heidisql.exe Main               6002 +27 TMainForm.SynMemoQueryPaintTransient
006ca488 heidisql.exe SynEdit           10224 +25 TCustomSynEdit.DoOnPaintTransientEx
006ca4be heidisql.exe SynEdit           10234  +1 TCustomSynEdit.DoOnPaintTransient
006bacdf heidisql.exe SynEdit            2600 +69 TCustomSynEdit.Paint
005ba82f heidisql.exe Vcl.Controls                TCustomControl.PaintWindow
005b4b69 heidisql.exe Vcl.Controls                TWinControl.PaintHandler
005b535c heidisql.exe Vcl.Controls                TWinControl.WMPaint
005ba7c9 heidisql.exe Vcl.Controls                TCustomControl.WMPaint
006fd8b9 heidisql.exe VirtualTrees      19414  +8 TBaseVirtualTree.WMPaint
005afe7d heidisql.exe Vcl.Controls                TControl.WndProc
005b499d heidisql.exe Vcl.Controls                TWinControl.WndProc
006c252a heidisql.exe SynEdit            6292 +22 TCustomSynEdit.WndProc
005b3fe0 heidisql.exe Vcl.Controls                TWinControl.MainWndProc
0053b4cc heidisql.exe System.Classes              StdWndProc
7c90e470 ntdll.dll                                KiUserCallbackDispatcher
7e398a0b USER32.dll                               DispatchMessageW
00684fd7 heidisql.exe Vcl.Forms                   TApplication.ProcessMessage
0068501a heidisql.exe Vcl.Forms                   TApplication.HandleMessage
00680386 heidisql.exe Vcl.Forms                   TCustomForm.ShowModal
0092f618 heidisql.exe Main               2173 +25 TMainForm.actTableToolsExecute
0053a7bb heidisql.exe System.Classes              TBasicAction.Execute
00594393 heidisql.exe Vcl.ActnList                TCustomAction.Execute
0053a65b heidisql.exe System.Classes              TBasicActionLink.Execute
0066b00b heidisql.exe Vcl.Menus                   TMenuItem.Click
0066c677 heidisql.exe Vcl.Menus                   TMenu.DispatchCommand
0066d8e6 heidisql.exe Vcl.Menus                   TPopupList.WndProc
0066d835 heidisql.exe Vcl.Menus                   TPopupList.MainWndProc
0053b4cc heidisql.exe System.Classes              StdWndProc
7e398a0b USER32.dll                               DispatchMessageW
00684fd7 heidisql.exe Vcl.Forms                   TApplication.ProcessMessage
0068501a heidisql.exe Vcl.Forms                   TApplication.HandleMessage
00685355 heidisql.exe Vcl.Forms                   TApplication.Run


Jan Willem
ansgar posted 3 years ago
To fix the "lost connection to database" error, you can increase the server variable "max_allowed_packet" of the target server.

The exception in TMainForm.SynMemoQueryPaintTransient could be avoidable if you just decrease the max size of log lines. See Tools > Preferences > Logging > "Cut long lines at..."
ansgar posted 3 years ago
Ah, and also minimize the setting for Tools > Preferences > Logging > "Log last ... queries". Perhaps you have increased that value to an incredibly large one, or have set it to 0 (= keep *all* lines)
brikkelt posted 3 years ago
Hey, thanks for the quick reply and the workarounds (especially the 'max_allowed_packet' one. That will keep me going for now.

It would of course be nice if Heidi wouldn't get in trouble in the first place (by protecting the user that has his settings set to fatal/default values), but I see on the issue tracker that there's enough work to be done already, so I'm not going to complain any longer :-)

Greetings and thanks again,

Jan Willem

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