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?";