Upgrading a GPM Installation

---

Revision as of 18:56, 29 August 2008

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.

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.