Confluence upgrade on SQL Server fails due to 'execute could not complete schema update' error
Confluence upgrade fails and the an error message similar to the following appears in
ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table SPACEPERMISSIONS add constraint FK_SPACEPERMISSIONS_CREATOR foreign key (CREATOR) references user_mapping ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Column 'user_mapping.user_key' is not the same data type as referencing column 'SPACEPERMISSIONS.CREATOR' in foreign key 'FK_SPACEPERMISSIONS_CREATOR'. ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update java.sql.SQLException: Column 'user_mapping.user_key' is not the same data type as referencing column 'SPACEPERMISSIONS.CREATOR' in foreign key 'FK_SPACEPERMISSIONS_CREATOR'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
- Microsoft SQL Server
To provide support for Unicode characters in Microsoft SQL Server, Confluence uses and expects nchar, nvarchar, and ntext column data types for certain tables. This error is caused when Confluence expects the column data type to be nchar, nvarchar, or ntext but sees a different data type.
Automated method using XML Backup/Restore functionality
- Follow these instructions from Manually Backing Up the Site to create an XML site backup
- Create a new SQL Server database configured as described in Database Setup for SQL Server
- Stop Confluence
- Rename or move the existing <confluence-home> folder (The <confluence_home> directory is the path defined in the following file: <confluence_install>/confluence/WEB-INF/classes/confluence-init.properties)
- Start Confluence and follow the setup wizard to create an empty site
Follow these instructions from Restoring a Site to restore the XML backup
Manual method using SQL Server database tools
Alwaysyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Ensure that you are using the correct collation for your product. The following examples use the collation of
SQL_Latin1_General_CP1_CS_AS. The collation required by your application version may be different. See the "Setup Guides for Microsoft SQL Server" section above for more information about your product and version.
Stop Confluence (all following steps are in SQL Server Management Studio)
Identify any fields that have the datatype varchar, char, or text
select distinct object_name(object_id) from sys.columns where system_type_id in (35,167,175) and OBJECT_NAME(object_id) not like 'sys%' order by 1;
Create a new SQL Server database configured as described in Database Setup for SQL Server
- Create a script to recreate the database tables
- Right click the old database in Object Explorer
- Click Tasks > Generate Scripts...
- If prompted select the current Confluence database
- On the Choose Scripting Options adjust the following options:
- Set Script USE DATABASE to false
- Set Script Full-Text Indexes to true
- Set Script Indexes to true
- In the Choose Objects Types screen, tick Tables
- In the Choose Tables screen, click Select All; click Next
- In the Output Option screen, click Script to file; click Finish
- Open the resulting file in a text editor and use find and replace to change varchar, char, and text to nvarchar, nchar, and ntext
- Ensure you use the full words in find and replace, so you don't accidentally end up with values like 'nvarnchar'
- Run the edited script file against the new Confluence database we created in Step 3 to populate the schema
- Select the new Confluence database in the Available Database dropdown
- Click File > Open > File
- Select the script from the picker
- Click Execute
- Disable constraint checks before importing data
Execute the following query against the new Confluence database
EXEC sp_MSforeachtable"ALTER TABLE ? NOCHECK CONSTRAINT all"
- Import data from the old Confluence database into the new database
- Right click the old database in Object Explorer
- Click Tasks > Export Data...
- In the Choose a Data Source screen, select the old database from the dropdown; click Next
- In the Choose a Destination screen, enter the server name you will be connecting to (this will most likely be the same as the original server) and select the new database from the dropdown; click Next
- Select Copy data from one or more tables or views; click Next
- In the Select Source Tables and Views screen, tick the top left box to select all tables; click Edit Mappings
- Ensure the Destination schema name is correct (this should most likely be set to dbo) and tick the Enabled identity insert; click OK
- Click Next
- In the Run Package screen, ensure Run immediately is ticked and click Finish
- Depending on the size of the database, and the speed of the database server(s) being used, this process will take time
- Re-enable the constraint check after importing data
Execute the following query against the new Confluence database to confirm all affected columns have been changed
EXEC sp_MSforeachtable"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
- Start Confluence