Error: "Upgrade failed, application will not start" Upgrade failed with MSSQL

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

After upgrade Confluence, you see below's error message when you accessing Confluence : 
"Upgrade failed, application will not start"

 

The following appears in the atlassian-confluence.log:

2014-08-14 12:46:05,005 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLException: Column names in each table must be unique. Column name 'id' in table 'users' is specified more than once.

Diagnosis

After you had confirmed that your database's collation is configured correctly, please kindly check if you are running on SQLAccess schema 

In Object Explorer, expand your server/database, expand Tables, right-click the table in question, and choose Script Table as > CREATE To > New Query Editor Window.

If you want to script multiple tables, you can turn on Object Explorer Details (F7 or from the View menu), highlight "Tables" on the left, then use Shift+ or Ctrl+ to select multiple tables in the right pane (just like you would select multiple files in Windows Explorer). Then you can do the same thing, right-click, Script Table as >

 

 

Sample of SQLAccess schema

USE [ConfluenceTest]
GO

/****** Object:  Table [2GIS\UK.Conf.SQLAccess].[AO_187CCC_SIDEBAR_LINK]    Script Date: 08/13/2014 15:47:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [2GIS\UK.Conf.SQLAccess].[AO_187CCC_SIDEBAR_LINK](
	[CATEGORY] [nvarchar](255) NULL,
	[CUSTOM_ICON_CLASS] [nvarchar](255) NULL,
	[CUSTOM_TITLE] [nvarchar](255) NULL,
	[DEST_PAGE_ID] [bigint] NULL,
	[HARDCODED_URL] [nvarchar](255) NULL,
	[HIDDEN] [bit] NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[POSITION] [int] NULL,
	[SPACE_KEY] [nvarchar](255) NULL,
	[TYPE] [nvarchar](255) NULL,
	[WEB_ITEM_KEY] [nvarchar](255) NULL,
 CONSTRAINT [pk_AO_187CCC_SIDEBAR_LINK_ID] PRIMARY KEY CLUSTERED 

 

 

Cause

Confluence is supported with dbo schema only 

Resolution

Please run below's query on your confluence database to change the database schema, and try upgrading again.
Do remember backup your database and test it out on staging instance.

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'schemaname'
set @newschema = 'dbo'


while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
 begin
 select @table = name from sys.tables
 where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)
 set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table
 exec(@sql)
 end
Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.