Compatibility of Job Batch with a proxy sql

[DBA-EURECIA]

Laravel Version

10.32.1

PHP Version

8.1.7

Database Driver & Version

SkySQL : mariadb 10.5.21-15 + maxscale 23.02.2 + maxscale readwritesplit V1.1.0

Description

When using maxscale (proxy sql to split query on a master database and a slave database), with Batch Jobs, Laravel throw an exception on PendingBatch->dispatch()

2023-11-15 17:12:24] local.ERROR: Call to a member function add() on null,"exception":"[object] (Error(code: 0): Call to a member function add() on null at /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Bus/PendingBatch.php:259)
[stacktrace]
#0 /var/www/maxscale-test/app/Http/Controllers/Api/Controller.php(392): Illuminate\\Bus\\PendingBatch->dispatch()
#1 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): App\\Http\\Controllers\\Api\\CampaignController->post()
#2 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(43): Illuminate\\Routing\\Controller->callAction()
#3 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Routing/Route.php(260): Illuminate\\Routing\\ControllerDispatcher->dispatch()
#4 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\\Routing\\Route->runController()
#5 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Routing/Router.php(727): Illuminate\\Routing\\Route->run()
#6 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}()
#7 /var/www/maxscale-test/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authorize.php(45): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()

Error come from this vendor/laravel/framework/src/Illuminate/Bus/PendingBatch.php method

     /**
     * Dispatch the batch.
     *
     * @return \Illuminate\Bus\Batch
     *
     * @throws \Throwable
     */
    public function dispatch()
    {
        $repository = $this->container->make(BatchRepository::class);

        try {
                $batch = $repository->store($this);

                $batch = $batch->add($this->jobs);
        } catch (Throwable $e) {
            if (isset($batch)) {
                $repository->delete($batch->id);
            }

            throw $e;
        }

        $this->container->make(EventDispatcher::class)->dispatch(
            new BatchDispatched($batch)
        );

        return $batch;
    }

The line $batch = $repository->store($this); call DatabaseBatchRepository->store(PendingBatch $batch) :

     /**
     * Store a new pending batch.
     *
     * @param  \Illuminate\Bus\PendingBatch  $batch
     * @return \Illuminate\Bus\Batch
     */
    public function store(PendingBatch $batch)
    {
        $id = (string) Str::orderedUuid();

        $this->connection->table($this->table)->insert([
            'id' => $id,
            'name' => $batch->name,
            'total_jobs' => 0,
            'pending_jobs' => 0,
            'failed_jobs' => 0,
            'failed_job_ids' => '[]',
            'options' => $this->serialize($batch->options),
            'created_at' => time(),
            'cancelled_at' => null,
            'finished_at' => null,
        ]);

        return $this->find($id);
    }`

This method do an INSERT and follow with a SEELECT with line return $this->find($id);
With a split read/write database proxy sql, the insert is send to master and immediately we are doing a select on the inserted data. Data replication is not done and the $batch is null throwing an exception on the next line of dispatch method $batch = $batch->add($this->jobs);

Solution
Adding a transaction force all requests to be played on database master

     /**
     * Dispatch the batch.
     *
     * @return \Illuminate\Bus\Batch
     *
     * @throws \Throwable
     */
    public function dispatch()
    {
        $repository = $this->container->make(BatchRepository::class);

        try {
            DB::transaction(function () use ($repository, &$batch) {
                $batch = $repository->store($this);

                $batch = $batch->add($this->jobs);
            }, 3);
        } catch (Throwable $e) {
            if (isset($batch)) {
                $repository->delete($batch->id);
            }

            throw $e;
        }

        $this->container->make(EventDispatcher::class)->dispatch(
            new BatchDispatched($batch)
        );

        return $batch;
    }

Steps To Reproduce

• Install laravel
• Install MariaDB
• Install Mariadb Maxscale
• Config Maxscale to use split read/write
• Add a controller using a Job Batch
• Call controller end-point => expect an error

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[crynobone]

This method do an INSERT and follow with a SEELECT with line return $this->find($id);
With a split read/write database proxy sql, the insert is send to master and immediately we are doing a select on the inserted data. Data replication is not done and the $batch is null throwing an exception on the next line of dispatch method $batch = $batch->add($this->jobs);

$batch->find() is already enforcing useWritePdo().

framework/src/Illuminate/Bus/DatabaseBatchRepository.php

Lines 71 to 87 in c3844dc

	/**
	* Retrieve information about an existing batch.
	*
	* @param string $batchId
	* @return \Illuminate\Bus\Batch|null
	*/
	public function find(string $batchId)
	{
	$batch = $this->connection->table($this->table)
	->useWritePdo()
	->where('id', $batchId)
	->first();
	if ($batch) {
	return $this->toBatch($batch);
	}
	}

[driesvints]

Seems Taylor doesn't wants to change anything here right now so closing this one sorry.

[DBA-EURECIA]

A proxy sql to split query is an external stack. For dev ops, i don't want Laravel to manage different database connection.
All queries are sent to the master database, it's the proxy sql who manage where to redirect them.

Without transaction here or a change to not insert+select immediately, Laravel will be incompatible with this type or product.
Here the doc of Maxscale on the subject for routing queries : https://mariadb.com/docs/xpand/architecture/components/maxscale/routers/readwritesplit/route-statements-maxscale-read-write-split-router/