1

Background

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

What I tried to do

  1. Use 2 databases (DB1, DB2)
  2. Get Master data from DB1 by getMasterData(), and insert it into DB2 by saveMasterData()
  3. I switched my database connection by connectDb()
  4. After getting the data using getMasterData(), I encountered 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.

1 Answer 1

0

I could solve by not using Capsule.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.