Spring validation-query for multi-tenant system

Question

I have a multi-tenant spring boot application running on MySQL database with separate schema for individual tenants. The dataSource is decided by passing a header code at runtime. How to make sure the connection doesnt drop in such a system? Once the system's inactivity passes beyond the set time, "SELECT 1" command needs to run and that doesn't specify anything to the system which dataSource to connect to, as a result of this the system throws sql exception.

Here are my config properties:

spring.datasource.url=jdbc:mysql://xxxxxxxx/xxx?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect spring.jpa.database=MYSQL spring.jpa.hibernate.ddl-auto=validate spring.datasource.test-on-borrow=true spring.datasource.testWhileIdle = true spring.datasource.timeBetweenEvictionRunsMillis = 60000 spring.datasource.validation-query=SELECT 1 spring.datasource.tomcat.max-wait=10000 spring.datasource.tomcat.max-active=50 spring.datasource.tomcat.test-on-borrow=true spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate4.SpringSessionContext hibernate.c3p0.max_size=20 hibernate.c3p0.min_size=5 hibernate.c3p0.idle_test_period=600

wish i could see your config files. 1. check for the timeout ,ideal time property in config file. 2. workaround when the new query comes it should try to build the connection first then execute query but ideally having a connection pool with correct configuration does the work in our production servers. — Arvind Lal

Yoku Yoku · Accepted Answer · 2018-09-17T12:35:21

What I found is that as soon as we have the hibernate.multiTenancy=DATABASE setting, the datasource validation query setting specified in application.properties file will no have any effect as we need to intercept the getConnection call and resolve the "datasource" tenant programatically. Hence I solved it using the extended CustomMultiTenantConnectionProviderImpl which implements MultiTenantConnectionProvider interface and override the definitions for getConnection and releaseConnection methods to do what is needed to be done. Now my connections are being validated properly.

Spring validation-query for multi-tenant system

1 Answers