MySQL socket timeout behaviour on @Transactional

user_16694214 0
Viewed 464

I am using spring-boot (2.3.4.RELEASE) with MySQL (8.0.21).

My database config in spring boot is

spring.datasource.url=jdbc:mysql://localhost:3306/testDB?connectTimeout=2000&socketTimeout=10000&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=toor
spring.datasource.dbcp2.test-while-idle=true
spring.datasource.dbcp2.pool-prepared-statements=true
spring.datasource.dbcp2.validation-query=SELECT 1 from dual where @@innodb_read_only = 0
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
MySQL Connector
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>
Dbcp2
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1.1</version>
</dependency>

I had tested the following code template and found this weird behaviour
@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;

@Transactional
public void f1() {
  jdbcTemplate.queryForObject("SELECT sleep(12)", new HashMap<>(), Integer.class);
}


@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;

@Transactional
public void f2() {
  Thread.sleep(12000);
  jdbcTemplate.queryForObject("SELECT count(*) from my_table", new HashMap<>(), Integer.class);
}
The first function is giving this error:
11:10:07.862 [qtp262911569-27] ERROR o.s.t.i.TransactionInterceptor - Application exception overridden by rollback exception
org.springframework.dao.RecoverableDataAccessException: PreparedStatementCallback; SQL [SELECT sleep(12)]; Communications link failure

The last packet successfully received from the server was 10,013 milliseconds ago. The last packet sent successfully to the server was 10,013 milliseconds ago.; nested exception is com.MySQL.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10,013 milliseconds ago. The last packet sent successfully to the server was 10,013 milliseconds ago.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:236)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:245)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:261)

While the second one works properly.

I know this has something to do with the socket timeout that is being set in the MySQL URL to 10 sec, in the config I mentioned above. But not sure what is happening behind the scenes. Can anyone help me to understand what is going on here?

spring-boot mysql java
1 Answers

You can try with changes in the application.properties as:-

spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.validationQuery = SELECT 1

each minute (60000 millis) test connection executing the query “SELECT 1” will be performed. In this way we can keep alive the database connection, periodically performing a validation query, and avoid to reach the MySQL’s wait_timeout.

user_121506930
Related