Troubleshooting SELECT Privileges Issue with Drupal 9 Views Integration

Question

I'm having an almost identical problem to this person (https://www.drupal.org/project/civicrm_entity/issues/3198146) so I'm hoping to get an answer for both of us.

It looks like this may also be the same issue.

I have a Drupal 9/CiviCRM install set up on a shared hosting account on A2Hosting. Everything has gone smoothly so far, and I was able to follow the Views integration instructions exactly. However, when I try to set up a simple View to test, I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user '<USER>'@'localhost' for table 'civicrm_contact': SELECT "civicrm_contact"."id" AS "id" FROM {civicrm_contact} "civicrm_contact" LIMIT 11 OFFSET 0; Array ( )

The error seems pretty self-explanatory - I need to assign SELECT privileges to the in question - but I've tried everything I can think of, namely:

1. I've given the USER in question ALL PRIVILEGES on the CiviCRM database in cPanel. I can't access the User list via phpMyAdmin, which I guess is common on shared hosting.

2. I've tried to use the command line to check the status of the USER's privileges, but I apparently need root access, which is not possible on the shared hosting plan.

3. I've contacted A2Hosting directly to see if they can help check (or correctly assign) the privileges for the USER in question. They say they can't.

So, I have a few questions that I'd love help answering (I'm a relative beginner at all of this):

• Is this a known issue? There are at least two of us now who've been stumped by it. Is there a quick fix I'm not seeing?

• Are the USER in cPanel and USER@localhost different entities? Is it possible that they would have different privileges?

• If Views integration is somehow not possible in a shared hosting environment, what is the preferred set up? Am I on the wrong hosting service?

Answer 1

For me and a few other users, with Civi & Drupal in separate databases, this was solved by comment #4 on Views integration error on D9:

Try removing the back ticks ... '`' from the prefix information in the settings.local.php

(or settings.php in my case.)

If it's not working for you with Civi & Drupal in the same database, assuming you've removed the code that you pasted into the settings file, maybe try Jon G's solution at How to get CiviCRM Views to work in Drupal 8 with separate CiviCRM database.