ERROR: null value in column "user_id" violates not-null constraint - Can't Import Site XML

[FreeSoftwareServers]

I recently had issues importing my site XML (https://community.atlassian.com/t5/Confluence-questions/ERROR-duplicate-key-value-violates-unique-constraint-quot-unq/qaq-p/1374506?utm_source=atlcomm&utm_medium=email&utm_campaign=mentions_answer&utm_content=topic) so I followed two guides to try to fix it myself.

First I followed this

https://confluence.atlassian.com/conf64/restore-passwords-to-recover-admin-user-rights-936511358.html?_ga=2.89207769.379733476.1589092221-1126927851.1588654092

And i basically deleted every row from cwd.users and then created admin/admin user. I was able to login, did an export and got a new error during import as follows.

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 "user_id" violates not-null constraint Detail: Failing row contains (262152, null, 98306, lastAuthenticated, 1483378795427, 1483378795427).

So I then tried following this --> https://confluence.atlassian.com/doc/restore-passwords-to-recover-admin-user-rights-158390.html

And added the following to my setenv.sh

CATALINA_OPTS="-Datlassian.recovery.password=password"

I started server w/ "start-confluence.sh" and was unable to login via "recovery_admin/password" combination.

I then logged back in as admin/admin and tried creating a user, but it failed and said to check server logs, but I can't figure out where to look and everything just seems screwed up. Thankfully my site is running fine, but I can't seem to do anything related to users and/or export/import my site. Any idea's how to fix this mess?

Thank You,

[FreeSoftwareServers · Accepted solution]

I was able to get past this by basically deleting all users, clearing almost everything from `user_mapping` manually via sql and using the "recovery_admin" user to export/import and create a new user. Simple right?