Axon Framework: how to configure mutiple databases?

Question

I'm using mysql as the event store, so axon-server-connector is excluded from the classpath. My use case is described as following.

1. Spring Boot 2.1.7.RELEASE and axon 4.3.2
2. I planned to have three databases, for the purpose of axon event store, projection writing and projection reading respectively.

@Configuration
public class DataSourceConfiguration {
    @Primary
    @Bean("axonMaster")
    @ConfigurationProperties("spring.datasource.hikari.axon-master")
    public DataSource axon() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("projectionRead")
    @ConfigurationProperties("spring.datasource.hikari.projection-write")
    public DataSource master() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("projectionWrite")
    @ConfigurationProperties("spring.datasource.hikari.projection-read")
    public DataSource slave() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
}

3. I tried to configure mutiple datasources with spring data jpa. The primary one is shown below.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "axonEntityManagerFactory",
        basePackages = "org.axonframework.eventsourcing.eventstore.jpa") // (1)
public class AxonEventStoreConfig {
    @Primary
    @Bean(name="axonEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("axonMaster") DataSource axonMaster) {
        return builder
                .dataSource(axonMaster)
                .packages("org.axonframework.eventsourcing.eventstore.jpa")
                .persistenceUnit("axonMaster") //(2)
                .build();
    }

    @Primary
    @Bean(name = "axonPlatformTransactionManager") //(3)
    public PlatformTransactionManager transactionManager(
            @Qualifier("axonEntityManagerFactory") EntityManagerFactory axonEntityManagerFactory) {
        return new JpaTransactionManager(axonEntityManagerFactory);
    }
}

Questions about this part are:
(1) Is it enough to set the basePackages to be org.axonframework.eventsourcing.eventstore.jpa? Maybe I need to add token package org.axonframework.eventhandling.tokenstore.jpa and soga package as well? I will use soga store.
(2) Is the packages here same with the previous one? What shall the name of persistenceUnit be?

The example project is uplodad to github: https://github.com/sincosmos/axon-multiple-databases
I cannot get the application run. Am I doing everything right? I referred to the example at https://github.com/AxonIQ/giftcard-demo, but the multiple databases version is based on axon 2.0, axon command bus need to be configured as well.

The goal seems simple, configure mutiple databases (one for event store) in an axon framework application, but even I spend serval days, I still get nothing done.

Could anyone please give me some suggestion or help? I will be very grateful.

/**************************** update 20200521 ****************************/

I made a progress after reading Allard's answer, now I can configure multiple databases for the application. The source code is uploaded to github https://github.com/sincosmos/axon-multiple-databases.git

Especially, for axon event store, the db configration is shown as belown.

@Configuration
@EnableTransactionManagement
public class AxonEventStoreConfig {
    @Bean("axonMaster")
    @ConfigurationProperties("spring.datasource.hikari.axon-master")
    public DataSource axon() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name="axonEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("axonMaster") DataSource axonMaster) {
        return builder
                .dataSource(axonMaster)
                .persistenceUnit("axonMaster")
                .properties(jpaProperties())
                .packages("org.axonframework.eventhandling.tokenstore",
                        "org.axonframework.modelling.saga.repository.jpa",
                        "org.axonframework.eventsourcing.eventstore.jpa")
                .build();
    }

    /**
     * Is it right to provide EntityManagerProvider like this ???  
     * For axon event store
     * @param entityManagerFactory
     * @return
     */
    @Bean
    public EntityManagerProvider entityManagerProvider(@Qualifier("axonEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return () -> entityManagerFactory.getObject().createEntityManager();
    }

    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
        props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
        props.put("hibernate.hbm2ddl.auto", "update");
        props.put("hibernate.show_sql", "true");
        return props;
    }
}

