---
title: "Overriding Drupal $databases settings"
date: "2024-11-21T02:59:21+00:00"
summary: "Learn how to override Drupal's $databases settings for MySQL variables and external database connections. Step-by-step guide included."
image:
type: "page"
url: "/acquia-cloud-platform/overriding-drupal-databases-settings"
id: "2af0a0c7-c903-4457-8077-4759add32383"
---

Table of contents will be added

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](https://www.drupal.org/docs/drupal-apis/database-api/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](#overriding-mysql-variable-settings)
*   [Work with databases external to Drupal](#working-databases-external)

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.

 

`SITE`

The site URL or the name of the [multisite](/acquia-cloud-platform/about-drupal-multisite-installations "About Drupal multisite installations").

 

`DB_CONNECTION`

The name of the database as indicated on the **Databases** tab in the Cloud Platform user interface. For more information, visit [Working with databases](/acquia-cloud-platform/working-databases "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](/acquia-cloud-platform/about-drupal-multisite-installations "About Drupal multisite installations") installation, you can use the specific database name, depending on your setup. For more information, visit [Database Configuration](https://www.drupal.org/docs/drupal-apis/database-api/database-configuration).
*   You must include [string quotes](https://www.php.net/manual/en/language.types.string.php) around the database name.

`VARIABLE_NAME`

The MySQL variable name.

 

`VARIABLE_VALUE`

The 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:

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.

### Sites not using Site Factory

### 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 not using Site Factory, you can add this snippet to `settings.php` in the [Acquia require line](/acquia-cloud-platform/acquia-require-line "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 
        ); 
      } 
    }

### Drupal 7

#### Adding configuration

You can add the following code snippet to `settings.php` in the [Acquia require line](/acquia-cloud-platform/acquia-require-line "Acquia require line") section:

    if (file_exists('/var/www/site-php')) { 
      // 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'] = array( 
        'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VALUE_1', 
        'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VALUE_2', 
      ); 
      
      // Connect to database 
      if (function_exists("acquia_hosting_db_choose_active")) { 
        acquia_hosting_db_choose_active(); 
      } 
    }

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

### 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:

*   [pre-settings-php hook](https://docs.acquia.com/site-factory/extend/hooks#section-hooks-executed-on-every-page-load)
*   [post-settings-php hook](https://docs.acquia.com/site-factory/extend/hooks#section-hooks-executed-on-every-page-load)

### Configuring pre-settings-php hook

Add the following snippet in the [pre-settings-php hook](https://docs.acquia.com/site-factory/extend/hooks#section-hooks-executed-on-every-page-load) that includes defining global variables and initial values:

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

### **Configuring post-settings-php hook**

Add the following snippet in the [post-settings-php hook](https://docs.acquia.com/site-factory/extend/hooks#section-hooks-executed-on-every-page-load) to set the MySQL variable values and select the database server to connect to:

    // Set the MySQL variable values 
    // Make sure to include the array brackets, [] 
    $databases[DB_CONNECTION]['default']['init_commands'] = array(   
      "VARIABLE_NAME_1" => "SET SESSION VARIABLE_NAME_1=VALUE_1", 
      "VARIABLE_NAME_2" => "SET SESSION VARIABLE_NAME_2=VALUE_2", 
    ); 
    
    // Connect to database 
    if (file_exists('/var/www/site-php')) { 
      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](/acquia-cloud-platform/about-drupal-multisite-installations "About Drupal multisite installations") name and `VARIABLE_NAME` with the name of the variable that you want to check.

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

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](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Database.php/function/Database%3A%3AgetConnection/11.x) 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 
    )

##### **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](/acquia-cloud-platform/about-drupal-multisite-installations "About Drupal multisite installations") 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](https://www.drupal.org/docs/drupal-apis/database-api/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](https://www.drupal.org/docs/7/creating-custom-modules/howtos/how-to-connect-to-multiple-databases-within-drupal) 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:

1.  [Define the secondary database in your settings.php file](#define-secondary-database)
2.  [Switch to the secondary Database inside of your module](#switch-secondary-database)
3.  [Query that database](#query-the-database)
4.  [Swap back to the default Database when finished](#swap-back)

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](https://api.drupal.org/api/drupal/sites%21default%21default.settings.php/10) 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:

*   [Adding additional databases to your configuration](https://www.drupal.org/docs/7/creating-custom-modules/howtos/how-to-connect-to-multiple-databases-within-drupal#s-drupal-8)
*   [Database Configuration](https://www.drupal.org/docs/drupal-apis/database-api/database-configuration)
    =====================================================================================================
    

###   
Switching to the database inside your module

Switch to the additional database by using the [Database::setActiveConnection()](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Database.php/function/Database%3A%3AsetActiveConnection/10) and [Database::getConnection()](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Database.php/function/Database%3A%3AgetConnection/10.x) 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.