3

I have my Drupal 9 database and my CiviCRM database in two separate databases on the same Amazon MySQL Aurora instance. The setup in our dev environment is identical and is working perfectly. However, after performing the upgrade to Drupal 9 and CiviCRM 5.37.2 from Drupal 8 I am now getting a MySQL access denied error in production, only when accessing CiviCRM with Views.

I have looked at this issue as an Amazon issue, a Drupal issue, and a CiviCRM issue. I am not positive it is a CiviCRM issue but the tables are specifically CiviCRM so I am hoping I can get some help, please.

This is the full error:

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'user'@'10.0.2.97' for table 'civicrm_contact': SELECT "civicrm_contact"."id" AS "id", "users_field_data_civicrm_uf_match"."uid" AS "users_field_data_civicrm_uf_match_uid", "contact_id_civicrm_contact"."id" AS "contact_id_civicrm_contact_id", "member_of_contact_id_civicrm_contact"."id" AS "member_of_contact_id_civicrm_contact_id" FROM {civicrm_contact} "civicrm_contact" INNER JOIN {civicrm_uf_match} "civicrm_uf_match" ON civicrm_contact.id = civicrm_uf_match.contact_id INNER JOIN {users_field_data} "users_field_data_civicrm_uf_match" ON civicrm_uf_match.uf_id = users_field_data_civicrm_uf_match.uid LEFT JOIN {civicrm_membership} "contact_id_civicrm_contact" ON civicrm_contact.id = contact_id_civicrm_contact.contact_id LEFT JOIN {civicrm_membership_type} "member_of_contact_id_civicrm_contact" ON civicrm_contact.id = member_of_contact_id_civicrm_contact.member_of_contact_id WHERE (users_field_data_civicrm_uf_match.uid = :users_field_data_uid) LIMIT 1 OFFSET 0; Array ( [:users_field_data_uid] => 3016 )

I have checked that when logging in directly to the database from the EC2 instance that I have access to the database and the specific table. My EC2 security group has the appropriate grants for Amazon Aurora.

I my settings.php has

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`civicrm_prod`.', ...

I updated the array tonight after the upgrade.

Any help is greatly appreciated.
Thanks,

Josh

1
  • Can you run SHOW GRANTS FOR 'user'@'10.0.2.97' in mysql?
    – Demerit
    Jun 19 at 14:03
4

Remove ` symbol added to the database name in settings.php

eg

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`civicrm_prod`.', ...

change to

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => 'civicrm_prod.', ...
1
  • Wow, thank you!. I do not know how that happened, I just copied and pasted from the CiviCRM CMS page.
    – Josh
    Jun 22 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.