Upgrade fails with ERROR that a table has more than one primary key error message
問題
The Confluence upgrade process failed because it detected multiple primary keys in one table, for example the LABEL table. The following appears in atlassian-confluence.log:
INFO [main] [com.atlassian.confluence.lifecycle] contextInitialized Starting Confluence 5.3 [build 4527 based on commit hash 660525579de30883af214a8e1a751cb99357bcff]
INFO [main] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization started
INFO [main] [com.atlassian.confluence.lifecycle] <init> Loading EhCache cache manager
WARN [main] [org.springframework.beans.TypeConverterDelegate] findDefaultEditor PropertyEditor [com.sun.beans.editors.EnumEditor] found through deprecated global PropertyEditorManager fallback - consider using a more isolated form of registration, e.g. on the BeanWrapper/BeanFactory!
INFO [main] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization completed in 21761 ms
INFO [main] [confluence.upgrade.upgradetask.LoginInfoDuplicateEntryRemovalUpgradeTask] doUpgrade No duplicate logininfo rows found. No action necessary.
INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Checking table CONTENT_PERM for redundant duplicate usernames
INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade No redundant duplicate usernames found in CONTENT_PERM
INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Finding rows with mixed case username references in LINKS table
INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Finding rows with mixed case username references in TRACKBACKLINKS table
INFO [main] [atlassian.confluence.upgrade.UpgradeTask] doUpgrade Finding rows with mixed case username references in FOLLOW_CONNECTIONS table
ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LowerCaseUsernameReferencesUpgradeTask@27651e61 failed during the SCHEMA_UPGRADE phase due to: Table 'LABEL' has more than one primary key
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LowerCaseUsernameReferencesUpgradeTask@27651e61 failed during the SCHEMA_UPGRADE phase due to: Table 'LABEL' has more than one primary key
...
Caused by: java.lang.IllegalStateException: Table 'LABEL' has more than one primary key原因
The cause of the issue is a hibernate bug mentioned on our bug report tracking system here: CONF-3613 - Getting issue details... STATUS
The specific issue that comes up as a result of the 5.2 upgrade is tracked at: CONF-31333 - Getting issue details... STATUS
Due to this bug, Confluence will read other tables from another schemas which leads to this issue.
診断
Try running this query against your Confluence database using the same database user that Confluence uses:
SELECT * FROM TAB;See if there are any duplicate tables retrieved by the SQL query above. In order to double check, please try to run this query as the sysdba, and replace <user> with the Confluence user's username:
SELECT * FROM all_objects WHERE owner = upper('<user>');See if there are any duplicate tables retrieved by the SQL query above.
ソリューション
Make sure that the Oracle schema user creation is created as mentioned in our documentation here. Granting only the appropriate roles to the Confluence user will ensure that it will only see the Confluence database, which should avoid this issue.
In the default Oracle XE install, all new users are a member of the "DBA" role which contains "SELECT ANY TABLE" permissions, so this may also apply to other editions.
To locate any role with "SELECT ANY TABLE":
SELECT grantee, privilege FROM dba_sys_privs WHERE privilege = 'SELECT ANY TABLE';
Then locate the roles on your database user:
SELECT granted_role from dba_role_privs where grantee = '<UPPERCASEUSERNAME>';
If the user has a role with SELECT ANY TABLE, the workaround is:
REVOKE <ROLE> FROM <USERNAME>;