Database Connection Only Works Some of the Time

Question

I have a CiviCRM instance that has its database hosted in RDS Aurora. This has worked well for several years. Recently I needed to restore from a copy of the Aurora cluster and I changed the name of the endpoint (database server). Nothing else changed. The change is working for the Drupal database and for CiviCRM some of the time.

I am using Drupal 9.3.6 and CiviCRM 5.46.2.

If I go to mysite.org/civicrm CiviCRM comes up well. If I go to a page that pulls CiviCRM data into Drupal Views then it cannot connect. My views could connect before this change was made. If I use the cv sql command from the command line I get access denied. If I copy and paste (meaning to reduce potential errors) from the file created by the CV command to access MySQL I can access the database.

Looking at the logs it appears that the connection fails for anything using API V3. I have tried to upgrade CiviCRM to see if that would solve this problem and the upgrade fails trying to determine installed extensions using a call to API V3.

I have checked all of the settings on the Aurora end and the restored cluster has the same settings as the cluster that has since been deleted.

Any help or suggestions on what to check are greatly appreciated.

Thanks, Josh

Answer 1

I solved this. ::Wipes sweat from brow:: In Drupal 9 you can no longer use database prefixes syntax.

This syntax no longer works: "$databases['default']['default']['prefix']= array()"

Without this syntax a new database array is needed. Something like this:

$databases['civicrm']['default'] = array (
'database' => 'civicrm_dev',

'username' => 'user', 'password' => 'password', 'host' => 'name-cluster-cluster.cluster-someID.us-west-2.rds.amazonaws.com', 'port' => '3306', 'namespace' => 'Drupal\Core\Database\Driver\mysql', 'driver' => 'mysql', );

See also: https://www.drupal.org/project/civicrm_entity/issues/3198146#comment-14352132

When I updated the settings file for Drupal I only changed the MySQL host once missing the connection to the CiviCRM database in the settings.php file.