Crash running script

Just got this when running a 14.6MB SQL file directly:

date/time         : 2012-10-29, 09:40:13, 742ms
user name         : ALVARO.GONZALEZ <admin>
registered owner  : ALVARO
operating system  : Windows 7 x64 Service Pack 1 build 7601
system language   : Spanish
system up time    : 7 days 1 hour
program up time   : 21 seconds
processors        : 2x AMD Athlon(tm) 64 X2 Dual Core Processor 4000+
physical memory   : 2562/4096 MB (free/total)
free disk space   : (C:) 1,10 GB (D:) 232,56 GB
display mode      : 1280x1024, 32 bit
process id        : $14d0
allocated memory  : 84,51 MB
executable        : heidisql.exe
exec. date/time   : 2012-10-29 09:39
version           : 7.0.0.4218
compiled with     : Delphi XE
madExcept version : 3.0m beta 1
callstack crc     : $0b8abc9f, $96228fb7, $96228fb7
exception number  : 1
exception class   : EDatabaseError
exception message : SQL Error (1046): No database selected.
main thread ($650):
00716f27 heidisql.exe dbconnection 1533 +27 TMySQLConnection.Query
0079daff heidisql.exe runsqlfile    126 +69 TRunSQLFileForm.FormActivate
0056f6a3 heidisql.exe Forms                 TCustomForm.Activate
00570760 heidisql.exe Forms                 TCustomForm.CMActivate
004e8ec4 heidisql.exe Controls              TControl.WndProc
004ed788 heidisql.exe Controls              TWinControl.WndProc
0056cda4 heidisql.exe Forms                 TCustomForm.WndProc
004ece28 heidisql.exe Controls              TWinControl.MainWndProc
004ab6c8 heidisql.exe Classes               StdWndProc
77560107 ntdll.dll                          KiUserCallbackDispatcher
74e496c0 USER32.dll                         SendMessageW
00571700 heidisql.exe Forms                 TCustomForm.ShowModal
007d6ff9 heidisql.exe Main         2961 +65 TMainForm.RunQueryFiles
007d6b0c heidisql.exe Main         2867  +9 TMainForm.actLoadSQLExecute
004ab023 heidisql.exe Classes               TBasicAction.Execute
004d76b1 heidisql.exe ActnList              TContainedAction.Execute
004d846c heidisql.exe ActnList              TCustomAction.Execute
004aaee7 heidisql.exe Classes               TBasicActionLink.Execute
00508f14 heidisql.exe Menus                 TMenuItem.Click
0050a89f heidisql.exe Menus                 DoClick
0050a98b heidisql.exe Menus                 TMenu.IsShortCut
004efb20 heidisql.exe Controls              TWinControl.IsMenuKey
004efb95 heidisql.exe Controls              TWinControl.CNKeyDown
004e8ec4 heidisql.exe Controls              TControl.WndProc
004ed788 heidisql.exe Controls              TWinControl.WndProc
005e4a8a heidisql.exe SynEdit      6248 +22 TCustomSynEdit.WndProc
004ece28 heidisql.exe Controls              TWinControl.MainWndProc
004ab6c8 heidisql.exe Classes               StdWndProc
77560107 ntdll.dll                          KiUserCallbackDispatcher
00575b36 heidisql.exe Forms                 TApplication.IsHintMsg
750a33a8 kernel32.dll                       BaseThreadInitThunk

Tried again (after carefully clicking on the only database showing up on the left tree) and got this:

date/time         : 2012-10-29, 09:43:33, 576ms
user name         : ALVARO.GONZALEZ
registered owner  : ALVARO
operating system  : Windows 7 x64 Service Pack 1 build 7601
system language   : Spanish
system up time    : 7 days 1 hour
program up time   : 45 seconds
processors        : 2x AMD Athlon(tm) 64 X2 Dual Core Processor 4000+
physical memory   : 2491/4096 MB (free/total)
free disk space   : (C:) 1,10 GB (D:) 232,56 GB
display mode      : 1280x1024, 32 bit
process id        : $16ac
allocated memory  : 124,05 MB
executable        : heidisql.exe
exec. date/time   : 2012-10-29 09:39
version           : 7.0.0.4218
compiled with     : Delphi XE
madExcept version : 3.0m beta 1
callstack crc     : $0b8abc9f, $4a6db03f, $4a6db03f
exception number  : 1
exception class   : EDatabaseError
exception message : SQL Error (2006): MySQL server has gone away.
main thread ($858):
00716f27 heidisql.exe dbconnection 1533 +27 TMySQLConnection.Query
0079daff heidisql.exe runsqlfile    126 +69 TRunSQLFileForm.FormActivate
0056f6a3 heidisql.exe Forms                 TCustomForm.Activate
00570760 heidisql.exe Forms                 TCustomForm.CMActivate
004e8ec4 heidisql.exe Controls              TControl.WndProc
004ed788 heidisql.exe Controls              TWinControl.WndProc
0056cda4 heidisql.exe Forms                 TCustomForm.WndProc
004ece28 heidisql.exe Controls              TWinControl.MainWndProc
004ab6c8 heidisql.exe Classes               StdWndProc
77560107 ntdll.dll                          KiUserCallbackDispatcher
74e496c0 USER32.dll                         SendMessageW
00571700 heidisql.exe Forms                 TCustomForm.ShowModal
007d6ff9 heidisql.exe Main         2961 +65 TMainForm.RunQueryFiles
007d6b0c heidisql.exe Main         2867  +9 TMainForm.actLoadSQLExecute
004ab023 heidisql.exe Classes               TBasicAction.Execute
004d76b1 heidisql.exe ActnList              TContainedAction.Execute
004d846c heidisql.exe ActnList              TCustomAction.Execute
004aaee7 heidisql.exe Classes               TBasicActionLink.Execute
00508f14 heidisql.exe Menus                 TMenuItem.Click
0050a89f heidisql.exe Menus                 DoClick
0050a98b heidisql.exe Menus                 TMenu.IsShortCut
00571d15 heidisql.exe Forms                 TCustomForm.IsShortCut
004efb48 heidisql.exe Controls              TWinControl.IsMenuKey
004efb95 heidisql.exe Controls              TWinControl.CNKeyDown
004e8ec4 heidisql.exe Controls              TControl.WndProc
0061db37 heidisql.exe VirtualTrees          TBaseVirtualTree.WMNCHitTest
004e8ec4 heidisql.exe Controls              TControl.WndProc
004ed788 heidisql.exe Controls              TWinControl.WndProc
00629a94 heidisql.exe VirtualTrees          TBaseVirtualTree.WndProc
004ece28 heidisql.exe Controls              TWinControl.MainWndProc
004ab6c8 heidisql.exe Classes               StdWndProc
77560107 ntdll.dll                          KiUserCallbackDispatcher
00575b36 heidisql.exe Forms                 TApplication.IsHintMsg
750a33a8 kernel32.dll                       BaseThreadInitThunk

