DB Sticky Connection is not purged then looping queue jobs

[AidasK]

Laravel Version

11.x

PHP Version

8.4.x

Database Driver & Version

Mysql, 8.4

Description

Then running artisan queue:work, and using database sticky option enabled, once database switches to the write connection it sticks for all future jobs. This is inefficient and creates additional load for the master database.

class DbPurgeServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        Queue::looping(fn() => DB::purge());
    }
}

Have not tested, but maybe same issue occurs with Cronjobs and Laravel Octane if new process was not created?

Steps To Reproduce

Reproducing is quite simple, you only need to create two jobs, one for writing and one for reading and dumping the log of the connection used.

[crynobone]

This seems to be the expected behaviour: 94dbf76

Moving to Ideas

[AidasK]

I must disagree. Within the scope of a single job, the database connection should not be inherited from the job executed before. One job should not impact other jobs.

[macropay-solutions]

@AidasK then run the worker in non daemon mode with php artisan queue:work --once

We did not had this issue because we used --once, but still, interesting. This will impact also OCTANE.

https://laravel.com/docs/11.x/database#the-sticky-option

The sticky Option
The sticky option is an optional value that can be used to allow the immediate reading of records that have been written to the database during the current request cycle. If the sticky option is enabled and a "write" operation has been performed against the database during the current request cycle, any further "read" operations will use the "write" connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application.

Bear in mind that if you put it to false you will probably not get the same data that you updated in db on an immediate read. Example create hydrated model which writes the model and then reads it to get the defaults from db.

[crynobone]

#37646

the reason has been detailed out here and workaround are available.