Cannot Restore XML Backup Due to Data Truncation - Limits on Columns
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.
症状
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)
atlassian-confluence.log に次のエラーが返される。
Import failed. (HibernateTemplate): encountered SQLException [Batch entry 0 insert into ...
または
ERROR Importing data task sf.hibernate.util.JDBCExceptionReporter logExceptions Data truncation: Data too long for column 'entity_key' at row 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.
ソリューション
- Before importing, enable SQL logging with parameters. You must shut down Confluence and configure log4j.properties.
- 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) - 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.