Question

I have a bunch of cases I'd like to delete. How do I go about it?

Answer 1

If you cannot access the database or if you're using FogBugz On Demand, the simplest way of approximating deletion is to sequester the case. Create a new project named "Sensitive Info", and add it to a new group called "Confidential". Set the group permissions such that only site admins have access, then move the case into the new project and close it. No one other than site admins will be able to see the case or any reference to it (including case relations). It will not show up in any searches except by site admins.

If you have access to the database and sequestration doesn't work for you, then the following is an example of one way to proceed. It is offered without guarantee as to its safety or efficacy, but simply as an example of how one might go about moving data in this way. This example will use MySQL, but the process should be applicable to Access or SQL Server.

First, back up your database.

Let me say that again: back up your database.

Is your database backed up? Okay, then let's proceed.

Next, you'll need to find the ID of the offending BugEvent. Bug events are the entries on the case page. They look like "Assigned to Joe Biden by Barack Obama" and a timestamp. They can also take the form of an email, with text like "Replied by Rich" and a timestamp at the top of the email. If you click on that timestamp, you'll see a #BugEvent value added to the URL. This is the bug event's ID.

Log into your FogBugz instance of MySQL on the command line. Run this command:

SHOW DATABASES;

You will get output like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| FogBugz            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

In this case, your FogBugz database is named FogBugz. Run this SQL:

SELECT * FROM FogBugz.BugEvent 
WHERE ixBugEvent = 8675309;

The text in the "s" field should match up to the text you want to delete. Note that if this is an encoded email, the text might be a block of gibberish.

Now, we want to create a table to hold the data we're going to remove. This is not strictly necessary if you just want to blow away data, but you might come to regret deleting data outright, and this gives you some insurance.

First, create a new database:

CREATE DATABASE DumpingGround;

Now, we want to create an exact replica of the BugEvent table in the new database. To do this, we use a handy feature of MySQL called "SHOW CREATE TABLE". (The equivalent in SQL Server is "Script Table As".)

SHOW CREATE TABLE FogBugz.BugEvent;

You'll get something like this:

| BugEvent | CREATE TABLE `BugEvent` (
  `ixBugEvent` int(11) NOT NULL auto_increment,
  `ixBug` int(11) default '0',
  `sVerb` varchar(128) default NULL,
  `dt` datetime default NULL,
  `ixPerson` int(11) default '0',
  `s` longtext,
  `fEmail` smallint(6) default '0',
  `fExternal` smallint(6) default '0',
  `sChanges` longtext,
  PRIMARY KEY  (`ixBugEvent`),
  KEY `bugindex` (`ixBug`),
  KEY `dtindex` (`dt`),
  KEY `BugEventDtAsc` (`dt`,`ixBugEvent`),
  KEY `BugEventDtDesc` (`dt`,`ixBugEvent`)
) 
ENGINE=MyISAM AUTO_INCREMENT=18675309 DEFAULT CHARSET=latin1 |

We want to create a copy of that table in a new database to store information. Take everything between the second set of pipes (||) and copy it off, adding a database name to the table name, so it goes from BugEvent to DumpingGround.BugEvent.

CREATE TABLE DumpingGround.BugEvent (
 ... 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Okay, now we have a place to put the data we're going to remove. Is your database backed up? Good. Here's the command to run.

INSERT INTO DumpingGround.bugevent 
SELECT * FROM FogBugz.bugevent
WHERE ixBugEvent = 8675309;

You should get this output:

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Right now, these two queries should give you the same results:

SELECT * FROM FogBugz.BugEvent 
  WHERE ixBugEvent = 8675309;
SELECT * FROM DumpingGround.BugEvent 
  WHERE ixBugEvent = 8675309;

Now, we just need to remove the data from the bug event in the FogBugz database. We don't want to delete the row entirely. It might cause problems with data integrity. So here's what we run:

   UPDATE FogBugz.BugEvent 
   SET sVerb = 'Removed', ixPerson = -1, s = '', sHTML = '', fHTML = 0, fEmail = 0 
   WHERE ixBugEvent = 8675309;

This changes how the event shows in the interface. It will now say "Removed by FogBugz". The timestamp and any other information, such as assignments or changes in priority, will be preserved, but the data is gone. You could also experiment with placing text into the "s" column, such as "Please contact an administrator to retrieve this text: Event# 8675309". That's up to you.

You'll need to secure the DumpingGround database to your satisfaction so that the data remains preserved but secure.