Configuring a XA datasource to mysql 8 DB with spring-boot and bitronix JTA manager fails with SqlException XAERR_RMERR

Question

I have created a Spring Boot 2 (2.1.6.RELEASE) project with dependencies on spring-boot-starter-data-jpa and spring spring-boot-starter-jta-bitronix with a XA datasource configured for Mysql DB (8.0.16).

The application property file (trimmed for relevance with placeholder values between <>) contains the following configuration:

spring:
  datasource:
    url: jdbc:mysql://<host>:<port>/<dbName>
    username: <username>
    password: <password>
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    database-platform: org.hibernate.dialect.MySQL8Dialect
    hibernate:
      ddl-auto: none
  jta:
    bitronix:
      properties:
        server-id: <serverid>

On starting the spring boot application, I received the error stacktrace below:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unsatisfied dependency expressed through method 'entityManagerFactory' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'entityManagerFactoryBuilder' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unsatisfied dependency expressed through method 'entityManagerFactoryBuilder' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaVendorAdapter' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.JpaVendorAdapter]: Factory method 'jpaVendorAdapter' threw exception; nested exception is bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named dataSource
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'entityManagerFactoryBuilder' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unsatisfied dependency expressed through method 'entityManagerFactoryBuilder' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaVendorAdapter' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.JpaVendorAdapter]: Factory method 'jpaVendorAdapter' threw exception; nested exception is bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named dataSource
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaVendorAdapter' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.JpaVendorAdapter]: Factory method 'jpaVendorAdapter' threw exception; nested exception is bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named dataSource
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.JpaVendorAdapter]: Factory method 'jpaVendorAdapter' threw exception; nested exception is bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named dataSource
Caused by: bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named dataSource
Caused by: bitronix.tm.recovery.RecoveryException: failed recovering resource dataSource
Caused by: com.mysql.cj.jdbc.MysqlXAException: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
Caused by: java.sql.SQLException: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency

score 8 · Accepted Answer · answered Jul 01 '19 at 07:52

Disclaimer: I am self documenting my own question in hope of helping others as surprisingly this question / issue was quite obscure to resolve.

From verifying the bitronix issue tracker on github https://github.com/bitronix/btm/issues/100, it was not very obvious on what the resolution was even though the issue was already closed without a direct answer.

Reading the bitronix FAQ at https://github.com/bitronix/btm/wiki/FAQ gave a hint on the issue though pertaining to Oracle which refers to missing user privileges.

Further investigation led to the MySQL 8 documentation page on privileges https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html with a special highlight on the following section.

Prior to MySQL 8.0, any user could execute the XA RECOVER statement to discover the XID values for outstanding prepared XA transactions, possibly leading to commit or rollback of an XA transaction by a user other than the one who started it. In MySQL 8.0, XA RECOVER is permitted only to users who have the XA_RECOVER_ADMIN privilege, which is expected to be granted only to administrative users who have need for it. This might be the case, for example, for administrators of an XA application if it has crashed and it is necessary to find outstanding transactions started by the application so they can be rolled back. This privilege requirement prevents users from discovering the XID values for outstanding prepared XA transactions other than their own. It does not affect normal commit or rollback of an XA transaction because the user who started it knows its XID.

Therefore, I added the required privilege to the datasource user through the following commands in MySQL (Replace username and host section as appropriate).

GRANT XA_RECOVER_ADMIN ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

With this change, the spring boot application starts without any issue.

Using WildFly 17 and MySQL 8 with XA datasources, we had ARJUNA016027 and ARJUNA016009 warnings logged every two minutes despite the application starting normally. Granting the XA_RECOVER_ADMIN role to the XA datasource user made them disappear. — David Le Borgne, Sep 12 '19 at 09:23

Configuring a XA datasource to mysql 8 DB with spring-boot and bitronix JTA manager fails with SqlException XAERR_RMERR

1 Answers1