People Directory empty or not displaying the proper number of people - When users have been deleted from an External User Management

The previous description of this issue was incorrect so I am updating it.

The symptoms of the issue are, if you go to your people directory it appears empty (or appears to not display a consistent number of users, i.e. 20 in the first page, but 50 in the next), even though you have a number of registered users. Often the case was your people directory used to work but does not any more. You users are managed externally, i.e. by LDAP, delegated to JIRA, etc and recently you have deleted a number of these users.

The reason the people directory appears blank, is because a number of your users have been deleted but their user info still exists in the confluence database.

Background

Each time a user clicks on their preferences, it creates a row in the CONTENT table to represent their userinfo. Something like this:

select * from CONTENT where CONTENTTYPE='USERINFO';

||CONTENTID||	CONTENTTYPE||	TITLE||	VERSION||	CREATOR||	CREATIONDATE||	LASTMODIFIER||	LASTMODDATE||	VERSIONCOMMENT||	PREVVER||	CONTENT_STATUS||	SPACEID	||CHILD_POSITION||	PARENTID||	MESSAGEID||	DRAFTPAGEID||	DRAFTSPACEKEY||	DRAFTTYPE||	DRAFTPAGEVERSION||	PAGEID||	PARENTCOMMENTID||	USERNAME||
|131073|	USERINFO|	(null)|	1|	(null)|	2008-05-28 14:41:10.0|	(null)|	2008-05-28 14:41:10.0|		|(null)|	current	|(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	admin|
|131074|	USERINFO|	(null)|	1|	admin|	2008-05-28 14:42:28.0|	admin|	2008-05-28 14:42:28.0|		|(null)| current	|(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	(null)|	asako|

As you can see the contentype = 'USERINFO' and the username= the name of the user.

Now once you delete a user in your external usermanagement (LDAP/JIRA/Crowd), it will disappear from Confluence user management, i.e. Searching Administration > Manage users will not find that user. However, their userinfo still exists in the content table. When populating the people directory, Confluence searches the CONTENT table pulling out all the people who have USERINFO rows in the table. However when it tries to display the person, it can't because the user no longer exists. If you have a number of these it can cause the people directory to not display the same amount of users across pages when searching through the people directory or in extreme cases it can cause the people directory to be empty.

Confirming that you are affected by this bug

You can confirm by checking if a recently deleted user (from LDAP/Jira etc) appears in your Administraton > Manage users. If they do not, check if they exist in the CONTENT table with a query similar to

select * from CONTENT where CONTENTTYPE='USERINFO' and USERNAME='insert_name';

Another test that we can use to confirm this is by going directly to the next set of users (i.e. not the first 50) by using the following URL:

http://confluence/browsepeople.action?startIndex=50

Try start indexes of 50, 100, 150, 200 to see if users are populated.

Workaround for version before 3.5.0

If this is the case, then you need to delete the USERINFO rows for users that no longer exist, by doing the following:
Please remember to make a backup of your db before performing these operations, so that you can roll back should you need to.

delete from CONTENT where CONTENTTYPE='USERINFO' and USERNAME='insert_name';

If you are encountering any Foreign Key constraints, please see this comment for details on how to remove the additional rows.

Workaround for 3.5.0 - 4.3.2

Please test these queries in a development environment first to confirm they will not affect stability.
Please remember to make a backup of your db before performing these operations, so that you can roll back should you need to.

NB: This will result in all content created by the removed users being listed as created/edited by 'Anonymous'.

-- Create set of users not in the cwd_user table but still have a USERINFO entry
create table missing_users as select distinct(username) from content where username not in (select lower_user_name from cwd_user);   

-- Create list of userinfo pages and attachmentids associated to missing users
create table userinfo_pages as select contentid from content where contenttype='USERINFO' and username in (select username from missing_users);
create table ui_attachmentids as select attachmentid from attachments where pageid in (select contentid from userinfo_pages);
	
-- Delete content causing FK errors
delete from bodycontent where contentid in (select contentid from userinfo_pages); 
delete from imagedetails where attachmentid in (select attachmentid from ui_attachmentids);
delete from attachmentdata where attachmentid in (select attachmentid from ui_attachmentids);
delete from attachments where prevver in (select attachmentid from ui_attachmentids);
delete from attachments where pageid in (select contentid from userinfo_pages);
update content set prevver=null where contentid in (select contentid from userinfo_pages);
delete from links where contentid in (select contentid from userinfo_pages);
delete from content where contentid in (select contentid from userinfo_pages);                     
	
-- Drop Tables
drop table userinfo_pages;
drop table missing_users;
drop table ui_attachmentids;

Details of fix

As of Confluence 4.3.3 and higher, Confluence will now keep content created by the deleted user, and their content will remain searchable. However, the people directory will no longer display deleted users. During upgrade, Confluence will reindex all users who have been deleted, but have USERINFO entries. At runtime, Confluence will update the index any time users are deleted in a remote directory, once that information is synchronised across.