Mycat-Server icon indicating copy to clipboard operation
Mycat-Server copied to clipboard

Published 20 hours ago • verified publisher icon MyCATApache

MyCat做Oracle分布式中间件，报错：Could not retrieve transation read-only status server

Open hy-wux opened this issue 6 years ago • 5 comments

使用MyCat连接Oracle 软件版本： JDK：1.8_191 MyCat：Mycat-server-1.6.6.1-release-20181031195535-linux Oracle：12c MySQL驱动：mysql-connector-java-5.1.35.jar（与mycat解压后的lib目录下的jar包版本一致）

配置如下： schema.xml配置：

<mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="ECIF_SHARE" checkSQLschema="false" dataNode="dn1"> <table name="mycat_sharding_table" dataNode="dn1,dn2" rule="auto-sharding-long" /> <table name="..." type="global" dataNode="dn1,dn2" /> 

<dataNode name="dn1" dataHost="dataHost01" database="share_01" />
<dataNode name="dn2" dataHost="dataHost02" database="share_02" />

<!-- Oracle -->
<dataHost name="dataHost01" maxCon="1000" minCon="1" balance="0"
	writeType="0" dbType="oracle" dbDriver="jdbc">
	<heartbeat>select * from dual</heartbeat>
	<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
	<writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01">
		<readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01"/>
	</writeHost>
</dataHost>
<dataHost name="dataHost02" maxCon="1000" minCon="1" balance="0"
	writeType="0" dbType="oracle" dbDriver="jdbc">
	<heartbeat>select * from dual</heartbeat>
	<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
	<writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02">
		<readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02"/>
	</writeHost>
</dataHost>

</mycat:schema> mycat_sharding_table表的作用是用来确认路由的，Oracle用两个用户模拟的两个节点，读写库都是同一个，未做读写分离。

rule.xml配置：

<mycat:rule xmlns:mycat="http://io.mycat/"> <tableRule name="auto-sharding-long"> id mod-long </tableRule> 2 </mycat:rule> mycat_sharding_table表仅根据ID值取余数确定路由。

使用场景是： 数据批量处理的时候，有create table、insert into ... select ...等DDL语句以及MyCAT不支持的SQL语句，所以需要直接做路由到数据库上执行。

执行日志： 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table /create table TPG_1103871274989387776 as select t0.CUST_NAME,t1.CERT_NO,'5555555555' as group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no where 1 = 2 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.584 DEBUG c.p.e.s.c.m.m.execute : <== Updates: 0 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户1','1065033023961645057','1') 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.727 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户9','1065033023961624578','2') 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.734 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server

The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline

The error occurred while setting parameters

SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO

Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

从日志看出几点问题： 1、数据插入成功，但是返回的影响记录数却是0： 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0

2、TPG_1103871274989387776表创建成功，并且成功插入数据后，无法select，原因应该是事务隔离级别的问题： 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server

The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline

The error occurred while setting parameters

SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO

Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

以上问题，不管我在server.xml中如何配置以下配置项，都会存在。 <mycat:server xmlns:mycat="http://io.mycat/"> false 3 </mycat:server>

以上问题，直接连接Oracle单库执行没有问题。

请问，应该如何设置MyCAT，才能在数据批量处理时，友好的支持Oracle，解决Could not retrieve transation read-only status server的问题。

hy-wux avatar Mar 08 '19 04:03 hy-wux

https://github.com/MyCATApache/Mycat-Server/issues/1704

apachemycat avatar Mar 28 '19 04:03 apachemycat

使用MyCat连接Oracle 软件版本： JDK：1.8_191 MyCat：Mycat-server-1.6.6.1-release-20181031195535-linux Oracle：12c MySQL驱动：mysql-connector-java-5.1.35.jar（与mycat解压后的lib目录下的jar包版本一致）

配置如下： schema.xml配置：

<mycat:schema xmlns:mycat="http://io.mycat/">

<dataNode name="dn1" dataHost="dataHost01" database="share_01" />
<dataNode name="dn2" dataHost="dataHost02" database="share_02" />

