Not able to show more than 49 users

Tried to reproduce the same issue in Local Drupal 10. There is no issue for me. Refer below screenshot.

Hi @iCU, @java008, the drupal.org community section project is a project specifically for the Community section on drupal.org, and not for general support for Drupal core. I am moving this into the core project as a support request. note that Drupal 10.0.0 is out-of-date and you should update to the latest Drupal 10 minor release for security reasons.

I am removing the Novice tag from this issue because this is a support request and the Novice tag is used by Drupal core mentors to tag issues that have a clear task and resolution.

I’m using this documentation as a source: https://www.drupal.org/community/contributor-guide/task/triage-novice-is... →

Have you modified the default view?

Adjusting the settings.php fixes the issue:

$databases['default']['default'] = array (
...
'init_commands' => [
'big_selects' => 'SET SQL_BIG_SELECTS=1',
],
'pdo' => [
PDO::ATTR_TIMEOUT => 5,
],
);

But since 50 users isn't really a big select, I suppose the database query is not very well formed.

Seems it is about some mysql settings. The same code I see in github for D11 so likely persists (https://github.com/drupal/drupal/blob/11.x/core/modules/user/src/Plugin/...). The error is misleading and fix is avoiding multiple IN conditions generating large sets. Even subquery does not help. But INNER JOIN furtunately works:

    if ($uids) {
      $roles = user_roles();
      $result = $this->database->query('SELECT DISTINCT [u1].[entity_id] AS [uid], [u1].[roles_target_id] AS [rid] FROM 
        (SELECT [entity_id], [roles_target_id] FROM {user__roles} WHERE [entity_id] IN ( :uids[] )) [u1],
        (SELECT [entity_id], [roles_target_id] FROM {user__roles} WHERE [roles_target_id] IN ( :rids[] )) [u2]
        WHERE [u1].[entity_id] = [u2].[entity_id]', [':uids[]' => $uids, ':rids[]' => array_keys($roles)]);
      // fix: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
      // {user__roles} [u] WHERE [u].[entity_id] IN ( :uids[] ) AND [u].[roles_target_id] IN ( :rids[] )', [':uids[]' => $uids, ':rids[]' => array_keys($roles)]);

Also which modules do you have installed?

How to get list of installed modules in raw list (beyond drush) ?
But related code is in core module. I have 2020 users, 5 roles in DB. I have no control on mysql as it is on hosting. I can reproduce in phpmyadmin as raw query and it always happens when "SELECT FROM WHERE IN () AND IN ()" or "SELECT FROM WHERE IN () SELECT FROM IN()" command is raised. Likely mysql does an UNION or so. INNER JOIN works well.

I have the same issue, I have +/-160 users on my website, I can test some things if you want