Laravel 11 + Spatie多租户队列未触发问题排查求助

环境与配置信息 #

database.php 配置 #

'tenant' => [
    'driver' => 'mysql',
    'url' => env('DB_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => null,
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

'landlord' => [
    'driver' => 'mysql',
    'url' => env('DB_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => 'landlord_db',
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

multitenancy.php 配置 #

'tenant_finder' => Spatie\Multitenancy\TenantFinder\DomainTenantFinder::class,
'switch_tenant_tasks' => [
    // \Spatie\Multitenancy\Tasks\PrefixCacheTask::class,
    Spatie\Multitenancy\Tasks\SwitchTenantDatabaseTask::class,
    // \Spatie\Multitenancy\Tasks\SwitchRouteCacheTask::class,
],
'tenant_model' => Tenant::class,

'queues_are_tenant_aware_by_default' => true,

'tenant_database_connection_name' => 'tenant',

/*
 * The connection name to reach the landlord database.
 */
'landlord_database_connection_name' => 'landlord',

.env 配置 #

DB_CONNECTION=tenant
DB_HOST=mysql_db
DB_PORT=3306
DB_DATABASE=landlord_db
DB_USERNAME=root
DB_PASSWORD=root

app.php 中间件配置 #

return Application::configure(basePath: dirname(__DIR__))
->withRouting(
    web: __DIR__ . '/../routes/web.php',
    api: __DIR__ . '/../routes/api.php',
    commands: __DIR__ . '/../routes/console.php',
    health: '/up',
)
  ->withMiddleware(function (Middleware $middleware) {
    $middleware->api(append: [
          \Spatie\Multitenancy\Http\Middleware\NeedsTenant::class,
           \Spatie\Multitenancy\Http\Middleware\EnsureValidTenantSession::class,
          \Laravel\Sanctum\Http\Middleware\EnsureFrontendRequestsAreStateful::class,
        \Illuminate\Session\Middleware\StartSession::class,
    ]);
})
->withExceptions(function (Exceptions $exceptions) {

 })

问题描述 #

执行php artisan queue:work时触发报错，日志如下：

[2024-09-20 20:05:25] local.ERROR: SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected (Connection: tenant, SQL: select * from `jobs` where `queue` = high and ((`reserved_at` is null and `available_at` <= 1726862725) or (`reserved_at` <= 1726862635)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 3D000): SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected (Connection: tenant, SQL: select * from `jobs` where `queue` = high and ((`reserved_at` is null and `available_at` <= 1726862725) or (`reserved_at` <= 1726862635)) order by `id` asc limit 1 FOR UPDATE SKIP LOCKED) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)

问题分析与解决方案 #

1. 队列连接配置错误 #

当前.env中DB_CONNECTION=tenant，队列默认使用该连接，但tenant连接的database字段为null，队列worker启动时没有HTTP请求的域名上下文（无法触发DomainTenantFinder），因此无法切换到具体租户数据库，导致查询jobs表时报错。

解决方案：
将队列的数据库连接指定为landlord（推荐把jobs表放在 landlord 库，统一管理任务）：

• 修改config/queue.php中的database驱动配置：

'database' => [
    'driver' => 'database',
    'table' => 'jobs',
    'queue' => 'default',
    'retry_after' => 90,
    'after_commit' => false,
    'connection' => 'landlord', // 指定使用 landlord 连接
],

• 确保.env中QUEUE_CONNECTION=database（默认即为database，若已修改需调整）。

2. 租户感知队列的正确性验证 #

由于queues_are_tenant_aware_by_default=true，任务会自动携带租户信息，但需确保：

• 任务分发时已绑定租户上下文，例如在HTTP请求中分发（此时已识别租户），或手动指定租户：

// 手动绑定租户分发任务
$tenant = Tenant::find($tenantId);
$tenant->dispatch(new YourJob());

3. 中间件顺序修正 #

当前API中间件顺序错误，会导致租户会话验证失效：
正确顺序：

$middleware->api(append: [
    \Laravel\Sanctum\Http\Middleware\EnsureFrontendRequestsAreStateful::class,
    \Illuminate\Session\Middleware\StartSession::class,
    \Spatie\Multitenancy\Http\Middleware\EnsureValidTenantSession::class,
    \Spatie\Multitenancy\Http\Middleware\NeedsTenant::class,
]);

顺序逻辑：

1. 先处理EnsureFrontendRequestsAreStateful保证请求状态
2. 启动会话StartSession
3. 验证会话中的租户EnsureValidTenantSession
4. 最后确保租户存在NeedsTenant

额外检查点 #

• 确认Tenant模型包含database字段，且创建租户时已正确设置数据库名称，SwitchTenantDatabaseTask能正常切换数据库。
• 若jobs表需放在租户库，需确保所有租户数据库已迁移该表，且worker执行任务时能正确切换租户（依赖任务携带的租户信息）。

内容的提问来源于stack exchange，提问作者Dothostia WHC