Summary

Importing a site XML backup of Confluence may fail with: Import failed. ERROR: null value in column "username" violates not-null constraint.

Environment

Issue identified on Confluence 7.4.7 but may affect other versions

Diagnosis

After receiving the error in the UI that the import has failed, check the application logs (atlassian-confluence.log) of the destination server around the time the import has failed, look for Import failed to identify why the import has failed. If you do encounter a message that says ERROR: null value in column "username" violates not-null constraint this article can be useful.

A similar message that you may find on the application log file:

Import failed. Check your server logs for more information. com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: null value in column "username" violates not-null constraint Detail: Failing row contains (11896111, null, null, null, null, null, 1901-01-01 10:44:12, null, 1901-01-01 10:44:12, f, f, null).

It's important to note that, on a healthy default Confluence database, a table/column that is set to NOT NULL won't have null values, the problem then, is that the XML file doesn't contain the data, and when the import is restoring the application database complains causing the process to fail. The investigation of this problem should be performed in both the database (structure and data) and the XML backup file (entities.xml).

You may use the ID from the error that will be shown (11896111) and look in both the XML file and database. Compare the XML structure of the missing object with another of the same object class and you'll notice the missing content, the entry starts and ends with the object tags:

<object class="Notification" .....
<id name="id">11896111</id>
....
</object>

In this example, the ID is related to the username column of the notifications table as you may identify per the object class, so we know that the problem is a row on the notifications table.

Cause

A null value on the column username and/or the data is missing from the XML backup file (entities.xml) causes the import to fail as the column doesn't expect the null.

Solution

There isn't a simple solution to this problem as it will require either data removal or the addition based on the extent of the problem. The column username is used in more than one table and the SQL will differ based on the table, database, and Confluence version. We may, remove the content that has no relation in the XML and complete the import or remove the data from the database, generate a new XML backup, and import. As this kind of problem is rare, we recommend reaching out to Atlassian support so our technical team can help you with the data manipulation and guidance through this problem.