Upgrade Fails due to Foreign Key Constraint Violation on Local Members Table
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:
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:
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.
Resolution
- Take a backup of your database.
Run the following query to remove the offending rows in your
local_memberstable:select * from local_members where groupid not in (select id from groups);
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.