Jobs\MigrateDatabase fails using TenantDatabaseManagers\PostgreSQLSchemaManager::class

[scramatte]

Bug description

Hello,

Jobs\MigrateDatabase fails using driver TenantDatabaseManagers\PostgreSQLSchemaManager::class

7 ERROR: no schema has been selected to create in at character 14 at /opt/eom/vendor/laravel/framework/src/Illuminate/Database/Connection.php:545)

If I migrate tenants table with artisan, it works. It only fails when on Events\TenantCreated jobs

I've tested tooo with the other pgsql driver TenantDatabaseManagers\PostgreSQLDatabaseManager::class And I'm unable to create database. Note that I'm using postgres user so, it should let me create anything!

a transaction block (SQL: CREATE DATABASE "tenant_e35341a4-4e84-4618-87d4-590726edb9b3" WITH TEMPLATE=template0)

[previous exception] [object] (PDOException(code: 25001): SQLSTATE[25001]: 
Active sql transaction: 7 ERROR:  CREATE DATABASE cannot run inside 
a transaction block at /opt/eom/vendor/laravel/framework/src/Illuminate/Database/Connection.php:545)
[stacktrace]

So, Ive come back to TenantDatabaseManagers\PostgreSQLSchemaManager driver as at least it let me create the database.

I've take a look to Jobs\MigrateDatabase code and I've seen. that exec Artisan command. So I've tried to exec the same command on shell and it works well.

php artisan tenants:migrate --tenants=f74739bd-51c6-44eb-a2c9-da474b7c5147

I've dumped baseConfig from TenantDatabaseManagers\PostgreSQLSchemaManager driver when Jobs\MigrateDatabase is running. And It looks fine as search_path is set to the correct schema.

[2023-01-09 11:08:51] local.INFO: Array
(
    [driver] => pgsql
    [url] => 
    [host] => 127.0.0.1
    [port] => 5432
    [database] => eom
    [username] => postgres
    [password] => **********
    [charset] => utf8
    [prefix] => 
    [prefix_indexes] => 1
    [search_path] => tenant_1b38fc06-86eb-45fd-afea-14cc5558cbca
    [sslmode] => prefer
)

I don't think that is permissions issue as I'm using postgres user and moreover it works from console. Any ideas? I can't progress on my project due to this issue.

Thank you for the help

Regards

Steps to reproduce

Setup laravel tenancy using boilerplate and set tenant database driver to TenantDatabaseManagers\PostgreSQLSchemaManager::class

You must enable Jobs\MigrateDatabase into app/Providers/TenancyServiceProvider.php too

Expected behavior

Job should runs without error and migrate all tenants tables

Laravel version

9.19

stancl/tenancy version

3.6

[stancl]

@abrardev99 Try to reproduce this tomorrow (using boilerplate code as mentioned in the reproduction steps)

edit: probably can be postponed, likely isn't a bug in the package

[scramatte]

I've just do it, with just boilerplate and occur the same issue. It looks that the problem occurs when you create tenant using Nova4.

[stancl]

Ah I see the issue in the original comment now. I don't think the schema manager has anything to do with this. It's just Nova running the creation logic inside a DB transaction which doesn't let you create databases in Laravel 8/9+. I'd search the issue tracker & our Discord servers for this, it's been discussed in the past.

[scramatte]

Nova doesn't let to create database but using schema it neither works. Moreover, queued job, ran out of Nova, so it should be ok isn't it?

[scramatte]

I've just try back with schema and queue job and nothing. Same error ...

[scramatte]

Can you send me references , I've searched myself on issue tracker and discord server but I haven't been able to found anything

[stancl]

720

[abordage]

Working solution based on https://github.com/laravel/nova-issues/issues/180#issuecomment-433493057

class CreateTenant extends Action implements ShouldQueue
{
    use InteractsWithQueue;
    use Queueable;

    public $withoutActionEvents = true;

    public $confirmButtonText = 'Create Tenant';

    /**
     * @throws Throwable
     */
    public function handle(ActionFields $fields): void
    {
        DB::commit();

        $tenant = Tenant::create(['name' => $fields['name']]);
        $tenant->domains()->create(['domain' => $fields['domain']]);
    }

    public function fields(NovaRequest $request): array
    {
        return [
            Text::make('Project Name', 'name')
                ->rules(['required', 'string', 'max:30', 'unique:tenants,name'])
            ,
            Text::make('First Domain', 'domain')
                ->rules(['required', 'string', 'unique:domains,domain'])
            ,
        ];
    }
}