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