問題

Confluence upgrade fails with the following ERROR:

The following appears in the atlassian-confluence.log

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`))

診断

• Check if the imagedetails table DDL has the correct constraint. If the constraint FKA768048734A4917E is missing or does not have the correct references compared to the DDL below, please proceed to Cause 1.

  | 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 .

  select 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);

原因

1. Confluence detected the constraint was missing, however, it is unable to alter the table to add the constraint.  Please proceed to Resolution 1. 
2. 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.

ソリューション

Resolution 1:

1. 1. Confluence をシャットダウンします。
   2. Confluence データベースをバックアップします

   3. Run the query below to add the constraint into the table. The query below was tested in MySQL.

      ALTER TABLE IMAGEDETAILS ADD CONSTRAINT FKA768048734A4917E FOREIGN KEY (ATTACHMENTID) REFERENCES ATTACHMENTS(ATTACHMENTID);

   4. Start Confluence. The upgrade should now run as expected. 

       

Resolution 2:

1. 1. Confluence をシャットダウンします。
   2. Confluence データベースをバックアップします

   3. Run the following queries:

      create 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;

   4. Start Confluence. The upgrade should now run as expected.