Background

This issue happened after switching DB with the way, How to change mySql connection dynamically without adding DB connection info in config by manual.

What I tried to do

  1. Use 2 DB (DB1, DB2)
  2. Get Master data from DB1 by “getMasterData()”, and insert them to DB2 by “saveMasterData()”
  3. I switched DB connection by “connectDb()”
  4. After get data by “getMasterData()”, I found the error when inserting by “saveMasterData()”

TableService.php

protected $capsule;

    public function getCapsule()
    {
        if (!$this->capsule) {
            $this->capsule = new Capsule();
        }

        return $this->capsule;
    }

public function saveTables(array $tables)
    {
        $savedTable = [];
        foreach ($tables["tables"] as $tableName => $table) {
            if (!isset($table["is_save"])) continue;

            $savedTable = Table::updateOrCreate(["name" => $tableName], $table);

            $targetTable = [
                "id" => $savedTable->id,
                "databaseId" => $savedTable->database_id,
                "name" => $savedTable->name,
                "parent_master_external_key" => $table["parent_master_external_key"] ?? "",
                "value_column" => $table["value_column"],
                "connection" => "mysql"
            ];
            $this->saveColumn($targetTable);

            if ($savedTable->master_table_flg) {
                $masterdataArray = $this->getMasterData($targetTable);
                $this->saveMasterData($masterdataArray, $targetTable);
            }
        }

        return $savedTable;
    }

private function getMasterData($masterTable)
    {
        //Get "DB1" connection data from DB2 and insert in $targetDb
        $targetDb = $this->connectDb($masterTable["databaseId"]);
        $connection = $masterTable["connection"];
        $databaseName = $targetDb->database;
        $tableName = $masterTable['name'];

        $masterdataArray = DB::connection($connection)->select(
            "
            SELECT *
            FROM `{$databaseName}` . `{$tableName}`"
        );

        return $masterdataArray;
    }

private function saveMasterData(array $masterdataArray, $targetTable)
    {
        $this->connectDb();

        foreach ($masterdataArray as $masterdata) {
            // $saveMasterData = MasterData::on($defaultConnectionName)->updateOrCreate(
            $saveMasterData = MasterData::updateOrCreate(
                [
                    "table_id" => $targetTable["id"]
                ],
                [
                    "table_id" => $targetTable["id"],
                    "value" => $targetTable["value_column"],
                    "parent_external_key" => $masterdata->{$targetTable["parent_master_external_key"]},
                ]
            );
        }
        return $saveMasterData;
    }

private function connectDb($databaseId = 0)
    {

        $capsule = $this->getCapsule();
        
        if (empty($databaseId)) {
            $targetDb = config("database.connections.mysql");
            $capsule->addConnection([
                'host' => $targetDb["host"],
                'database' => $targetDb["database"],
                'username' => $targetDb["username"],
                'password' => $targetDb["password"],
            ], "job_insert_system");

            $capsule->setEventDispatcher(new Dispatcher(new Container));
            $capsule->bootEloquent();

            return $targetDb;
        }

        $targetDb = Database::find($databaseId);
        $capsule->addConnection([
            'host' => $targetDb->host,
            'database' => $targetDb->database,
            'username' => $targetDb->user_name,
            'password' => mb_strstr((base64_decode($targetDb->password)), "*"),
        ], $targetDb->service);

        $capsule->setEventDispatcher(new Dispatcher(new Container));
        $capsule->bootEloquent();
        return $targetDb;
    }

config/database.php

'default' => env('DB_CONNECTION', 'mysql'),

'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'db'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'DB2'),
            'username' => env('DB_USERNAME', 'username'),
            'password' => env('DB_PASSWORD', 'password'),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
]

.env

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=DB2
DB_USERNAME=username
DB_PASSWORD=password

Requirement

Kindly let me know how to solve the issue and insert master data in DB2 by “saveMasterData()”.

Thank you.