How does Jira change its database schema (eg expand the length of existing columns) between versions

[Khuong Vu]

I have seen seen a number of errors like this recently:

java.sql.SQLException: Cannot change column 'OBJECT_ATTRIBUTE_ID': used in a foreign key constraint 'fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id'

OR

java.sql.SQLException: Cannot change column 'ID': used in a foreign key constraint 'fk_ao_589059_audit_item_asc_item_audit_item_id' of table 'jiradb.AO_589059_AUDIT_ITEM_ASC_ITEM'

I would fix the first error with 2 alter tables commands:

1. ALTER TABLE AO_8542F1_IFJ_OBJ_ATTR_VAL DROP FOREIGN KEY fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id;
2. RESTART JIRA.
3. ALTER TABLE AO_8542F1_IFJ_OBJ_ATTR_VAL ADD CONSTRAINT fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id
   FOREIGN KEY (OBJECT_ATTRIBUTE_ID) REFERENCES AO_8542F1_IFJ_OBJ_ATTR(ID);

I would fix the 2nd error with 2 more command:

1. ALTER TABLE AO_589059_AUDIT_ITEM_ASC_ITEM DROP FOREIGN KEY fk_ao_589059_audit_item_asc_item_audit_item_id;
2. RESTART JIRA
3. ALTER TABLE AO_589059_AUDIT_ITEM_ASC_ITEM ADD CONSTRAINT fk_ao_589059_audit_item_asc_item_audit_item_id
   FOREIGN KEY (AUDIT_ITEM_ID) REFERENCES AO_589059_AUDIT_ITEM(ID);

I kept going like this a few more times because there was one new SQLException come up each time I restarted Jira.  Can anyone tell me what is wrong? 

PS.  I have created a support zip file, but this form does not allow attachment.

[Nic Brough -Adaptavist-]

Just to reassure you that you're not doing anything wrong - the support .zip file is for Atlassian support, not Community - we can't do much with one because we have no access to your system!  Atlassian can't either if it is a Server system, but the file does contain stuff they can use to start investigating your issue.

However.  You should not ever have to be messing with the database behind an Atlassian system.  It's something you should only do when Atlassian support tells you to (there's some good reasons to be reading one for some odd queries, but even then, it's mostly about upgrades or migrations, never day-to-day usage)

Jira's upgrade code is not that complex, from the outside, to us humans, when it comes to the database.  It is build to run, alter your database and change data to match, all without us humans having looked at it.

The errors you are getting are likely to be down to the database being misconfigured - the Jira user having the right access for running normally, but not having the "altar" rights it needs to perform an upgrade properly.

I am sorry, but to fix this, you are going to need to restore to the backup you took before upgrading, grant the Jira user all the rights it needs to the database, and then re-do the upgrade.

[Khuong Vu]

Nic, thanks for your quick and insightful response. 

I think you are on to something, because our IT department has recently changed the file/directory permissions on our Jira server.  

As soon as we figure out the problem with our Jira user in terms of its permission to alter tables, we would definitely update this post.