Empty database

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.

Tried to drop the database itself and then recreate it?

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.

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.

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

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?

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.

After some quick testing, it looks like as soon as you disable FOREIGN_KEY_CHECKS the deletion order or items does not matter.

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.

Fixed in r3577.

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 :-?

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

Yes, can confirm a crash, although with a different callstack. Fuck. Similar one see issue #2224.

Relax. Nobody was hurt