Many primary key constraints are missing during upgrade

Andrew Ardill

We are upgrading from Confluence version 3.0.1 to 3.5.13

During the upgrade process, multiple errors are encountered (though each causes the upgrade progress to halt - after fixing the issue and resuming the next issue is found).

We are using Microsoft SQL Server 2008.

The errors are all very similar. They relate to either a foreign or primary key constraint that is missing or can't be created.

Some examples:

ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Unsuccessful: alter table external_members add constraint FKD8C8D8A5117D5FDA foreign key (groupid) references groups
ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Foreign key 'FKD8C8D8A5117D5FDA' has implicit reference to object 'groups' which does not have a primary key defined on it.

ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Unsuccessful: alter table CONTENT_PERM_SET add constraint FKBF45A7992CAF22C1 foreign key (CONTENT_ID) references CONTENT
ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Foreign key 'FKBF45A7992CAF22C1' has implicit reference to object 'CONTENT' which does not have a primary key defined on it.

ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Unsuccessful: alter table IMAGEDETAILS add constraint FKA768048734A4917E foreign key (ATTACHMENTID) references ATTACHMENTS
ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Column 'ATTACHMENTS.ATTACHMENTID' is not the same data type as referencing column 'IMAGEDETAILS.ATTACHMENTID' in foreign key 'FKA768048734A4917E'.

ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Unsuccessful: alter table external_members add constraint FKD8C8D8A5117D5FDA foreign key (groupid) references groups
ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Foreign key 'FKD8C8D8A5117D5FDA' has implicit reference to object 'groups' which does not have a primary key defined on it.

ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Unsuccessful: alter table external_members add constraint FKD8C8D8A5F25E5D5F foreign key (extentityid) references external_entities
ERROR main hibernate.tool.hbm2ddl.SchemaUpdate execute Foreign key 'FKD8C8D8A5F25E5D5F' has implicit reference to object 'external_entities' which does not have a primary key defined on it.

To resolve these issues we have had to run queries like the following:

alter table CONTENT add constraint PK_CONTENT primary key (CONTENTID)
alter table ATTACHMENTS add constraint PK_ATTACHMENTS primary key (ATTACHMENTID)
ALTER TABLE IMAGEDETAILS DROP CONSTRAINT PK_IMAGEDETAILS //not actual constraint name
alter table IMAGEDETAILS alter column ATTACHMENTID decimal (19,0) not null
alter table IMAGEDETAILS add constraint PK_IMAGEDETAILS primary key (ATTACHMENTID)
alter table groups add constraint PK_groups primary key (id)
alter table external_entities add constraint PK_external_entities primary key (id)

Do you know why this might have happened? What is the best way to proceed, as we are still getting errors and many tables that don't have primary keys seem like they should?

Andrew Ardill

Still getting lots of problems, but thought we would share what has helped so far.

We need a script to restore all of the primary keys:

