Multiple MySQL connections. Error in initializing tenant, prompting that the tenant database does not exist

[popfaker]

Bug description

After creating a tenant, copy the data of one tenant to another tenant, and prompt that the tenant database does not exist.

My function is to copy data from another tenant to the current tenant after the tenant is created successfully. When there is only one MySQL connection, the function is OK. However, when multiple MySQL connections are made, the program prompts that the tenant database does not exist.

tenancy()->initialize($anotherTenant); ERROR Log display:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'saas_1'
Database saas_1 does not exist

Steps to reproduce

Tenant::create(['tenancy_db_connection'=>'tenant1',...]);

ERROR

Database saas_1 does not exist

In database.php

'central' => [
            'read' =>['host'=>192.168.0.10],
            'write' =>['host'=>192.168.0.10],
            'sticky' => true,
            'driver' => 'mysql',
            'port' => 3306,
            'database' => 'central',
            'username' => 'homestead',
            'password' => 'secret',
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
'tenant' => [
            'read' =>['host'=>192.168.0.10],
            'write' =>['host'=>192.168.0.10],
            'sticky' => true,
            'driver' => 'mysql',
            'port' => 3306,
            'database' => '',
            'username' => 'homestead',
            'password' => 'secret',
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
'tenant1' => [
            'read' =>['host'=>192.168.0.30],
            'write' =>['host'=>192.168.0.30],
            'sticky' => true,
            'driver' => 'mysql',
            'port' => 3306,
            'database' => '',
            'username' => 'homestead',
            'password' => 'secret',
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

In the tenants table, the table data is as follows:：

Tenant 1's data:{"tenancy_db_name": "saas_1","tenancy_db_connection": "tenant"} Tenant 2's data:{"tenancy_db_name": "saas_2","tenancy_db_connection": "tenant1"}

In App\Providers\TenancyServiceProvider

public function events(){
	return [
		...
		Events\TenantCreated::class => [
                JobPipeline::make([
                    Jobs\CreateDatabase::class,
                    Jobs\MigrateDatabase::class,
                    Jobs\SeedDatabase::class,
                    Tenant\Jobs\CreateSmsGateway::class,// init sms gateway
                ])->send(function (Events\TenantCreated $event) {
                    return $event->tenant;
                })->shouldBeQueued(false),
         ],
         ...
	];
}

In Tenant\Jobs\CreateSmsGateway

public function handle(){
    {
        //tenancy()->end(); // This is invalid

        /** @var Tenant $anotherTenant */
        $anotherTenant = tenancy()->query()->whereKeyNot($this->tenant->getKey())->oldest()->first();
        if (!$anotherTenant) return;

        
        // init tenant ERROR,database saas_1 does not exists
        tenancy()->initialize($anotherTenant);
        
		....
        
    
  }

Expected behavior

Multiple MySQL database connections, and multiple tenants can be set.

And the tenant switch is correct

Laravel version

6.20.44

stancl/tenancy version

3.4.6

[stancl]

Is this related to databases you're creating using the package? Could this be related to #515?

[popfaker]

Is this related to databases you're creating using the package? Could this be related to #515?

Hi. In Stancl\Tenancy\Database\DatabaseManager

/**
     * Create the tenant database connection.
     */
    public function createTenantConnection(TenantWithDatabase $tenant)
    {
        $this->app['config']['database.connections.tenant'] = $tenant->database()->connection();
    }

In Stancl\Tenancy\Bootstrappers\DatabaseTenancyBootstrapper

public function bootstrap(Tenant $tenant)
    {
        /** @var TenantWithDatabase $tenant */

        // Better debugging, but breaks cached lookup in prod
        if (app()->environment('local')) {
            $database = $tenant->database()->getName();
            if (! $tenant->database()->manager()->databaseExists($database)) {
                throw new TenantDatabaseDoesNotExistException($database);
            }
        }

        $this->database->connectToTenant($tenant);
    }

It is wrong to set only one tenancy_db_connection field.

Multi-connections,When the tenant connection is modified, reinitializing other tenants will fail.

Solution:

• Write the host, username and password of the database into the data field

• In a non local environment

Tenant 1's data:{"tenancy_db_name": "saas_1","tenancy_db_host": "192.168.0.10","tenancy_db_username":"homestead","tenancy_db_password":"secret"}

Tenant 2's data:{"tenancy_db_name": "saas_2","tenancy_db_host": "192.168.0.30","tenancy_db_username":"homestead","tenancy_db_password":"secret"}

Although it solved my problem. But I still have questions

• Under the multi database connection, it should be possible to save only tenancy_db_connection fields in data

• Why do you want to check whether the database exists,only in the local environment? This will cause errors in switching tenant connections

Thank you very much:)

[stancl]

Please answer the questions I asked in the comment above

[popfaker]

Is this related to databases you're creating using the package? Could this be related to #515?

Yes, it solved my problem. Thank you

[stancl]

I mean the linked issue can't solve your problem here 😄 but if your issue is solved I'm closing this.