...

We have been happily using OTRS 1.3.1 on Suse 9 since 2005 and it has been working flawlessly (I know v1.3.1 is very old but works the way we want it so there has never been an incentive to upgrade!) .

A feature we use is FreeText fields which we pre-fill using an sql statement in a Perl script that is run when an new ticket with a keyword in its subject field is automatically moved to a certain queue.

This is interesting, can you explain how have you done it?

As I said in my original e-mail, up to now I do not have much experience with how our OTRS works (so far I have been just a user). I have UNIX and some MySQL experience so following my post I have started to investigate how this works (the person who installed and configured our OTRS back in early 2005 was a contractor and didn't document anything - he left in 2006). My initial investigation shows that we have two Perl scripts in the /opt/otrs/CustomCode directory called NewStarts.pl and Leavers.pl which are run when a ticket arrives for someone new joining our office and the other is run when a ticket arrives for someone leaving. These scripts fill in the free text fields differently depending on the Newstart or Leave text string in the e-mail subject field (I have listed both Perl scripts at the end of this e-mail). Searching the dump of the otrs database show that Leavers.pl is found once: INSERT INTO generic_agent_jobs VALUES ('Move Leavers from Incoming','NewCMD','/opt/otrs/CustomCode/Leavers.pl'); It may take me some time but if I find out how this all works then I will let you know.

...

This brings me to thinking about upgrading as our system is old but we only have our working system and we have no backup system that I can test a upgrade on. Also, I have inherited the OTRS system so I do not know much about it.

The upgrade process is straighforward unless you have customized otrs modifying PM files. You can do a test upgrade during your off-peak times by backing up your DB and backing up the entire OTRS directory. Just remember to "suspend" getting email from your incoming otrs mail account , aka comment out a cron job: this way if something goes wrong, at least you can restore your previous OTRS without loosing any regular email. Otherwise you can temporarily change your OTRS email (from OTRS configuration) to a new account, just for testing incoming emails, so your original mailbox will be left untouched in case of rollback.

We had some problems regarding the DB upgrade but we switched from MSSQL to MYSQL during the process, so if you already are on mysql you should experience no problems.

As I said in my other reply, I may use the following command to make a copy of the whole disk when the PC is rebooted with a Knoppix CD: dd if=/dev/hda bs=1k conv=sync,noerror | gzip /media/sda1/<filename>.gz and then I will try an upgrade ensuring that there is no network connection so that e-mail will be disabled.

...

Question:

From reading the various documents it seems that the process of upgrading OTRS must be done in steps. Am I correct in that I cannot go from version 1.3.1 straight to the present version but must do it in steps. If I am correct, what steps must be taken to get up to the current version of OTRS?

The steps involve upgrading the database by using the provided scripts in the tar.gz you downloaded and replace OTRS files. The rest of OTRS (PM configuration) can be imported, if I remember well. Maybe docs or other people can help you better than me for this part.

It has been confirmed that I must go from 1.3.1 to 2.0 and then each point release at a time after that.

...

Question:

Reading the UPGRADING file that came with version 1.3.1 (I see that it is present in every subsequent release) one of its points says "Backup everything (database, Kernel/Config.pm, Kernel/Config/GenericAgent.pm, var/*)".

I can backup the files and directories mentioned but is there any documentation of how to backup the MySQL database as I have little experience with it?

For mysql just do from shell: $ mysqldump otrsdbname >otrsdump.sql

Then backup the entire OTRS directory.

The mysql documentation is your friend: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

After my post I found that each night our OTRS database is backed up to a file and then copied with other OTRS files to a Solaris server which is later in the night backed up to tape. The backup of the database uses mysqldump. Thanks for your reply. Paul McIlfatrick The original NewStarts.pl file - I have now added extra free text fields so that all 8 are used. NewStarts.pl ============ #!/usr/bin/perl -w use DBI; $TicketNumber = $ARGV[0]; # $TicketID = $ARGV[1]; $dbh = DBI->connect("dbi:mysql:otrs:otrs.<domain>", "<user>", "<password>"); $contents = ""; $emailText="Not Done"; $phoneText="Not Done - NU"; $assetText="Not Done"; $pcText="Not Done"; # Get contents from submitted new start form $sql = "SELECT article.a_body FROM article,ticket WHERE ticket.id = article.ticket_id AND article.article_type_id = 1 AND article.a_subject LIKE 'New User%' AND ticket.tn = $TicketNumber"; $sth = $dbh->prepare($sql); $sth->execute(); @row = $sth->fetchrow_array; $contents = $row[0]; $sth->finish; # search for "Location: Off Shore" and change emailText @lines = split(/\n/,$contents); foreach my $line_val (@lines){ if ($line_val =~ m/^Location:/i && $line_val =~ m/Off Shore/i){ $emailText="Done - N/A OS"; $phoneText="Done - N/A OS"; $assetText="Done - N/A OS"; $pcText="Done - N/A OS"; } elsif ($line_val =~ m/^User Start Date:.*/i && $pcText !~ /^Done.*/){ $line_val =~ s/User Start Date://; $line_val =~ s/[^0-9\/]//g; if ($line_val !~ m/\d\d\/\d\d\/\d\d*/){ $line_val=""; } $pcText = "Not Done - $line_val"; } } $sql = "update ticket set freekey1='EMail', freetext1='$emailText', freekey2='Phone', freetext2='$phoneText', freekey3='Assets', freetext3='$assetText', freekey4='PC', freetext4='$pcText' WHERE tn='$TicketNumber'"; $sth = $dbh->prepare($sql); $sth->execute || die "Could not execute SQL statement ... maybe invalid?"; $dbh->disconnect; The original Leavers.pl file - I have now added extra free text fields so that all 8 are used. Leavers.pl ========== #!/usr/bin/perl -w use DBI; $TicketNumber = $ARGV[0]; # $TicketID = $ARGV[1]; $dbh = DBI->connect("dbi:mysql:otrs:otrs.<domain>", "<user>", "<password>"); $sql = "update ticket set freekey1='EMail', freetext1='Not Done', freekey2='Phone', freetext2='Not Done - LE', freekey3='Assets', freetext3='Not Done', freekey4='PC', freetext4='Not Done' WHERE tn='$TicketNumber'"; $sth = $dbh->prepare($sql); $sth->execute || die "Could not execute SQL statement ... maybe invalid?";

I have no experience with 1.3, but perhaps it's a config option (like in 2.x) to enable (1) the freefields you want in the FreeText screen. With your suggestion that it might be a configuration option, this morning I did a search of /opt/otrs/... for 'FreeKeyField' and found that in the /opt/otrs/Kernel/Output/HTML/Standard/AgentFreeText.dtl file the lines with TicketFreeKeyField5..8 were commented out with '#'! To get 8 FreeTeext field displayed in the 'Free Text' option all I had to do was remove the '#' characters from this file! Furthermore, I have a test&accept and a 'play-around' OTRS environment running in a VMWare server environment (Fedora), I can highly recommend this to you. You can copy your production and try out the upgrade steps. (Up to a point you may even have a replacement running.) Running Ubuntu or Fedora recommended. Rather than go down the VM route, I may disconnect the network cable from the OTRS PC, attach a large USB external drive and then reboot the PC with a Knoppix DVD and backup the whole 40Gbyte disk using the command: dd if=/dev/hda bs=1k conv=sync,noerror | gzip /media/sda1/<filename>.gz After doing this, and still with no network connection so no new tickets are raised, I can then try an upgrade while having the option of restoring the disk back to the way it was. Like the UPGRADING file will tell you, you will have to go through the different versions to upgrade. And still there maybe some catches in the process. As I gathered from reading the documentation with each release. Thanks Paul A feature we use is FreeText fields which we pre-fill using an sql statement in a Perl script that is run when an new ticket with a keyword in its subject field is automatically moved to a certain queue. With OTRS 1.3.1 we can have up 8 FreeText fields and when you zoom in on a ticket all these are displayed on the right of the window. However, when you click on the 'Free Text' option only 4 FreeText fields are presented for editing! I am sure this has been fixed in a later version of OTRS but is it something easily fixed in the present version by editing a file/files? This brings me to thinking about upgrading as our system is old but we only have our working system and we have no backup system that I can test a upgrade on. Also, I have inherited the OTRS system so I do not know much about it.