Difference between user table and API

Must-share information (formatted with Markdown):

  • Version in use: 7.9.4 (previous LTS)
  • I try to find out why I have a difference in the amount of users in the users table and the users shown through the API
  • Different SQL queries to get the reason of why this difference exists

Hi guys,

I’m currently a little bit confused by the amount of users shown in the database and the amount of users shown either in the admin panel or through the web API. Selecting all users through SQL I get 302 users, substracting those which are not active I get 287. But when I search for all users using the web API I only get 262. I tried to sort things out several ways but I can’t get the reason why this difference exists.

Can anyone explain this to me, please? Is there a flag I may overlook?

Regards, Thomas

I have a short update on this. I made a dump of the database and used in on a test environment. This environment is about the same as production. However, the amount shown in the GUI or API is still something else than the amount shown in the database. And in production the users I miss in the test environment are shown as expected.

I’m really out of ideas, why I have a difference, even though the databases are completely the same.

Regards, Thomas

Hi, could you tell us a bit more about what you are trying to achieve? Is there something unexpected in the API, a user that should be there but is not, or the opposite? If that’s the case, are you using any authentication delegation (SAML, github, etc)?

My very naive question is “why do you worry about what’s on the db”?

Being more precisely, I try to understand why I do have a difference with one and the same database in two environments. The amount of the users inside the table users is the same, but in production I see all of them through GUI and API, in our test environment I only get 262 out of 302. Technically there’s no difference between both of them, the database in test is a dump from production.

This causes a big ? to me …

Which SonarQube API endpoint are you using to list users?

It’s /api/users/search

This endpoint relies on the elasticsearch index, not the database. You might have your indexes out of sync.

You can try to trigger a full elasticsearch reindex on your test environment.

Hey, thank you, that’s it! That trick solved it. I never was aware of that Elastic search indexes also users and so to force a reindex.

Once again, thank you!

1 Like

I’m glad that solved the issue. My hypothesis is that you reused your test environment from another SQ version (or another database snapshot). Following our upgrade guide, every time you change the SQ version or restore a database snapshot, you should restart from a fresh filesystem directory (meaning you don’t reuse the SQ_HOME/data/es7 subfolder).

As I wrote in the previous posts I reused a database dump from our production environment. :wink:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.