使用SQLEXCEPTION XAERR_RMERR配置XA数据源到MySQL 8 dB和Bitronix JTA Ma

Question

我已经创建了一个Spring Boot 2(2.1.6.Release(项目，其依赖关系对Spring-boot-Starter-starter-data-jpa和Spring Spring-boot-boot-starter-jta-bitronix，with为MySQL DB配置的XA数据源(8.0.16(。

应用程序属性文件(与＆lt;>之间的占位符值相关的修剪(包含以下配置：

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>

启动Spring Boot应用程序时，我收到了下面的错误StackTrace：

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

Answer 1

免责声明：我自我记录了自己的问题，希望能帮助他人，因为这个问题/问题很晦涩难懂。

通过验证github上的bitronix问题跟踪器https://github.com/bitronix/bitronix/btm/issues/100，即使该问题已经在没有直接答案的情况下关闭，也不是什么解决方案。/p>

在https://github.com/bitronix/btm/wiki/faq上阅读Bitronix FAQ，尽管与Oracle有关，但提示了该问题，这是指丢失的用户特权。

进一步的调查导致特权上的MySQL 8文档页面https://dev.mysql.com/doc/refman/8.0/en/privileges-providers.html，在以下部分中具有特别的亮点。

MySQL 8.0之前，任何用户都可以执行XA恢复语句 发现出色准备的XA交易的XID值， 可能导致用户对XA事务的提交或回滚 除了开始它的人。在MySQL 8.0中，XA恢复为 仅允许使用具有XA_RECOVE_ADMIN特权的用户 期望仅授予有需要的行政用户 为此。例如，对于 XA应用程序崩溃了，有必要查找 申请启动的出色交易，因此可以是 回滚。此特权要求阻止用户 发现出色准备的XA交易的XID值 除了自己的。它不会影响正常的提交或回滚 XA事务是因为启动的用户知道其XID。

因此，我通过MySQL中的以下命令向数据源用户添加了所需的特权(替换用户名和主机部分均适当(。

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

随着此更改，Spring Boot应用程序启动而没有任何问题。