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

Related content

  • No related content found

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 Back up a Site to create an XML site backup
    Back up a Site
    1. Go to Administration menu , then General Configuration. > Backup and restore
    2. Select Back up site or spaces
    3. In the Create a backup screen:
      1. Select Site
      2. Give your XML export a file name prefix. Confluence will add a time stamp and unique identifier to the end of this file name. If this field is left blank, the file will be assigned a default prefix and time stamp, for example, Confluence-site-export-2015-04-14-11-07-36-639.xml.zip
      3. Select Save permanently if you want your file to remain in the <confluence-home>/restore/site folder. Otherwise, the backup will be deleted in 72 hours by default to maintain storage capacity. You can change the storage time by configuring the system property confluence.backuprestore.backup.ttl-in-hours. See Configuring System Properties to learn how.
      4. Select Include attachments to include attachments in your backup
    4. Select Back up
    5. You will receive a confirmation message, select Back up now to start the task

    This process can take some time for large sites.

  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

    Restore a Site

    You can't restore a backup into an earlier version of Confluence. 

    For example, if your XML backup was generated from Confluence 8.3, you can't import it into Confluence 7.19.


    To check whether your backup can be successfully restored:

    • Check which Confluence version you are using in Administration menu , then General Configuration.> System Information. The version will be listed next to Confluence Version.
    • Check which Confluence version your XML backup was generated from. See How to Determine XML Backup Confluence Version.

    (tick) If you are restoring a backup to a later version, it can be restored successfully.

    (error) If you are restoring a backup to an earlier version, this is not supported and your import may fail.

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 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;

  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 varchar, char, and text to nvarchar, nchar, 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

In this section

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.