database Questions - FogBugz Knowledge Exchange

Cleaning up old plugin tables

2012-01-20T16:09:11Z

What is the correct procedure to clean up unused plugin tables?

Somehow throughout the life of our FogBugz installation we've managed to accrue a lot of plugin tables. It looks like some of them are actually unused as we have the same name plugins with different indexes.

Is there a good way to identify which tables are unneeded and drop them?

Keep a seperate Wiki Create Date field.

2012-01-12T23:48:25Z

I've run into a problem with the way the wiki's are stored. To create a report of just the Wiki articles created, you have to join the revisions and wikipage tables.

If each page row kept a createdate column, it would make new article detection much simpler for API users.

How do I clear all of the cases from a database?

2012-01-11T15:17:58Z

Possible Duplicate:
How do I delete cases?

I want to clear all of the cases from my database and reset the autonumber back to 1.

How can I do this easily? Or at least, how can I do this?

I am importing data from our old system, and I want to run some import tests, but preserving my original bug numbers is very important, so subsequent imports have to allow me to use all the bug numbers starting from 1.

Is Microsoft Access support being deprecated?

2010-09-27T21:24:33Z

I heard that FogBugz support for Microsoft Access is being deprecated.

Is that true, and what does that mean?

Move Database To New SQL Server Instance

2010-11-03T20:19:40Z

What has to change where in the Fogbugz 8 configuration when a database is moved from one SQL Server instance to another? Anything special needed to be done when the old DB is SQL 2005 and the new is SQL 2008?

(see this post for moving the FogBugz site)

Importing Your FogBugz Database into FogBugz On Demand

2009-10-15T20:37:11Z

How can I import the database from my FogBugz installation into FogBugz On Demand?

MySQL: Moving database from one MySQL instance to another

2009-10-13T22:06:17Z

How do you go about migrating a FogBugz database from one MySQL instance to another?

(see this post for moving the FogBugz site)

How do I find bugs with no bug events?

2010-05-24T18:50:10Z

Some of my bug records appear to have no bug events. How do I find them?

SQL Server: Set up FogBugz database on a separate non-trusted server

2009-10-13T22:10:03Z

How can you set up a FogBugz database on a separate non-trusted server?

Plugin: Search cases with [input] in custom field

2011-11-08T14:01:39Z

Hi.

I am developing a plugin where I wish to get a list of case id's (i.e. ixBug) of cases that have a certain value in a custom field.

I'm thinking that the SQL could look like this: SELECT Bug.ixBug FROM Bug,Plugin_8_CustomBugData WHERE Plugin_8_CustomBugData.invoice=@userinput. Which I've tried to translate to code:

query.AddSelect("Bug.ixBug, Bug.ixPersonAssignedTo");
query.AddLeftJoin("Plugin_8_CustomBugData", "Bug.ixBug = Plugin_8_CustomBugData.ixBug");
query.AddSelect("Plugin_8_CustomBugData.invoice41");

But this gives me an error "Unknown column 'Plugin_8_CustomBugData.invoice41' in 'field list'". On one of the many pages I've come across, I've found out that plugin tables are not available to other plugins.

I'v been searching here and on google with no luck. All the results are about the XML API (which I am not using) or links to CAllPluginsApi. Either I don't get the CAllPluginsApi or it is not what I need. There's not much documentation to work from.

I'm running FogBugz v8.6.50 on MySQL.

The FogBugz Database Format (Schema)

2009-10-13T20:26:24Z

Where can I find details on the FogBugz database schema?

Importing your cases from another tool

2009-10-15T22:32:54Z

How can you import all of your cases from another issue/bug tracker?

Access: Converting your Access Database to Another Format

2009-10-13T20:12:54Z

Official Fog Creek Notice: MS Access support is being deprecated in FogBugz 8.0.

How do you convert an MS Access database to another format?

MySQL: Importing data from On Demand or trial

2009-10-13T21:10:11Z

How can I import a FogBugz On Demand trial database into MySQL?

How do I remove information from my FogBugz database?

2009-11-30T17:11:25Z

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

On Demand database backup are very slow

2011-08-29T07:01:23Z

I wanted to download a local copy of the FogBugz database of our FogBugz On Demand account. But after 4 hours I canceled the operation - the progress bar showed something like 5 percent completed.

I know the feature is meant for migrating a FogBugz installation, and not for ad hoc backups, but it was so slow that it was not usable.

I know having a local copy of the database perhaps is a bit paranoid, but the recent hurricane warning prompted me to take action.

How can I 'undo' resolving a case as a duplicate?

2010-10-13T12:54:21Z

