Loading...

Overriding Drupal $databases settings

The $databases variable in Drupal is a fundamental item to Drupal site configuration and is used to set the database connection details. With this variable, you can define one or more database connections. For more information, visit Database Configuration.

You might need to change the default Drupal settings to accommodate different database configurations, including the setting of values for MySQL variables and working with databases external to Drupal. You can use and modify the $databases variable in the settings.php file to:

Overriding MySQL variable settings

You might need to override MySQL default variable settings for variables like wait_timeout and transaction_isolation in Cloud Platform or Site Factory. While you cannot implement an override on the MySQL server directly as this is centrally managed, you can override that setting on a per session basis, for example, for every PHP process that bootstraps Drupal.

Note the following variables present in the code snippet included for the various use cases described later in this document:

Variable nameDescriptionNotes
AH_SITE_GROUPThe name of your site group. 
SITEThe site URL or the name of the multisite. 
DB_CONNECTIONThe name of the database as indicated on the Databases tab in the Cloud Platform user interface. For more information, visit Working with databases.
  • This is used in the $databases variable in your site's settings.php file.
  • If you are using a single-SQL server Drupal installation, you can set this value to default.
  • For a multisite Drupal installation, you can use the specific database name, depending on your setup. For more information, visit Database Configuration.
  • You must include string quotes around the database name.
VARIABLE_NAMEThe MySQL variable name. 
VARIABLE_VALUEThe MySQL variable value.
  • This value can be a number or a string.
  • If it is a string, ensure that the value is surrounded by the quote " character. For example,
    'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"'

Configuring for your use case

The following tabs provide details for configuring the MySQL variable values, depending upon your application installation configuration use case:

 

Sites not using BLT

Adding configuration

The following snippet:

  • Updates the code near the require line to disable database auto-connect.
  • Sets the values of the MySQL variables for the PHP session.
  • Calls the acquia_hosting_db_choose_active() function.

If you are not using BLT, you can add this snippet to settings.php in the Acquia require line section:

if (file_exists('/var/www/site-php')) { 
  global $conf; 
  
  // Do not autoconnect to database 
  $conf['acquia_hosting_settings_autoconnect'] = FALSE; 
  
  // Use your existing 'require' statement here 
  require('/var/www/site-php/AH_SITE_GROUP/AH_SITE_GROUP-settings.inc'); 
  
  // Set the MySQL variable values 
  // Make sure to include the array brackets, [] 
  $databases[DB_CONNECTION]['default']['init_commands'] = [ 
    'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VARIABLE_VALUE_1', 
    'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VARIABLE_VALUE_2', 
  ]; 
  
  // Connect to database 
  if (function_exists('acquia_hosting_db_choose_active')){ 
    acquia_hosting_db_choose_active( 
      $conf['acquia_hosting_site_info']['db'], 
      'default', 
      $databases, 
      $conf 
    ); 
  } 
}

For example, you can use the following code snippet to set the wait_timeout variable to 1200 and the transaction_isolation variable to READ-COMMITTED:

if (file_exists('/var/www/site-php')) { 
  global $conf; 
  
  // Do not autoconnect to database 
  $conf['acquia_hosting_settings_autoconnect'] = FALSE; 
  
  // Use your existing 'require' statement here 
  require('/var/www/site-php/AH_SITE_GROUP/AH_SITE_GROUP-settings.inc'); 
  
  // Set the MySQL variable values 
  $databases['default']['default']['init_commands'] = [ 
    'wait_timeout' => 'SET SESSION wait_timeout=1200', 
    'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"' 
  ]; 
  
  // Connect to database 
  if (function_exists('acquia_hosting_db_choose_active')){ 
    acquia_hosting_db_choose_active( 
      $conf['acquia_hosting_site_info']['db'], 
      'default', 
      $databases, 
      $conf 
    ); 
  } 
}

Sites using BLT and current Drupal version

Adding configuration

The following snippet:

  • Updates the code near the require line to disable database auto-connect.
  • Sets the values of the MySQL variables for the PHP session.
  • Calls the acquia_hosting_db_choose_active() function.

If you are using BLT and a later version of Drupal, do the following: 

  1. In your settings.php file, locate the line that requires blt.settings.php. For example, 

    require DRUPAL_ROOT . "/../vendor/acquia/blt/settings/blt.settings.php";
  2. Add the following code before the require ... blt.settings.php line:

    // On Acquia Cloud, do not immediately connect to the database. 
    $conf['acquia_hosting_settings_autoconnect'] = FALSE;
  3. Add the following code after the require ... blt.settings.php line:

    // Set the MySQL variable values. 
    // Make sure to include the array brackets, [] 
    $databases[DB_CONNECTION]['default']['init_commands'] = [   
        'VARIABLE_NAME_1" => 'SET SESSION VARIABLE_NAME_1=VALUE_1', 
        'VARIABLE_NAME_2" => 'SET SESSION VARIABLE_NAME_2=VALUE_2',  
    ]; 
    // On Acquia Cloud, connect to the Database. 
    if (function_exists("acquia_hosting_db_choose_active")) { 
       acquia_hosting_db_choose_active(); 
    }

    For example, an updated settings.php file when the wait_timeout variable is set to 1200 is:

    if (file_exists('/var/www/site-php')) { 
      // On Acquia Cloud, do not immediately connect to the database. 
      $conf['acquia_hosting_settings_autoconnect'] = FALSE; 
      
      // Original code put in by BLT. 
      require DRUPAL_ROOT . "/../vendor/acquia/blt/settings/blt.settings.php"; 
      
      // Set the Wait Timeout value. 
      $databases['default']['default']['init_commands'] = [ 
        'wait_timeout' => 'SET SESSION wait_timeout=1200',  
      ]; 
      
      // On Acquia Cloud, connect to the Database. 
      if (function_exists("acquia_hosting_db_choose_active")) { 
        acquia_hosting_db_choose_active(); 
      } 
    }
  4. After implementing the preceding changes, if you get the following error when running a drush command:

    TypeError: count(): Argument #1 ($value) must be of type Countable|array, null given in count() (line 155 of /usr/ah/lib-pub/D9-settings.functions.inc).
    1. Delete the following snippet:

      if (function_exists("acquia_hosting_db_choose_active")) { 
          acquia_hosting_db_choose_active(); 
      }
    2. Add the following snippet instead:

      if (function_exists("acquia_hosting_db_choose_active")) {    
          acquia_hosting_db_choose_active($conf['acquia_hosting_site_info']['db'], 'default', $databases, $conf); 
      }

