XA transaction manager cause the statement to fail at MySQL 8

[azexcy]

Bug Report

refer https://github.com/apache/shardingsphere/actions/runs/4748316219

When execute register storage unit, will throw exception

REGISTER STORAGE UNIT pipeline_it_0 ( URL='jdbc:mysql://mysql_0.host:3306/pipeline_it_0?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true', USER='test_user', PASSWORD='Test@123')

[ERROR] 2023-04-19 22:22:45.801 [Curator-SafeNotifyService-0] c.g.common.eventbus.EventBus.default - Exception thrown by subscriber method renew(org.apache.shardingsphere.mode.manager.cluster.coordinator.registry.config.event.datasource.DataSourceChangedEvent) on subscriber org.apache.shardingsphere.mode.manager.cluster.coordinator.subscriber.ConfigurationChangedSubscriber@fc5f2a9 when dispatching event: org.apache.shardingsphere.mode.manager.cluster.coordinator.registry.config.event.datasource.DataSourceChangedEvent@17ad1e0b
 org.apache.shardingsphere.transaction.xa.jta.exception.XATransactionPrivilegeException: Failed to create XA transaction manager, requires `XA_RECOVER_ADMIN` privileges
 	at org.apache.shardingsphere.transaction.xa.jta.datasource.checker.dialect.MySQLDataSourcePrivilegeChecker.checkPrivilege(MySQLDataSourcePrivilegeChecker.java:66)
 	at org.apache.shardingsphere.transaction.xa.jta.datasource.checker.dialect.MySQLDataSourcePrivilegeChecker.checkPrivilege(MySQLDataSourcePrivilegeChecker.java:46)
 	at org.apache.shardingsphere.transaction.xa.XAShardingSphereTransactionManager.lambda$init$0(XAShardingSphereTransactionManager.java:58)
 	at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
 	at org.apache.shardingsphere.transaction.xa.XAShardingSphereTransactionManager.init(XAShardingSphereTransactionManager.java:58)
 	at org.apache.shardingsphere.transaction.ShardingSphereTransactionManagerEngine.lambda$init$0(ShardingSphereTransactionManagerEngine.java:64)
 	at java.util.Map.forEach(Map.java:630)
 	at org.apache.shardingsphere.transaction.ShardingSphereTransactionManagerEngine.init(ShardingSphereTransactionManagerEngine.java:64)
 	at org.apache.shardingsphere.transaction.rule.TransactionRule.createTransactionManagerEngine(TransactionRule.java:80)
 	at org.apache.shardingsphere.transaction.rule.TransactionRule.<init>(TransactionRule.java:62)
 	at org.apache.shardingsphere.transaction.rule.builder.TransactionRuleBuilder.build(TransactionRuleBuilder.java:36)
 	at org.apache.shardingsphere.transaction.rule.builder.TransactionRuleBuilder.build(TransactionRuleBuilder.java:32)
 	at org.apache.shardingsphere.infra.rule.builder.global.GlobalRulesBuilder.buildRules(GlobalRulesBuilder.java:54)
 	at org.apache.shardingsphere.mode.manager.ContextManager.createMetaDataContexts(ContextManager.java:352)
 	at org.apache.shardingsphere.mode.manager.ContextManager.alterDataSourceConfiguration(ContextManager.java:277)
 	at org.apache.shardingsphere.mode.manager.cluster.coordinator.subscriber.ConfigurationChangedSubscriber.renew(ConfigurationChangedSubscriber.java:72)
 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 	at java.lang.reflect.Method.invoke(Method.java:498)
 	at com.google.common.eventbus.Subscriber.invokeSubscriberMethod(Subscriber.java:87)
 	at com.google.common.eventbus.Subscriber$SynchronizedSubscriber.invokeSubscriberMethod(Subscriber.java:144)
 	at com.google.common.eventbus.Subscriber$1.run(Subscriber.java:72)
 	at com.google.common.util.concurrent.DirectExecutor.execute(DirectExecutor.java:30)
 	at com.google.common.eventbus.Subscriber.dispatchEvent(Subscriber.java:67)
 	at com.google.common.eventbus.Dispatcher$PerThreadQueuedDispatcher.dispatch(Dispatcher.java:108)
 	at com.google.common.eventbus.EventBus.post(EventBus.java:212)
 	at org.apache.shardingsphere.infra.util.eventbus.EventBusContext.post(EventBusContext.java:51)
 	at java.util.Optional.ifPresent(Optional.java:159)
 	at org.apache.shardingsphere.mode.manager.cluster.coordinator.registry.GovernanceWatcherFactory.lambda$watch$0(GovernanceWatcherFactory.java:55)
 	at org.apache.shardingsphere.mode.repository.cluster.zookeeper.ZookeeperRepository.lambda$watch$0(ZookeeperRepository.java:243)
 	at org.apache.curator.framework.recipes.cache.TreeCacheListenerWrapper.sendEvent(TreeCacheListenerWrapper.java:71)
 	at org.apache.curator.framework.recipes.cache.TreeCacheListenerWrapper.event(TreeCacheListenerWrapper.java:42)
 	at org.apache.curator.framework.recipes.cache.CuratorCacheListenerBuilderImpl$2.lambda$event$0(CuratorCacheListenerBuilderImpl.java:149)
 	at java.util.ArrayList.forEach(ArrayList.java:1259)
 	at org.apache.curator.framework.recipes.cache.CuratorCacheListenerBuilderImpl$2.event(CuratorCacheListenerBuilderImpl.java:149)
 	at org.apache.curator.framework.recipes.cache.CuratorCacheImpl.lambda$putStorage$6(CuratorCacheImpl.java:287)
 	at org.apache.curator.framework.listen.MappingListenerManager.lambda$forEach$0(MappingListenerManager.java:92)
 	at org.apache.curator.framework.listen.MappingListenerManager.forEach(MappingListenerManager.java:89)
 	at org.apache.curator.framework.listen.StandardListenerManager.forEach(StandardListenerManager.java:89)
 	at org.apache.curator.framework.recipes.cache.CuratorCacheImpl.lambda$callListeners$9(CuratorCacheImpl.java:301)
 	at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1640)
 	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:750)

It looks like the XA data source is reporting the error, but there is no XA configured at startup.
The server.yaml config:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: it_db_mysql
      server-lists: zk.host:2181
      timeToLiveSeconds: 60
      operationTimeoutMilliseconds: 500
      retryIntervalMilliseconds: 500
      maxRetries: 3

authority:
  users:
    - user: proxy
      password: Proxy@123
  privilege:
    type: ALL_PERMITTED

props:
  max-connections-size-per-query: 1
  kernel-executor-size: 16  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-hint-enabled: true
  sql-show: false
  sql-federation-type: ADVANCED
  proxy-mysql-default-version: 8.0.11
  cdc-server-port: 33071 # CDC server port

Which version of ShardingSphere did you use?

master

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

No exception

Actual behavior

Have exception and add storage unit failed.

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Follow the E2E config, the enviroment

1. MySQL: 8
2. user permission

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO `test_user`@`%`;
GRANT CREATE, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX ON pipeline_it_0.* TO `test_user`@`%`;
...

Not use super admin, and not grant `XA_RECOVER_ADMIN ` privileges

Example codes for reproduce this issue (such as a github link).

[azexcy]

@FlyingZC Hi, can you help look at this problem?

[FlyingZC]

Maybe we should let the user configure whether to use xa transactions.

[sandynz]

Close it, it's fixed by #25696