I resolved several cases with a 'duplicate' resolved status and entered the case of which I believed both were duplicates. A coworker disputed that the cases were duplicates. I've re-opened the cases – and removed the contents of the 'Duplicate of' field, but the 'original' case still lists those cases as duplicates and the 'duplicate' cases still indicate that they are duplicates of the 'original' case. How can I 'fix' this?

[I'm not averse to modifying the DB directly. I think it would be helpful to document how this could be done, if only via the DB backend.]

[I've also subsequently edited the re-opened 'duplicate' cases and removed the contents of the 'Duplicate of' field, but doing so does not 'fix' this.]

plugin: SQL wrapper w/ left joins

2011-09-14T22:48:29Z

Hello,

I have a question about executing a specific type of SQL query through the wrapper exposed to the plugins:

My plugin has a database table which contains an "ixPerson" column, which corresponds to the column of the same name in the Fogbugz core "Person" table. I am able to successfully execute queries which join these two tables on the "ixPerson" field, and it works as expected. In addition, I need to be able to select all records in the Fogbugz core "Person" table that DO NOT have records in my plugin's table, based on the "ixPerson" field. I have looked up ways to do this online, specifically here:

http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx

Several examples are given, but all but one of them use nested WHERE clauses. I don't know how I would go about using a nested WHERE clause through the wrapper, or if its even possible. If someone could give me some tips, or point me to any Fogbugz documentation on this if it exists, I would be most grateful.

I attempted the version that uses the LEFT JOIN and no nested WHERE clauses, but it did not return any results to me. My temporary solution is two SQL queries and loops to identify (and remove) records which exist in both tables. It works, but I would like a single query solution if possible!

Thanks in advance!

-Don

Shrink my FogBugz database by removing old email attachments?

2009-12-15T16:54:58Z

I have a bunch of old customer emails and attachments that I want to get rid of. How can I get rid of large email attachments that are older than 6 months old?

Moving to MS SQL Server Express, need to reduce database size by deleting email attachments

2010-02-03T18:21:08Z

Possible Duplicate:
Shrink my FogBugz database by removing old email attachments?

We have been using the full version of MS SQL Server Standard for our FogBugz 7.0 installation, but are now downgrading to MS SQL Server Express. However, SQL Server Express has a 4GB database limit and our database is over 6GB in size mostly due to many large file attachments from email.

How do I remove all file attachments over a certain size or older than a certain time period? I would like to maintain the text from the cases themselves for reference, but do not need the attachments.

Thanks

Terrell

Data too long for column 'sToken' at row 1

2010-07-08T16:09:11Z

We have the following error displaying in our Fogbugz:

7/8/2010 10:44 AM (GMT-05:00): The Fog Creek Maintenance Service has reported an error. (200) CCmd: Data too long for column 'sToken' at row 1
SQL:  INSERT    INTO SorterToken          (sToken,          nLatestDoc)  VALUES (?,          ?)
Params: SI
1: FROM:"¨k¤k¶Ê±¡/¤k©Ê¿³¾Ä¶Ê±¡/¤k¥Î¤fªA/¨k©
2: 24555


Wasabi.Runtime.WasabiException (0x80004005): Data too long for column 'sToken' at row 1
SQL:  INSERT    INTO SorterToken          (sToken,          nLatestDoc)  VALUES (?,          ?)
Params: SI
1: FROM:"¨k¤k¶Ê±¡/¤k©Ê¿³¾Ä¶Ê±¡/¤k¥Î¤fªA/¨k©
2: 24555
 ---> MySql.Data.MySqlClient.MySqlException: Data too long for column 'sToken' at row 1
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
  at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
  at Wasabi.Runtime.ADO.Command.ExecuteNonQuery() in c:\src\Wasabi\Wasabi.Runtime\ADO\Command.cs:line 94
  at FogCreek.FogBugz.CCmd.ExecuteNonQuery(Nullable`1 nCacheClearLevel) in c:\src\FogBugz\distribution\src-Website\CCmd.was:line 237
  at Wasabi.Runtime.Error.Raise(Int32 number, String source, String description, String helpfile, Nullable`1 helpcontext) in c:\src\Wasabi\Wasabi.Runtime\Error.cs:line 88
  at FogCreek.FogBugz.CCmd.ExecuteNonQuery(Nullable`1 nCacheClearLevel) in c:\src\FogBugz\distribution\src-Website\CCmd.was:line 247
  at FogCreek.FogBugz.CToken.Insert(String sTokenInit, Int32 nLatestDocInit) in c:\src\FogBugz\distribution\src-Website\CToken.was:line 215
  at FogCreek.FogBugz.CBayesianLearner.LearnTokens(CTokenBag tokenBag, Int32 ixAreaDestinationInit, Int32 ixAreaSourceInit, Boolean bUndoPreviousInit, Boolean bBinaryClassificationInit) in c:\src\FogBugz\distribution\src-Website\CBayesianLearner.was:line 251
  at FogCreek.FogBugz.CBug.Train(Int32 ixAreaFrom, Int32 ixAreaTo) in c:\src\FogBugz\distribution\src-Website\CBug.was:line 3131
  at FogCreek.FogBugz.CTrainingRequest.PerformRequest() in c:\src\FogBugz\distribution\src-Website\CTrainingRequest.was:line 276
  at FogCreek.FogBugz.__Global.SingleHeartbeatFogBugz(Int32 cMaxBayesianRequests, Int32 cMaxMailMessages, Int32 nEmailTimeout) in c:\src\FogBugz\distribution\src-Website\singleHeartbeat.was:line 93
  at FogCreek.FogBugz.__Global.SingleHeartbeatPlatform(Int32 cMaxBayesianRequests, Int32 cMaxMailMessages, Int32 nEmailTimeout) in c:\src\FogBugz\distribution\src-Website\singleHeartbeat.was:line 45
  at FogCreek.FogBugz.__Global.StartHeartbeat() in c:\src\FogBugz\distribution\src-Website\heartbeat.was:line 70

Can you please assist?

ERROR [IM002] Data source name not found and no default driver specified

2010-03-29T20:51:25Z

Hi all,

since this morning I am getting the above error. We set up the server just about a week ago and used it successfully until Friday. When I got in this morning it failed.

I checked the ConnectionString (as recommended)
ensured that SQL runs
verified user account and permissions
verified that logging in with that account and DB works
used database server's IP address instead of computer name

Then I thought upgrading to 7.1.18 might fix it. The upgrade included database upgrades that ran without trouble. Still, it wouldn't connect to our database afterwards.

Any ideas what could be wrong? Thanks

Case FC1869023

Support for COALESCE in database queries in plugins

2011-08-18T18:57:24Z

I tried to use a COALESCE in a CBugQuery in a plugin I'm writing and FogBugz threw an exception:

System.ArgumentException: Unknown function: COALESCE

Since MySQL and SQL Server both support it, why doesn't FogBugz (now that Access support is deprecated)?

Case FC2115036

FogBugz Pre-Sales Q: Web & DB

2010-02-22T20:53:30Z

Greetings,

Our Dev team has been looking at the tool, they state that it works with MS IIS, Question, does it "share" IIS nicely or does it take over the whole box like sharepoint services?

Second - if using the smaller DB engines - any info on database growth numbers?

(need to determine if we can get by without purchasing full squirrelly server

Thanks

Sample MySQL configuration (my.cnf / my.ini)

2011-07-21T14:11:07Z

Could someone post a good my.cnf / my.ini file for use with FogBugz? (see also MySQL gotchas)

Removed active directory - fogbugz no longer working after trying re-install

2011-07-15T14:53:45Z

So, here's the scenario.

Everything was working fine. Then i stupidly installed AD on my server, and then un-installed it.

This caused all sorts of user errors. After unsuccessfully trying to get them working, FB app domain would still not run.

I decided to try re-installing. Now, I get the following error when trying to reach my FB site:

You do not have a version of the FogBugz database that FogBugz could read. It is possible that restarting your web server will reset things back to normal. If you are trying to get FogBugz to create a blank database for you, please add a table named Version with a column named ixVersion (int) and the value 0 in it. Please backup your data and reinstall FogBugz.

So, what do i do now?!

Fogbugz On Demand Snapshot

2011-07-27T19:06:35Z

Is it possible to get a snap shot of our current Fogbugz database for our server. The purpose would be to increase speed of reports and other applications we currently have to do via xml exchange.

Thanks

Brandon

Using plugin API is it possible to get all bug events for a particular bug?

2011-07-12T15:31:23Z

I am building a plugin that requires me to get all the events for a bug. Is there an prescribed way to do this?

How much database expertise do I need to host FogBugz myself?

2011-07-07T18:29:57Z

I'm considering running FogBugz myself, but need to know how much I need to know about SQL Server or MySQL to do so safely.

OLE DB Error: "Incorrect Syntax near the keyword DEFAULT"

2009-10-15T01:59:31Z

I'm getting the following error message:

Error Message:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'DEFAULT'.
/fogbugz/CBug.asp, line 127

What's the deal?