Sites using Site Factory

Adding configuration

The following process:

  • Updates the code near the require line to disable database auto-connect.
  • Sets the values of the MySQL variables for the PHP session.
  • Calls the acquia_hosting_db_choose_active() function.

As Site Factory site owners, you can implement the configuration through the following hooks:

Add the following snippet in the pre-settings-php hook that includes defining global variables and initial values:

global $conf; 
$conf['acquia_hosting_settings_autoconnect'] = FALSE;
 

 

Verifying configuration for your use case

After you implement the preceding changes based on your use case, you must verify that the override settings are applied. You can do that by connecting to the database and checking the appropriate MySQL variable through the following drush command.

Current Drupal version

Run the following drush command to confirm the value set for VARIABLE_NAME:

drush eval 'print_r(Drupal\Core\Database\Database::getConnection(DB_CONNECTION, "default")->query("SHOW VARIABLES LIKE '\'VARIABLE_NAME\'';")->fetchObject());'

For example, you an check the value for the wait_timeout variable as follows:

$ drush eval 'print_r(Drupal\Core\Database\Database::getConnection("default", "default")->query("SHOW VARIABLES LIKE '\'wait_timeout\'';")->fetchObject());' 
stdClass Object 
( 
    [Variable_name] => wait_timeout 
    [Value] => 1200 
)
 
Drupal 7

Run the following drush command to confirm the value set for VARIABLE_NAME:

$ drush sqlq "SHOW VARIABLES LIKE 'VARIABLE_NAME';"
For example, you an check the value for the wait_timeout variable as follows:
$ drush sqlq "SHOW VARIABLES LIKE 'wait_timeout';" 
wait_timeout 1200
 

 Working with databases external to Drupal

You might need to access one or more databases, in addition to the Drupal configured database. This is different to a Drupal multisite setup. The $databases variable is set up so as to use the first key as the identifier for each database, known as the Connection Key. For more information, visit Database Configuration. With this key, you can define a second or more databases that Drupal can access. Ensure that there is at least one default key, which defines the primary database. For example, a second database connection is identified as extra:

$databases['default']
$databases['extra']

You can connect to external databases through the following primary steps:

  1. Define the secondary database in your settings.php file
  2. Switch to the secondary Database inside of your module
  3. Query that database
  4. Swap back to the default Database when finished

The following example details the process of modifying the default Drupal database and a second, additional database in a custom module and querying the second database:

Defining the secondary database in your settings.php file

The default.settings.php file in the View Source section defines the format for the $databases variable. The first key indicates the database name and the second key indicates the replication database name, or a "target" name, if you want to use primary or replica database replication for your site.

$databases['database_name']['database_target']

To access a different database in addition to your Drupal database, use the first key name for the $databases variable. For example, the following code snippet defines the configurations for the default Drupal database and an additional database, named "second_db". Note the keys, default and second, used for the $databases variable: 

// Default Drupal database configuration
$databases['default']['default'] = array (
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'drupal',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '3306',
  'isolation_level' => 'READ COMMITTED',
  'driver' => 'mysql',
  'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
  'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
);
// Second database configuration
$databases['second']['default'] = array(
  'database' => 'second_db',
  'username' => 'drupal',
  'password' => 'drupal',
  'host' => 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  'prefix' => '',
);


For more information, visit:


Switching to the database inside your module

Switch to the additional database by using the Database::setActiveConnection() and Database::getConnection() static methods. For the example,

// Change the active connection to the additional database
Database::setActiveConnection('second');
// Get the database connection for the additional database
$second_db_conn = Database::getConnection();

Querying the database

Perform the query to the database that is not the default Drupal database as follows:

// Execute a query on tables in the additional database
$result = $second_db_conn->select('some_table', 't')
      ->fields('t', ['id', 'column_name'])
      ->range(0, 1)
      ->execute()
      ->fetchAll();
// Do something with the result
// ...

Swapping back to the default database when finished

Change the connection to the default Drupal database connection as follows:

// Switch back to the default Drupal database.
Database::setActiveConnection();

You have implemented a change between your default Drupal database and an additional database.

Did not find what you were looking for?

If this content did not answer your questions, try searching or contacting our support team for further assistance.

Back to Section navigation