Drupal7 connecting to multiple databases and troubleshooting

This article describes how to connect Drupal7 to multiple databases, how to operate instances, and how to solve common problems. For more information, see Drupal

If you encounter these problems:
1. how does Drupal Connect to multiple databases?
2. after Drupal connects to multiple databases, it finds that the program reports an error. why?
3. when Drupal acquires, adds, modifies, or deletes multiple databases, data is not correctly written to the database or empty data is read. how can this problem be solved?
4. I only want to call a function in Drupal or control other databases, but it fails?
Please take a closer look at the introduction and how to solve your problem.
1. how does Drupal Connect to multiple databases?
To allow Drupal to connect to multiple databases, you need to convert $ db_url to an array.
By default, the URL format (string) for connecting to a single database is as follows ):
The code is as follows: $ db_url = 'MySQL: // username: password @ localhost/databasename ';
$ Db_url = 'mysqli: // username: password @ localhost/databasename ';
$ Db_url = 'pgsql: // username: password @ localhost/databasename ';

The URL format (array) of multiple databases is supported ):
The code is as follows: $ db_url ['default'] = 'MySQL: // drupal: drupal @ localhost/drupal ';
$ Db_url ['mydb'] = 'MySQL: // user: pwd @ localhost/anotherdb ';
$ Db_url ['db3'] = 'MySQL: // user: pwd @ localhost/yetanotherdb ';

When querying a different database, you can simply set the database to the active database through the reference Key of $ db_url.
The code is as follows: Db_set_active ('mydb ');
Db_query ('select * FROM table_in_anotherdb ');
// After the data is obtained, switch back to the default database connection.
Db_set_active ('default ');
?>

This is the basic operation for Drupal database operations.

2. after Drupal connects to multiple databases, it finds that the program reports an error. why?

When connecting to multiple databases, an error is reported, which may be due to the following reasons:

1. an SQL error occurred while connecting to other databases. this is a human code error;
2. when the database is connected, an error is reported even if the SQL statement is correct because the data table cannot be found in other databases;

Solution:
In the first case, modify the SQL statement based on the SQL error.
In the second case, check whether the database connection is crossed. This means that the data table of another database is called, but the database connection has been changed to another place. For database connection crossover, check whether the SQL statement after the function db_set_active is in the active database.

III. does Drupal not work properly when obtaining, adding, modifying, or deleting multiple databases?

1. in Drupal, SQL statements do not contain the data table prefix. you only need to use braces {} to include the table to add the data table prefix during database operations.
Example: db_query ('select * FROM {table_in_anotherdb }');
However, if a database user has permissions for multiple databases, you do not need to set $ db_url to connect to the database and directly operate on the current database connection.
Set $ db_prefix to perform cross-database operations:
The code is as follows:
$ Db_prefix = array (
'Default' => ",
'Authmap' => 'Z _',
'Profile _ fields '=> 'usertable. z _',
'Profile _ values' => 'usertable. z _',
'Users _ roles '=> 'usertable. z _',
'Users _ Fields' => 'usertable .',
'Role' => 'usertable. z _',
'Session' => 'usertable. z _',
'Users' => 'usertable. z _',
);

When the code above acts, the current Drupal user and other information all use usertable, so that multiple Drupal can share a user information database usertable, where z _ represents the data table prefix.

Note:

A) the users table is used to store the basic information of Drupal users and the UID and basic fields shared by all users;
B) the sessions table is used to store Drupal user Sessions, which can count the number of online users of all sites;
C) the role table is used to store roles of all Drupal sites;
D). users_roles: permission to store all Drupal sites;
Through the above $ db_prefix, you can globally set the database to be used for that table and the prefix of that table. this is just to facilitate the use of standard {table} in Drupal SQL statements }.

2. if you do not use $ db_prefix, the straightforward method is to directly store the database table name in the SQL statement.

For example:
The code is as follows:
Db_query ("SELECT uid FROM test. z_table1 WHERE name = '% s' and pass =' % s'", $ name, md5 ($ pass ));

The preceding SQL statement is directly located in the test database and z_table data table.
Therefore, when Drupal acquires, adds, modifies, or deletes multiple databases, the data is not correctly written to the database or empty data is read, check whether the database and data table you control are in the correct position.

4. call a function in Drupal or control other databases

See the following function framework code:
The code is as follows:
Function test_fuc (){
Global $ db_url; // Obtain global variables
$ Db_url ['DB _ logs'] = 'mysqli: // username: password @ localhost/databasename ';
Db_set_active ('Db _ logs ');
$ Codehere; // the SQL statement used to operate the db_logs database connection
Db_set_active ('default ');
}

In particular, $ db_url is a global variable and must be referenced by global in a local function:
The code is as follows: global $ db_url; // Obtain the global variable
After setting the database, remember to use db_set_active ('default'); and set the database connection to restore to the default value.