Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Edited

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.

1 answer

1 vote

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.

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.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Jira Service Management

Next level incident management in Jira Service Management

Hello Atlassian Community! We’re thrilled to announce some exciting changes to incident management in Jira Service Management Cloud, in order to give your teams extra muscle during incidents, and ...

1,463 views 20 12
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you