Custom schema for outbox-polling causes table doesn't exist error

I’m trying to use custom tables and schema for agent and outbox events in outbox-polling coordination strategy with MariaDB. I have defined 4 datasources in my application and Hibernate Search could successfully find tables for outbox-polling only when I pointed both catalog and schema to one of the datasources, where the tables were created. This resulted in Hibernate Search not being able to process events that needed accessing tables from other datasources (let’s say MySchema2 or MySchema3) - “table doesn’t exist” error occurs.

I pasted part of my configuration below. My question is, if it’s possible to work with multiple datasources and custom tables in this release? Or maybe my configuration is wrong?

myApp.datasource.MySchema1.url= myUrl = MySchema1 = MySchema1


If I understand correctly you’re using datasource-based multitenancy, and want to put your outbox-polling tables in only one of those datasources?

That’s not currently supported, no. Hibernate Search will process events for each tenant separately, and expects each tenant to have its own set of outbox-polling tables in the same datasource.

At best, you can put the outbox-polling tables in a different schema, but in the same datasource.

I suspect the “table doesn’t exist” errors you’re getting are because you didn’t create the outbox-polling tables in each datasource?

Also, for multi-tenancy, see this: Hibernate Search 6.2.1.Final: Reference Documentation

By multiple datasources I meant that my application uses multiple schemas. Should I use only one schema for outbox-polling to work correctly? It seemed like event processor operates only inside of the schema the outbox-polling tables are placed.

By multiple datasources I meant that my application uses multiple schemas

Ok. So you take advantage of Hibernate ORM’s schema-based multitenancy?

It seemed like event processor operates only inside of the schema the outbox-polling tables are placed.

There is one processor by tenant, yes. And it currently assumes that when it opens a session with a given tenant, the events it’ll retrieve will only be those of that tenant.

If, when using schema-based multitenancy, you put the outbox-polling tables in a schema that’s not the one of the tenant, it means you will be sharing your events between all tenants, which I think won’t work ATM.

@mbekhta I think your work on discriminator-based multitenancy could be adapted to also work for schema-based multitenancy where the outbox table lives in a schema that is common to all tenants? WDYT?

No, it’s perfectly fine to use multiple schemas, with or without Hibernate ORM’s schema-based multitenancy. At least it should be fine; if you have a reproducer for a particular problem we can certainly look at it.

What is not going to work is using schema-based multitenancy and putting your outbox-polling tables in a schema that is supposedly common to all tenants.

To be sure we’re on the same page - I use multiple schemas in order to group data for better clarity only, there are no tenants specified in my application.
So do you think that if outbox-polling tables are in schema1 and certain event needs to use schema1.some_table_from_schema1 and schema2.some_table_from_schema2, it is going to work or not?

Ok. I don’t see where this idea of datasource comes from, then, because there’s definitely only going to be one datasource in Hibernate ORM. But whatever.

It should work.

Back to your initial problem of the table not being found, I can only guess but I’d recommend having a look at your startup logs, from top to bottom, to see if a table creation failed.

Ok, thank you for your help :slight_smile: