Laravel 9使用Job调用存储过程无数据库操作问题排查

我有一个执行特定任务的存储过程SubjectAllocation，控制器直接调用时正常，但改用Laravel Job异步执行后，Job能正常运行且成功发送邮件，但MySQL数据库无任何操作，手动单独执行该存储过程则可正常生效。相关代码如下：

控制器代码 #

try {
    $emailAddress = auth()->user()->email ?? NULL;
    SubjectAllocationAllJob::dispatch(
        $request->course,
        $request->regulation,
        $request->batch,
        $request->semester,
        $emailAddress
    );
    Alert::success('Exam Application Started Successfully. You will recieve email shortly');

    return redirect()->route('admin.exam.preprocess.subjectmapping.all');
} catch (Exception $e) {
    Alert::error('Error Mapping Exam Application');

    return redirect()->back()->withInput();
}

SubjectAllocationAllJob代码 #

<?php

namespace App\Jobs\ExamPreProcess;

use Illuminate\Bus\Queueable;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use Illuminate\Queue\SerializesModels;
use Illuminate\Queue\InteractsWithQueue;
use App\Mail\SubjectMappingAllSuccessMail;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Contracts\Queue\ShouldBeUnique;

class SubjectAllocationAllJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $regulation;
    public $course;
    public $batch;
    public $semester;
    public $email;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($regulation, $course, $batch, $semester, $email)
    {
        $this->regulation = $regulation;
        $this->course = $course;
        $this->batch = $batch;
        $this->semester = $semester;
        $this->email = $email;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        DB::select(
            'CALL SubjectAllocation(?,?,?,?,?)',
            array(
                $this->course,
                $this->regulation,
                $this->batch,
                $this->semester,
                session('ExamID')
            )
        );

        if (!empty($this->email)) {
            Mail::to($this->email)->send(new SubjectMappingAllSuccessMail());
        }
    }
}

存储过程代码 #

DELIMITER $$
DROP PROCEDURE IF EXISTS SubjectAllocation $$
CREATE PROCEDURE `SubjectAllocation`(
    IN coursesin VARCHAR(25),
    IN regulationsin VARCHAR(25),
    IN batchin VARCHAR(25),
    IN semesterin VARCHAR(25),
    IN examyearin VARCHAR(2)
)
BEGIN
DROP
    TEMPORARY TABLE IF EXISTS `subjectassign`; CREATE TEMPORARY TABLE `subjectassign` (
        `subject_id` INT, `student_id` INT,
        `subjecttype_id` INT
    ); INSERT INTO `subjectassign`
SELECT
    `subjects`.`id`,
    `students`.`id`,
    `subjects`.`subjecttype_id`
FROM
    `subjects`,
    `students`
WHERE
    `subjects`.`programme_id` = `students`.`programme_id`
    AND `subjects`.`regulation_id` = `students`.`regulation_id`
    AND `subjects`.`course_id` = `students`.`course_id`
    AND `subjects`.`subjectallocationtype_id` IS NULL
    AND `subjects`.`semester` = semesterin
    AND `students`.`regulation_id` = regulationsin
    AND `students`.`course_id` = coursesin
    AND `students`.`batch_id` = batchin; DELETE `subjectassign`
FROM
    `subjectassign`
    RIGHT JOIN `exam_applications` ON `subjectassign`.`student_id` = `exam_applications`.`student_id`
    AND `subjectassign`.`subject_id` = `exam_applications`.`subject_id`; INSERT INTO `exam_applications`(
        `student_id`, `subject_id`, `exammonth_id`,
        `examtype_id`, `exampapertype_id`,
        `verification`, `display`
    )
SELECT
    DISTINCT `student_id`,
    `subject_id`,
    examyearin,
    '1',
    '1',
    'N',
    'Y'
FROM
    `subjectassign`;
DROP
    TEMPORARY TABLE IF EXISTS `tmp_internalmark`; CREATE TEMPORARY TABLE `tmp_internalmark` (
        `subject_id` INT, `student_id` INT
    ); INSERT INTO `tmp_internalmark`
SELECT
    `subjects`.`id`,
    `students`.`id`
FROM
    `subjects`,
    `students`
WHERE
    `subjects`.`programme_id` = `students`.`programme_id`
    AND `subjects`.`regulation_id` = `students`.`regulation_id`
    AND `subjects`.`course_id` = `students`.`course_id`
    AND `subjects`.`subjectallocationtype_id` IS NULL
    AND `subjects`.`semester` = semesterin
    AND `students`.`regulation_id` = regulationsin
    AND `students`.`course_id` = coursesin
    AND `students`.`batch_id` = batchin; DELETE `tmp_internalmark`
FROM
    `tmp_internalmark`
    RIGHT JOIN `internal_marks` ON `tmp_internalmark`.`student_id` = `internal_marks`.`student_id`
    AND `tmp_internalmark`.`subject_id` = `internal_marks`.`subject_id`; 
    INSERT INTO `internal_marks`(
        `student_id`, `subject_id`, `exammonth_id`,
        `batch_id`
    )
SELECT
    DISTINCT `student_id`,
    `subject_id`,
    examyearin,
    batchin
FROM
    `tmp_internalmark`; 
END $$
DELIMITER ;

问题排查与解决 #

1. 参数传递顺序错误 #

控制器调用SubjectAllocationAllJob::dispatch()时，参数顺序为course, regulation, batch, semester, email，但Job构造函数的参数顺序是regulation, course, batch, semester, email，两者顺序错位，导致存储过程接收到的coursesin和regulationsin参数值颠倒，查询条件不匹配，因此没有数据被处理。

修复方法：
调整控制器中的dispatch参数顺序，与Job构造函数一致：

SubjectAllocationAllJob::dispatch(
    $request->regulation,
    $request->course,
    $request->batch,
    $request->semester,
    $emailAddress
);

2. Session无法在异步Job中获取 #

异步Job运行在独立进程中，不属于请求上下文，因此session('ExamID')无法获取有效值，导致存储过程的examyearin参数为空，插入操作无法匹配正确条件。

修复方法：
将ExamID作为参数传递给Job，而非依赖Session：

• 修改Job的构造函数，新增$examId属性与参数：

public $regulation;
public $course;
public $batch;
public $semester;
public $email;
public $examId;

public function __construct($regulation, $course, $batch, $semester, $email, $examId)
{
    $this->regulation = $regulation;
    $this->course = $course;
    $this->batch = $batch;
    $this->semester = $semester;
    $this->email = $email;
    $this->examId = $examId;
}

• 控制器dispatch时传入ExamID：

SubjectAllocationAllJob::dispatch(
    $request->regulation,
    $request->course,
    $request->batch,
    $request->semester,
    $emailAddress,
    session('ExamID')
);

• 修改Job的handle方法，使用$this->examId替代session调用：

DB::select(
    'CALL SubjectAllocation(?,?,?,?,?)',
    array(
        $this->course,
        $this->regulation,
        $this->batch,
        $this->semester,
        $this->examId
    )
);

3. 查看队列日志排查隐藏错误 #

即使邮件发送成功，存储过程调用仍可能存在参数错误或其他异常，可查看Laravel队列日志（默认路径storage/logs/queue.log）获取具体报错信息，进一步定位问题。

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