Upgrading Request Tracker

2010/10/14

I recently upgraded a Request Tracker (RT) environment from version 3.2.2 to version 3.8.7. These are my learnings and thoughts from the experience.

The environment that RT 3.2.2 was running on was an old hand rolled from source deployment on Fedora Core 3. The database was on a separate Debian 4 host running a backported MySQL 4.1.

A new web hosting and database back end was available based on Ubuntu 10.04 LTS and MySQL 5.1. This was the target environment for the upgraded RT.

Based on the available target environment the simplest approach to deliver this upgrade was to:

  • Install and configure RT 3.8.7 from packages on Ubuntu 10.04 LTS. Instructions here.
  • Dump the database using mysqldump from the old MySQL server.
  • Load the DB into the new database server.
  • Run the upgrade steps.
  • Validate the data and functionality.

As I tested and read up on the upgrade steps I soon found that this wasn’t going to be the big hassle I first imagined it to be. The details of the migration went like this:

  • Turn off all inbound SMTP email to the primary mail server. This is necessary as I want any emails bound for our ticketing system to queue up at the senders end as my migration involved a change of host for the ultimate destination for ticket emails.
  • Shutdown the old RT apache instance on the old web server and its MTA as well.
  • Shutdown the new RT apache instance on the new web server and its MTA as well.
  • Use mysqldump to copy the prod RT database to a .sql file on the new database server.

mysqldump –user={rtuser} –password={rtuserpasswd} –host={oldrtdbhost} –opt –skip-lock-tables –single-transaction –default-character-set=binary –databases {rtdbname} > {pathto .sql file}

  • Use mysql to load the .sql file into the new database server.

mysql –user={rtuser} –password={rtuserpasswd} –host={newrtdbhost} < {pathto .sql file}

  • Use the rt-setup-database script on the new RT web server to upgrade the DB from 3.2.2 to 3.7.87.

rt-setup-database –action upgrade –dba {rtuser} –prompt-for-dba-password

  • Build the schema upgrade script.

perl /usr/share/request-tracker3.8/etc/upgrade/upgrade-mysql-schema.pl {rtdbname}:{dbhost} {rtusername} {rtuserpasswd} > {path to schema .sql}

  • Apply the schema upgrade script.

mysql –user {rtuser} –password={rtuserpasswd} -h {newrtdbhost} {rtdbname} < {path to schema .sql}

  • Use the rt-setup-database script on the new RT web server to upgrade the DB from 3.7.87 to 3.8.7.

rt-setup-database –action upgrade –dba {rtuser} –prompt-for-dba-password

  • Start the web server and MTA on the new RT web server
  • Logged in to the new RT instance and made sure it all looked okay. I had to tweak settings in the following locations:
    • The rights all needed redoing. I blew them all away and reassigned rights to my groups from scratch.
    • Because I had started using Exim4 integration I had to go through each queues config and update its correspondence and comment address.
    • I tuned the default dashboard to look more like our original 3.2.2 dashboard. All the new stuff can be turned on later. I didnt want to move the agents cheese too much at first.
  • I then had to jump on our primary MX and fix up all its forwarder configs for our queue email addresses so that emails addressed to our queues would be delivered to the new host.
  • Turned in bound email on the primary mail server back on.
  • Sent and received test messages.

That was it. Of course there were teething problems from the migration. These included:

  • I hadn’t checked the $Organization value in the config of our new RT to make sure it matched the value in the old config. I ended up having to fix it.
  • I had a few problems with the change of the queue correspondence and comment address. The exim4 integration expects a comment address of {queuename}-comment@host.domain.com.au. Our comment addresses didn’t match this and I had to quickly update our forwarder configs to deliver to the correct address on the RT host.
  • I needed to give all the agents the ModifyCustomField right.
  • In my original exim4 reconfigure I hadnt configured exim to rewrite outbound emails from the host so all emails sent from RT left with an address of queuename@host.domain.com.au when what I wanted was queuename@domain.com.au. A quick `dpkg–reconfigure exim4-config` allowed me to turn on the rewriting of sent emails and the address they should be rewritten as.
  • RT will truncate ticket transactions with large bodies. See the MaxInlineBody RTConfig option or have your agents adjust their settings in their preferences.
  • Agents didnt have the right to modify requestor details. Had to grant them the AdminUsers and ShowConfigTab rights.

Somethings I learnt and knowledge I want to retain:

  • To test how exim4 will handle an email address on a host use this command

exim -d -bt {email address}

That’s it. So far things have been running really well.

Advertisements

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

2010/10/13

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.