That's what I call a Monday.

I finally ran the script from the command line client. No idea why that worked because select @@max_allowed_packet; returns the same value in both clients :-?

I can already answer my question: it didn't work in the command line client. It simply didn't crash so I hadn't noticed part of the data was missing.

I can run my script from the command line if I start the client with a specific option:

mysql --max_allowed_packet=50MB ...

Apparently, it isn't something you can change after establishing a connection. Can something similar be done with HeidiSQL? If not, would that make a sensible feature request?

Not sure. When you say you cannot change the max_allowed_packet variable effectively after establishing a connection, then how does the MySQL client do that?

No idea about the internals but it's probably a parameter that's used to establish the connection, such as the username or password.

Hm that looks a bit confusing when reading the docs. I cannot set max_allowed_packet in a session scope, only the global scope seems to be allowed. See when you try to fire:

SET @@SESSION.max_allowed_packet:=1024;
/* SQL Error (1621): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value */

Now, as you said, it's not possible to set this global value effectively in one session. A reconnect is required.

The documentation on mysql_real_connect states that you can run

mysql_options(FHandle, MYSQL_READ_DEFAULT_FILE, nil);

where you can have a max_allowed_packet value in the default ini file. Read here.

Ah ok, that just does not work. Not sure if that's a bug. My test:
- create a test.ini file with this contents:
[client]
max_allowed_packet=3000
- add a mysql_options(FHandle, "path\test.ini", nil) in the HeidiSQL connection code

max_allowed_packet is still on the old value (1024).

Oh, that *does* work, I only had a file permissions problem, obviously, as after saving the file with admin privs I suddenly got some "

SQL Error (2020): Got packet bigger than 'max_allowed_packet' bytes

. Hurray.

Hm, but it's still a bit mysterious, as I'm not able to look up the new variable value. Firing SHOW VARIABLES always shows the default 1M, anyway what I pass in my config file.

Ah, found the right docs for it here:

On the client side, max_allowed_packet has a default of 1GB. Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

The session value of this variable is read only.

That means, HeidiSQL could
* offer a new numeric session setting for that variable
* apply a reasonable default for it (let's say 1M as the server does)
* auto-create a my.ini file somewhere (in a temp folder) and place the parameter value in it.
* add the mysql_options command as mentioned above, so the ini file is read by libmysql.dll

But what if your dump file contains a bigger query than this value allows? Let it crash? I could even, as an alternative to the above hassle, warn you before sending too big queries, and offer to auto-fire a "SET @@GLOBAL.max_allowed_packet:=10M" or whatever.

Mostly that variable should not be a problem, as HeidiSQL for example ensures a multi-query batch is smaller than the server side max_allowed_packet variable.

Again, I'm sorry I can't help with the internals. If I understood correctly, the MySQL library does not allow to set max_allowed_packet explicitly but provides an indirect way to do so (a external *.ini file). Then I guess there're two possibilities:

1. Provide a "Path to custom *.ini file" setting
2. Provide a direct "max_allowed_packet" setting

... both of which should be added to Session Manager.

#1 is more powerful but #2 is more user friendly. I'm not sure I have a favourite... But I'd definitively love to have any because I'm been having problems running custom scripts for a while.

More and more options tend to decrease usability, so I would like to shed more light on my alternative idea: Warn the user if a query is larger than the current server-side max_allowed_packet, including the possibility to fire "SET @@GLOBAL.max_allowed_packet:=10M" or whatever". But it seems I forgot that this does not have an effect for the current session, so forget it.

For the first option you pointed out (path to .ini file), HeidiSQL would be unable to know the client side max_allowed_packet value, as there is no way in MySQL to query it, only the server-sided can be queries with SHOW VARIABLES. HeidiSQL needs to know the value, e.g. when you export SQL directly to another server or database, or when you fire a multi-statement batch.

The second idea looks better to me, although this would even be one more setting on top of the myriad of session settings. But I'm getting space problems in the first tab of the session manager, so that will be thought about carefully.

You can always add an "Advanced" tab and move there some of the most esoteric settings such as "Set client time zone" or "Startup script".

BTW, would you like to have a proper bug report so you don't have to look into it right now?

Yes, please file a report. There are too many things to look at currently.

Done: http://code.google.com/p/heidisql/issues/detail?id=3002

My 8 year old daughter just looked over my shoulder into the HeidiSQL code, and read aloud "Papa, what is 'max packet size'?". And I tried to explain her that this "packet" is not something you normally send by shipping. But I guess my explanation failed. :)

xD