Question

System.user uniqueness constraint bug?

0

Hey all, I've been seeing some odd behavior in Mx6 that I suspect may be a bug. Before I start firing tickets at Mendix, I figured I'd get a second opinion. In Mx6 I've seen several instances where I would want to commit a list of Accounts, and I would get the error that 'name' must be unique. The first time this happened, was with rather complex data-import logic, so I figured there might be some issue with attempted auto-commits, even though no new users were created in the microflows. Recently, I ran into the issue again, in a much clearer scenario. I have small microflow that changes a boolean for all Accounts, indicating that an information message must be displayed when the user visits the application homepage. The microflow is no more than 'retrieve all accounts --> iterate through accounts, change boolean --> commit list'. There are no eventHandlers on either system.user or administration.account. Yet the list commit will always fail, claiming that 'name must be unique'. This issue appears on the production environment. I've not been able to replicate it on the test or acceptance environments, nor local. In short; I'm stumped. Has anyone encountered similar behavior, or can hazard a guess as to what's going on?

asked 2016-03-29

Christiaan Westgeest

3 answers

Jaap Pulleman · Answer 1 · 2016-03-29

Short answer: your System$User table most likely got corrupted in the past as it appears to contain at least 2 user entities that share the same Name attribute, which violates the unique constraint of that attribute. Removing those users so only 1 of each name remains should resolve your issue.

Long answer: Was this project started in 6.3.1 or converted from an older version, if the latter, what is the version this project was started in? The reason I am asking this is there were some rare situations in older Mx5 (if I remember correctly, else <mx5) versions="" where="" duplicate="" inserts="" of="" system.user="" could="" occur.="" this="" would="" happen="" in="" rare="" circumstances="" under="" very="" heavy="" load.<="" p="">

You could backup a copy of the database and restore it locally then fire the following SQL query:

select name, COUNT(name) AS nameCount from system$user GROUP BY name HAVING COUNT(name) > 1

If any results turn up your user table is violating the unique name constraint of the system$user table and you should try to remove the excess entries of the same user. In that case you could technically view your System$User table as corrupted.

You could also write a microflow to do this, retrieve all System$User entities sorted by name. Iterate over them and when n+1 has the same name as n (where n = iteration counter) you have encountered the same issue.

Christiaan Westgeest · Answer 2 · 2016-03-30

My initial solution was to commit in an iterator instead of batch, with error-handling on the commit. I set logging on the error handler. Not pretty, but it did allow the microflow to run its course, and show me if there were any duplicate users.

As it turns out, there were indeed two duplicates.

I am puzzled (and a little concerned) as to how these came to be though. User accounts are created when a user accesses the application for the first time using an SSO mechanism. The logic checks whether the username in the incoming request matches one in the database. If not, a new account is created.

For duplication to come about, both this check and the uniqueness constraint must have failed. Or there is another way this duplication might have happened, but I can't think of it.

One factor that may be of influence is that we're running on a Cloud Foundry infrastructure, using elastic runtime to loadbalance over two application instances. I still can't really think of a plausible scenario where this would cause duplication, but it may be relevant.

I'll monitor the situation, but any additional thoughts/insights are welcome.

Nikel Kruizinga · Answer 3 · 2016-03-30

I have been able to reliably reproduce this issue (using a breakpoint) in the past on a 4.x Mendix version. In that project I worked around it by manually re checking before commit, and doing the commit within a synchronized Java action.

This was also for a project that would create users as part of a sign in procedure.