Apache2 virtual host for Request Tracker on Ubuntu 10.04 LTS

2010/10/13

This post is done from memory so YMMV.

After completing the steps in this article you might have a functional Apache2 virtual host for your Request Tracker instance. This is useful when you don’t want to host your RT instance under http://{your IP address}/rt and instead host it under something like http://rt.yourdomain.com.au.

You should have built your RT instance using my guide here.

  • Add your virtual host configuration to apache as you would normally.  See the apache docs if you need to.
  • Copy the RT config from /etc/request-tracker3.8/apache2-modperl2.conf into your virtual host config and amend as necessary.
  • Edit /etc/request-tracker3.8/RT_SiteConfig.d/50-debconf and change the values the following values:
  • Update your rtconfig with a `/usr/sbin/update-rt-siteconfig`

Integrate Request Tracker and Exim4 on Ubuntu 10.04 LTS

2010/10/13

This article will tell you how to integrate Request Tracker 3.8.7 and Exim4 on Ubuntu 10.04 LTS.

This integration allows Exim4 to automatically accept and route inbound emails to the correct queues in the RT config. The benefit to doing it this way is that you don’t need to touch the Exim4 config when adding new queues into RT.

You should start by deploying your RT instance as per my guide here.

Create a new file /etc/exim4/conf.d/main/10_request-tracker3 and enter the following:

QUEUENAME_QUERY   = \
SELECT Name FROM Queues WHERE  \
CorrespondAddress =  '${quote_mysql:$local_part}@${quote_mysql:$domain}' \
AND  Disabled = '0'
hide mysql_servers = $DBHOST/$DBNAME/$DBUSER/$DBPASSWORD
domainlist rt3_domains = $DOMAINLIST #domains we accept emails for; separate multiples with a colon
RT3_URL = http://{IP Address of host}/rt/

You will need to replace the $**** values with your configuration information.

Create a new file /etc/exim4/conf.d/router/310_request_tracker3 and enter the following:

request_tracker3:
 debug_print = "R: request_tracker3 for \
 $local_part$local_part_suffix@$domain \
 (calling ${substr_1:${if eq{$local_part_suffix}{}\
 {-correspond}\
 {$local_part_suffix}}})"
 driver = redirect
 domains = +rt3_domains
 local_parts = mysql; QUEUENAME_QUERY
 local_part_suffix = -comment
 local_part_suffix_optional
 pipe_transport = request_tracker3_pipe
 data = "|/usr/bin/rt-mailgate \
 --queue \"${lookup mysql{QUEUENAME_QUERY}}\" \
 --action ${substr_1:${if eq{$local_part_suffix}{}\
 {-correspond}\
 {$local_part_suffix}}} \
 --url RT3_URL"
 user = www-data

Create a new file /etc/exim4/conf.d/transport/30_request-tracker3 and enter the following:

request_tracker3_pipe:
debug_print = "T: request_tracker3_pipe for $local_part@$domain"
driver = pipe
return_fail_output
allow_commands = /usr/bin/rt-mailgate

Once you have completed the customised exim4 files you should tell exim4 to regenerate its configuration. `update-exim4.conf`


Installing Request Tracker on Ubuntu 10.04 LTS

2010/10/13

This guide will help you to deploy a new Request Tracker (RT) 3.7.8 instance. At the end of this you will have the following:

  • A blank Request Track 3.7.8 instance
  • RT Integrated with Exim4 so you don’t need to manually edit config files to setup new queue email addresses.

Lets get started:

  • Run up your Ubuntu 10.04 LTS host as per your normal methods.
  • Install and configure the mail server.
    • `apt-get install exim4-daemon-heavy`
    • `dpkg-reconfigure exim4-config`
    • Configure exim4 as per the needs of your environment. Important points:
      • Do split your config into small files
  • Install and configure a database server. Up to you which one you want to use and if its local or remote. We will use a local one for this example.
    • `apt-get install mysql-server libdbd-mysql-perl`
    • Document the mysql root password you selected.
  • Install and configure Request Tracker
    • `apt-get install rt3.8-apache2 rt3.8-db-mysql request-tracker3.8 rt3.8-clients`
    • Choose your RT instance name. THIS IS IMPORTANT as it will appear in various places.
    • Handle RT_SiteConfig.pm permissions. Yes.
    • Use dbconfig. Generally you should probably say yes. It depends on the environment and your plans though.
    • Configure rt3 user password. Leave blank and let it generate one for you. Make sure you document it.

This will give you a default Request Tracker 3.8.7 instance on http://{ip_address_of_host}/rt