Confluence upgrade on SQL Server fails due to 'execute could not complete schema update' error

Still need help?

The Atlassian Community is here for you.

Ask the community

Problem

Confluence upgrade fails and the an error message similar to the following appears in atlassian-confluence.log

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)

Diagnosis

Environment

  • Microsoft SQL Server

Cause

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.

Resolution

Automated method using XML Backup/Restore functionality

Click here to expand the automated method...
  1. Follow these instructions from Manually Backing Up the Site to create an XML site backup
    Manually Backing Up the Site
    1. Go to  > General Configuration > Backup & Restore.
    2. Choose Also save a copy to the backups directory to store a copy of the backup in the same folder as Confluence's backups
      If you do not archive the backup it will be made available for you to download, and then deleted from the server after 24 hours.
    3. Choose Include attachments to include attachments in your backup.
    4. Choose Export.
      The process can take some time.

    If you have a large site, you should temporarily disable the Clean Temporary Directory scheduled job, as this job deletes the contents of the /temp directory at 4am each day, and will delete your export files. You should re-enable this job once the export is complete.

  2. Create a new SQL Server database configured as described in Database Setup for SQL Server
  3. Stop Confluence
  4. 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)
  5. Start Confluence and follow the setup wizard to create an empty site
  6. Follow these instructions from Restoring a Site to restore the XML backup

    Restoring a Site
    To upload and import a small site:
    1. Go to  > General Configuration > Backup and Restore
    2. Under Upload a site or space export file, click Choose File and browse for your space export file.
    3. Uncheck Build Index if you want to create the index at a later stage.
    4. Choose Upload and import.

    To import a site from the home directory: 

    1. Copy your export file to <confluence-home>/restore. 
      (If you're not sure where this directory is located, the path is listed in the Backup and Restore screen)
    2. Go to  > General Configuration > Backup and Restore
    3. Select your site export file under Import from home directory
    4. Uncheck Build Index if you want to create the index at a later stage.
    5. Choose Import.

Manual method using SQL Server database tools

Click here to expand manual method...

Always back up your 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.

  1. Stop Confluence (all following steps are in SQL Server Management Studio)

  2. Identify any fields that have the datatype varcharchar, 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;
  3. Create a new SQL Server database configured as described in Database Setup for SQL Server

  4. Create a script to recreate the database tables
    1. Right click the old database in Object Explorer
    2. Click Tasks > Generate Scripts...
    3. If prompted select the current Confluence database
    4. On the Choose Scripting Options adjust the following options:
      1. Set Script USE DATABASE to false
      2. Set Script Full-Text Indexes to true
      3. Set Script Indexes to true
    5. In the Choose Objects Types screen, tick Tables
    6. In the Choose Tables screen, click Select All; click Next
    7. In the Output Option screen, click Script to file; click Finish
  5. Open the resulting file in a text editor and use find and replace to change varcharchar, and text to nvarcharnchar, and ntext
    1. Ensure you use the full words in find and replace, so you don't accidentally end up with values like 'nvarnchar' 
  6. Run the edited script file against the new Confluence database we created in Step 3 to populate the schema
    1. Select the new Confluence database in the Available Database dropdown
    2. Click File Open > File
    3. Select the script from the picker
    4. Click Execute
  7. Disable constraint checks before importing data
    1. Execute the following query against the new Confluence database

      EXEC sp_MSforeachtable"ALTER TABLE ? NOCHECK CONSTRAINT all"
  8. Import data from the old Confluence database into the new database
    1. Right click the old database in Object Explorer
    2. Click Tasks > Export Data... 
    3. In the Choose a Data Source screen, select the old database from the dropdown; click Next
    4. 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
    5. Select Copy data from one or more tables or views; click Next
    6. In the Select Source Tables and Views screen, tick the top left box to select all tables; click Edit Mappings
    7. Ensure the Destination schema name is correct (this should most likely be set to dbo) and tick the Enabled identity insert; click OK
    8. Click Next
    9. 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
  9. Re-enable the constraint check after importing data
    1. 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"
  10. Start Confluence
Last modified on Nov 2, 2018

Was this helpful?

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