View source for UpgradingFrom3011To344OnSolaris9

=== Oct 21 2005 14:06:42 ===

This is a description of my experience upgrading from RT 3.0.11 to 3.4.4

All went well! err... just kidding ;-)

==== Some Issues ====

* Solaris Issues
* Upgrading existing custom written scripts
* Installed version 5.8.5 of perl
* That is straightforward enough.
* Performance problems

==== Used Software ====

* apache 1.3.27
* mod_perl 1.29
* mod_ssl 2.3.14-1.3.27
* mysql 4.0.18

==== configuring CPAN ====

 'perl -MCPAN -e shell'

* asks to configure CPAN
* manually done according to

http://www.sun.com/bigadmin/features/articles/req_track_1.html

Thanks Amy! :-)

* CPAN command 'look' goes to unix shell -&gt; 'exit' to get back out.
* When installing from the cpan prompt we can use 'look "package"' to get to a shell prompt within that directory.
* carry out the make etc. commands and return to the cpan prompt with 'exit'
* This will allows us to install modules manually

==== compiling apache ====

* I compiled apache with static modules mod_perl and mod_ssl
* The instructions for this were from mod_perl-1.29
** the file INSTALL.simple.mod_ssl under the mod_perl tree
* there was a problem in a '.c' file where:
** "I have to edit the .c files of any app that makes a call to

 PEM_F_DEF_CALLBACK

* and replace that line with

 PEM_F_PEM_DEF_CALLBACK."

* This turned out to be in the file:
* ''''apache_1.3.27/src/modules/ssl/ssl_engine_pphrase.c''''

* Then from the mod_perl subdirectory I did:

 perl Makefile.PL USE_APACI=1 EVERYTHING=1 SSL_BASE=/usr/local/openssl \
 APACI_ARGS=--prefix=/usr/local/apache,--enable-module=ssl,--enable-module=rewrite

* That compiled perfectly.

==== ACTUAL UPGRADE ====

* The main reason for this upgrade was the fact that 'update.html' loaded very slowly.
* The database was taking a long time to process the query. More about the query later.
* The 'update.html' file (the file that invokes the query) is the file you request when you go to comment on a ticket.

----

* I started with a 3.0.11 install
* This setup has a database of about 5.6Gig (the size of the dump file)
* This install had the usual stuff with dependencies.
** That is, there were some problems with perl module dependencies that I had to solve manually.
* So the 3.0.11 install was working well except for the above mentioned problem namely:
* slow when executing a particular query

----

=== ACTIONS ===

==== PREPARATION ====

----

