Error with duplicate entry for key unq_lwr_username

[Cloud Licensing]

Hello. I am on confluence 6.15.2, and planning to upgrade to the latest. 

When I tried to restore a backup from XML, I bumped into an issue which complains about the lower_username. 

This article says how to fix https://confluence.atlassian.com/confkb/restore-of-a-site-xml-backup-fails-due-to-duplicate-entry-for-key-unq_lwr_username-error-998896818.html

However, when I run the update command (with confluence stopped, and i.e. UPDATE user_mapping SET lower_username = 'malek.serka@cat.com ' WHERE username = 'malek.serka@cat.com';) I am still met with 

ERROR CODE: 1062. Duplicate Entry for Key 'unq_lwr_username

Note, this error is showing on the DB, so it's more a DB issue, but has anyone come across this? As the fix given by Atlassian doesn't fix the problem, it just gives the same error when trying to update the DB  

MYSQL version 5.6

[Thomas Deiler]

Dear @Cloud Licensing ,

just to be sure. You performed the fix on the SOURCE DB, before creating the XML dump, right?

Can you try to find the duplicates on your own? This article could help.

So long

Thomas

[Cloud Licensing]

Hello, 

No, I can't even perform the fixes on the DB, it doesn't let me. 

The article in the OP I followed but I can't even delete the values, it's complaining:

"Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`content`, CONSTRAINT `FK_CONTENT_USERNAME` FOREIGN KEY (`USERNAME`) REFERENCES `user_mapping` (`user_key`))"

[Thomas Deiler]

Dear @Cloud Licensing ,

I recommend that you first replicate the MySQL to a "sandbox" like area for further experiments. Don't harm your data.

Then you need manually to solve the constraint, if possible. I am not an expert for MySQL, but eventually the MySQL community can assist.

Another way to solve this issue, is to use mysqldump to export the whole DB into a single file. Then remove the constraint from there by editing, afterwards restore the modified dump.

Thumbs up

Thomas

[Diego Patrignani]

Hi @Cloud Licensing , G'day!
Hope you are doing good!

This is tricky as by your description, it seems that you have duplicated entries instead of null values so the workaround listed at https://confluence.atlassian.com/confkb/restore-of-a-site-xml-backup-fails-due-to-duplicate-entry-for-key-unq_lwr_username-error-998896818.html may not be enough.

Please check the below article that should help you in this case, you can proceed by cleaning the XML file or deleting the duplicated entries at the database.
https://confluence.atlassian.com/confkb/confluence-site-xml-import-fails-with-duplicate-key-error-438993638.html

Best Regards,
Diego Patrignani 

[Cloud Licensing]

Hello,

Deleting the entries does not seem possible. When I do try, I get:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`content`, CONSTRAINT `FK_CONTENT_USERNAME` FOREIGN KEY (`USERNAME`) REFERENCES `user_mapping` (`user_key`))