Using multiple data sources (Micronaut) javax.persistence.TransactionRequiredException

Issue

I am trying to use multiple database connections with micronaut (1.2.0) no injection method worked for me this is my configuration:

application.yml

datasources:
  default:
    url: ${JDBC_URL:`jdbc:mysql://localhost/cossine?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC`}
    username: ${JDBC_USER:******}
    password: ${JDBC_PASSWORD:*****}
    driverClassName: ${JDBC_DRIVER:com.mysql.cj.jdbc.Driver}
  keycloak:
    url: ${JDBC_KYC_URL:`jdbc:mysql://localhost/keycloak?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC`}
    username: ${JDBC_KYC_USER:******}
    password: ${JDBC_KYC_PASSWORD:*******}
    driverClassName: ${JDBC_DRIVER:com.mysql.cj.jdbc.Driver}
jpa:
  default:
    packages-to-scan:
      - 'com.acmain.domain'
    properties:
      hibernate:
        hbm2ddl:
          auto: none
        show_sql: false
  keycloak:
    packages-to-scan:
      - 'com.acmain.kdomain'
    properties:
      hibernate:
        hbm2ddl:
          auto: none
        show_sql: true

When I use the default configuration it works without problems, but when using the second like this :

 private final EntityManager entityManager;

    public UserEntityImpl(@CurrentSession(value = "keycloak") EntityManager em) {
        this.entityManager = em;
    }

    @Override
    @Transactional
    public UsersResponse findAll(@NotNull SortingAndOrderArguments args) {
        List<UserEntity> users;
        StringBuilder qlString = new StringBuilder("SELECT g FROM UserEntity as g");
...........................

one I get the following error:

Hibernate: select count(userentity0_.id) as col_0_0_ from user_entity userentity0_
Hibernate: select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.enabled as enabled3_0_, userentity0_.first_name as first_na4_0_, userentity0_.last_name as last_nam5_0_, userentity0_.realm_id as realm_id6_0_ from user_entity userentity0_ order by userentity0_.email asc limit ?
14:10:59.822 [RxCachedThreadScheduler-1] ERROR i.m.h.s.netty.RoutingInBoundHandler - Unexpected error occurred: no transaction is in progress
javax.persistence.TransactionRequiredException: no transaction is in progress
    at org.hibernate.internal.AbstractSharedSessionContract.checkTransactionNeededForUpdateOperation(AbstractSharedSessionContract.java:398)
    at org.hibernate.internal.SessionImpl.checkTransactionNeededForUpdateOperation(SessionImpl.java:3617)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1473)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1469)
    at org.springframework.orm.hibernate5.SessionFactoryUtils.flush(SessionFactoryUtils.java:147)
    at org.springframework.orm.hibernate5.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:95)
    at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:96)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:922)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:730)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:534)
    at io.micronaut.spring.tx.annotation.TransactionInterceptor.intercept(TransactionInterceptor.java:88)
    at io.micronaut.aop.MethodInterceptor.intercept(MethodInterceptor.java:40)
    at io.micronaut.aop.chain.InterceptorChain.proceed(InterceptorChain.java:150)
    at io.micronaut.validation.ValidatingInterceptor.intercept(ValidatingInterceptor.java:157)
    at io.micronaut.aop.MethodInterceptor.intercept(MethodInterceptor.java:40)
    at io.micronaut.aop.chain.InterceptorChain.proceed(InterceptorChain.java:150)
    at com.acmain.repository.$UserEntityImplDefinition$Intercepted.findAll(Unknown Source)
    at com.acmain.service.UserService.getAllUsers(UserService.java:181)
    at com.acmain.controller.UserController.getUsers(UserController.java:48)
    at com.acmain.controller.$UserControllerDefinition$Intercepted.$$access2(Unknown Source)
    at com.acmain.controller.$UserControllerDefinition$Intercepted$$proxy2.invokeInternal(Unknown Source)
    at io.micronaut.context.AbstractExecutableMethod.invoke(AbstractExecutableMethod.java:144)
    at io.micronaut.aop.chain.InterceptorChain.lambda$new$1(InterceptorChain.java:93)
    at io.micronaut.aop.chain.InterceptorChain.proceed(InterceptorChain.java:150)
    at io.micronaut.validation.ValidatingInterceptor.intercept(ValidatingInterceptor.java:157)
    at io.micronaut.aop.MethodInterceptor.intercept(MethodInterceptor.java:40)
    at io.micronaut.aop.chain.InterceptorChain.proceed(InterceptorChain.java:150)
    at com.acmain.controller.$UserControllerDefinition$Intercepted.getUsers(Unknown Source)
    at com.acmain.controller.$$UserControllerDefinition$InterceptedDefinition$$exec3.invokeInternal(Unknown Source)
    at io.micronaut.context.AbstractExecutableMethod.invoke(AbstractExecutableMethod.java:144)
    at io.micronaut.context.DefaultBeanContext$BeanExecutionHandle.invoke(DefaultBeanContext.java:2792)
    at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:294)
    at io.micronaut.web.router.RouteMatch.execute(RouteMatch.java:122)
    at io.micronaut.http.server.netty.RoutingInBoundHandler.lambda$buildResultEmitter$19(RoutingInBoundHandler.java:1408)
    at io.reactivex.internal.operators.flowable.FlowableCreate.subscribeActual(FlowableCreate.java:71)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedFlowable.subscribeActual(RxInstrumentedFlowable.java:68)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.internal.operators.flowable.FlowableMap.subscribeActual(FlowableMap.java:37)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedFlowable.subscribeActual(RxInstrumentedFlowable.java:68)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.internal.operators.flowable.FlowableSwitchIfEmpty.subscribeActual(FlowableSwitchIfEmpty.java:32)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedFlowable.subscribeActual(RxInstrumentedFlowable.java:68)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14868)
    at io.micronaut.http.context.ServerRequestTracingPublisher.lambda$subscribe$0(ServerRequestTracingPublisher.java:52)
    at io.micronaut.http.context.ServerRequestContext.with(ServerRequestContext.java:52)
    at io.micronaut.http.context.ServerRequestTracingPublisher.subscribe(ServerRequestTracingPublisher.java:52)
    at io.reactivex.internal.operators.flowable.FlowableFromPublisher.subscribeActual(FlowableFromPublisher.java:29)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedFlowable.subscribeActual(RxInstrumentedFlowable.java:68)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.internal.operators.flowable.FlowableSwitchMap.subscribeActual(FlowableSwitchMap.java:49)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedFlowable.subscribeActual(RxInstrumentedFlowable.java:68)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.reactivex.internal.operators.flowable.FlowableSwitchMap$SwitchMapSubscriber.onNext(FlowableSwitchMap.java:129)
    at io.micronaut.reactive.rxjava2.InstrumentedSubscriber.lambda$onNext$0(InstrumentedSubscriber.java:80)
    at io.micronaut.reactive.rxjava2.InstrumentedSubscriber.onNext(InstrumentedSubscriber.java:84)
    at io.reactivex.internal.operators.flowable.FlowableSubscribeOn$SubscribeOnSubscriber.onNext(FlowableSubscribeOn.java:97)
    at io.micronaut.reactive.rxjava2.InstrumentedSubscriber.lambda$onNext$0(InstrumentedSubscriber.java:80)
    at io.micronaut.reactive.rxjava2.InstrumentedSubscriber.onNext(InstrumentedSubscriber.java:84)
    at io.reactivex.internal.subscriptions.DeferredScalarSubscription.complete(DeferredScalarSubscription.java:132)
    at io.reactivex.internal.operators.flowable.FlowableFromCallable.subscribeActual(FlowableFromCallable.java:50)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.micronaut.reactive.rxjava2.RxInstrumentedCallableFlowable.subscribeActual(RxInstrumentedCallableFlowable.java:65)
    at io.reactivex.Flowable.subscribe(Flowable.java:14918)
    at io.reactivex.Flowable.subscribe(Flowable.java:14865)
    at io.reactivex.internal.operators.flowable.FlowableSubscribeOn$SubscribeOnSubscriber.run(FlowableSubscribeOn.java:82)
    at io.reactivex.internal.schedulers.ScheduledRunnable.run(ScheduledRunnable.java:66)
    at io.reactivex.internal.schedulers.ScheduledRunnable.call(ScheduledRunnable.java:57)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

what am I missing?

also tried

 @PersistenceContext(name = "keycloak")
    private final EntityManager entityManager;

Solution

well just need to add

  @Transactional("keycloak")

to the method

Is not documented here https://micronaut-projects.github.io/micronaut-sql/latest/guide/#hibernate

But found the uses here

https://github.com/micronaut-projects/micronaut-sql/blob/master/hibernate-jpa/src/test/groovy/io/micronaut/configuration/hibernate/jpa/MultipleDataSourceJpaSetupSpec.groovy

Answered By - Jeisson Huerfano