Tenant connections not getting purged while tenancy:migrate is running

[janiskelemen]

We are running into a scaling issue using tenancy:migrate at the moment. The problem is that the tenant connections are not purged correctly. We have now 400+ tenants and our migration process fails at some point due to "too many open mysql connections". This can be reproduced locally. When you start your tenancy:migrate command the mysql connections are going up until all migrations are done and then they go back down.

Information

• hyn/multi-tenant version: 5.5
• laravel version: 6
• database driver and version: mysql 8 & mysql 5.6
• webserver software and version: nginx
• php version: 7.4

[fletch3555]

@ArlonAntonius / @bkintanar, we should definitely look at this. This is the code that does the loop over the tenants. I don't immediately see the issue.

[janiskelemen]

Currently, my workaround is to kill the sleeping mysql connection after the purge command. This might not be ideal since I cannot be sure that the sleeping connection belongs to the migration operation or not but I can at least run my migrations again for now.

...
if ($websites->count() > 1) {
    $this->connection->purge();
    $kill = optional(\Illuminate\Support\Facades\DB::connection('system')->select("select concat('KILL ',id,';') as mysqlcommand from information_schema.processlist where db='" . $website->uuid . "' AND Command='Sleep' ORDER BY id DESC LIMIT 1;"));
    if ($kill && $kill[0] instanceof \stdClass) {
        \Illuminate\Support\Facades\DB::connection('system')->select($kill[0]->{'mysqlcommand'});
    }
 }
...

[bkintanar]

@janiskelemen this would solve the migration issue, but not the seeder issue. I tried decreasing the wait_timeout to 1, and ran database seeders, and only one database seeder was run and it threw a too many connection exception.

I don't see an immediate solution at the moment.

[janiskelemen]

@bkintanar I'm running into an issue anyways that our AWS mysql server does not let me kill the mysql connections due to owner permission issues... so this workaround does not work in production.

[janiskelemen]

I found a workaround now. Just create a new command and run the tenancy:migrate command in chunks of 10 websites per command using shell_exec in order to fire the command as a new process which is needed so the connections are killed entirely when the chunk migration finishes. This will open max 10 connections.

$websites = Website::all()->pluck('id');

$chunks = $websites->chunk(10);

$chunks->each(function ($ids) {
$this->info('Migrate chunk set of tenants...');
$this->info($ids->map(function ($value) { return '--website_id=' . $value; })->implode(' '));

$this->info(shell_exec('php artisan tenancy:migrate --force ' . $ids->map(function ($value) { return '--website_id=' . $value; })->implode(' ')));