Multiple SQL Server database errors during Confluence upgrade
症状
Confluence upgrade fails due to error messages in the atlassian-confluence.log
Rolling back the database and attempting upgrade does not help. There is inconsistent data in the database.
Resolving each error message that comes up in the logs and attempting to upgrade results in another database error in the logs. Listed here are the errors that come in sequence:
atlassian-confluence.log に次のメッセージが表示される。
ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; SQL [alter table CONTENT_PERM add constraint cp_unique_user_groups unique (CPS_ID, CP_TYPE, USERNAME, GROUPNAME)]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.CONTENT_PERM' and the index name 'cp_unique_user_groups'. The duplicate key value is (21397507, Edit, NDore, <NULL>).; nested exception is java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.CONTENT_PERM' and the index name 'cp_unique_user_groups'. The duplicate key value is (21397507, Edit, NDore, <NULL>).
ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1 errors were encountered during upgrade:
2012-01-31 14:27:25,865 ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; bad SQL grammar [alter table ATTACHMENTDATA add constraint ad_unique_attachmentid unique (ATTACHMENTID)]; nested exception is java.sql.SQLException: There is already an object named 'ad_unique_attachmentid' in the database.ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; bad SQL grammar [drop index attch_idver_idx on ATTACHMENTDATA]; nested exception is java.sql.SQLException: Cannot drop the index 'ATTACHMENTDATA.attch_idver_idx', because it does not exist or you do not have permission.ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; bad SQL grammar [drop index attch_data_idx on ATTACHMENTDATA]; nested exception is java.sql.SQLException: Cannot drop the index 'ATTACHMENTDATA.attch_data_idx', because it does not exist or you do not have permission. ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; SQL [alter table CONTENT_PERM add constraint cp_unique_user_groups unique (CPS_ID, CP_TYPE, USERNAME, GROUPNAME)]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.CONTENT_PERM' and the index name 'cp_unique_user_groups'. The duplicate key value is (21397508, Edit, NDore, <NULL>).; nested exception is java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.CONTENT_PERM' and the index name 'cp_unique_user_groups'. The duplicate key value is (21397508, Edit, NDore, <NULL>).ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; bad SQL grammar [alter table ATTACHMENTDATA add constraint ad_unique_attachmentid unique (ATTACHMENTID)]; nested exception is java.sql.SQLException: There is already an object named 'ad_unique_attachmentid' in the database.ERROR [Thread-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]contextInitialized 1: StatementCallback; bad SQL grammar [drop index attch_idver_idx on ATTACHMENTDATA]; nested exception is java.sql.SQLException: Cannot drop the index 'ATTACHMENTDATA.attch_idver_idx', because it does not exist or you do not have permission.原因
No root cause identified.
ソリューション
These steps relate to the errors above in order. 7 errors, 7 steps here. As we are unsure of the root cause of this, we have not been able to reproduce and thus have not tested whether you can perform all these steps in order (at one time) or if you have to perform each one in turn after a failed upgrade. You DO NOT roll-back the database in between each upgrade attempt.
- Remove the duplicate row(s) from dbo.CONTENT_PERM and restart Confluence
- Delete the index ad_unique_attachmentid from table dbo.ATTACHMENTDATA and restart Confluence
- Added index attch_idver_idx on table dbo.ATTACHMENTDATA to give the script something to drop and restart Confluence
- Added index attch_data_idx on table dbo.ATTACHMENTDATA to give the script something to drop and restart Confluence
- Remove duplicate row(s) from dbo.CONTENT_PERM and restart Confluence
- Again deleted the index ad_unique_attachmentid from table dbo.ATTACHMENTDATA and restart Confluence
- Again add the same two temporary indexes on dbo.ATTACHMENTDATA to give the script something to drop and restart Confluence
This was a process of trial and error as each error message came after fixing the last and restarting the upgrade. Several of these steps should be able to be run at the same time.
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.