SQL Scripts for 3.6.x to 3.7 schema upgrade

Background

In general, there are two ways to upgrade JIRA's database, both of which are described in the Upgrading JIRA document:

• XML backup/restore (recommended) — doing a full XML export and import into the new database.
• Connect JIRA to a copy of your old database — connecting the new JIRA to the old database, and letting it automatically upgrade the database tables.

In 3.7.x, the 'Connect JIRA to a copy of your old database' method will not work. If you point JIRA 3.7.x to an older database, JIRA will print a warning and refuse to do anything. This is because a large number of database schema changes were made between 3.6 and 3.7 (see JIRA 3.7 Database Schema Changes) and these changes are too great for JIRA's database engine to upgrade automatically.

So, the vast majority of users should follow the XML backup/restore method described in the Upgrading JIRA document.

However, there are a handful of users with large installations, for which a full export/import is impractical as it takes a relatively long time. These users may need to use the 'Connect JIRA to a copy of your old database' method — This page is intended for these users. We have provided SQL scripts for each database, which will make the required changes to a 3.6.x database so it can be upgraded without a full export/import.

DB2 upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your DB2 database: db2 backup database sample to /home/db2/backups
3. Download the following script db2_3.7_migration.ddl and modify the connect statement within the file
4. Execute the script using the following command: db2 +c -t -v -f db2_3.7_migration.ddl
   If you see errors like:

   insert into SCHEMEISSUESECURITIES (select ID, SCHEME, SECURITY, TYPE, PARAMETER from TMP_SCHEMEISSUESECURITIES)
   SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a
   query is an empty table.  SQLSTATE=02000
   

   This is OK, it simply means that the inner SQL query did not return any data to be inserted into the new temporary table. This can occur if you are not using certain features in JIRA.
5. Point your new instalation of JIRA 3.7 at your DB2 database and watch for any errors during the statup sequence.
6. If you see any other errors please contact support for further assistance.

PostgreSQL 8+ upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your PostgreSQL database pg_dump -d database name > backup filename.sql
   • for example pg_dump -d jiradb > jiradb_365_14112006.sql
3. Download the following script postgres_3.7_migration.sql
4. Execute the script using the following command: psql -Ujirauser -v schema_name=public -d jiradb -f postgres_3.7_migration.sql
   1. Where -U is the username, -v is the name of your schema -d is the database -f is the location of the script file
5. Point your new instalation of JIRA 3.7 at your database and watch for any errors during the startup sequence.
6. If you see any errors please contact support for further assistance.

Oracle 10g upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your Oracle database. There are multiple strategies here, so we will leave this up to your DBA.
3. Download the following script oracle_3.7_migration.sql
4. Connect to SQL*Plus and execute the following script:

   SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 19 12:56:11 2006
   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
   With the Partitioning, OLAP and Data Mining options
   
   SQL> @/home/oracle/oracle_3.7_migration.sql
   

5. If you see any errors please contact support for further assistance.
6. Point your new instalation of JIRA 3.7 at your DB2 database and watch for any errors during the statup sequence.

Microsoft SQL Server upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your SQL Server database: osql -U username -P password -Q "BACKUP DATABASE db_name TO DISK = backup_path_and_filename"
   • for example osql -U sa -P secret -Q "BACKUP DATABASE jiradb TO DISK = 'C:\MyBackup.dat'"

1. Download the following script sqlserver_3.7_migration.sql
2. Execute the script: osql -U username -P password -d db_name -i mssql_3.7_migration.sql
   • for example osql -U sa -P secret -d jiradb -i sqlserver_3.7_migration.sql
3. If everything goes well the following should be displyed

   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'NOTIF_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'NOTIF_PARAMETER'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'SEVER_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'EVENT_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'PERM_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'PERM_PARAMETER'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'LAYOUT_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'SEC_TYPE'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'SEC_PARAMETER'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The COLUMN was renamed to 'POSITIONSEQ'.
   Caution: Changing any part of an object name could break scripts and stored procedures.
   The object was renamed to 'projectversion'.
   

4. Point your new instalation of JIRA 3.7 at your SQL Server database and watch for any errors during the statup sequence.

Sybase upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your SQL Server database
   • for example using isql tool

     1> dump database db_name to "backup_path_and_filename"
     2> go
     

3. Download the following script sybase_3.7_migration.sql
4. Execute the script: osql -U username -P password -D db_name -i sybase_3.7_migration.sql
   • for example osql -U sa -P -D jiradb -i sybase_3.7_migration.sql
5. If everything goes well the following should be displyed

   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Column name has been changed.
   (return status = 0)
   Object name has been changed.
   (return status = 0)
   

6. Point your new instalation of JIRA 3.7 at your Sybase database and watch for any errors during the statup sequence.

MySQL upgrade notes

1. Shutdown your JIRA instance
2. Perform a backup of your MySQL database: mysqldump --opt db_name > db_name.sql
   • for example mysqldump --opt jiradb > jiradb_before37.sql
3. Download the following script mysql_3.7_migration.sql
4. Execute the script: mysql --user=username --password=password db_name < mysql_3.7_migration.sql
   • for example mysql --user=root --password=password jiradb < mysql_3.7_migration.sql
5. Point your new installation of JIRA 3.7 at your MySQL database and watch for any errors during the statup sequence.

Attachments