Missing or duplicate users with Get Users API from Postgres in Bitbucket DC
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Discover how to resolve duplicate or missing user names in Bitbucket's Get Users API results.
Envionriment
Bitbucket Data Center 7.x, 8.x and above.
Diagnosis
Consider a Bitbucket instance having usernames that contain special characters, such as
'(apostrophe) in the Postgres Database.The list of users fetched by the GET Users REST API endpoint or the Exporting list of users and user permissions from the Bitbucket UI returns an incomplete set of users, resulting in some users showing up multiple times and other users not at all.
Cause
Database's collation setting
The database entries are sorted as if there were no special characters at all; however, when the results are sorted again in Java, the special character is taken into account, and then there are aliasing issues.
In PostgreSQL, collation determines how strings are sorted and compared. C collation performs sorting based on the binary values of characters (ASCII order), whereas UTF8 collation sorts by dictionary rules, not ASCII values.
Solution
Check and update the collation for the cwd_user table to C
Please ensure a consistent backup before altering the DB, and shut down Bitbucket before running the below ALTER query.
Run the below SQL query to figure out the current collation of your Database:
SELECT datcollate AS collation
FROM pg_database
WHERE datname = current_database();Run the below SQL query to update the collation to C for only the lower_user_name column in the cwd_user table. This will impact only one column in one table:
ALTER TABLE cwd_user
ALTER COLUMN lower_user_name
TYPE VARCHAR(255) COLLATE "C";Related bug
Was this helpful?