After I start the application (event store related tables will be created automatically if it's the first time), the database conntection pool for axon will be exhausted very soon. The logs are pasted for your reference.

Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
Hibernate: select min(domaineven0_.global_index)-1 as col_0_0_ from domain_event_entry domaineven0_
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
19:59:57.223 [EventProcessor[com.baeldung.axon.querymodel]-0] WARN  o.a.e.TrackingEventProcessor - Fetch Segments for Processor 'com.baeldung.axon.querymodel' failed: no transaction is in progress. Preparing for retry in 1s
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
Hibernate: select min(domaineven0_.global_index)-1 as col_0_0_ from domain_event_entry domaineven0_
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
19:59:58.293 [EventProcessor[com.baeldung.axon.querymodel]-0] WARN  o.a.e.TrackingEventProcessor - Fetch Segments for Processor 'com.baeldung.axon.querymodel' failed: no transaction is in progress. Preparing for retry in 2s
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
Hibernate: select min(domaineven0_.global_index)-1 as col_0_0_ from domain_event_entry domaineven0_
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
20:00:00.361 [EventProcessor[com.baeldung.axon.querymodel]-0] WARN  o.a.e.TrackingEventProcessor - Fetch Segments for Processor 'com.baeldung.axon.querymodel' failed: no transaction is in progress. Preparing for retry in 4s
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
Hibernate: select min(domaineven0_.global_index)-1 as col_0_0_ from domain_event_entry domaineven0_
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
20:00:04.465 [EventProcessor[com.baeldung.axon.querymodel]-0] WARN  o.a.e.TrackingEventProcessor - Fetch Segments for Processor 'com.baeldung.axon.querymodel' failed: no transaction is in progress. Preparing for retry in 8s
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
Hibernate: select min(domaineven0_.global_index)-1 as col_0_0_ from domain_event_entry domaineven0_
Hibernate: select tokenentry0_.segment as col_0_0_ from token_entry tokenentry0_ where tokenentry0_.processor_name=? order by tokenentry0_.segment ASC
20:00:12.531 [EventProcessor[com.baeldung.axon.querymodel]-0] WARN  o.a.e.TrackingEventProcessor - Fetch Segments for Processor 'com.baeldung.axon.querymodel' failed: no transaction is in progress. Preparing for retry in 16s
20:00:17.327 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=15, active=15, idle=0, waiting=0)
20:00:22.178 [HikariPool-2 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Pool stats (total=10, active=0, idle=10, waiting=0)
20:00:23.092 [HikariPool-3 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-3 - Pool stats (total=10, active=0, idle=10, waiting=0)

Status of these connections are "Sleep" when I check the connection status in mysql workbench. Change the connection pool size does no help. I also checked the stack of the jvm and no deadlock were found. I set datasource leakDetectionThreshold to 10000 but as you can see no datasource leak information was print. Can you help with this?

/**************************** update 20200522 ****************************/

It turns out "javax.persistence.TransactionRequiredException: no transaction is in progress" happened when the event processor trying to access the mysql event store. I configured transaction managers for each datasource but the error continues. Have no idea what is going on...

Answer 1

When using multiple databases, you probably won't be able to rely on autoconfiguration anymore, as Spring and Axon wouldn't know which one of the two databases you'd want to use.

Axon doesn't use an EntityManager directly. Instead, all components require an EntityManagerProvider. You may be able to use that to your advantage.

If you want all Axon components to use a certain database, simply define an EntityManagerProvider bean that returns the EntityManager that connects to that database. Spring manages the EntityManager completely, so you only need a single instance for all your EntityManager Sessions.

If you want different components to use different EntityManagers (e.g. Event Store in one database, Tokens and Sagas in another), then you will need to configure these components yourself. Sometimes, it's just easiest to copy the bean definitions from the AutoConfiguration classes and adapt them to suit your needs. See https://github.com/AxonFramework/AxonFramework/tree/master/spring-boot-autoconfigure/src/main/java/org/axonframework/springboot/autoconfig

Lastly, the entities that you need to scan depend on the components that you expect to use. Spring Boot autoconfiguration will scan the following Axon packages by default (if you don't specify any @EntityScan yourself):

• org.axonframework.eventhandling.tokenstore (for tokens)
• org.axonframework.modelling.saga.repository.jpa (for sagas)
• org.axonframework.eventsourcing.eventstore.jpa (for the event store)

Note that the @EnableJpaRepositories annotation is used to scan for @Repository classes. Axon doesn't use those, so there is no point scanning Axon packages for them. Axon does define entities, so @EntityScan will make sense.