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

Crash running script

User, date Message
Written by kalvaro
2 years ago
Category: Running SQL scripts
595 posts since Thu, 29 Nov 07
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 :-?
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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.
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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?

Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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?
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
No idea about the internals but it's probably a parameter that's used to establish the connection, such as the username or password.
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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.
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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).
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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.
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
Ah, found the right docs for it here:

mysql-docs wrote: 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.
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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.
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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.
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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?
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
Yes, please file a report. There are too many things to look at currently.
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
Done: http://code.google.com/p/heidisql/issues/detail?id=3002
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
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. :)
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
xD
 

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