<!-- Oracle -->
<dataHost name="dataHost01" maxCon="1000" minCon="1" balance="0"
	writeType="0" dbType="oracle" dbDriver="jdbc">
	<heartbeat>select * from dual</heartbeat>
	<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
	<writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01">
		<readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01"/>
	</writeHost>
</dataHost>
<dataHost name="dataHost02" maxCon="1000" minCon="1" balance="0"
	writeType="0" dbType="oracle" dbDriver="jdbc">
	<heartbeat>select * from dual</heartbeat>
	<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
	<writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02">
		<readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02"/>
	</writeHost>
</dataHost>

</mycat:schema> mycat_sharding_table表的作用是用来确认路由的，Oracle用两个用户模拟的两个节点，读写库都是同一个，未做读写分离。

rule.xml配置：

<mycat:rule xmlns:mycat="http://io.mycat/">

id mod-long

2

</mycat:rule> mycat_sharding_table表仅根据ID值取余数确定路由。

使用场景是： 数据批量处理的时候，有create table、insert into ... select ...等DDL语句以及MyCAT不支持的SQL语句，所以需要直接做路由到数据库上执行。

执行日志： 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table /create table TPG_1103871274989387776 as select t0.CUST_NAME,t1.CERT_NO,'5555555555' as group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no where 1 = 2 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.584 DEBUG c.p.e.s.c.m.m.execute : <== Updates: 0 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户1','1065033023961645057','1') 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.727 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户9','1065033023961624578','2') 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.734 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server

The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline

The error occurred while setting parameters

SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO

Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

从日志看出几点问题： 1、数据插入成功，但是返回的影响记录数却是0： 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0

2、TPG_1103871274989387776表创建成功，并且成功插入数据后，无法select，原因应该是事务隔离级别的问题： 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server

The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline

The error occurred while setting parameters

SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO

Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

以上问题，不管我在server.xml中如何配置以下配置项，都会存在。 <mycat:server xmlns:mycat="http://io.mycat/">

false 3

</mycat:server>

以上问题，直接连接Oracle单库执行没有问题。

请问，应该如何设置MyCAT，才能在数据批量处理时，友好的支持Oracle，解决Could not retrieve transation read-only status server的问题。

请问，你的前端是用mysql协议去连mycat的吗？ 我想用oracle的tns或oracle的jdbc去连mycat，但是总是报错：

1个mycat后面接了2个oracle实例 tnsping mycat 提示,sqlplus 登录提示,pl/sql 登录提示,都是 TNS-12569: TNS:packet checksum failure

jdbc连接mycat提示 Size Data Unit (sdu) mismatch

有知道jdbc怎么连mycat（oracle）的吗？

wanghy8166 avatar Apr 09 '19 02:04 wanghy8166

连接MyCAT需要使用MySQL驱动及协议，连接成功后可以使用Oracle语法的SQL语句进行操作。但是毕竟MySQL和Oracle还是不同的，所以会有很多坑。

发送自 Windows 10 版邮件https://go.microsoft.com/fwlink/?LinkId=550986应用

