Prevent duplicate records in sys_user_grmember

richardyaxl · 3 weeks ago

In our ServiceNow instance, I have noticed that it is possible to insert duplicate records into the sys_user_grmember table. However, in my out of the box PDI there seems to be something in place that prevents this, and throws and "Invalid insert" error when trying to add a user to a group that is already a member.

I have looked at Business rules, ACLs, and indexing but I am struggling to identify where this is set so that I can duplicate it. Please can somebody help me to identify how this is configured?

Philippe Casidy · 3 weeks ago

Hi,

I have this behavior in Yokohama release.
When I try to insert an already existing member in sys_user_grmember, I got this "Invalid Insert".

Looking at the log I can read:

txid=83... SUA_47f3... SEVERE *** ERROR *** FAILED TRYING TO EXECUTE ON CONNECTION glide.. (connpid=3...): INSERT INTO sys_user_grmember (...)
Unique Key violation detected by database ((conn=34.) Duplicate entry 'd7eec1d01b4a2159cc4bcb33-50e5c4fc038d4b...' for key 'group')
java.sql.SQLIntegrityConstraintViolationException: (conn=346..) Duplicate entry 'd7eec1d0159cc4bcb33-50e5c49dbcfc038d4b...' for key 'group'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:229)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)

I looked at the sys_user_gr_member table and could in see in the related lists:

And I guess, that you cannot have duplicates for the index "group".

I have to pause on this for now but would love to read the results of your investigations.

(and yes, you can always create a BR if required)

Regards,

Philippe

View solution in original post

Philippe Casidy · 3 weeks ago

Hi, thanks for sharing your results.