* tar up the existing rt3 directory and place the '.tar' file somewhere safe.
** (This will save the whole tree including modifications, and config for easy rollback if upgrade don't work)

* shutdown apache (webserver)
** make absolutely sure that the backup file is OK.
** Test for restorability.

* backup existing database with dumputility

* rename existing rt3 directory to rt3011

* unzip and untar newly downloaded rt-3.4.4.tar.gz

==== INSTALLATION ====

----

* configure and test for all modules

 ./configure --with-db-rt-pass='dadada' --prefix=/usr/local/rt3
 perl sbin/rt-test-dependencies --with-mysql --with-modperl1
 
 make upgrade

----

* run rt-setup-database on several directories

 /usr/local/rt3/sbin/rt-setup-database --dba root --prompt-for-dba-password \
 --action schema --datadir /your/rt/sourcedir/rt-3.4.4/etc/upgrade/&lt;version&gt;

* where version is each one of

 3.1.0
 3.1.15
 3.1.17
 3.3.0
 3.3.11

* 3.1.0 went quickly
* 3.1.15 had no sql statements and said just that namely:

 'Couldn't find schema file for mysql'

* 3.1.17 same
* 3.3.0 took considerably longer:

 started at 11:37 - ended: 12:17 -- 34 minutes
 (depending on the speed of your machine and size of your database)

* 3.3.11 started at 12:18 ended 12:18

----

* make changes to the [[RT SiteConfig|RT_SiteConfig]].pm file.
* I kept getting this error:

 '*error:*  no connection to syslog available at
 /webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/Log/Dispatch/Syslog.pm line 77'

* to deal with that read [[NoConnectionToSyslog]]
* I had to also make sure that the $organisation variable was set correctly

----

* make changes to apache's httpd.conf file.
* in this installation that meant changing the '[[DocumentRoot]]' and any other entries that refer to the rt3 directory.
* if you've installed the new RT version to a directory of the same name as the old, then you don't need to change anything

----

* apply any modifications that you may have made to your previous version.
* this may mean that some things are broken.
* in my case I need to change a few things, mainly where 'Ticket' became 'Object'

----

=== DEALING WITH PERFORMANCE PROBLEM ===

* As stated above, I need to do this upgrade because of speed/performance problems.
* This manifested itself mainly through the use of the 'comment' page.
* The following SQL query is the culprit:

 SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL ACL_2, Groups Groups_3, \
 CachedGroupMembers CachedGroupMembers_4  WHERE ((ACL_2.RightName = 'OwnTicket')) AND \
 ((CachedGroupMembers_4.MemberId = Principals_1.id)) \
 AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) \
 AND ((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) \
 AND ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) \
 AND  ( (    ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' \
 AND (   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = 'UserDefined' \
 OR Groups_3.Domain = 'ACLEquivalence')) \
 OR ( ( (Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 23)  \
 OR ( Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 238803)  ) \
 AND Groups_3.Type = ACL_2.PrincipalType) ) \
 AND (ACL_2.ObjectType = 'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue' \
 AND ACL_2.ObjectId = 23) )    ORDER BY main.Name ASC;

* I tried many things including:
** dropping all indexes on the Groups table and creating new indexes according to this article:
** http://issues.bestpractical.com/Ticket/Display.html?id=6624
** doing 'optimize' and 'analyze' tables in mysql:
** 'optimize table Users, Principals, ACL, Groups, [[CachedGroupMembers]];'
** 'analyze table Users, Principals, ACL, Groups, [[CachedGroupMembers]];'
* The optimize actually includes the analyze if I understand the mysql documentation correctly.
* The optimize made some difference in query speeds but on the whole, all of the above gave me no improvement.

* After a fairly intensive email exchange, which can be view on the rt-users mailing list starting on 25Oct2005.
* The thread is titled 'Huge Attachments table in 3.4.4'
* Jesse offered me some patches.
* 2 files named: 'clone.patch' and 'rights.patch'
* The 'rights.patch' file contains changes to the way SQL queries are formulated and includes changes to:

 Groups_Overlay.pm
 Principals_Overlay.pm
 Users_Overlay.pm

* The clone.patch file contains a change to the [[SearchBuilder]].pm file.
** For this to work, the [[DBIx]]::[[SearchBuilder]] package needs to be recompiled.
** To do that, I wanted to remove the existing [[DBIx]]::[[SearchBuilder]] package.
** Since that isn't the easiest thing to do in the Perl Module world, I wrote this mini script that does it.

 <nowiki>#!/usr/bin/perl
 
 use ExtUtils::Install;
 # you'll need to adjust the path to you .packlist file for that module
 $packlist = '/webhosting/stow/perl-5.8.5/perl5/lib/site_perl/5.8.5/sun4-solaris/auto/DBIx/SearchBuilder/.packlist';
 
 #print $packlist;
 
 uninstall($packlist);
 </nowiki>

* then I used cpan to download the [[DBIx]]::[[SearchBuilder]] package

 'perl -MCPAN -e shell'
 cpan&gt; get DBIx::SearchBuilder
 cpan&gt; look DBIx::SearchBuilder
 

* then I modified the code in [[SearchBuilder]].pm
* the compilation complained
* I needed to install Clone

 cpan&gt; install Clone

* Everything compiled nicely

* So now I reloaded the application by stopping.... and starting apache
* and....... the speed problem was solved

* However I found a problem probably caused by the patches, namely
* Members of groups were not showing up.
* I used mysqladmin -v processlist to find that it was the following query:

 SELECT DISTINCT main.* FROM Groups main , Principals Principals_1 \
 WHERE ((Principals_1.Disabled = '0')) AND  ((main.Domain = 'UserDefined')) \
 AND ((main.id = Principals_1.id)or(main.id != '2552'))    ORDER BY main.Name ASC

* Since there were no indexes on Principals.Disabled and Groups.Domain, I created those 2 indexes:

 create index Principals4 on Principals(Disabled);
 create index Groups5 on Groups(Domain);

* This seemed to have done the trick!
* I could no longer fault that page load.

=== THAT'S ABOUT IT ===