发件人: wanghy8166 [email protected] 发送时间: Tuesday, April 9, 2019 10:13:49 AM 收件人: MyCATApache/Mycat-Server 抄送: 小伍技术课堂; Author 主题: Re: [MyCATApache/Mycat-Server] MyCat做Oracle分布式中间件，报错：Could not retrieve transation read-only status server (#2141)

使用MyCat连接Oracle 软件版本： JDK：1.8_191 MyCat：Mycat-server-1.6.6.1-release-20181031195535-linux Oracle：12c MySQL驱动：mysql-connector-java-5.1.35.jar（与mycat解压后的lib目录下的jar包版本一致）

配置如下： schema.xml配置：

<mycat:schema xmlns:mycat="http://io.mycat/">http://io.mycat/%22%3E

<dataHost name="dataHost01" maxCon="1000" minCon="1" balance="0"

    writeType="0" dbType="oracle" dbDriver="jdbc">

    <heartbeat>select * from dual</heartbeat>

    <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>

    <writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01">

            <readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_01" password="share_01"/>

    </writeHost>

<dataHost name="dataHost02" maxCon="1000" minCon="1" balance="0"

    writeType="0" dbType="oracle" dbDriver="jdbc">

    <heartbeat>select * from dual</heartbeat>

    <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>

    <writeHost host="writeHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02">

            <readHost host="readHost" url="jdbc:oracle:thin:@192.168.56.128:1521:orcl" user="share_02" password="share_02"/>

    </writeHost>

</mycat:schema> mycat_sharding_table表的作用是用来确认路由的，Oracle用两个用户模拟的两个节点，读写库都是同一个，未做读写分离。

rule.xml配置：

<mycat:rule xmlns:mycat="http://io.mycat/">http://io.mycat/%22%3E

id mod-long

2

</mycat:rule> mycat_sharding_table表仅根据ID值取余数确定路由。

使用场景是： 数据批量处理的时候，有create table、insert into ... select ...等DDL语句以及MyCAT不支持的SQL语句，所以需要直接做路由到数据库上执行。

执行日志： 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table /create table TPG_1103871274989387776 as select t0.CUST_NAME,t1.CERT_NO,'5555555555' as group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no where 1 = 2 2019-03-08 12:13:15.552 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.584 DEBUG c.p.e.s.c.m.m.execute : <== Updates: 0 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户1','1065033023961645057','1') 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.727 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table /insert into TPG_1103871274989387776 values ('客户9','1065033023961624578','2') 2019-03-08 12:13:15.728 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.734 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline The error occurred while setting parameters SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

从日志看出几点问题： 1、数据插入成功，但是返回的影响记录数却是0： 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */insert into TPG_1103871274989387776 values ('客户28','1065033023961649179','0') 2019-03-08 12:13:15.648 DEBUG c.p.e.s.c.m.m.insert : ==> Parameters: 2019-03-08 12:13:15.693 DEBUG c.p.e.s.c.m.m.insert : <== Updates: 0

2、TPG_1103871274989387776表创建成功，并且成功插入数据后，无法select，原因应该是事务隔离级别的问题： 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Preparing: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO 2019-03-08 12:13:15.735 DEBUG c.p.e.s.c.m.m.execute : ==> Parameters: 2019-03-08 12:13:15.736 ERROR c.p.e.s.p.m.e.s.EcifSuspectServiceImpl :

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server The error may involve com.pactera.ecc.share.common.mapper.DatabaseMapper.execute-Inline The error occurred while setting parameters SQL: /*!mycat:sql=select * from mycat_sharding_table */create table TPC_1103871274989387777 as select t0.cust_no,t2.group_no from CUS_BAS_PERSON t0 inner join CUS_BAS_CERTIFICATE t1 on t1.cust_no = t0.cust_no inner join TPG_1103871274989387776 t2 on t0.CUST_NAME = t2.CUST_NAME and t1.CERT_NO = t2.CERT_NO Cause: java.sql.SQLException: Could not retrieve transation read-only status server

; ]; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server

以上问题，不管我在server.xml中如何配置以下配置项，都会存在。 <mycat:server xmlns:mycat="http://io.mycat/">http://io.mycat/%22%3E

false 3

</mycat:server>

以上问题，直接连接Oracle单库执行没有问题。

请问，应该如何设置MyCAT，才能在数据批量处理时，友好的支持Oracle，解决Could not retrieve transation read-only status server的问题。

请问，你的前端是用mysql协议去连mycat的吗？ 我想用oracle的tns或oracle的jdbc去连mycat，但是总是报错：

1个mycat后面接了2个oracle实例 tnsping mycat 提示,sqlplus 登录提示,pl/sql 登录提示,都是 TNS-12569: TNS:packet checksum failure

jdbc连接mycat提示 Size Data Unit (sdu) mismatch

有知道jdbc怎么连mycat（oracle）的吗？

― You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/MyCATApache/Mycat-Server/issues/2141#issuecomment-481074243, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AL1ekiZvORWH9coepmyPwqTOzlCA6aVbks5ve_ddgaJpZM4bkoj6.

hy-wux avatar Apr 09 '19 02:04 hy-wux

非常感谢，我再考虑一下。

wanghy8166 avatar Apr 09 '19 02:04 wanghy8166

您好，您这个问题解决了么？我现在也是遇到了这个问题，在写入单库时没有问题，写入MyCat就会报这个错

zjx-ctrl avatar Mar 03 '22 04:03 zjx-ctrl