alter table ATTACHMENTDATA add constraint PK_ATTACHMENTDATA primary key (ATTACHMENTDATAID)
alter table ATTACHMENTS add constraint PK_ATTACHMENTS primary key (ATTACHMENTID)
alter table BANDANA add constraint PK_BANDANA primary key (BANDANAID)
alter table BODYCONTENT add constraint PK_BODYCONTENT primary key (BODYCONTENTID)
alter table CLUSTERSAFETY add constraint PK_CLUSTERSAFETY primary key (CLUSTERSAFETYID)
alter table CONFANCESTORS add constraint PK_CONFANCESTORS primary key (DESCENDENTID,ANCESTORPOSITION)
alter table CONFVERSION add constraint PK_CONFVERSION primary key (CONFVERSIONID)
alter table CONTENT add constraint PK_CONTENT primary key (CONTENTID)
alter table CONTENT_LABEL add constraint PK_CONTENT_LABEL primary key (ID)
alter table CONTENT_PERM add constraint PK_CONTENT_PERM primary key (ID)
alter table CONTENT_PERM_SET add constraint PK_CONTENT_PERM_SET primary key (ID)
alter table CONTENTLOCK add constraint PK_CONTENTLOCK primary key (CONTENTLOCKID)
alter table cwd_app_dir_group_mapping add constraint PK_cwd_app_dir_group_mapping primary key (id)
alter table cwd_app_dir_mapping add constraint PK_cwd_app_dir_mapping primary key (id)
alter table cwd_app_dir_operation add constraint PK_cwd_app_dir_operation primary key (app_dir_mapping_id,operation_type)
alter table cwd_application add constraint PK_cwd_application primary key (id)
alter table cwd_application_attribute add constraint PK_cwd_application_attribute primary key (application_id,attribute_name)
alter table cwd_directory add constraint PK_cwd_directory primary key (id)
alter table cwd_directory_attribute add constraint PK_cwd_directory_attribute primary key (directory_id,attribute_name)
alter table cwd_directory_operation add constraint PK_cwd_directory_operation primary key (directory_id,operation_type)
alter table cwd_group add constraint PK_cwd_group primary key (id)
alter table cwd_group_attribute add constraint PK_cwd_group_attribute primary key (id)
alter table cwd_membership add constraint PK_cwd_membership primary key (id)
alter table cwd_user add constraint PK_cwd_user primary key (id)
alter table cwd_user_attribute add constraint PK_cwd_user_attribute primary key (id)
alter table cwd_user_credential_record add constraint PK_cwd_user_credential_record primary key (id)
alter table DECORATOR add constraint PK_DECORATOR primary key (DECORATORID)
alter table external_entities add constraint PK_external_entities primary key (ID)
alter table external_members add constraint PK_external_members primary key (extentityid,groupid)
alter table EXTRNLNKS add constraint PK_EXTRNLNKS primary key (LINKID)
alter table FOLLOW_CONNECTIONS add constraint PK_FOLLOW_CONNECTIONS primary key (CONNECTIONID)
alter table groups add constraint PK_groups primary key (id)
alter table IMAGEDETAILS add constraint PK_IMAGEDETAILS primary key (ATTACHMENTID)
alter table INDEXQUEUEENTRIES add constraint PK_INDEXQUEUEENTRIES primary key (ENTRYID)
alter table KEYSTORE add constraint PK_KEYSTORE primary key (KEYID)
alter table LABEL add constraint PK_LABEL primary key (LABELID)
alter table LINKS add constraint PK_LINKS primary key (LINKID)
alter table local_members add constraint PK_local_members primary key (userid,groupid)

-- this table doesn't exist for some reason?
-- alter table logininfo add constraint PK_logininfo primary key (id)
alter table NOTIFICATIONS add constraint PK_NOTIFICATIONS primary key (NOTIFICATIONID)
alter table os_group add constraint PK_os_group primary key (id)

-- The next table had a duplicate entry, so we deleted it.
-- The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'confluenceuser.OS_PROPERTYENTRY' and the index name 'PK_OS_PROPERTYENTRY'. The duplicate key value is (name, 1234, key).
set rowcount 1
delete from [confluence_stage].[confluenceuser].[OS_PROPERTYENTRY]
WHERE entity_name = name' and entity_id = 1234 and entity_key = 'key'
alter table OS_PROPERTYENTRY add constraint PK_OS_PROPERTYENTRY primary key (entity_name,entity_id,entity_key)
alter table os_user add constraint PK_os_user primary key (id)
alter table os_user_group add constraint PK_os_user_group primary key (group_id,user_id)
alter table PAGETEMPLATES add constraint PK_PAGETEMPLATES primary key (TEMPLATEID)
alter table PLUGINDATA add constraint PK_PLUGINDATA primary key (PLUGINDATAID)
alter table remembermetoken add constraint PK_remembermetoken primary key (id)
alter table SPACEGROUPPERMISSIONS add constraint PK_SPACEGROUPPERMISSIONS primary key (SPACEGROUPPERMID)
alter table SPACEGROUPS add constraint PK_SPACEGROUPS primary key (SPACEGROUPID)
alter table SPACEPERMISSIONS add constraint PK_SPACEPERMISSIONS primary key (PERMID)
alter table SPACES add constraint PK_SPACES primary key (SPACEID)
alter table TRACKBACKLINKS add constraint PK_TRACKBACKLINKS primary key (LINKID)
alter table TRUSTEDAPP add constraint PK_TRUSTEDAPP primary key (TRUSTEDAPPID)
alter table TRUSTEDAPPRESTRICTION add constraint PK_TRUSTEDAPPRESTRICTION primary key (TRUSTEDAPPRESTRICTIONID)
alter table users add constraint PK_users primary key (id)

I'll update this with other stuff as we fix it