tenants:run with jobs combined with queue:work keeps multiple database connections open

[jacksnchz]

tenants:run with jobs combined with queue:work keeps multiple database connections open

[stancl]

Then my guess is that it's this:

It might be that in the example above, the connection is removed from Laravel's DatabaseManager but is actually still kept open on MySQL.

We'll look into it 👍🏻 @lukinovec could you try to reproduce this in a testing app?

[stancl]

Currently problem is solved using queue:listen instead but the performance is better with queue:work command

What's the difference between those two in how the DB connections work?

The problem sounds like the DB connections don't get cleaned up, but $tenant->run() should be properly cleaning up the old connection.

[jacksnchz]

Currently problem is solved using queue:listen instead but the performance is better with queue:work command

What's the difference between those two in how the DB connections work?

The problem sounds like the DB connections don't get cleaned up, but $tenant->run() should be properly cleaning up the old connection.

Hi,

Main difference between queue:listen and queue:work is that first one the framework is booted each job (closing connection also) while queue:work framework is booted only one time, so it is much more efficient.

I believe that connection need to be closed by tenant run or manually but I am stuck.

[stancl]

> isset(app('db')->getConnections()['tenant']);
= false

> $tenant->run(fn () => app('db')->getConnections()['tenant']);
= Illuminate\Database\MySqlConnection {#3828} // tenant connection exists inside the run() Closure

> isset(app('db')->getConnections()['tenant']);
= false // cleaned up afterwards

Could it be that you're using tenants:run instead of $tenant->run()? Could you show the exact code?

It might be that in the example above, the connection is removed from Laravel's DatabaseManager but is actually still kept open on MySQL.

[jacksnchz]

Hi, I am using tenants:run and database connection is open in MySQL (listing from SHOW FULL PROCESSLIST)

Here you have an example:

Command class:

class MyCommand extends Command
{
    /**
     * {@inheritdoc}
     * @var string
     */
    protected $signature = 'myCompany:my-command';

    /**
     * Execute the console command.
     *
     * @return void
     */
    public function handle(): void
    {
        MyJob::dispatch();
    }
}

Job class:

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

    public function handle()
    {
        logger()->info("running");
    }
}

Steps to reproduce:

1. php artisan queue:work --tries=3 --sleep=3 --daemon
2. tenants:run myCompany:my-command
3. Every time I run tenants:run, a new connection (per tenant) is open keeping previous one open forever

[stancl]

What if you did:

Tenant::all()->runForEach(function () {
    MyJob::dispatch();
});

and called myCompany:my-command without tenants:run.

Does that also reproduce the issue?

[jacksnchz]

Hi again,

Yes, following your advice produce same result

[stancl]

FWIW regarding the solution, I'm not sure if this will be feasible:

Keep connections open by tenant and use it instead of create new one.

We may have to close the connections completely and then reopen them as needed.

[jacksnchz]

Yes maybe keep connections open is not feasible, close connects will be a good choise.

[chinmaypurav]

@jacksnchz Can you try usingTenant Aware Commands?

I am not entirely sure it will work, but I was facing similar problem on my staging (PHP 8).

And this helped. further testing pending

[jacksnchz]

Hi @chinmaypurav,

Thank you for your help but the behaviour using TenantAwareCommand is worst than original idea, more connections are kept open.

My temporary solution was replace queue:work by queue:listen command (loosing performance but it does not overloads the database connections).

[stancl]

@jacksnchz This PR should fix the issue #1128

Could you try testing the change locally to see if it resolves the issue for you?

Essentially just open vendor/stancl/tenancy/src/Bootstrappers/QueueTenancyBootstrapper.php and change line ~73 like this:

- if ($runningTests) {
+ if (true) {

to test the changes from the PR in a simpler way.