Error with duplicate entry for key unq_lwr_username

Question

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_db00h3 · Answer

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

Cloud Licensing · Answer

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`))

Cloud Licensing · Answer

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`))"