Unable to import LDAP users using Import by Group due to com.liferay.portal.kernel.exception.NoSuchUserException: No User exists with the primary key NNNNNNNN

Issue

• If you are unable to import LDAP users by the Group import method due to the following error, there may be orphaned entries in your database's Users_UserGroups table: com.liferay.portal.kernel.exception.NoSuchUserException: No User exists with the primary key NNNNNNNN

Environment

• Liferay DXP 7.4

Resolution

• The Users_UserGroups table maps Users to the User Groups that they are a member of.
   
  If your LDAP Group import is failing, there is a strong possibility that there is orphaned data in this table that points to Users that no longer exist in the database. Taking the example from the Group Import log:
  com.liferay.portal.kernel.exception.NoSuchUserException: No User exists with the primary key NNNNNNNN
   
  This means there is an entry (or entries) in the Users_UserGroups table that has a userId of NNNNNNNN, even though no corresponding entry exists in the User_ table. This is what leads to the exception. During the import process, Liferay attempts to look up all users that are a member of an imported LDAP Group. This is what causes it to query the Users_UserGroups table and ultimately results in this Exception.
   
  You can verify this information by checking the Users_UserGroups table for the existence of orphaned data, with a query such as the following:
  SELECT * FROM Users_UserGroups WHERE userId NOT IN (SELECT userId FROM User_)
  
  
• If you do find any duplicate entries, it is strongly recommended that you do not remove the entries from your database by manual intervention. Instead, you may execute the following Groovy Script from Control Panel > Server Administration > Script

 

 

NOTE: Be sure to take a backup of your database prior to making any changes. Always test the changes in a lower environment before making changes in production.

Making manual changes to the database is not recommended, and should be carefully assessed based on your own business needs and risk factors. Any such operation is performed at the sole discretion of your own team.

import com.liferay.portal.kernel.dao.jdbc.AutoBatchPreparedStatementUtil;
import com.liferay.portal.kernel.dao.jdbc.DataAccess;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeleteOrphanedUsersUserGroupsData {

	public void doDeleteOrphanedUsersUserGroupsData() {
		Connection con = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;

		try {
			con = DataAccess.getConnection();

			ps1 = con.prepareStatement(
				"select distinct(userId) from Users_UserGroups where userId " +
					"not in (select userId from User_)");
			ps2 = AutoBatchPreparedStatementUtil.concurrentAutoBatch(
				con, "delete from Users_UserGroups where userId = ?");

			rs = ps1.executeQuery();

			while (rs.next()) {
				long userId = rs.getLong("userId");

				if (_log.isInfoEnabled()) {
					_log.info(
						"Found orphaned user id " + userId + " in " +
							"Users_UserGroups table. Queuing entries with " +
								"this user id for deletion.");
				}

				ps2.setLong(1, userId);

				ps2.addBatch();
			}

			ps2.executeBatch();

			if (_log.isInfoEnabled()) {
				_log.info("Successfully executed all queued deletions");
			}
		}
		catch (Exception e) {
			_log.error("Error while executing script", e);
		}
		finally {
			DataAccess.cleanUp(ps1);
			DataAccess.cleanUp(con, ps2, rs);
		}
	}

	private static Log _log = LogFactoryUtil.getLog(
		DeleteOrphanedUsersUserGroupsData.class);

}

out.println(
	"Running groovy script to delete orphaned data from the Users_UserGroups " +
		"table...");

DeleteOrphanedUsersUserGroupsData deleteOrphanedUsersUserGroupsData =
	new DeleteOrphanedUsersUserGroupsData();

deleteOrphanedUsersUserGroupsData.doDeleteOrphanedUsersUserGroupsData();

out.println("Script finished running.");

Once this has been completed, please execute the Group import again to see that this behavior is resolved.

Additional Information

• Configuring User Import and Export
• Running Scripts From the Script Console