Cross database JOIN won't work if databases are in different services/containers

I don't think there is anything in CiviCRM Entity that needs to be done for this error.

"SELECT command denied to user" would mean that the database user accessing the database does not have permissions to query the CivICRM tabes .. this could be because CiviCRM tables are installed in a different database, and the database user would need access to both databases.

Note, that if CiviCRM tables are installed in a separate database, the Drupal settings.local.php needs a connection to it:

$databases['civicrm']['default'] = array (
  'database' => 'database name', // update this
  'username' => 'database username', // update this
  'password' => 'database password', // update this
  'prefix' => '',
  'host' => 'DATABASE_HOST' // maybe update this,
  'port' => '3306', // maybe update this
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);

@markusa, it seems like you didn't read the whole issue description. I detail *exactly* when this happens but your response seems like it didn't address any of that.

It's not a problem of the settings files, because they *do* work when just creating a simple View of contacts (or whatever). The error only arises when trying to do a JOIN across contacts to users. *And* it only happens if the databases are in separate service containers (or servers). If they are in the same container the error doesn't appear (all else being the same).

I also mentioned above what CiviCRM Entity *could* do: that is, provide documentation so people know that currently CiviCRM Entity won't fully work if the CiviCRM database is on a different server or service container.

Currently I don't see any documentation of this module for Drupal 9+.

The available documentation is here: https://docs.civicrm.org/sysadmin/en/latest/integration/drupal/views/#dr...

CiviCRM Entity never sets database user in any way. It uses the database connections in the settings.local.php

Fact is CE + Views will contruct one query when accesses data across two databases .. but only one database connection is used, thus its database user is used.

When using separate databases, and separate database users / passwords for each connection .. then Drupal needs to use one db user to connect to both databases. If one database user does not have access to both databases, then when the query that uses a join across tables is generated, then there is this error.

I think it would depend on whether the base table of the View is a CivICRM table or Drupal table, for which database connection, and thus database user is used.

In Drupal 7 and 8, we were able to use database prefixes, so the "Views integration" code that was used in Drupal 7, did work in 8 and 9, but was deprecated in 9, and was slated to be removed in Drupal 10, so we stopped relying on it. I'm not sure if that would help your case, but it may still work in D9.

I realize that if you use 2 different containers, that's the same as 2 different servers .. so giving the same user access to both databases is not easy/possible.

Another option you have is to install the CiviCRM tables into the Drupal database. In that scenario, you wouldn't need the extra connection in the civicrm.settings.php, and wouldn't have any issue

So your options are:
1) Both databases in one container, use same database user for both databases
2) Both databases in one container, give both database users privileges for both databases
3) Install CiviCRM tables into Drupal database and remove $databases['civicrm']['default'] from settings.local.php

Any other options that include code changes, if any are possible, would be a research project. That project would involve figuring if database queries can be constructed with Drupal API, that use 2 separate database connections. 1 query 2 connection credentials. AFAIK this is not possible in Drupal.

So I propose then that we update the documentation to include the caveats: https://docs.civicrm.org/sysadmin/en/latest/integration/drupal/views/#dr... and also add a link to the docs in civicrm/admin/setting/uf, which currently looks like this for Drupal 9:

But this is misleadingly simplified, as noted in this conversatoin.

I will create an issue here https://lab.civicrm.org/documentation/docs/sysadmin/-/issues to do that.