java - 如果我们保持空闲一段时间，则在连接到 Azure MSSQL DB 时，Spring Boot 应用程序中的数据库连接将关闭
问题描述
我们有一个多租户架构（相同的应用程序根据标头连接到 azure 中的不同数据库）spring boot 应用程序，它与 azure mssql db 交互以获取记录并部署在 azure 应用程序服务中。
当我们通过 spring boot rest 服务的端点 url 访问记录时，它工作正常，但如果我们保持空闲一段时间，它会抛出以下异常。
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2392) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2376) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1916) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:4301) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:4202) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3206) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7959) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7945) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:2488) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:2493) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:2633) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at sun.reflect.GeneratedMethodAccessor209.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.14.jar!/:na]
at com.sun.proxy.$Proxy95.setAutoCommit(Unknown Source) ~[na:na]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:66) ~[hibernate-core-5.2.9.Final.jar!/:5.2.9.Final]
... 62 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:724) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2946) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2585) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.14.jar!/:na]
at com.sun.proxy.$Proxy95.prepareStatement(Unknown Source) ~[na:na]
at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1521) ~[spring-jdbc-4.3.8.RELEASE.jar!/:4.3.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:627) ~[spring-jdbc-4.3.8.RELEASE.jar!/:4.3.8.RELEASE]
... 28 common frames omitted
数据源配置
@ConfigurationProperties(prefix = "spring.datasource")
public class MultiTenantConfiguration {
@Autowired
private Environment environment;
Logger logger = Logger.getLogger("com.microsoft.sqlserver.jdbc");
@Bean
@Primary
public DataSource multiTenantDataSource() {
logger.setLevel(Level.FINE);
Map<Object,Object> resolvedDataSources = new HashMap<Object,Object>();
String[] profiles = environment.getActiveProfiles();
Pattern p = Pattern.compile("([a-z]*)\\-([a-z]{3}+)");
OperationsKeyVault opv = new OperationsKeyVault();
String keyVaultName = System.getenv("KEYVAULT_NAME");
for (String profile : profiles) {
Matcher m = p.matcher(profile);
if (!m.matches()) {
continue;
}
DataSourceBuilder dataSourceBuilder = new DataSourceBuilder(this.getClass().getClassLoader());
String tenantId = m.group(2);
dataSourceBuilder
.driverClassName(opv.GetSecret(keyVaultName, "mlsi-datasource-driver-class-name", "application/json"))
.url(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-url", "application/json"))
.username(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-username", "application/json"))
.password(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-password", "application/json"));
resolvedDataSources.put(tenantId, dataSourceBuilder.build());
}
MultiTenantDataSource dataSource = new MultiTenantDataSource();
dataSource.setDefaultTargetDataSource(resolvedDataSources.get("fth"));
dataSource.setTargetDataSources(resolvedDataSources);
dataSource.afterPropertiesSet();
return dataSource;
}
}
存储库配置
@Configuration
@EnableConfigurationProperties(JpaProperties.class)
@EnableJpaRepositories(basePackages = "com.example.mls.feature.repository", entityManagerFactoryRef = "featureEntityManagerFactory")
@EnableTransactionManagement
@Import(MultiTenantConfiguration.class)
@ComponentScan("com.example.mls.feature")
public class FeatureConfiguration {
@Autowired
private JpaProperties jpaProperties;
@Autowired
@Qualifier(value="multiTenantDataSource")
private DataSource multiTenantDataSource;
@Bean(name="featureEntityManagerFactory")
@Primary
public EntityManagerFactory featureEntityManagerFactory() {
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
factory.setJpaPropertyMap(jpaProperties.getProperties());
factory.setPackagesToScan("com.example.mls.feature.model");
factory.setDataSource(multiTenantDataSource);
factory.afterPropertiesSet();
return factory.getObject();
}
@Bean
public RepositoryRestConfigurer repositoryRestConfigurer() {
return new RepositoryRestConfigurerAdapter() {
@Override
public void configureRepositoryRestConfiguration(RepositoryRestConfiguration config) {
config.exposeIdsFor(BaseUuidEntity.class, Recipient.class, NotificationRecipient.class);
}
};
}
}
由于 azure mssql 数据库有 4 分钟的连接超时无法更改，我们还尝试了以下几个属性用于自动重新连接
spring.datasource.core.validation-query==SELECT 1
spring.datasource.core.validation-interval=10000
spring.datasource.core.test-on-borrow=true
spring.datasource.core.test-while-idle=true
spring.datasource.core.time-between-eviction-runs-millis=100000
spring.datasource.core.max-age=120000
我们还尝试了 hikari 和 tomcat 连接池
所以请帮忙解决这个问题
谢谢。
解决方案
您的属性错误。属性名称中没有核心。例如，这是正确的。
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1
spring.datasource.validation-timeout=10000
spring.datasource.validation-interval=180000
推荐阅读
- c# - 后端无法识别 json 主体 Angular
- r - R-Code = mutate 函数和 case when = values 是动态的
- c - 我如何检查数字是否只能除以我输入的 2 个素数
- react-native - 返回上一个屏幕时响应调用函数和设置状态
- kframework - K 框架 - 与玩具语言混淆
- c++ - PCL:: CropBox 不起作用，意味着它不会过滤我想要的点
- carriage-return - 如何在 AWK 中匹配 0x2102
- java - startActivity() 在 Runnable 接口中不起作用
- c++ - 如何检查 mpl::vector_c 中的值？
- java - 房间 | 多对多关系附加字段