Upgrading a GPM Installation

This is a set of instructions for the steps required to upgrade the software that makes up a Windows installation of The GPM. The components are a MySQL installation, an Apache installation and a Perl installation. While this set of directions is specific to a Windows installation of the software suite, the basic directions should still apply to a UNIX-like environment.

Notes regarding these directions:

• File names and text to enter at a command prompt will be shown in typewriter font.
• Text enclosed in parentheses in file names, paths or console commands should be replaced by the correct text for your specific installation, without the parentheses. E.g., if your MySQL service runs as MySQL4, the console command to stop the service should be written as net stop MySQL4.
• Remember to back up all configuration files.

MySQL Upgrades

Upgrading MySQL 4 to MySQL 5

Note: this set of directions applies to upgrading from MySQL version 4.0.12 to 5.0.67, but should work from any 4.0 to any 5.0 version. The official upgrade guide for MySQL can be found at Upgrading MySQL. Directions specific to the GPM can be found below.

1. Download version 5 from MySQL Downloads.
2. Copy the existing my.ini file to the desktop or some other location outside the MySQL installation folder (by default, this is C:\Program Files\MySQL\MySQL Server (version number)\. This will allow you to replace the automatically generated my.ini file to maintain your previous settings.
3. Back up your MySQL data. Do this however you see fit; either from the command line with the mysqldump utility, or by using the DOS xcopy utility after stopping the MySQL service. Do not skip this step. MySQL AB has done an excellent job at making upgrades as smooth as possible, but problems can always occur.
4. Stop the MySQL service. You can do this either from the Services Control Panel in the Administrative Tools folder, or by typing net stop (MySQL service name) at the command prompt. The service name will be displayed in the Services Control Panel.
5. Remove the MySQL service. You can do this by typing C:\(MySQL Installation Path)\bin\mysqld --remove on the command line.
6. Install the new version of MySQL. Follow the directions as they appear, and answer questions as prompted. You can either install the new version over the old, or into a new directory. However, if you install into a new directory, take care to not delete your old data directory, as the default data directory for MySQL is C:\Program Files\MySQL\MySQL Server (version number)\data.
7. Configure the new version of MySQL. The official documentation for the Configuration Wizard can be found at Configuration Wizard. Recommended settings:
   1. Configuration type: "Detailed Configuration"
   2. Server Type: "Server Machine" (unless your GPM installation has a dedicated MySQL server)
   3. Database Usage: "Multifunctional Database"
   4. InnoDB Tablespace: use the default settings
   5. Concurrent Connections: use the default settings
   6. Networking and Strict Mode: use the default settings
   7. Character set: set as required for your environment (thegpm.org uses the default setting)
   8. Security Options: choose a root password and do not create an anonymous account. Note: this root password will be transient, as it will be superseded by the root password stored in the mysql database, at which you will point the new server version in a later step.
   9. Confirmation: click the "Execute" button and ensure all four steps complete successfully.
      1. If the service will not start, it may be due to not having removed the old service. Click the "Back" button; at a command prompt, type C:\(MySQL Installation Path)\bin\mysqld --remove and then click "Execute" again.
      2. If the security settings did not apply correctly, click the "Back" button and try again. If the settings will still will not apply correctly, please refer to Troubleshooting a MySQL Installation Under Windows.
8. Stop the MySQL service, either from the command line or the Services Control Panel.
9. Open an Explorer window to the new MySQL installation directory (by default, C:\Program Files\MySQL\MySQL Server 5.0\).
10. Rename the automatically created my.ini file. Copy your backed-up my.ini file into this directory.
11. Edit the my.ini file in the installation directory. Just above the line reading
    #*** INNODB Specific options ***
    insert the following lines:

    # sets pointer size to allow tables up to 256 TB (integer between 2 and 7
    # for number of bytes to allocate for the row pointer).
    myisam_data_pointer_size=6

12. Save the edited my.ini file.
13. Restart the MySQL Service. This will start the database server with the newly set cursor length, which we will use below.
    • If the service does not restart, check your my.ini file for errors such as typos or missed comment characters (#).
14. Open a console window. Navigate to the bin folder of your new MySQL installation.
15. Type mysql_upgrade.exe -uroot -p, hit enter, then supply the root user password. This will scan and upgrade any tables in your database to the new table structure used by MySQL 5.0. Take note of any warnings generated by the upgrade program; they may not get fixed automatically. This step may take over an hour to complete. One of the things this upgrade should do is change the maximum amount of data you can store in the peptide table. By default, MySQL version 4 had a maximum table size of roughly 4 gigabytes. By changing the cursor length in the my.ini file, the maximum table size should be set to 256 terabytes.
16. Log into your mysql installation as root.
    1. type: use gpmdb;
    2. type: show table status like 'peptide'; If the Data_length value is approaching the Max_data_length value, type: repair table peptide;. This should reset the maximum size of the table to a much larger number. This step may take many minutes to complete, depending on the size of the peptide table.
    3. type: show table status like 'peptide';. Check the Max_data_length value and compare it to the size before the repair.
       • If the number has changed to a much larger value, the upgrade is complete.
       • If the number has not changed, manually set the value as follows:
       1. Record the Avg_row_length value from the above output of show table status like 'peptide'; (In the example below, I will use 75). Choose a very large number in comparison to your current number of rows in the peptide (for the example below, I will use one billion).
       2. Type: alter table peptide max_rows=1000000000 avg_row_length=75; This will take several minutes to complete.
17. The upgrade is now complete.

Migrating MySQL from 32-bit to 64-bit

Prerequisites, 32-bit installation: you must have sufficient storage space on your 32-bit installation for roughly five times the space of your current database storage. E.g., if your GPMDB installation takes up 30 gigabytes (GiB) of disk space, you may need up to 150 GiB of storage to create the database dump files.

Prerequisites, 64-bit installation: a bare installation of 64-bit MySQL is preferred, but not required. The directions below will remove any existing tables/databases from the various databases that make up an installation of GPMDB including the usernames and passwords defined in the mysql database. If there is information already existing on your 64-bit MySQL installation in databases that share names with any of the tables in GPMDB, back them up before proceeding. If the 64-bit installation is pristine and otherwise unused, there is no data to back up, so that step can be skipped.

• On the 32-bit installation:

1. Go to a command prompt.
2. Navigate to the \bin\ directory of your MySQL installation.
3. Type: mysqldump -u(root-like username) -p --all-databases -f --add-drop-database --add-drop-table --verbose --result-file=c:\path\to\(dump_file_name). This may take quite some time to run, depending on the size of your installation.
   1. -u: the username for the MySQL installation login.
   2. -p: tells the program to prompt the user for the password for the username defined after the -u option.
   3. --all-databases: dumps information for all databases in the installation.
   4. -f: forces the dump process to continue if there are errors.
   5. --add-drop-database: adds the 'drop database' command before creating the database, to ensure the table structures remain the same.
   6. --add-drop-table: adds a 'drop table' command before creating each table, to ensure the numbering of auto_increment fields remains consistent.
   7. --verbose: this makes the mysqldump program print out status information as it writes out the database information to disk. This is purely as a metric to help you gauge how much time remains in the dump process, and to possibly assist in troubleshooting, should problems arise. It can be left out.
   8. --result-file: the path and name of the dump file to be created. Without this option specified, the data is printed to the console and would need to be redirected to be written to disk.
4. If desired, compress the output before transferring it to the 64-bit installation.

• On the 64-bit installation:

1. Ensure you have backed up any existing information in the 64-bit database you do not want to lose, such as other mysql users, if any.
2. Place the (dump_file_name) file you created on the 32-bit installation to some location on the 64-bit installation (e.g., C:\data\(dump_file_name)) and ensure it is uncompressed.
3. Go to a command prompt.
4. Navigate to the \bin\ directory of your MySQL installation.
5. Log into MySQL as a root-like user: type mysql -u(root-like username) -p
6. At the MySQL prompt, type: source c:\data\(dump_file_name); This will start the importation process, which will take quite some time. Once it completes, use the web interface to check to ensure that the data accessible through the 64-bit installation matches what is available through the web interface on the 32-bit installation.