Recovering from a changed $Organization value in Request Tracker & MySQL

One thing that Request Tracker (RT) is picky about is your setting for $Organization. For a new RT instance its not a big deal, you select something and go with it.

When you’re performing RT upgrades though, you must pay close attention to this setting. Changing this value during the upgrade will break any ticket links in your RT database. See here.

In my case I found the error after the upgrade had been completed and new data had gone into the new RT environment. I had to determine how to fix this problem.

Fortunately the RT Users Mailing list had the solution here. There are some typos in that SQL though so here I am to set the record straight. These fixes were done on MySQL 5.1.

In my case, the $Organization value in my old instance was ‘domain.com.au’. During the upgrade the $Organization value was set to ‘host.domain.com.au’.

Now before we go diving into the SQL to fix this situation you should:

  • Ensure you have a good backup of your production database
  • Ensure you have a testing environment you can play with first and that you have backups of it as well.
  • Did I mention that you should ensure you have a good backup of your production database?
  • GO now and backup your production database.
  • Understand that I wont be responsible for your actions or their results if you follow this guide. If you’re not sure about any of this then go and test it first or find someone/something that can give you direct assistance to resolve this problem.

Lets note those two important values:

OLDORGANIZATION = domain.com.au

NEWORGANIZATION = host.domain.com.au (this is the one we want to change back to the OLDORGANIZATION)

Now get a connection to your MySQL database that has the privs necessary to update tables in the RT Database.

Here is the SQL and I apologise for the formatting in advance:

If you want to see whether you still have links that are not repaired:

mysql> select Base from Links where Base not like ‘%fsck.com-rt://OLDORGANIZATION/ticket/%’;

mysql> select Target from Links where Target not like ‘%fsck.com-rt://OLDORGANIZATION/ticket/%’;

If you want to see whether you still have transactions that are not yet repaired:

mysql> select OldValue from Transactions where OldValue not like ‘%fsck.com-rt://OLDORGANIZATION/ticket/%’; mysql> select NewValue from Transactions where NewValue not like ‘%fsck.com-rt://OLDORGANIZATION/ticket/%’;

To repair your Links table:

mysql> update Links set Base=replace(Base,’fsck.com-rt://NEWORGANIZATION/ticket/’,’fsck.com-rt://OLDORGANIZATION/ticket/’);

mysql> update Links set Target=replace(Target,’fsck.com-rt://NEWORGANIZATION/ticket/’,’fsck.com-rt://OLDORGANIZATION/ticket/’);

To repair your Transactions table:

mysql> update Transactions set OldValue=replace(OldValue,’fsck.com-rt://NEWORGANIZATION/ticket/’,’fsck.com-rt://OLDORGANIZATION/ticket/’);

mysql> update Transactions set NewValue=replace(NewValue,’fsck.com-rt://NEWORGANIZATION/ticket/’,’fsck.com-rt://OLDORGANIZATION/ticket/’);

That’s it. Your links should now all be working again.

Advertisements

2 Responses to Recovering from a changed $Organization value in Request Tracker & MySQL

  1. […] the config of our new RT to make sure it matched the value in the old config. I ended up having to fix […]

  2. beowabbit says:

    Thanks very much for posting this; I was bitten by it in the exact same way, and this page saved me a lot of trouble figuring out how to recover.

    Sites that use RTFM will need the following as well:

    update Links set Base=replace(Base,’fsck.com-rtfm://OLDORGANIZATION/article/’,’fsck.com-rtfm://NEWORGANIZATION/article/’);
    update Links set Target=replace(Target,’fsck.com-rtfm://OLDORGANIZATION/article/’,’fsck.com-rtfm://NEWORGANIZATION/article/’);
    update Transactions set OldValue=replace(OldValue,’fsck.com-rtfm://OLDORGANIZATION/article/’,’fsck.com-rtfm://NEWORGANIZATION/article/’);
    update Transactions set NewValue=replace(NewValue,’fsck.com-rtfm://OLDORGANIZATION/article/’,’fsck.com-rtfm://NEWORGANIZATION/article/’);

    (And of course, remember to change $Organization in RT_SiteConfig.pm .)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: