JIRA 4.0 Database Schema Changes for MySQL and Oracle

If you are using the MySQL or Oracle database, two column data types have been changed as described in the JIRA 4.0 Database Schema Changes for MySQL and Oracle section below.

Recommended method of upgrade

The easiest way to upgrade to JIRA 4.0 is to perform an XML backup and restore as described in the Migrating JIRA to Another Server instructions.

Alternative method of upgrade

If in the past, instead of performing an XML backup and restore, you have been upgrading by "pointing" the new version of JIRA at an old database, this is still possible. However, the procedure is more complicated. You will need to use SQL scripts to perform database schema changes.

MySQL notes for alternative method of upgrade

1. Shutdown your JIRA instance.
2. Perform a backup of your MySQL database as follows:

   mysqldump --opt db_name > db_name.sql

   e.g.:  mysqldump --opt jiradb > jiradb_before4.sql
3. Download the attached mysql_4_0.sql script.
4. Execute the following script:

   mysql --user=username --password=password db_name < mysql_4_0.sql

   e.g.:  mysql --user=root --password=password jiradb < mysql_4_0.sql
5. If you see any errors, please contact Support for further assistance.
6. Point your new installation of JIRA 4.0 at your MySQL database and watch for any errors in the log during the startup sequence.

Note: The following warnings regarding database changes can be ignored. They will only appear the first time you start JIRA after upgrading your JIRA database to 4.0.

main WARN     [core.entity.jdbc.DatabaseUtil] Entity "ExternalGadget" has no table in the database
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "GadgetUserPreference" has no table in the database
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "Issue" has 24 fields but table "jiraissue" has 23 columns.
main WARN     [core.entity.jdbc.DatabaseUtil] Field "resolutiondate" of entity "Issue" is missing its corresponding column "RESOLUTIONDATE"
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "PortletConfiguration" has 7 fields but table "portletconfiguration" has 6 columns.
main WARN     [core.entity.jdbc.DatabaseUtil] Field "gadgetXml" of entity "PortletConfiguration" is missing its corresponding column "GADGET_XML"

Oracle notes for alternative method of upgrade

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 attached oracle_4_0.sql script.
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_4_0.sql
   

5. If you see any errors, please contact Support for further assistance.
6. Point your new installation of JIRA 4.0 at your Oracle database and watch for any errors in the log during the startup sequence.

Note: The following warnings regarding database changes can be ignored. They will only appear the first time you start JIRA after upgrading your JIRA database to 4.0.

main WARN     [core.entity.jdbc.DatabaseUtil] Entity "ExternalGadget" has no table in the database
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "GadgetUserPreference" has no table in the database
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "Issue" has 24 fields but table "jiraissue" has 23 columns.
main WARN     [core.entity.jdbc.DatabaseUtil] Field "resolutiondate" of entity "Issue" is missing its corresponding column "RESOLUTIONDATE"
main WARN     [core.entity.jdbc.DatabaseUtil] Entity "PortletConfiguration" has 7 fields but table "portletconfiguration" has 6 columns.
main WARN     [core.entity.jdbc.DatabaseUtil] Field "gadgetXml" of entity "PortletConfiguration" is missing its corresponding column "GADGET_XML"

JIRA 4.0 Database Schema Changes

The table below summarises the changes to the database schema. Please note that if you have developed any custom utilities which query or modify the JIRA database directly (i.e. without using the JIRA API), you need to check whether the utilities need to be updated.

Column data types

The following database column data types have been changed. Their column name, old and new data types, as well as the database table they belong to, are shown below:

For MySQL:

For Oracle: