Spring Boot 2 disable Autocommit Hikari with multiple datasources

Question

I am attempting to turn off auto-commit on Hikari with multiple data sources, but I'm not having any luck. I'm using Spring Boot 2 (2.0.3.RELEASE). Here is my config:

application.properties

spring.datasource.primary.driver=com.mysql.cj.jdbc.Driver
spring.datasource.primary.url=jdbc:mysql://localhost:3306/spark?autoReconnect=true
spring.datasource.primary.username=xxxx
spring.datasource.primary.password=xxxx
spring.datasource.primary.max-active=100
spring.datasource.primary.max-idle=5
spring.datasource.primary.min-idle=1
spring.datasource.primary.test-while-idle=true
spring.datasource.primary.test-on-borrow=true
spring.datasource.primary.validation-query=SELECT 1
spring.datasource.primary.time-between-eviction-runs-millis=5000
spring.datasource.primary.min-evictable-idle-time-millis=60000


spring.datasource.ucm.driver=com.mysql.cj.jdbc.Driver
spring.datasource.ucm.url=jdbc:mysql://localhost:3306/usercentral?autoReconnect=true
spring.datasource.ucm.username=xxx
spring.datasource.ucm.password=xxx
spring.datasource.ucm.max-active=100
spring.datasource.ucm.test-while-idle=true
spring.datasource.ucm.test-on-borrow=true
spring.datasource.ucm.validation-query=SELECT 1
spring.datasource.ucm.time-between-eviction-runs-millis=5000
spring.datasource.ucm.min-evictable-idle-time-millis=60000
spring.datasource.ucm.hikari.auto-commit=false  # <- Not working

Here's my configuration class where the data sources are setup

@Primary
@Bean
@ConfigurationProperties("spring.datasource.primary")
public DataSourceProperties primaryDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("spring.datasource.primary")
public DataSource primaryDataSource() {
    return primaryDataSourceProperties().initializeDataSourceBuilder().build();
}

@Bean
@ConfigurationProperties("spring.datasource.ucm")
public DataSourceProperties ucmDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean(name="ucmDataSource")
@ConfigurationProperties("spring.datasource.ucm")
public DataSource ucmDataSource() {
    return ucmDataSourceProperties().initializeDataSourceBuilder().build();
}

Here is what is being output when the pool is created:

-2018-08-23 15:48:22.845 -DEBUG 21455 --- [nio-8081-exec-1] com.zaxxer.hikari.HikariConfig           : 1151 : allowPoolSuspension.............false
-2018-08-23 15:48:22.846 -DEBUG 21455 --- [nio-8081-exec-1] com.zaxxer.hikari.HikariConfig           : 1151 : autoCommit......................true
-2018-08-23 15:48:22.846 -DEBUG 21455 --- [nio-8081-exec-1] com.zaxxer.hikari.HikariConfig           : 1151 : catalog.........................none
-2018-08-23 15:48:22.846 -DEBUG 21455 --- [nio-8081-exec-1] com.zaxxer.hikari.HikariConfig           : 1151 : connectionInitSql...............none

How do I turn off auto-commit on this connection pool?

Hikari is the default in Spring Boot 2, so maybe just leave out the ".hikari."? Also, none of the options after "password" will work - Hikari uses different ones as shown on the [Hikari homepage](https://github.com/brettwooldridge/HikariCP) and in [this answer](https://stackoverflow.com/a/51079239/3080094). — vanOekel, Aug 25 '18 at 10:11
That looks like it worked, it's funny because if I use ignoreUnknownFields = false in my configurationProperties, the app fails to start due to "app.datasource.ucm.auto-commit" being unresolved, but if I set ignoreUnknownFields to true, it appears to work with autocommit turned off! Thanks — csyperski, Aug 27 '18 at 16:05

score 3 · Answer 1 · edited Dec 30 '19 at 05:17

I know that this is very late but this issue exploded my brain for ~2 days and this post is at the top of google search so I will post here the solution for others.

It's actually quite simple, just that I missed it in the docs here https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-two-datasources

long story short make sure that you add .type(HikariDataSource.class) before you build your class.

Also if you use jhipster you should also point your config to the hikari part like this @ConfigurationProperties("spring.datasource.other.hikari")

Water · Answer 2 · 2018-09-14T05:09:59.383

2

I am using Spring Boot 2.0.4.RELEASE, and faced the same problem. Spent hours with related posts showing non-working properties...

Removing .hikari worked for me.

spring.datasource.ucm.autocommit=false

edited Sep 14 '18 at 05:09

answered Sep 14 '18 at 03:18

Water

540
7
7

score 0 · Answer 3 · answered Sep 14 '18 at 06:12

0

Alternative solution to your problem, configure default-auto-commit property as false. It should also work.

spring.datasource.ucm.default-auto-commit=false

Spring Boot Ref 1

default.auto-commit

answered Sep 14 '18 at 06:12

Narayan Yerrabachu

1,714
1
19
31

score 0 · Answer 4 · answered Sep 14 '18 at 06:16

0

Make false default-auto-commit should work.

spring.datasource.ucm.default-auto-commit=false

answered Sep 14 '18 at 06:16

Eklavya

17,618
4
28
57

Spring Boot 2 disable Autocommit Hikari with multiple datasources

4 Answers4

Linked