Upgrade Fails due to Foreign Key Constraint Violation on Local Members Table
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
Symptoms
You are upgrading a version of Confluence from 3.1 or any version up to 3.4.x. This does not apply to Confluence 3.5 and above.
The following appears in the
atlassian-confluence.log:
1
2
3
4
5
6
7
2012-03-22 19:36:54,235 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table local_members add constraint FK6B8FB445117D5FDA foreign key (groupid) references groups (id)
2012-03-22 19:36:54,235 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Cannot add or update a child row: a foreign key constraint fails (`conf31`.<result 2 when explaining filename '#sql-6e0_9b'>, CONSTRAINT `FK6B8FB445117D5FDA` FOREIGN KEY (`groupid`) REFERENCES `groups` (`id`))
2012-03-22 19:36:54,250 ERROR [main] [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 (`conf31`.<result 2 when explaining filename '#sql-6e0_9b'>, CONSTRAINT `FK6B8FB445117D5FDA` FOREIGN KEY (`groupid`) REFERENCES `groups` (`id`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
Diagnosis
Run the following query against your database:
1
select count(1) from local_members where groupid not in (select id from groups);If the count is >0, you are encountering this issue.
Cause
The Confluence database has rows in the local_members table that reference groups that are no longer in the groups table. The upgrade tasks in Confluence 3.2 add a foreign key constraint to the local_members table, but the relationship cannot contain null values in the referenced table, hence the upgrade fails.
Solution
Resolution
Take a backup of your database.
Run the following query to remove the offending rows in your
local_memberstable:1select * from local_members where groupid not in (select id from groups);
Was this helpful?