Cannot Restore XML Backup Due to Data Truncation - Limits on Columns

Still need help?

The Atlassian Community is here for you.

Ask the community

Atlassian recommends disabling the XML backup both for performance and reliability. Setting up a test server and the Production Backup Strategy is better done with an SQL dump. Upgrading Confluence is better done without the XML backup.

The one operation for which an XML backup is required is database migration. For this we recommend a commercial database migration tool. Vote for Cannot Restore XML Backup Due to Data Truncation - Limits on Columns to add a more robust strategy for large implementation migrations. Atlassian does not support migrating to a new database.

Symptoms

The xml backup fails when migrating to a persistent database.

From Postgres (an example - other databases will see this as well) database logs:

ERROR: value too long for type character varying(200)

The following appears in the atlassian-confluence.log:

Import failed. (HibernateTemplate): encountered SQLException [Batch entry 0 insert into ...

or

ERROR Importing data task sf.hibernate.util.JDBCExceptionReporter logExceptions Data truncation: Data too long for column 'entity_key' at row 1

Cause

  1. Most persistent databases create character limits on its tables - Postgres, for example. HSQLDB, the built-in memory database, does not respect limits to database columns. It will allow longer length strings than a persistent database can handle.

Resolution

 
  1. Before importing, enable SQL logging with parameters. You must shut down Confluence and configure log4j.properties.
  2. Check the atlassian-confluence.log file. Find the error message and the corresponding row with a value that's too long. You may find it helpful to run this command on your database table, to determine how many characters each field takes:
    desc os_propertyentry; (where os_propertyentry is the name of the table that's rejecting the import)
  3. Find the string that's the culprit and remove it from your HSQLDB instance, then redo both the export and the import. You can usually use the UI to remove the content itself, otherwise you may need DBVisualizer to access your HSQLDB instance.
Last modified on Mar 30, 2016

Was this helpful?

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