Duplicate users in the Homepage.person and Homepage.Loginname table

Posted on by

For a while now we had some weird errors in the SystemOut.log of the News application:

[17/05/19 12:28:05:427 CEST] 00011248 RetryConnecti W Bad grammer exception public abstract java.lang.Object org.springframework.orm.ibatis.SqlMapClientOperations.queryForObject(java.lang.String,java.lang.Object) throws org.springframework.dao.DataAccessException(ARG[0]: getPersonWithLoginNamesByLoginName-PERSON, ARG[1]: {schema=HOMEPAGE, loginName=wsadmin}) SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred while applying a parameter map.
— Check the getPersonWithLoginNamesByLoginName-PERSON-InlineParameterMap.
— Check the results (failed to retrieve results).
— Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred while applying a parameter map.
— Check the getPersonWithLoginNamesByLoginName-PERSON-InlineParameterMap.
— Check the results (failed to retrieve results).
— Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I checked the error with IBM and they blamed it on duplicates in the HOMEPAGE.PERSON and/or HOMEPAGE.LOGINNAME table.

It turned out that I had multiple problems in the Homepage database. These queries uncovered them:

SELECT p.USER_MAIL_LOWER, COUNT(p.USER_MAIL_LOWER) FROM HOMEPAGE.HOMEPAGE.PERSON p GROUP BY p.USER_MAIL_LOWER HAVING COUNT(p.USER_MAIL_LOWER) > 1
SELECT l.LOGINNAME, COUNT(t.LOGINNAME) FROM HOMEPAGE.HOMEPAGE.LOGINNAME l GROUP BY l.LOGINNAME HAVING COUNT(l.LOGINNAME) > 1

The first query returned 3 duplicate email addresses in the HOMEPAGE.PERSON table. Further investigation showed that of these 3 users, one user had a single entry in the HOMEPAGE.LOGINNAME table. As you can’t simply delete a user from the HOMEPAGE.PERSON table, that entry was solved by clearing out the email address of the entry with corresponding entry in the LOGINNAME table. The other 2 entries however were quite special. Not only did both entries per user have a corresponding entry in the HOMEPAGE.LOGINNAME table. These users also had 2 entries in the EMPINST.EMPLOYEE TABLE. We had duplicate email addresses in our EMPLOYEE table! The source LDAP also had duplicate records for these 2 users with the same email address. Still, that doesn’t explain how these duplicate records ended up in the EMPLOYEE table as TDI should not allow duplicate email addresses. Solving this problem, did not fix our error yet.

The second query returned one entry: My system user. That explained the enormous number of errors I got in my log. Investigating the PERSON_IDs in the PERSON table showed that one entry was from before our last migration and the other from after. The old entry therefore seemed to be the bad entry. I deleted this row from the table in SQL and this solved the problem.