Spring Boot configuration for Tomcat’s pooling data source

In one of projects I was just working on in my professional career I’ve faced the problem of a Spring Boot configuration for a Tomcat’s database connection pool. The main issue was that after some time the pool was giving database connections that were not valid so we were trying different pool configuration parameters – but this is not relevant here. What is relevant is that all re-configuring approaches had no effect.

Is Spring Boot properly configuring the data source?

At some point I stopped believing that the Spring Boot is applying data source parameters from configuration file which was application.yml having following part related to the data source:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/some_db
    username: root
    password: root
    driver-class-name: "com.mysql.jdbc.Driver"
    tomcat:
      removeAbandoned: true
      removeAbandonedTimeout: 120
      logAbandoned: true
      maxActive: 50
      maxIdle: 5
      maxWait: 1000
      validationQuery: "select 1"
      testOnBorrow: true
      testOnConnect: true
      testWhileIdle: true

I’ve realized that I cannot easily verify what are actual data source parameters being applied by the Spring Boot. At last I’ve added a diagnostic REST endpoint showing what are actual parameters of the data source configured by Spring Boot. It was looking like this:

import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

@RestController
public class InfoController {
    private static final Logger LOGGER = LoggerFactory.getLogger(InfoController.class);
    private static final List<String> TOMCAT_DB_CONN_POOL_PROPERTIES = Arrays.asList(
            "driverClassName", "maxActive", "maxAge", "maxIdle", "maxWait", "minEvictableIdleTimeMillis",
            "minIdle", "name", "removeAbandonedTimeout", "timeBetweenEvictionRunsMillis", "validationInterval",
            "validationQuery", "validationQueryTimeout", "removeAbandoned", "testOnBorrow", "testWhileIdle",
            "testOnConnect", "poolSweeperEnabled", "logAbandoned", "jmxEnabled", "url", "dataSourceJNDI",
            "initialSize", "suspectTimeout", "username", "useDisposableConnectionFacade");

    @Autowired
    private DataSource dataSource;

    @RequestMapping(path = "/data-source-info", method = RequestMethod.GET, produces = "application/json")
    public Object getInfo() {
        Map<String, Object> dataSourceDetails = new TreeMap<>();
        dataSourceDetails.put("data-source-class", dataSource.getClass().getName());
        addObjPropertiesUnderKey(dataSource, "data-source-config", dataSourceDetails);
        return dataSourceDetails;
    }

    static void addObjPropertiesUnderKey(Object obj, String key, Map<String, Object> map) {
        try {
            Map<String, Object> properties = new TreeMap<>();
            TOMCAT_DB_CONN_POOL_PROPERTIES.stream().forEach(property -> addPropertyIfNotNull(obj, property, properties));
            if (!properties.isEmpty()) {
                map.put(key, properties);
            }
        } catch (Exception e) {
            LOGGER.warn("Cannot fetch properties of object: {}.", obj, e);
        }
    }

    static void addPropertyIfNotNull(Object obj, String property, Map<String, Object> map) {
        try {
            String value = BeanUtils.getProperty(obj, property);
            if (value != null) {
                map.put(property, value);
            }
        } catch (Exception e) {
            LOGGER.warn("Cannot get property '{}' from  object of class {}. {}", property, obj.getClass(), e.getMessage());
        }
    }
}

The above solution uses dynamic property access with BeanUtils to not bind the application code to the actual data source implementation class (org.apache.tomcat.jdbc.pool.DataSource in case of Tomcat db-connection pool). And of course the set of property names is specific for this data source class.

The first look at the output produced by this REST controller confirmed my suspicion. Only first 4 parameters were properly applied (url, user, password and driver class). The remaining parameters (all with prefix spring.datasource.tomcat.) were ignored by the Spring Boot!

Spring Boot versions and the parameters naming

It turned out the data source parameters definition given in project’s application.yml file was conforming to the current version of the Spring Boot – which was 1.5.8 at the time of writing this article. But the Spring Boot version used in the project was much older: 1.3.8.

Please note the difference between the section “Connection to a production database” for Spring Boot 1.5.8 and the same section for Spring Boot 1.3.8!

For Spring Boot 1.3.8 we have:

And for Spring Boot 1.5.8 we have:

Now it got clear that parameters naming with “tomcat” was not valid for the Spring Boot version used in the project. The naming scheme was changed in Spring Boot somewhere between versions 1.3.x and 1.5.x! The correct form of this part of application.yml for Spring Boot 1.3.8 is:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/some_db
    username: root
    password: root
    driver-class-name: "com.mysql.jdbc.Driver"
    removeAbandoned: true
    removeAbandonedTimeout: 120
    logAbandoned: true
    maxActive: 50
    maxIdle: 5
    maxWait: 1000
    validationQuery: "select 1"
    testOnBorrow: true
    testOnConnect: true
    testWhileIdle: true

After this change the actual data source parameters dump provided by my diagnostic endpoint confirmed that now values from the configuration file are used.

Very tricky!

Conclusions

  1. One should always refer to the documentation in version corresponding to the version of a framework being used.
  2. It’s really hard to solve a problem without a diagnostic tool (like the InfoController class presented above) so don’t hesitate to invest time into preparing one. In a result it will save your time.
Advertisements

About krzysztoftomaszewski

I've got M.Sc. in software engineering. I graduated in 2005 at Institute of Computer Science, Warsaw University of Technology, Faculty of Electronics and Information Technology. I'm working on computer software design and engineering continuously since 2004.
This entry was posted in database, Java, Spring, Tomcat and tagged , . Bookmark the permalink.

3 Responses to Spring Boot configuration for Tomcat’s pooling data source

  1. ddz says:

    good to know

  2. I found the same problem using Spring boot 1.5.9.RELEASE. I suspected the *tomcat*, but it probably would have taken me longer to deep-dive if I hadn’t found your article. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s