切换数据库后触发“Database connection [default] not configured”错误求解决

问题背景 #

实现动态切换MySQL连接后出现异常：计划从DB1获取主数据并插入DB2，但取数成功后，执行插入操作时触发错误。

操作流程 #

• 同时使用DB1、DB2两个数据库
• 调用getMasterData()从DB1拉取主数据
• 调用saveMasterData()将数据插入DB2
• 通过connectDb()方法切换数据库连接

相关代码文件 #

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

解决方法 #

1. 避免重复初始化Capsule核心组件 #

每次调用connectDb()都重复初始化事件分发器和重启Eloquent会导致连接状态混乱，修改getCapsule()和connectDb()：

public function getCapsule()
{
    if (!$this->capsule) {
        $this->capsule = new Capsule();
        // 仅初始化一次核心组件
        $this->capsule->setEventDispatcher(new Dispatcher(new Container));
        $this->capsule->bootEloquent();
    }

    return $this->capsule;
}

private function connectDb($databaseId = 0)
{
    $capsule = $this->getCapsule();
    
    if (empty($databaseId)) {
        $targetDb = config("database.connections.mysql");
        $connectionName = "job_insert_system";
        // 避免重复添加相同连接
        if (!$capsule->getConnectionResolver()->connection($connectionName)) {
            $capsule->addConnection([
                'driver' => 'mysql',
                'host' => $targetDb["host"],
                'database' => $targetDb["database"],
                'username' => $targetDb["username"],
                'password' => $targetDb["password"],
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
            ], $connectionName);
        }
        $capsule->setAsGlobal();
        return $targetDb;
    }

    $targetDb = Database::find($databaseId);
    $connectionName = $targetDb->service;
    if (!$capsule->getConnectionResolver()->connection($connectionName)) {
        $capsule->addConnection([
            'driver' => 'mysql',
            'host' => $targetDb->host,
            'database' => $targetDb->database,
            'username' => $targetDb->user_name,
            'password' => mb_strstr((base64_decode($targetDb->password)), "*"),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ], $connectionName);
    }
    $capsule->setAsGlobal();
    return $targetDb;
}

2. 明确指定DB2连接执行插入 #

saveMasterData()中必须明确指定使用DB2对应的连接，避免模型使用错误连接：

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

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

3. 修复DB1取数的连接使用 #

getMasterData()中应使用动态添加的DB1连接名，而非固定的mysql：

private function getMasterData($masterTable)
{
    $targetDb = $this->connectDb($masterTable["databaseId"]);
    $connectionName = $targetDb->service;
    $databaseName = $targetDb->database;
    $tableName = $masterTable['name'];

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

    return $masterdataArray;
}

内容的提问来源于stack exchange，提问作者Hayato Kuroda