SQL Scripts for 3.6.x to 3.7 schema upgrade

Added by Dylan Etkin, last edited by Andrew Lui on Jun 26, 2008  (view change)
JIRA Documentation

Index
Audience People who are upgrading from pre-JIRA 3.6.x to post-3.7, and cannot use the recommended 'XML backup/restore' upgrade method because it would take too long.


If you are upgrading JIRA by the recommended method, ignore this page.



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.

These scripts will only work on JIRA 3.6.x databases (they refer to a table only added in 3.6). If you are upgrading from an earlier release, please:
  1. Download JIRA 3.6.5 Standalone
  2. Back-up your database, and create a copy to be upgraded to 3.7.
  3. Configure it to point to your 3.7 copy of the database
  4. (optionally) Edit atlassian-jira/WEB-INF/classes/jira-application.properties and set jira.autoexport=false to speed up the process.
  5. Start JIRA Standalone. By watching the logs (atlassian-jira.log or logs/catalina.out, you will see JIRA automatically upgrading tables to the 3.6.x format.
  6. Proceed with the instructions below.
If you are using HSQLDB with JIRA, you must follow the 'XML backup/restore' instructions in the Upgrading JIRA guide, as simply copying the .script file will not work. The format of the .script file has changed between the HSQLDB versions, and therefore, copying the .script file will result in the following error on startup.

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

  Name Size Creator (Last Modifier) Creation Date Last Mod Date Comment  
File sybase_3.7_migration.sql 0.5 kB Dylan Etkin Sep 20, 2006 Sep 20, 2006  
File mysql_3.7_migration.sql 0.7 kB Dylan Etkin Sep 20, 2006 Sep 20, 2006  
File sqlserver_3.7_migration.sql 12 kB Jed Wesley-Smith Mar 22, 2007 Mar 22, 2007  
File db2_3.7_migration.ddl 4 kB Justin Koke Oct 18, 2006 Oct 18, 2006  
File oracle_3.7_migration.sql 0.7 kB Justin Koke Oct 18, 2006 Oct 18, 2006  
File postgres_3.7_migration.sql 0.9 kB Justin Koke Oct 18, 2006 Oct 18, 2006  

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.