CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Adarsh Sojitra
Adarsh Sojitra

Reputation: 2199

How to dynamically reset database config in Laravel?

I am trying to get sizes of the databases hosted on multiple servers. For Example, I have 2 databases on Server A and 3 databases on Server B.

I want to get sizes of all those databases from my Laravel application. Actually, I know how to reset the configuration. Here is the code I am using but it is only giving me the correct database size of the first database. Others are getting the size of NULL which is not correct because I know that the size of one of the databases on Server B is 0.6MB because I've installed WordPress on it.

$databases = array();
foreach($servers as $server){
    // Setting Config
    Config::set('database.connections.mysqlClient.host',$server->ip);
    Config::set('database.connections.mysqlClient.password',$server->mysql_password);

    foreach($server->databases as $database){
        $database->size = DB::connection('mysqlClient')->select('SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) "db_size"
            FROM   information_schema.tables 
            where table_schema = "'.$database->name.'";'
        )[0]->db_size;

        $databases[] = $database;
    }
}

As I've Configured credentials inside the loop, it should update the credentials or config for mysqlClient for each server. If it updates the config, I should get correct sizes for all the database which is not the case.

The script is not throwing any Exception. I am sure that there is something wrong with Config. It is because when I try to get sizes of all the database on the specific server, I get perfect results.

Upvotes: 5

Views: 7569

Answers (1)

Chris
Chris

Reputation: 58182

Untested as I don't have something like this currently setup, but I'm sure you need to purge the DB after a config swap:

$databases = array();
foreach($servers as $server){
    // Setting Config
    Config::set('database.connections.mysqlClient.host',$server->ip);
    Config::set('database.connections.mysqlClient.password',$server->mysql_password);

    foreach($server->databases as $database){
        DB::purge('mysqlClient'); // <-- Get rid of old connection
        $database->size = DB::connection('mysqlClient')->select('SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) "db_size"
            FROM   information_schema.tables 
            where table_schema = "'.$database->name.'";'
        )[0]->db_size;

        $databases[] = $database;
    }
}

Edit: There is also \DB::reconnect('mysqlClient'); but people have documented issues with it

Upvotes: 9

Related Questions