Manual synchronization error

I encountered the same issue. To fix, I navigated to http://sitename.com/civicrm/admin/setting/uf?reset=1 and copied the entire array for the $databases and pasted it into my settings.php. According to the docs, Drupal 9 should recognize the CiviCRM database automatically as long as you have the correct prefixes, but it seems this module doesn't support that yet and needs this deprecated method.

The original site referred to is using a separate DB for CiviCRM information. I believe this is the root of the issue.

When we look at the logs (including the error message posted) it is always using the Drupal database "name_of_drupal_db.civicrm_uf_match" instead of "name_of_civicrm_db.civicrm_uf_match".

This is because the code uses a database call instead of API. It can be fixed by changing the function getSyncContactIds in src/CivicrmMemberRoles.php. Sorry I don't know how to do a proper pull request:

// Find contacts with applicable types.
/*    $select = $this->database->select('civicrm_uf_match', 'uf')
      ->fields('uf', ['contact_id']);
    $select->leftJoin('civicrm_membership', 'm', 'uf.contact_id = m.contact_id');
    $select->isNotNull('m.id')->condition('m.membership_type_id', $types, 'IN');

    if ($limit) {
      $select->range(0, $limit)->orderRandom();
    }

    return $select->execute()->fetchCol();
 */

$this->civicrm->initialize();
$uFMatches = \Civi\Api4\UFMatch::get()
  ->addSelect('contact_id')
  ->addJoin('Membership AS membership', 'LEFT', ['membership.contact_id', '=', 'contact_id'])
  ->addWhere('membership.membership_type_id', 'IN', $types)
  ->setLimit($limit)
  ->execute()
  ->column('contact_id');

  return $uFMatches;
 }

With this fix there is no need to maintain the database array in settings.php (which used to be required for Views etc but is no longer needed in D9)

I have attached a patch that I worked on for a client site that had this same problem, It is slightly different in that I stuck with using the database but I instantised 2 database arrays as per civicrm entity which provides d9 views integration and modified the database query appropriately. That being said I think with the API or database way it is a bit of 6 of 1 half a dozen of the other, I'll let the maintainers decide which way they want to go.

In created a PR to implement corrections proposed by jonhalle.
I've not tested it on 2 databases (one for drupal, an another for civicrm).

@seamus_lee your patch gives this error: "Class "Drupal\civicrm_member_roles\Database" not found". Need to add use Drupal\Core\Database\Database; I think.

The MR https://git.drupalcode.org/project/civicrm_member_roles/-/merge_requests/2 works for me. The code looks good to me.

Released in 8.x-1.0-beta3

Marking this as fixed