在JBoss 7.1.1中为MySQL Cluster创建XA数据源:连接只读错误 [英] Creating XA Datasource in JBoss 7.1.1 for MySQL Cluster : Connection Read Only Error

问题描述

我们正在JBoss 7.1.1应用程序服务器中为后端集群MySQL服务器(主-主配置)配置XA数据源.下面是数据源配置:

We are configuring the XA datasource in the JBoss 7.1.1 application server for the backend clustered MySQL server (Master-Master configuration). Below are the datasource configuration:

<xa-datasource jndi-name="java:/datasoucrenew" pool-name="datasoucrenew" enabled="true" use-ccm="false">
                    <xa-datasource-property name="URL">
                        jdbc:mysql://10.2.0.35:3306,10.2.0.36:3306/test_prod?autoReconnect=true
                    </xa-datasource-property>
                    <xa-datasource-property name="password">
                        password
                    </xa-datasource-property>
                    <xa-datasource-property name="user">
                        user
                    </xa-datasource-property>
                    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    <driver>mysql-connector-java-5.1.12-bin.jar</driver>
                    <xa-pool>
                        <min-pool-size>0</min-pool-size>
                        <max-pool-size>100</max-pool-size>
                        <use-strict-min>true</use-strict-min>
                        <is-same-rm-override>false</is-same-rm-override>
                        <interleaving>false</interleaving>
                        <pad-xid>false</pad-xid>
                        <wrap-xa-resource>false</wrap-xa-resource>
                    </xa-pool>
                    <security>
                        <user-name>user</user-name>
                        <password>password</password>
                    </security>
                    <validation>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                        <exception-sorter class-name="org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter"/>
                    </validation>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </xa-datasource>

我们面临以下问题:

1. 在DB URL jdbc:mysql://10.2.0.35:3306,10.2.0.36:3306/test_prod?autoReconnect = true中，如果10.27.40.35服务器已关闭，则出现以下异常.但是，如果我将URL配置反转为jdbc:mysql://10.2.0.36:3306,10.2.0.35:3306/test_prod?autoReconnect = true，它可以正常工作.您能帮我找出造成此问题的原因吗?

一开始我一直以为这是一个驱动程序问题.但是使用相同的驱动程序，当我运行独立应用程序时，它可以正常工作.

At the starting i was thinking this is an driver issue. But with same driver, when i ran the standalone application, it worked without any issues.

12:06:08,782 ERROR [stderr] (MSC service thread 1-1) com.mysql.jdbc.jdbc2.optional.MysqlXAException: **Connection is read-only. Queries leading to data modificati
on are not allowed.**
12:06:08,784 ERROR [stderr] (MSC service thread 1-1)    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java
:607)
12:06:08,788 ERROR [stderr] (MSC service thread 1-1)    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:586)
12:06:08,790 ERROR [stderr] (MSC service thread 1-1)    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:526)
12:06:08,793 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.start(XAManagedConnection.java:259)
12:06:08,796 ERROR [stderr] (MSC service thread 1-1)    at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.j
ava:636)
12:06:08,799 ERROR [stderr] (MSC service thread 1-1)    at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.j
ava:397)
12:06:08,802 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.enlist(
TxConnectionListener.java:587)
12:06:08,806 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:2
64)
12:06:08,809 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnec
tionManagerImpl.java:467)
12:06:08,813 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractCon
nectionManager.java:599)
12:06:08,817 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionM
anager.java:467)
12:06:08,819 ERROR [stderr] (MSC service thread 1-1)    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
12:06:08,821 ERROR [stderr] (MSC service thread 1-1)    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceCon

注意: 我们正在使用mysql-connector-java-5.1.12-bin.jar mysql驱动程序，因为最新版本21 OR 24 OR 27给出以下错误:

Note: We are using the mysql-connector-java-5.1.12-bin.jar mysql driver as the the latest version 21 OR 24 OR 27 are giving below error:

java.lang.ClassCastException: $Proxy0 cannot be cast to com.mysql.jdbc.ConnectionImpl
Completed
    at com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.wrapConnection(MysqlXADataSource.java:76)
    at com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.getXAConnection(MysqlXADataSource.java:52)

非常感谢您提供的解决此问题的答案.

Really appreciate a your answers to fix this issue.

关于

Manjunath

Manjunath

推荐答案

能否请您尝试用jdbc:mysql:loadbalance:替换jdbc:mysql:.它应该使第二台服务器能够以读/写而不是只读的方式进行访问.

Could you please try lo replace jdbc:mysql: with jdbc:mysql:loadbalance:. It should make the second server be able to access as read/write instead of read-only.

BR，伊利亚

这篇关于在JBoss 7.1.1中为MySQL Cluster创建XA数据源:连接只读错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！