Error on space import

[Richard Whiffen]

Need help getting around this error on import:

Import failed. Check your server logs for more information. Hibernate operation: could not insert: com.atlassian.confluence.labels.Label#7930192; SQL ; Duplicate entry '7930192' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '7930192' for key 'PRIMARY'

Steps taken to get into this mess:

We imported a space from a space-export file, 100% successful.

The export used was determined to be old and contained outdated pages.

Space was deleted (this takes away longer than I thought it would...)

New export version was copied to server into the confluence-data/restore/ directory

Restore was attempted again via the admin interface, but fails at 44% with Unique Constraint error.

Read the following KB article: https://confluence.atlassian.com/display/CONFKB/Unable+to+Import+Space+due+to+Primary+Key+Constraint

Which seems to address the problem. When I grep the logs, there is only one key listed. I delete the space, delete the key (per the kb article) and re-do the import. It fails again, but with yet another key violation (different key this time). It seems like I'm going to have to keep trying this one key at a time. Since it takes almost an hour to delete the space, if there are more than a dozen of these keys, I'll be here for weeks.

Is there another way to get around this? Is there a way to find these orphaned keys before I do the import? Is there a way to sift the incoming keys from the import prior to using it to pre-check them?

Rich

[Richard Whiffen · Accepted solution]

Here's what I'm trying so far:

I've taken the ID's from the import: grep "<property name=\"entityId\" type=\"long\">" entities.xml > entities.txt

I've taken the existing ID's from the DB (mysql): select entity_id from OS_PROPERTYENTRY WHERE entity_name = 'confluence_ContentEntityObject' INTO OUTFILE 'db.txt';

I've sorted and unique'd them: sort -u entities.txt > imp-ids.txt; sort -u db.txt > db-ids.txt;

I've found the intersection of the sets: comm -12 imp-ids.txt db-ids.txt > conflict.txt

This has given my 2291 ID's that I believe are in conflict. I have done another test import and the failing ID was listed in my conflict.txt file, so I believe I have the list of ID's to delete.

[Richard Whiffen]

So this worked, I did a for loop and did a delete from OS_PROPERTYENTRY WHERE entity_name = 'confluence_ContentEntityObject' AND entity_id=${ID} (this isn't the exact syntax, don't cut and paste...)

[BernardoA]

Hi Richard,

As far as I know is necessary to delete one by one, but hoenstly I don't know other way to remove all duplicated.