Upgrading Confluence to 5.3 or above fails with "SET options have incorrect settings: 'ARITHABORT'"
Problem
When upgrading Confluence to version 5.3 or latest, the upgrade will fail on the user_mapping index creation due the ARITHABORT option not being set correctly.
The following appears in the atlassian-confluence.log:
2015-04-02 15:25:13,531 ERROR [localhost-startStop-1] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.UserMappingLowerUsernameSchemaUpgradeTask@2ae48ea0 failed during the UPGRADE phase due to: StatementCallback; SQL [create unique index user_mapping_unq_lwr_username on user_mapping(lower_username) where lower_username is not null]; CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.; nested exception is java.sql.SQLException: CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.UserMappingLowerUsernameSchemaUpgradeTask@2ae48ea0 failed during the UPGRADE phase due to: StatementCallback; SQL [create unique index user_mapping_unq_lwr_username on user_mapping(lower_username) where lower_username is not null]; CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.; nested exception is java.sql.SQLException: CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Diagnosis
Environment
This misbehaviour only affects Confluence when the database was originally in MS SQL Server 2000 and then migrated to a newer version.
Diagnostic Steps
First run below query against Confluence database to make sure the SET ARITHABORT option is ON:
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT';If you get a number and ARITHABORT in return, means the option is set to ON.
- Now right Click over Confluence database > Properties > Options:
- Check if you Compatibility mode is set to MSSQL 2000:
If either your database ARITHABORT option is set to OFF or the Compatibility version is set to 2000 you will need to address that before trying the upgrade.
Cause
The SET ARITHABORT option was introduced in MSSQL Server 2008. This option is set by default to ON and it will OFF only if you restored a MSSQL Server 2000 database into the the MSSQL Server 2008 (the minimal supported version for confluence 5.3 series), .and hence you will face this error during the user_mapping index creation upgrade task.
Resolution
The first thing you will need to do is to restore your Confluence application directories and database to a state prior the upgrade attempt, once you have that then follow below steps:
- Shutdown Confluence.
Log into MSSQL and use the Confluence database.
Run below query to set the ARITHABORT option to ON:
SET ARITHABORT ON;Run the below query to set the Compatibility mode to MSSQL 2008:
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100;If you are in MSSQL 2012 then change the compatibility level to 110 instead of 100.
Start Confluence again.
- Once you confirm the application is up and running, backup the database and try the upgrade again.