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 theUsers_UserGroupstable that has a userId of NNNNNNNN, even though no corresponding entry exists in theUser_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 theUsers_UserGroupstable and ultimately results in this Exception.
You can verify this information by checking theUsers_UserGroupstable 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