Upgrade to 5.7.x fails due to IMAGEDETAILS constraint FKA768048734A4917E
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Problem
Confluence upgrade fails with the following ERROR:
The following appears in the atlassian-confluence.log
1
2
3
4
2015-03-31 18:14:56,816 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table IMAGEDETAILS add constraint FKA768048734A4917E foreign key (ATTACHMENTID) references CONTENT (CONTENTID)
2015-03-31 18:14:56,816 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Cannot add or update a child row: a foreign key constraint fails (`confluence`.`#sql-5690_1963`, CONSTRAINT `FKA768048734A4917E` FOREIGN KEY (`ATTACHMENTID`) REFERENCES `CONTENT` (`CONTENTID`))
2015-03-31 18:14:56,817 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`confluence`.`#sql-5690_1963`, CONSTRAINT `FKA768048734A4917E` FOREIGN KEY (`ATTACHMENTID`) REFERENCES `CONTENT` (`CONTENTID`))
Diagnosis
Check if the
imagedetailstable DDL has the correct constraint. If the constraintFKA768048734A4917Eis missing or does not have the correct references compared to the DDL below, please proceed to Cause 1.1 2 3 4 5 6 7 8| IMAGEDETAILS | CREATE TABLE `IMAGEDETAILS` ( `ATTACHMENTID` bigint(20) NOT NULL, `HEIGHT` int(11) DEFAULT NULL, `WIDTH` int(11) DEFAULT NULL, `MIMETYPE` varchar(30) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ATTACHMENTID`), CONSTRAINT `FKA768048734A4917E` FOREIGN KEY (`ATTACHMENTID`) REFERENCES `ATTACHMENTS` (`ATTACHMENTID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
Run the following query to check to see if there are any entries in the IMAGEDETAILS table that don't exist in the ATTACHMENTS table. If any NULL values are returned, please proceed to Cause 2.
1select i.ATTACHMENTID, a.ATTACHMENTID from IMAGEDETAILS i left join ATTACHMENTS a on i.ATTACHMENTID = a.ATTACHMENTID where i.ATTACHMENTID not in (select ATTACHMENTID from ATTACHMENTS);
Cause
Confluence detected the constraint was missing, however, it is unable to alter the table to add the constraint. Please proceed to Resolution 1.
There are entries in the IMAGEDETAILS table that don't exist in the ATTACHMENTS table. Most likely, when an attachment was deleted the call to delete it from IMAGEDETAILS either didn't happen or failed. Please proceed to Resolution 2.
Solution
Resolution
Resolution 1:
Shut down Confluence
Backup your Confluence database
Run the query below to add the constraint into the table. The query below was tested in MySQL.
1ALTER TABLE IMAGEDETAILS ADD CONSTRAINT FKA768048734A4917E FOREIGN KEY (ATTACHMENTID) REFERENCES ATTACHMENTS(ATTACHMENTID);Start Confluence. The upgrade should now run as expected.
Resolution 2:
Shut down Confluence
Backup your Confluence database
Run the following queries:
1 2 3 4 5create table delete_me as (select i.ATTACHMENTID from IMAGEDETAILS i left join ATTACHMENTS a on i.ATTACHMENTID = a.ATTACHMENTID where i.ATTACHMENTID not in (select ATTACHMENTID from ATTACHMENTS)); delete from IMAGEDETAILS where ATTACHMENTID in (select * from delete_me); drop table delete_me;Start Confluence. The upgrade should now run as expected.
Was this helpful?