I'm facing this issue i do deployment through jenkins.Our project is a multiTenant project which follows (Single database & Separate Schemas).
Total 4 microservice we have and same tenantdatasource.java ,hibernateConfig.java in all microservice we have kept, along with same hikari pool config in application.properties.
Whenever i do deployment it runs fine till 1 day and next day our api's starts giving error. connection getting lost after 30000ms , it says Hikari Fill pool skipped, pool is at sufficient level.But runs fine in local.
2022-09-07 10:34:17.050 DEBUG 304980 --- [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=4)
2022-09-07 10:34:17.050 DEBUG 304980 --- [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Fill pool skipped, pool is at sufficient level.
2022-09-07 10:34:26.421 DEBUG 304980 --- [http-nio-9091-exec-8] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=3)
2022-09-07 10:34:26.422 INFO 304980 --- [http-nio-9091-exec-8] c.m.s.tenant.TenantConnectionProvider : Inside catch for get any conncetion org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
2022-09-07 10:34:26.422 INFO 304980 --- [http-nio-9091-exec-8] c.m.session.tenant.RequestInterceptor : java.lang.NullPointerException
2022-09-07 10:34:26.422 INFO 304980 --- [http-nio-9091-exec-8] c.m.session.tenant.TenantSchemaResolver : TenantSchemaResolver: qa-tenant-1
2022-09-07 10:34:26.423 INFO 304980 --- [http-nio-9091-exec-8] c.m.s.tenant.TenantConnectionProvider : INSIDE GET CONNECTION
2022-09-07 10:34:26.423 INFO 304980 --- [http-nio-9091-exec-8] c.m.s.tenant.TenantConnectionProvider : INSIDE GET ANY CONNECTION
2022-09-07 10:34:26.423 DEBUG 304980 --- [http-nio-9091-exec-3] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=3)
2022-09-07 10:34:26.423 INFO 304980 --- [http-nio-9091-exec-3] c.m.s.tenant.TenantConnectionProvider : Inside catch for get any conncetion org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30003ms.
2022-09-07 10:34:26.424 INFO 304980 --- [http-nio-9091-exec-3] c.m.session.tenant.RequestInterceptor : java.lang.NullPointerException
2022-09-07 10:34:26.424 INFO 304980 --- [http-nio-9091-exec-3] c.m.session.tenant.TenantSchemaResolver : TenantSchemaResolver: qa-tenant-1
2022-09-07 10:34:26.424 INFO 304980 --- [http-nio-9091-exec-3] c.m.s.tenant.TenantConnectionProvider : INSIDE GET CONNECTION
2022-09-07 10:34:26.424 INFO 304980 --- [http-nio-9091-exec-3] c.m.s.tenant.TenantConnectionProvider : INSIDE GET ANY CONNECTION
2022-09-07 10:34:30.005 DEBUG 304980 --- [MessageBroker-2] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=2)
2022-09-07 10:34:30.005 DEBUG 304980 --- [MessageBroker-1] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Timeout failure stats (total=10, active=10, idle=0, waiting=3)
2022-09-07 10:34:30.005 INFO 304980 --- [MessageBroker-2] c.m.s.tenant.TenantConnectionProvider : Inside catch for get any conncetion org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30004ms.
2022-09-07 10:34:30.005 INFO 304980 --- [MessageBroker-1] c.m.s.tenant.TenantConnectionProvider : Inside catch for get any conncetion org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-
Hikari config
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=180000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.auto-commit =true
spring.datasource.hikari.leak-detection-threshold=40000
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACE
HibernateConfig.java
import org.hibernate.MultiTenancyStrategy;
import org.hibernate.cfg.Environment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Component
public class HibernateConfig {
private static final Logger LOG = LoggerFactory.getLogger(HibernateConfig.class);
@Autowired
private JpaProperties jpaProperties;
@Bean
JpaVendorAdapter jpaVendorAdapter() {
return new HibernateJpaVendorAdapter();
}
@Primary
@Bean
LocalContainerEntityManagerFactoryBean entityManagerFactory(
DataSource dataSource,
TenantConnectionProvider multiTenantConnectionProviderImpl,
TenantSchemaResolver currentTenantIdentifierResolverImpl,
JpaVendorAdapter jpaVendorAdapter) {
Map<String, Object> jpaPropertiesMap = new HashMap<>(jpaProperties.getProperties());
jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);
jpaPropertiesMap.put(Environment.FORMAT_SQL, true);
jpaPropertiesMap.put(Environment.SHOW_SQL, true);
jpaPropertiesMap.put(Environment.USE_SECOND_LEVEL_CACHE, false);
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.meis.*");
em.setJpaVendorAdapter(jpaVendorAdapter);
em.setJpaPropertyMap(jpaPropertiesMap);
return em;
}
}
TenantConnectionProvider.java
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@Component
public class TenantConnectionProvider implements MultiTenantConnectionProvider {
private final DataSource datasource;
private final Logger logger = LoggerFactory.getLogger(TenantConnectionProvider.class);
@Value("${master.schema.name}")
public String defaultTenant;
public TenantConnectionProvider(DataSource dataSource) {
this.datasource = dataSource;
}
@Override
public Connection getAnyConnection() throws SQLException {
return datasource.getConnection();
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
try {
connection.close();
logger.info("Connection closed");
} catch (Exception e) {
logger.error("Connection not closed", e.toString());
}
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
// log.debug("Get connection for tenant {}", tenantIdentifier);
final Connection connection = getAnyConnection();
connection.setSchema(tenantIdentifier);
connection.setCatalog(tenantIdentifier);
return connection;
}
@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
// log.debug("Release connection for tenant {}", tenantIdentifier);
connection.setSchema(tenantIdentifier);
connection.setCatalog(tenantIdentifier);
releaseAnyConnection(connection);
}
@Override
public boolean supportsAggressiveRelease() {
return false;
}
@Override
@SuppressWarnings("rawtypes")
public boolean isUnwrappableAs(Class aClass) {
return false;
}
@Override
public <T> T unwrap(Class<T> aClass) {
return null;
}
}
server.port=9091
spring.profiles.active=qa
logging.file.name=/home/ubuntu/logs/session-app.log
logging.file.max-size=10MB
logging.level.org.springframework.web: DEBUG
logging.level.org.hibernate: ERROR
## MULTIPART (MultipartProperties)
# Enable multipart uploads
spring.servlet.multipart.enabled=true
# Max file size.
spring.servlet.multipart.max-file-size=200MB
# Max Request Size
spring.servlet.multipart.max-request-size=215MB
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.ddl-auto=none
admin.default.topic=admin
byPassToken=hhhkhguuyiiih786566bhhkhgjbnkhgxfcgvhjghfgfvkhhhgcvhgfvbnbvbvvcdfdzzxknnlkbj
# Connection pool configuration
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=180000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.leak-detection-threshold=40000
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACE