Application Launcher

Cloud Platform

Overriding Drupal $databases settings

Cloud Platform

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:

Override MySQL variable settings
Work with databases external to Drupal

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 name	Description	Notes
`AH_SITE_GROUP`	The name of your site group.

Override MySQL variable settings
Work with databases external to Drupal

Overriding MySQL variable settings

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

Variable name	Description	Notes
`AH_SITE_GROUP`	The name of your site group.

Override MySQL variable settings
Work with databases external to Drupal

Overriding MySQL variable settings

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

Variable name	Description	Notes
`AH_SITE_GROUP`	The name of your site group.

Configuring for your use case

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

Warning

The following code snippets are for reference purpose and may not match with your settings.php file. You must locate your require('/var/www/site-php/...') line and add the extra suggested lines after it.
Ensure you are not calling require('/var/www/site-php/...') several times in your code; you must call it only once, ensuring you have placed any additional lines before or after that statement.

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 Site Factory, 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 
    ); 
  } 
}

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.

Note

Ensure that you replace SITE with the site URL or the mulitsite name and VARIABLE_NAME with the name of the variable that you want to check.

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());'

Note

If you verify the override values by using a drush sql-cli session, you might not see the set override values. Therefore, you must use the Drupal\Core\Database\Database::getConnection function.

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 
)

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']

Note

The steps in the drupal.org page are applicable to Drupal versions 8 and 7. However, Drupal 8 steps apply to current Drupal versions as well.

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

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.

Configuring for your use case

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

Warning

The following code snippets are for reference purpose and may not match with your settings.php file. You must locate your require('/var/www/site-php/...') line and add the extra suggested lines after it.
Ensure you are not calling require('/var/www/site-php/...') several times in your code; you must call it only once, ensuring you have placed any additional lines before or after that statement.

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 Site Factory, 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 
    ); 
  } 
}

Verifying configuration for your use case

Note

Ensure that you replace SITE with the site URL or the mulitsite name and VARIABLE_NAME with the name of the variable that you want to check.

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());'

Note

If you verify the override values by using a drush sql-cli session, you might not see the set override values. Therefore, you must use the Drupal\Core\Database\Database::getConnection function.

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 
)

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

Note

The steps in the drupal.org page are applicable to Drupal versions 8 and 7. However, Drupal 8 steps apply to current Drupal versions as well.

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

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

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

// 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.

Overriding MySQL variable settings

Overriding MySQL variable settings

Overriding MySQL variable settings

Configuring for your use case

Adding configuration

Verifying configuration for your use case

Working with databases external to Drupal

Defining the secondary database in your settings.php file

Database Configuration

Switching to the database inside your module

Querying the database

Swapping back to the default database when finished

Did not find what you were looking for?

Configuring for your use case

Adding configuration

Verifying configuration for your use case

Working with databases external to Drupal

Defining the secondary database in your settings.php file

Database Configuration

Switching to the database inside your module

Querying the database

Swapping back to the default database when finished

Did not find what you were looking for?