MySQL multi-tenanted queues share one physical queue table: TransportSchemaName is a database, so there are no per-tenant queue tables

[jeremydmiller]

Found while fixing #3815. Distinct from that issue and larger: #3815 is an off-by-one fan-out, this is that MySQL has no per-tenant queue tables at all.

The shape

In MySQL a schema is a database. MySqlTransport.TransportSchemaName defaults to "wolverine_queues" and the queue tables are qualified with it unconditionally:

internal class QueueTable : Table
{
    public QueueTable(MySqlTransport parent, string tableName) : base(
        new DbObjectName(parent.TransportSchemaName, tableName))

So every tenant data source, whatever database its connection string names, resolves wolverine_queues.wolverine_queue_<name> to the same physical table on that server. The per-tenant fan-out in MultiTenantedQueueSender / MultiTenantedQueueListener / forEveryDatabase picks a different connection per tenant, but they all land on one table.

PostgreSQL is unaffected: its TransportSchemaName is a schema nested inside each tenant database, so each tenant genuinely has its own copy.

Observed

A host with RegisterStaticTenants for three tenant databases plus the main one, publishing to a MySQL queue.

MySQL — four databases, one queue table:

Database                 |  TABLE_SCHEMA      TABLE_NAME
-------------------------|  ----------------------------------------------
wolverine       (main)   |  wolverine_queues  wolverine_queue_countone
tenant_db1               |  wolverine_queues  wolverine_queue_countone_scheduled
tenant_db2               |
tenant_db3               |  (that is the complete list)

PostgreSQL, same configuration — one queue table per database:

postgres (main)  ->  queue_counts_tenanted.wolverine_queue_countone
db1              ->  queue_counts_tenanted.wolverine_queue_countone
db2              ->  queue_counts_tenanted.wolverine_queue_countone
db3              ->  queue_counts_tenanted.wolverine_queue_countone

With 10 rows physically present in the single MySQL table, CountAsync() returned 40 — the same table counted once per tenant data source.

Why it matters

• No tenant isolation for MySQL queues. Every tenant's sends land in one table, and a listener on any tenant connection can pick up another tenant's messages. Depending on how the listener filters, this is either cross-tenant delivery or a shared work queue masquerading as a partitioned one.
• CountAsync()/ScheduledCountAsync() multiply by the tenant count, so GetAttributesAsync() reports a queue depth that is N times the truth. OracleQueue.CountAsync double-counts on a multi-tenanted store: forEveryDatabase visits Main twice #3815 reduces the multiplier from N+1 to N; it does not fix this.
• PurgeAsync/SetupAsync/TeardownAsync/CheckAsync all repeat their work N times against one table.

Caveat on scope

This only bites when the tenant databases live on the same MySQL server — which is what RegisterStaticTenants with connection strings differing only by Database= produces, and the common multi-tenancy-by-database setup. Tenants on separate servers each get their own wolverine_queues database and behave correctly.

Not verified

Oracle uses the same fixed-schema pattern (TransportSchemaName = "WOLVERINE_QUEUES", and in Oracle a schema is a user), so it may have the same exposure depending on whether tenants land on separate instances/PDBs or separate users on one instance. There is no Oracle multi-tenancy test fixture to settle it with.

Likely directions

1. Derive the queue table's schema from the tenant's own database rather than a single transport-wide name, so MySQL gets per-tenant tables the way PostgreSQL does.
2. Or, if a shared queue table is the intended MySQL model, stop fanning forEveryDatabase out over tenants for MySQL entirely (visit the transport's one database once) and document that MySQL queues are not tenant-isolated.

(1) matches every other transport's semantics; (2) is smaller but changes what multi-tenanted MySQL queues promise.

[jeremydmiller]

Escalating this: while scoping the fix I found the same root cause reaches the message store, not just the queue transport. MySQL multi-tenancy currently provides no isolation at all.

Confirmed in code

MySqlTenantedMessageStore builds every tenant store with the globally configured schema name:

private MySqlMessageStore buildTenantStoreForConnectionString(string connectionString)
{
    var settings = new DatabaseSettings
    {
        ...
        ConnectionString = connectionString,
        Role = MessageStoreRole.Tenant,
        SchemaName = _persistence.EnvelopeStorageSchemaName   // <-- same for every tenant
    };

In PostgreSQL that is harmless: the schema is a namespace inside whatever database the tenant's connection string names, so isolation comes from the connection string. In MySQL the schema is the database, so qualifying every table with one fixed name discards the tenant's Database= entirely and every tenant reads and writes the same physical tables.

Confirmed by measurement

Running the existing, supported MySqlTests.MultiTenancy.static_multi_tenancy suite (3/3 green) and then looking at where the tables actually landed:

MySQL                                  PostgreSQL (same style of config)
--------------------------------       --------------------------------
static_multi_tenancy   8 tables        postgres (main)   1696 tables
tenant_db1             0 tables        db1                 16 tables
tenant_db2             0 tables        db2                 16 tables
tenant_db3             0 tables        db3                 13 tables

Every wolverine table — wolverine_incoming_envelopes, wolverine_outgoing_envelopes, wolverine_nodes, the saga tables — lives in one shared database. The tenant databases are empty. The suite passes because it only asserts that the tenancy source resolves the right connection strings (Describe().DatabaseName), never that anything is stored separately.

So the original report here — one shared wolverine_queues queue table — is one symptom of a broader defect, not the whole of it.

What this means for the fix

The queue-table-only fixes I sketched above are no longer sufficient on their own. The real question is how MySQL should map Wolverine's two-level schema.table model onto MySQL's one-level database.table:

1. Tenant database is the schema. For a tenant store, derive SchemaName from the connection string's Database= (and the transport's queue schema likewise), so each tenant gets its own copy of every table. Matches PostgreSQL/SQL Server semantics. Changes where existing multi-tenanted MySQL deployments' tables live, so it needs a migration note.
2. Reject the combination. If a single configured schema name is fundamental to the MySQL provider, then multi-tenancy-by-database is not actually supported on MySQL and should throw at configuration time rather than silently sharing storage.

(1) preserves the promise every other provider makes; (2) is honest and much smaller. Either way the current behaviour — green tests, silent sharing — is the worst option.