Problem:

While the bug CONFSERVER-36018: Duplicates in the People Directory due to duplicates in the user_mapping table (fixed in Confluence Server 5.10.0) has been resolved, the duplicated users still exist in Confluence database.

As per the update provided in the bug report:

UPDATE
This bug no longer causes duplicates in the people directory because we have hack/patch to hide duplicates on that page, even if duplicates exist in the index. However, dups still appear in @ mentions and other places.When dups show in @ mentions, the dup will show as "Unknown user (<username>) (<username>)

The process of cleaning the database of duplicated users includes exhaustive SQL queries, and the SQL queries sometimes does not work due to differences between database usages and Confluence versions.

Suggestions

It'll be great if we include some kind of tool to "cleanup" the database in the application. We can add a "hidden" admin function just like "Rebuild Ancestor Table" action (Rebuilding the Ancestor Table - Atlassian Documentation)

Is it possible to place a null constraint on lower_username? And potentially username as well? Currently there are no null constraints on this table.

Another fix that would be ideal is to fix the user indexing upgrade task so that it handles null values gracefully, and performs this operation to remove them.