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

Empty database

User, date Message
Written by kalvaro
4 years ago
Category: General
587 posts since Thu, 29 Nov 07
Is there a GUIish way to remove all the objects from a database when there're foreign keys out there?

So far, I have to issue a SET @@FOREIGN_KEY_CHECKS=0 command in a query tab before removing the items in the database tab and them remember to set it back to true.
Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
Tried to drop the database itself and then recreate it?
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
I suppose that if I did that I'd need to recreate the user permissions (apart from login as root). It's easier to just disable FOREIGN_KEY_CHECKS. I just wanted to make sure that I was not missing an existing feature.
Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
No, not a feature in Heidi yet. Although that sounds like if that FOREIGN_KEY_CHECKS could be disabled before dropping any table and reactivated afterwards. Hm.. why not.
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
You may not be able to set it back to 1 but, well, you're deleting tables after all.

Whatever, removing items can be tricky. Apparently, you also need to remove triggers, procedures and functions in a specific order or you'll get error messages like: SQL Error (1360): Trigger does not exist
Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
Ok, then
1. disable foreign key checks
2. delete triggers
3. delete views
4. delete tables and procs and events
5. enable foreign key checks
or?
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
Hmmm... I think the error message has a different reason. When a table gets dropped all its triggers are gone but HeidiSQL tries to drop them later anyway.

The deletion order you propose looks good but I haven't had time to give it a deep look yet.
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
After some quick testing, it looks like as soon as you disable FOREIGN_KEY_CHECKS the deletion order or items does not matter.
Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
For triggers, it looks like they get deleted automatically by the server if you delete the associated table. Anyway, that must not mean we never delete triggers, as you should be able to delete a trigger alone. So, first triggers then tables, as said above. Foreign key checks disabled before heals the initial problem. Sticking to the above mentioned order of actions.
Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
Fixed in r3577.
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
Sorry I didn't have the chance of testing it before but it happens that HeidiSQL now crashes when I try to drop all DB objects. Well, actually, I presume a crash would generate a bug report or something; it just closes suddenly and silently :-?

Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
Oh, managed to get one:

date/time : 2010-11-05, 11:50:54, 148ms
computer name : ***********************
user name : *********************** <admin>
registered owner : ***********************
operating system : Windows XP Service Pack 3 build 2600
system language : Spanish
system up time : 8 days 18 hours
program up time : 9 seconds
processors : 2x Intel(R) Pentium(R) 4 CPU 3.00GHz
physical memory : 307/991 MB (free/total)
free disk space : (C:) 5,52 GB (E:) 12,43 GB
display mode : 1280x1024, 32 bit
process id : $660
allocated memory : 13,85 MB
executable : heidisql.exe
exec. date/time : 2010-11-04 17:32
version : 5.1.0.3579
compiled with : Delphi 2010
madExcept version : 3.0k
callstack crc : $23605a31, $5a11123e, $5a11123e
exception number : 1
exception class : EAccessViolation
exception message : Access violation at address 00764C2C in module 'heidisql.exe'. Read of address 00000098.

main thread ($1710):
00764c2c heidisql.exe mysql_connection 2852 +0 TDBObjectDropComparer.Compare
0076cabb heidisql.exe mysql_connection 3132 +0 TArray.QuickSort<mysql_connection.TDBObject>
0076bf7c heidisql.exe mysql_connection 3132 +0 TArray.Sort<mysql_connection.TDBObject>
00766721 heidisql.exe mysql_connection 3132 +0 TList<mysql_connection.TDBObject>.Sort
006c269e heidisql.exe Main 2649 +47 TMainForm.actDropObjectsExecute
00499e77 heidisql.exe Classes TBasicAction.Execute
00555e39 heidisql.exe ActnList TContainedAction.Execute
00556bf4 heidisql.exe ActnList TCustomAction.Execute
00499d3b heidisql.exe Classes TBasicActionLink.Execute
00526cbc heidisql.exe Menus TMenuItem.Click
0052828f heidisql.exe Menus TMenu.DispatchCommand
0052946e heidisql.exe Menus TPopupList.WndProc
0053eac8 heidisql.exe Controls TWinControl.MainWndProc
005293bd heidisql.exe Menus TPopupList.MainWndProc
0049a524 heidisql.exe Classes StdWndProc
7e398a0b USER32.dll DispatchMessageW
0056b229 heidisql.exe Forms TApplication.ProcessMessage
0056b26e heidisql.exe Forms TApplication.HandleMessage
0056b599 heidisql.exe Forms TApplication.Run
007a7742 heidisql.exe heidisql 66 +15 initialization

Written by ansgar
4 years ago
4936 posts since Fri, 07 Apr 06
Yes, can confirm a crash, although with a different callstack. Fuck. Similar one see issue #2224.
Written by kalvaro
4 years ago
587 posts since Thu, 29 Nov 07
Relax. Nobody was hurt wink
 

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