Lumen调用MySQL存储过程报Packets out of order错误如何解决？

环境信息 #

• Lumen v7.0
• MySQL v8.0.20
• Ubuntu 18.04.4 LTS

错误现象 #

调用自定义存储过程时抛出错误：

Packets out of order. Expected 1 received 6. Packet size=26

注意：直接在MySQL客户端执行CALL lending_ewallets_balance_in_merchant(87,165);可正常返回预期结果。

相关配置与代码 #

数据库连接配置 #

'mysql' => [
    'read' => [
        'host' => [
            env('DB_HOST')
        ],
    ],
    'write' => [
        'host' => [
            env('DB_HOST'),
        ],
    ],
    'driver' => 'mysql',
    'port' => env('DB_PORT', 3306),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => env('DB_PREFIX', ''),
    'strict' => env('DB_STRICT_MODE', true),
    'engine' => env('DB_ENGINE', null),
],

存储过程调用代码 #

function storedProcedureBalanceInMerchant($userId, $businessId)
{
    try {
        $stmt = DB::connection('mysql')->getPdo()->prepare("CALL lending_ewallets_balance_in_merchant(?,?);");
        $stmt->execute([$userId, $businessId]);
        $pdoDataResults = [];
        do {
            $rowSet = $stmt->fetchAll(PDO::FETCH_ASSOC);
            array_push($pdoDataResults, $rowSet);
        } while ($stmt->nextRowset());

        return loanBnpl($pdoDataResults);
    } catch (Exception $exception) {
        dd($exception->getMessage());
        return ['loan' => collect([]), 'bnpl' => collect([])];
    }
}

存储过程定义 #

DELIMITER $$
CREATE DEFINER=`administrator`@`localhost` PROCEDURE `lending_ewallets_balance_in_merchant`(IN `user_id_param` BIGINT UNSIGNED, IN `business_id_param` INT UNSIGNED)
    NO SQL
BEGIN

DECLARE dossier_id INT;
DECLARE query_string VARCHAR(255) DEFAULT '';
DECLARE cursor_List_isdone BOOLEAN DEFAULT FALSE;

DECLARE user_dossiers CURSOR FOR
Select ld.id, lwp.query_string
FROM lending_users_dossiers ld 
JOIN lending_where_to_pays lwp ON ld.lending_where_to_pay_id = lwp.id
WHERE user_id = user_id_param
  AND (ld.status = 'activated' OR ld.status = 'finished');
  # 'finished' is for loans

DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_List_isdone = TRUE;

Open user_dossiers;  

loop_List: LOOP
  FETCH user_dossiers INTO dossier_id, query_string;  
    IF cursor_List_isdone THEN
      LEAVE loop_List;
    END IF;

    SET @qry = CONCAT(
                "SELECT ld.id lending_dossier_id, ld.type, SUM(let.credit) balance
                FROM lending_users_dossiers ld
                JOIN lending_ewallet_transactions let
                ON ld.id = let.lending_dossier_id
                WHERE ld.id = ", dossier_id,
                " AND ", business_id_param, " IN(", query_string, ")",
                "GROUP BY ld.id, ld.type");

    PREPARE stmt FROM @qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
END LOOP loop_List;

Close user_dossiers;
END$$
DELIMITER ;

解决方案 #

该错误多因存储过程返回多结果集时，PDO未正确处理状态，或游标+动态SQL的组合导致数据包顺序混乱，可尝试以下方案：

方案1：在存储过程末尾添加收尾语句 #

在存储过程的END前添加SELECT 1;，确保最后返回明确的空结果集，帮助PDO正确处理结果集切换：

Close user_dossiers;
SELECT 1; -- 添加该行
END$$

方案2：优化PDO结果集处理逻辑 #

在每次获取结果集后调用closeCursor()清理游标状态，避免数据包混乱：

function storedProcedureBalanceInMerchant($userId, $businessId)
{
    try {
        $pdo = DB::connection('mysql')->getPdo();
        $stmt = $pdo->prepare("CALL lending_ewallets_balance_in_merchant(?,?);");
        $stmt->execute([$userId, $businessId]);
        $pdoDataResults = [];
        
        do {
            $rowSet = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($rowSet)) {
                $pdoDataResults[] = $rowSet;
            }
            $stmt->closeCursor(); // 清理游标状态
        } while ($stmt->nextRowset());

        return loanBnpl($pdoDataResults);
    } catch (Exception $exception) {
        dd($exception->getMessage());
        return ['loan' => collect([]), 'bnpl' => collect([])];
    }
}

方案3：重写存储过程，避免游标+动态SQL组合 #

用单条SQL替代游标和动态SQL，从根源上解决多结果集问题：

DELIMITER $$
CREATE DEFINER=`administrator`@`localhost` PROCEDURE `lending_ewallets_balance_in_merchant`(IN `user_id_param` BIGINT UNSIGNED, IN `business_id_param` INT UNSIGNED)
    NO SQL
BEGIN
    SELECT 
        ld.id lending_dossier_id, 
        ld.type, 
        SUM(let.credit) balance
    FROM lending_users_dossiers ld 
    JOIN lending_where_to_pays lwp ON ld.lending_where_to_pay_id = lwp.id
    JOIN lending_ewallet_transactions let ON ld.id = let.lending_dossier_id
    WHERE ld.user_id = user_id_param
      AND (ld.status = 'activated' OR ld.status = 'finished')
      AND FIND_IN_SET(business_id_param, lwp.query_string)
    GROUP BY ld.id, ld.type;
END$$
DELIMITER ;

说明：用FIND_IN_SET替代动态IN条件，前提是query_string为逗号分隔的数字字符串。

方案4：更新MySQL驱动版本 #

确保PHP的mysqlnd驱动为最新版本，旧版本可能存在与MySQL 8.0的兼容问题，执行以下命令更新：

sudo apt-get update && sudo apt-get install php-mysqlnd

内容的提问来源于stack exchange，提问作者Martin AJ