Database Upgrades to 6.5 and 6.6 from Previous Versions

FootPrints 6.5 and 6.6 contain performance enhancements that by default apply to new projects only.

FootPrints 6.5 enhancements:
 

• Description index in master table for faster keyword searches on large databases (not applicable to FootPrints database versions)
• Full-text indexes on multi-line fields and Description field for MySQL and Microsoft SQL Server database versions
• Indexing for all non-FootPrints database versions on the Priority and mrUrgent columns added.

FootPrints 6.6 enhancements:

• An mrTIMESTAMP column is added to MS SQL Server versions database only.

FootPrints 6.0 to 6.5

The script MR60to65.pl can be run from the command line to update existing projects. This upgrade will improve search performance for databases containing very large numbers of Issues.  It will not necessarily improve performance for smaller databases, so we only recommend running this utility if your database is very large, and/or you would like to increase performance and you have performed all of the steps outlined in the topic on Performance.

IMPORTANT CONSIDERATIONS:

• Make sure you backup your database first!
• No one should access FootPrints or the FootPrints database while the upgrade is taking place. We recommend you pause or disable the web server.
• Make sure you only run this utility during off-peak hours, or you risk creating the index incorrectly!
• You must allot sufficient time for this upgrade. The process can take from 1-3 hours (for 10,000 tickets or less), to 1-2 days (500,000+ tickets). It has been successfully tested on 1 million tickets.

The upgrade script will add a new column called "mrALLDESCRIPTIONS" to each MASTERx table. This column will contain the current and all appended descriptions, allowing you to search all descriptions without having to a table join on MASTERx and MASTERx_DESCRIPTIONS. This will improve performance for all databases.

For Microsoft SQL Server and MySQL, this column and all text columns (for example, mrDESCRIPTION and all multi-line project or address book fields) will get a full-text index added to them. This will allow us to use the CONTAINS, in the case of MS SQL, or MATCH in the case of MySQL operators to search these columns which is faster than LIKE and will also improve performance.

Also, for all databases, it will add a new index on the mrPRIORITY and mrURGENT columns.

Here is an example of how to run the script, 'MR60to65.pl', assuming FootPrints is installed in C:\FootPrints:

Open a DOS command prompt, and type:

cd \FootPrints\cgi

C:\FootPrints\bin\perl\bin\perl MR60to65.pl ALL

When the upgrade is complete, the command prompt will return. No one should access FootPrints or the database while the upgrade is taking place.

This will upgrade ALL projects. If, for some reason, you want to only do this for one project, the CMMASTER and ABMASTER environment variables can be set for a particular project and then run the script without the ALL parameter. For example:

cd \FootPrints\cgi

set CMI=c:\FootPrints

set CMMASTER=c:\FootPrints\db\MASTER1

set ABMASTER=c:\FootPrints\db\ABMASTER1

C:\FootPrints\bin\perl\bin\perl MR60to65.pl

FootPrints 6.5 to 6.6

This enhancement applies to MS SQL Server only. It is run automatically at time of upgrade. Upgrades may take a few extra minutes depending on the size of your database. The upgrade wizard will automatically go to the next step when this step is complete.

FootPrints 6.0 to 6.6

If you are upgrading from 6.0 to 6.6 on MS SQL Server, you can upgrade directly to 6.6. After upgrading you  can run MR60to65.pl as described above.