I am in the middle of changing my spring + hibernate + mysql setup to be multi-tenant. First of all, I have the following in my application.properties: I am not sure if I'm supposed to make it connect to a specific schema here (test) after introducing multi-tenancy? It doesn't make sense to me, as it's supposed to use a default

Multi-tenant hibernate doesn’t switch between tenants

I am in the middle of changing my spring + hibernate + mysql setup to be multi-tenant. First of all, I have the following in my application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false
spring.datasource.username=root
spring.datasource.password=root

I am not sure if I’m supposed to make it connect to a specific schema here (test) after introducing multi-tenancy? It doesn’t make sense to me, as it’s supposed to use a default schema if no tenant is provided, otherwise connect to the schema associated with the tenant. However, if I remove it I get an error that no database was provided.

Secondly, the multi-tenant part doesn’t seem to be working. All my queries are made in the test schema. I have implemented the following MultiTenantConnectionProvider:

@Component
public class TenantConnectionProvider implements MultiTenantConnectionProvider {
private Datasource datasource;

public TenantConnectionProvider(DataSource datasource) {
    this.datasource = datasource;
}

...

@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
    logger.info("Get connection for tenant {}", tenantIdentifier);
    final Connection connection = getAnyConnection();
    connection.setSchema(tenantIdentifier);
    return connection;
}

@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
    logger.info("Release connection for tenant {}", tenantIdentifier);
    connection.setSchema(DEFAULT_TENANT);
    releaseAnyConnection(connection);
}
}

I am getting no errors, and when I make a query it correctly prints Get connection for tenant with the correct tenantIdentifier that matches the name of one of my other schemas. Still, it queries the test schema. What am I missing? Thanks!

EDIT:

It seems like connection.setSchema(tenantIdentifier) has no effect. In the method’s description, it says the following: If the driver does not support schemas, it will silently ignore this request. So I’m guessing my driver does not support it? What to do in that case?

Answer

Using connection.createStatement().execute("USE " + tenantIdentifier); instead of connection.setSchema(tenantIdentifier); solved my problem.

Advertisement

Answer