Git Product home page Git Product logo

mycat-server's People

Contributors

abirdman avatar apachemycat avatar astonegod avatar bengong avatar brotherbin avatar coderczp avatar digdeep126 avatar fireflyhoo avatar funnyant avatar hashjang avatar huangyiminghappy avatar junwen12221 avatar kkzfl22 avatar lian88jian avatar linzhiqiang0514 avatar magicdoom avatar mycatmerger avatar quinnzhuang avatar runfriends avatar sohudo avatar songgw avatar songwie avatar stonelion avatar wdw1206 avatar xiaozhangwx avatar yanjunli avatar zagzhang avatar zhuam avatar zwyqz avatar zzzcrazypig avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

mycat-server's Issues

Error happen when backend is jdbc connection.

Mysql jdbc driver at app side will send some packets like the following text, backend jdbc connection can not parse and execute it.

"/* mysql-connector-java-5.1.34 ( revision: [email protected] ) */show variables where variable_name ='language' or variable_name = 'net_write_timeout' or variable_name = 'interactive_timeout' or variable_name = 'wait_timeout' or variable_name = 'character_set_client' or variable_name = 'character_set_connection' or variable_name = 'character_set' or variable_name = 'character_set_server' or variable_name = 'tx_isolation' or variable_name = 'transaction_isolation' or variable_name = 'character_set_results' or variable_name = 'timezone' or variable_name = 'time_zone' or variable_name = 'system_time_zone' or variable_name = 'lower_case_table_names' or variable_name = 'max_allowed_packet' or variable_name = 'net_buffer_length' or variable_name = 'sql_mode' or variable_name = 'query_cache_type' or variable_name = 'query_cache_size' or variable_name = 'license' or variable_name = 'init_connect'"

测试sequence报错

我在dn1上创建自增表MYCAT_SEQUENCE
CREATE TABLE MYCAT_SEQUENCE ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (name) ) ENGINE=InnoDB;
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 0, 100);
按照文档中的设置创建了三个函数
启用了server.xml中的1
sequence_db_conf.properties 中增加了GLOBAL=dn1
schema.xml中增加了table name="MYCAT_SEQUENCE" primaryKey="name" dataNode="dn1"

在mycat中执行SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');报如下错误:
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo7.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo4.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo3.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo5.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo2.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo4.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo3.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
ERROR 1305 (42000): FUNCTION pdinfo2.MYCAT_SEQ_NEXTVAL does not exist
mysql> SELECT MYCAT_SEQ_NEXTVAL('GLOBAL');
+-----------------------------+
| MYCAT_SEQ_NEXTVAL('GLOBAL') |
+-----------------------------+
| 101,100 |
+-----------------------------+
1 row in set (0.05 sec)

group by 语句跨分片查询结果不对

select user_id from sam_glucose group by user_id
实际只能查询出两个结果,但是这个语句查询出了3条结果,没有把两个分片的数据合并;

使用 select user_id,count(*) from sam_glucose group by user_id 这样的写法就自动合并为2条记录了。
跨分片的情况下,必须要加count关键字才可以正确分组,这块最好能优化一下。

Getting Started 的url返回404

Getting Started: [zh-CN: https://github.com/MyCATApache/Mycat-doc/blob/master/MyCat_In_Action_%E4%B8%AD%E6%96%87%E7%89%88.doc]

另外,建议文档都改为md格式的,doc格式很少人愿意下载来看。而且版本变更也显示不出来。

group by date_format(inputdate, '%y-%m-%d') 报错

默认的SQL解析器和druidparser都报错
select date_format(inputdate, '%y-%m-%d') as inputdate, sum(requestid) as requestid, sum(factprice) as factprice from biaoben group by date_format(inputdate, '%y-%m-%d')
这个语句执行报错
02-05 17:23:32.343 WARN BusinessExecutor9 -caught exception
java.lang.NullPointerException
at org.opencloudb.mpp.DataMergeService.toColumnIndex(DataMergeService.java:304)
at org.opencloudb.mpp.DataMergeService.onRowMetaData(DataMergeService.java:148)

命令行连接Mycat 中文显示乱码,windows环境

命令行连接Mycat 中文显示乱码,windows环境下运行mycat和mysql命令行工具
mysql> select id,name,vip from users order by id limit 5;
+----+-----------+--------------+
| id | name | vip |
+----+-----------+--------------+
| 1 | 濮氬埄钀? | 鏅€氱敤鎴? |
| 2 | 鍚村織鍑? | 鏅€氱敤鎴? |
| 3 | 寮犲皯绋? | 鏅€氱敤鎴? |
| 4 | 璋匠鏄? | 鏅€氱敤鎴? |
| 5 | 鍚村€? | 鏅€氱敤鎴? |
+----+-----------+--------------+
5 rows in set (0.01 sec)

JDBC连接mycat和命令行直接连接mysql 都正常

mycat 1.3版本问题很多,希望开发团队注意

自1.2.3版本后,引入了aio,druid等等好多新的内容,但这些内容测试不够充分,问题很多。
希望开发团队在添加新特性的时候,还是要优先保证原有的功能动作正常。

希望能够追加1.2.x的分支,继续维护。目前该版本序列应该是比较稳定可靠的。

mycat直接执行存储过程,一直处于执行中状态,无法显示结果

在mycat中直接执行mysql中的存储过程,一直处于执行中状态,无法显示结果。
目前可以通过/!mycat: sql=select * from base_user where id=1;_/CALL proc_test(); 这个注解的方式正常执行,但是需要把存储过程中的sql写到注解中,感觉有些不是很方便,不知道还有没有其他方式能正常执行!

schema.xml 非分片表配置问题及优化建议

现在碰到个问题,想请教下:schema.xml 将库里每张表,不管是不是分片,都要每个表都配置下。要是100多张表,只有10来张需要分片,其他90张只是普通表不需要,一一配置太麻烦了。有其他方法将这些90多张表不用配置,这个物理库需要分片的配置,不分片不用配?

我们现在应用较多,有的应用几百张表,有些可能要随时ddl加表什么的,一一个个表配置太麻烦了,有没有有效的方法?比如cobar的schema本身属性有个默认库,普通表不用配置全放在这个默认库里?

如果有这个属性,应用端就可以随意用ddl建普通表了,而schema.xml 无须任何配置。而不是建一张表,就得提前在schema.xml 配置好,这么繁琐了。

最近测试MyCat时发现的几个问题

测试环境:

硬件:CPU E7500, 内存 4G
软件:WIN7 64位系统,JDK1.7.0_72 64位,MySQL Community 5.6.22.0单实例

测试使用的表按照《MyCat_In_Action_中文版》中快速上手部分使用的SQL进行创建,schema.xml使用默认设置,没有进行修改。

测试出现的问题:

一、普通表,以hotnews表为例

向hotnews表插入5条数据,其ID为1、2、3、4、5

1.1 查询SQL:SELECT * from hotnews where id > 1;
结果:只查到了ID为4的数据

同样,以id>1为条件执行update和delete时也只影响ID为4的数据。

1.2 查询SQL:select * from hotnews where id between 1 and 3;
结果:
java.lang.ClassCastException: com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr cannot be cast to com.alibaba.druid.sql.ast.expr.SQLPropertyExpr
at org.opencloudb.parser.druid.MycatSchemaStatVisitor.getColumn(MycatSchemaStatVisitor.java:111)
at org.opencloudb.parser.druid.MycatSchemaStatVisitor.visit(MycatSchemaStatVisitor.java:23)
at com.alibaba.druid.sql.ast.expr.SQLBetweenExpr.accept0(SQLBetweenExpr.java:48)
at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40)
at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62)
...

二、子表,以orders表为例

2.1 使用Insert语句向orders表中插入数据有一定概率5分钟后报ERROR 1064 can't find parent sharding node for sql,这个已经有人反映过了。

2.2 以customer_id以外的列作为条件进行update delete或者select时会报错,比如执行SQL:select * from orders where id = 1;
结果:
java.lang.NullPointerException
at org.opencloudb.route.util.RouterUtil.ruleCalculate(RouterUtil.java:411)
at org.opencloudb.route.util.RouterUtil.findRouteWithcConditionsForTables(RouterUtil.java:703)
at org.opencloudb.route.util.RouterUtil.tryRouteForOneTable(RouterUtil.java:577)
...

2.3 以customer_id为条件进行update delete或者select时会报错,比如执行SQL:update orders set note = "abc" where customer_id = 1;
结果:
java.lang.IllegalArgumentException: can't find datanode for sharding column:SHARDING_ID val:1
at org.opencloudb.route.util.RouterUtil.ruleCalculate(RouterUtil.java:415)
at org.opencloudb.route.util.RouterUtil.findRouteWithcConditionsForTables(RouterUtil.java:703)
at org.opencloudb.route.util.RouterUtil.tryRouteForOneTable(RouterUtil.java:577)

v1.3.2 数据少的时候查询排序结果正常,当数据量多时则异常:(DataMergeService.java:274) -data Merge error: java.lang.NullPointerException

现象:
(1)把时间范围条件缩得很短时,数据量少时可以查出来结果,时段很长数据量多时就报如下错误,数据量应该也不是很大,分片1有47行汇总数据,分片2有469行汇总数据,分片3~12汇总数据没有。
(2)SQL格式:
select sum(...) As s,date_format(....) As dt,name
from T1
inner join T2 on ......
inner join T3 on....
where .....
group by dt
order by dt
去掉order by dt 后正常查出结果
(3)异常log:
01/27 18:42:42.383 DEBUG $_NIOREACTOR-0-RW -ServerConnection [id=2, schema=test01, host=192.168.1.18, user=root,txIsolation=3, autocommit=false, schema=test01]rollback
01/27 18:42:42.383 DEBUG $_NIOREACTOR-0-RW -no session bound connections found ,no need send rollback cmd
01/27 18:42:42.384 WARN BusinessExecutor0 -data Merge error:
java.lang.NullPointerException
at org.opencloudb.mpp.DataMergeService.handleRowData(DataMergeService.java:236)
at org.opencloudb.mpp.DataMergeService.access$200(DataMergeService.java:48)
at org.opencloudb.mpp.DataMergeService$2.run(DataMergeService.java:260)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
01/27 18:42:42.384 WARN BusinessExecutor4 -data Merge error:
java.lang.NullPointerException
at org.opencloudb.mpp.DataMergeService.handleRowData(DataMergeService.java:236)
at org.opencloudb.mpp.DataMergeService.access$200(DataMergeService.java:48)
at org.opencloudb.mpp.DataMergeService$2.run(DataMergeService.java:260)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

。。。。

}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@270f009c, host=localhost, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/27 18:59:35.182 DEBUG $_NIOREACTOR-3-RW -release channel MySQLConnection [id=3, lastTime=1422356375078, schema=db02, borrowed=true, fromSlaveDB=false, threadId=116, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3310, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/27 18:59:35.185 WARN BusinessExecutor3 -caught exception
java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 515
at java.util.Vector.get(Unknown Source)
at org.opencloudb.mpp.tmp.RowDataSorter.getSortedResult(RowDataSorter.java:78)
at org.opencloudb.mpp.DataMergeService.getResults(DataMergeService.java:125)
at org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler.outputMergeResult(MultiNodeQueryHandler.java:284)
at org.opencloudb.mpp.DataMergeService$1.run(DataMergeService.java:93)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
01/27 18:59:35.186 DEBUG BusinessExecutor3 -error all end ,clear session resource
01/27 18:59:35.186 DEBUG BusinessExecutor3 -clear data
01/27 18:59:35.186 DEBUG BusinessExecutor3 -clear data

1.2.2.1 java.nio.BufferOverflowException

Exception in thread "AIOExecutor2" java.nio.BufferOverflowException
at java.nio.Buffer.nextPutIndex(Buffer.java:513)
at java.nio.DirectByteBuffer.put(DirectByteBuffer.java:291)
at org.opencloudb.mysql.BufferUtil.writeUB3(BufferUtil.java:39)
at org.opencloudb.net.mysql.CommandPacket.write(CommandPacket.java:115)
at org.opencloudb.mysql.nio.MySQLConnection.sendQueryCmd(MySQLConnection.java:288)
at org.opencloudb.mysql.nio.MySQLConnection$StatusSync.execute(MySQLConnection.java:449)
at org.opencloudb.mysql.nio.MySQLConnection.doExecute(MySQLConnection.java:546)
at org.opencloudb.mysql.nio.MySQLConnection.execute(MySQLConnection.java:526)
at org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler._execute(MultiNodeQueryHandler.java:144)
at org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler.access$6(MultiNodeQueryHandler.java:137)
at org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler$2.run(MultiNodeQueryHandler.java:159)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

产生原因:
我自己写的定时器循环读取(select update) mycat中的数据时,会出现中断,mycat log报上面的异常,mycat 是在本地开发环境eclipse中启动,如果换成服务器上启动mycat,没出现过这种情况,这个会是什么问题产生的?
结果:这样会导致当前查询阻塞,需要重启tomcat服务器解决,但是不影响其他业务查询mycat,如系统登录等等。

4台机器做了两个主从,把其中一个主的mysql服务关闭,mycat服务无法继续工作

配置如下:





<dataNode name="dn1" dataHost="mainHost" database="sam_test" />
<dataNode name="dn2" dataHost="submeterHost" database="sam_test" />

<dataHost name="mainHost" maxCon="1000" minCon="10" balance="1"
    writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="192.168.18.78:3306" user="root" password="root">
        <!-- can have multi read hosts -->
        <readHost host="hostS1" url="192.168.18.74:3306" user="root" password="123456" />
    </writeHost>
</dataHost>

<dataHost name="submeterHost" maxCon="1000" minCon="10" balance="1"
    writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM2" url="192.168.18.59:3306" user="root" password="123456">
        <!-- can have multi read hosts -->
        <readHost host="hostS2" url="192.168.18.81:3306" user="root" password="123456" />
    </writeHost>
</dataHost>

望能得到哪些高手解答,目前mycat其他各项功能都测试通过了,打算把mycat应用到公司的正式环境下,希望能够搞清楚这个问题就比较放心了!

mysql连接后端连接vertica数据库报错。

mycat后端连接的是vertica数据。通过mysql客户端以后,可以正常的插入数据。
但是在java代码里面通过jdbc的方式插入程序报错。
java代码如下:

Class.forName("com.vertica.jdbc.Driver") ;
Class.forName("com.mysql.jdbc.Driver") ;
Connection con = DriverManager.getConnection(mycatUrl, mycatUser, mycatPW);
Statement stmt = con.createStatement();
stmt.execute(
"insert into tablea(name) values('myname')"
);

my.log的日志如下:

01/27 17:26:35.074 INFO $_NIOREACTOR-2-RW -ServerConnection [id=162, schema=realtimedata, host=113.106.251.85, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]'realtime_mycat' login success
01/27 17:26:35.080 DEBUG $_NIOREACTOR-2-RW -ServerConnection [id=162, schema=test, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=test]/* mysql-connector-java-5.1.34 ( Revision: [email protected] ) /SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
01/27 17:26:35.080 DEBUG $_NIOREACTOR-2-RW -ServerConnection [id=162, schema=realtimedata, host=113.106.251.85, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]/
mysql-connector-java-5.1.34 ( Revision: [email protected] ) /SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect', route={
1 -> realtime_vertica{/
mysql-connector-java-5.1.34 ( Revision: [email protected] ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'}
} rrs
01/27 17:26:35.081 DEBUG $_NIOREACTOR-2-RW -select read source host_mastar_realtimedata for dataHost:realtime_vertica_db
01/27 17:26:35.085 WARN $_NIOREACTOR-2-RW -execute sql err : errno:4856 [Vertica]VJDBC ERROR: Syntax error at or near "WHERE" con:JDBCConnection [autocommit=true, txIsolation=0, running=false, borrowed=true, id=0, host=183.61.2.158, port=5433]
01/27 17:26:35.085 DEBUG $_NIOREACTOR-2-RW -release connection JDBCConnection [autocommit=true, txIsolation=0, running=false, borrowed=true, id=0, host=183.61.2.158, port=5433]
01/27 17:26:35.085 DEBUG $_NIOREACTOR-2-RW -release channel JDBCConnection [autocommit=true, txIsolation=0, running=false, borrowed=true, id=0, host=183.61.2.158, port=5433]
01/27 17:26:35.095 INFO $_NIOREACTOR-2-RW -close connection,reason:stream closed ,ServerConnection [id=162, schema=realtimedata, host=113.106.251.85, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]
01/27 17:26:35.096 DEBUG $_NIOREACTOR-2-RW -ServerConnection [id=162, schema=realtimedata, host=113.106.251.85, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata] socket key canceled
01/27 17:31:49.405 INFO $_NIOREACTOR-3-RW -ServerConnection [id=163, schema=realtimedata, host=183.61.2.158, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]'realtime_mycat' login success
01/27 17:31:49.407 DEBUG $_NIOREACTOR-3-RW -ServerConnection [id=163, schema=realtimedata, host=183.61.2.158, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]show databases
01/27 17:31:49.414 DEBUG $_NIOREACTOR-3-RW -ServerConnection [id=163, schema=realtimedata, host=183.61.2.158, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]show tables
01/27 17:31:49.417 DEBUG $_NIOREACTOR-3-RW -ServerConnection [id=163, schema=realtimedata, host=183.61.2.158, user=realtime_mycat,txIsolation=3, autocommit=true, schema=realtimedata]select @@version_comment limit 1

分片查询排序结果不对

问题如下:
select * from t1 order by id_ desc limit 5
t1表是2个节点的分片,执行查询后,从两个节点上各取了5条结果,数据也是对的。但是在进行合并后输出的结果是按asc的排序输出的。

Insert into child table时,NIO会机率性卡死,revision 647 引入的问题。

更新

最近查了下原来的svn版本库,发现是647对NIO的更新引入的问题。

mycat:cpu: i3 4核心,os: ubuntu 14.04 64bit, jdk 1.7.0_02, 使用mysql协议连接mysql服务器。
mysql:cpu: i3 4核心,os: xp sp3, 单机双mysql实例配置

故障现象:
只要是对child table进行insert操作,就会机率性卡死,5分钟后返回,log中抛同样的异常。

初步跟踪了代码(版本与master同步):
SystemConfig中缺省已经有避免单核心的逻辑,所以可以排除单线程这个原因。
FetchStoreNodeOfChildTableHandler中的execute()函数,5分钟阀值在此处定义,卡死是因为dataNode始终为null,所以一直在循环等待直到超时。dataNode为空,是因为MySQLConnection中的handleData()函数没有进入handleRowPacket()触发上述Handler的rowResponse(),其实出错时连handleData()都没触发。
使用wireshark监听网络通信,mysql应答是正常的。
也就是说,mycat接受到了正常应答包,但没有触发handleData()函数。难道时NIOReactor的问题?

when update/delete a child table, throws a NullPointerException.

java.lang.NullPointerException
at org.opencloudb.route.util.RouterUtil.ruleCalculate(RouterUtil.java:411)
at org.opencloudb.route.util.RouterUtil.findRouteWithcConditionsForTables(RouterUtil.java:703)
at org.opencloudb.route.util.RouterUtil.tryRouteForOneTable(RouterUtil.java:577)
at org.opencloudb.route.util.RouterUtil.tryRouteForTables(RouterUtil.java:466)
at org.opencloudb.route.impl.DruidMysqlRouteStrategy.routeNormalSqlWithAST(DruidMysqlRouteStrategy.java:48)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:46)
at org.opencloudb.route.RouteService.route(RouteService.java:112)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:165)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:154)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:125)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:250)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:56)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:357)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:276)
at org.opencloudb.net.AIOReadHandler.completed(AIOSocketWR.java:141)
at org.opencloudb.net.AIOReadHandler.completed(AIOSocketWR.java:1)
at sun.nio.ch.Invoker.invokeUnchecked(Invoker.java:126)
at sun.nio.ch.UnixAsynchronousSocketChannelImpl.finishRead(UnixAsynchronousSocketChannelImpl.java:430)
at sun.nio.ch.UnixAsynchronousSocketChannelImpl.finish(UnixAsynchronousSocketChannelImpl.java:191)
at sun.nio.ch.UnixAsynchronousSocketChannelImpl.onEvent(UnixAsynchronousSocketChannelImpl.java:213)
at sun.nio.ch.EPollPort$EventHandlerTask.run(EPollPort.java:293)
at sun.nio.ch.AsynchronousChannelGroupImpl$1.run(AsynchronousChannelGroupImpl.java:112)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)

mycat配置一主一从,主停掉以后,select语句都不能执行了

问题:mycat配置了一主一从,主使用service mysql stop停掉以后,select语句也无法执行了。

mycat服务端日志不停地报错:
01-26 14:04:11.416 INFO $_NIOConnector -close connection,reason:hearbeat connecterr ,[thread=$_NIOConnector,class=MySQLDetector,host=172.17.209.103,port=3306,localPort=0,schema=null]
01-26 14:04:12.417 INFO $_NIOConnector -close connection,reason:hearbeat connecterr ,[thread=$_NIOConnector,class=MySQLDetector,host=172.17.209.103,port=3306,localPort=0,schema=null]
01-26 14:04:13.417 INFO $_NIOConnector -close connection,reason:hearbeat connecterr ,[thread=$_NIOConnector,class=MySQLDetector,host=172.17.209.103,port=3306,localPort=0,schema=null]

执行select * from travelrecord语句报错
[Err] 1003 - Connection refused: no further information

《MyCat_In_Action_中文版.doc》中示例,报错can't find (root) parent sharding node for sql:INSERT INTO orders (id, customer_id)

根据《MyCat_In_Action_中文版.doc》中的步骤在windows下面测试的时候,报标题所示错误,请各位帮忙分析下原因,具体环境如下:

windows7,64位
JDK1.7
MySQL5.0.45,Community Edition

具体错误信息如下:
2/31 14:15:53.700 WARN $_NIOREACTOR-0-RW -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into orders(id,customer_id) values(2,2) err:java.sql.SQLNonTransientException: can't find (root) parent sharding node for sql:INSERT INTO orders (id, customer_id)
VALUES (2, 2)
java.sql.SQLNonTransientException: can't find (root) parent sharding node for sql:INSERT INTO orders (id, customer_id)
VALUES (2, 2)
at org.opencloudb.parser.druid.impl.DruidInsertParser.parserChildTable(DruidInsertParser.java:138)
at org.opencloudb.parser.druid.impl.DruidInsertParser.statementParse(DruidInsertParser.java:50)
at org.opencloudb.parser.druid.impl.DefaultDruidParser.parser(DefaultDruidParser.java:60)
at org.opencloudb.route.impl.DruidMysqlRouteStrategy.routeNormalSqlWithAST(DruidMysqlRouteStrategy.java:35)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:46)
at org.opencloudb.route.RouteService.route(RouteService.java:112)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:162)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:154)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:125)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:263)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:56)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:370)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:264)
at org.opencloudb.net.NIOSocketWR.asynRead(NIOSocketWR.java:186)
at org.opencloudb.net.AbstractConnection.asynRead(AbstractConnection.java:226)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:97)
at java.lang.Thread.run(Thread.java:722)

1.3.0.2 druid解析器,in语句多主键路由缓存命中错误

错误原因:循环处理多个主键时,命中一个就退出了循环,应该对所有主键都循环一遍,不管是否命中。
测试语句SELECT * from pd_info where pid in (40,1138,268,495);
表配置:
table name="pd_info" primaryKey="pid" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8" rule="rule1"
规则配置:PartitionByLong
property name="partitionCount">8</property
property name="partitionLength">128</property

ERROR 1003 (HY000): Reload config failure

mysql> reload @@config;
ERROR 1003 (HY000): Reload config failure

error log:
02/12 15:50:51.569 WARN $_NIOREACTOR-0-RW -register error
java.nio.channels.ClosedChannelException
at java.nio.channels.spi.AbstractSelectableChannel.register(AbstractSelectableChannel.java:194)
at org.opencloudb.net.NIOSocketWR.register(NIOSocketWR.java:27)
at org.opencloudb.net.NIOReactor$RW.register(NIOReactor.java:154)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:86)
at java.lang.Thread.run(Thread.java:722)

sequnce db方式很不稳定,我昨天测试的正常,下班后把测试机关了今天再启动,sequnce db就无法正常工作

问题描述:
第一天往mycat_sequence表中配置了相关表的sequence信息,同时把那三个function也都在数据库创建完成,sequence_db_conf.properties也做了配置。启动的时候,往数据库插入数据,提示:
java.lang.RuntimeException: can't fetch sequnce in db,sequnce :SAM_GLUCOSE
at org.opencloudb.sequence.handler.IncrSequenceMySQLHandler.getSeqValueFromDB(IncrSequenceMySQLHandler.java:101)
at org.opencloudb.sequence.handler.IncrSequenceMySQLHandler.nextId(IncrSequenceMySQLHandler.java:71)
at org.opencloudb.parser.ExtNodeToString4SEQ.nextSequenceNode(ExtNodeToString4SEQ.java:61)
at com.foundationdb.sql.unparser.NodeToString.toString(NodeToString.java:267)
at com.foundationdb.sql.unparser.NodeToString.maybeParens(NodeToString.java:1471)
at com.foundationdb.sql.unparser.NodeToString.resultColumn(NodeToString.java:737)
at com.foundationdb.sql.unparser.NodeToString.toString(NodeToString.java:95)
at com.foundationdb.sql.unparser.NodeToString.nodeList(NodeToString.java:1465)
at com.foundationdb.sql.unparser.NodeToString.nodeList(NodeToString.java:1453)
at com.foundationdb.sql.unparser.NodeToString.resultColumnList(NodeToString.java:726)
at com.foundationdb.sql.unparser.NodeToString.toString(NodeToString.java:93)
at com.foundationdb.sql.unparser.NodeToString.rowResultSetNode(NodeToString.java:707)
at com.foundationdb.sql.unparser.NodeToString.toString(NodeToString.java:125)
at com.foundationdb.sql.unparser.NodeToString.insertNode(NodeToString.java:605)
at com.foundationdb.sql.unparser.NodeToString.toString(NodeToString.java:85)
at org.opencloudb.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:76)
at org.opencloudb.route.MyCATSequnceProcessor.access$2(MyCATSequnceProcessor.java:66)
at org.opencloudb.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:101)

我在org.opencloudb.sequence.handler.IncrSequenceMySQLHandler.getSeqValueFromDB 方法中加断点debug后,自己恢复正常了,在2015-01-29一整天测试都正常。
29号下班时我把测试机关了。30号我上班后把测试机打开,启动mycat服务都正常,执行查询操作也都正常,但是插入数据的时候 sequence又报:
java.lang.RuntimeException: can't fetch sequnce in db,sequnce :SAM_GLUCOSE 同样的问题了。
这次加debug断点调试也不行了。好不稳定啊!求解?

v1.3.0.2 节点通配符配置优化建议

关于通配符配置,可否改简单点,如:
< table name="..." type="global" datanode="dn$04,dn$59"/>
....
< table name="..." primaryKey="id" autoIncrement="TRUE" rule="..." dataNode="dn$04,dn$59"/>
....
< table name="..." type="global" dataNode="dn0"/>
.....
< table name="..." primaryKey="id" autoIncrement="TRUE" rule="..." dataNode="dn0,dn1"/>
....

< dataNode name="dn$04" dataHost="H3306" database="db$0105" />
< dataNode name="dn$59" dataHost="H3307" database="db$0610" />

数据库就是:db01、db02....db10

这样不受那么多限制,配置也灵活,也不会隐含很多东西在后面,用起来简单

distinct只在单个DataNode下生效,跨DataNode查询会查出多个重复的结果

测试环境:

硬件:CPU E7500, 内存 4G
软件:WIN7 64位系统,JDK1.7.0_72 64位,MySQL Community 5.6.22.0单实例

测试使用的表按照《MyCat_In_Action_中文版》中快速上手部分使用的SQL进行创建,schema.xml使用默认设置,没有进行修改。

测试使用的是hotnews这个比较简单的表,象棋中插入5条数据,ID分别是1到5,title全部为“123”,查询SQL:select distinct title from hotnews;
结果:三个123(因为数据保存在三个DataNode上)

mycat系统崩溃

系统大概运行了1个礼拜左右,有天晚上凌晨系统突然停止服务,后台所有数据库操作都报错

mysql服务器autocommit设置为0时通过mycat命令行插入到表中的数据,在实际的物理节点没有数据 例如mysql> explain insert into pets(id,name,owner) values(1,'l','l'); +-----------+---------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------+ | dn1 | insert into pets(id,name,owner) values(1,'l','l') | +-----------+---------------------------------------------------+ 1 row in set (0.00 sec),insert into pets(id,name,owner) values(1,'l','l'); Query OK, 1 row affected (0.02 sec) 但是实际的物理库中,mysql> select * from pets; Empty set (0.00 sec)什么都没有,在mycat中commit也什么都没有,mysql> select * from pets; Empty set (0.00 sec),把实际物理库autocommit改为1就可以了

druidparser 分区字段会路由,like 情况下会报错

说明:
druid解析器会走自定义分片规则,导致分片字段like 情况下异常,
(RouterUtil.java:673) -can't find any valid datanode :

重现:
如sql 为 select * from table where 分区字段 like '%3233%'
会传递到路由函数导致,
Integer nodeIndex = tableConfig.getRule().getRuleAlgorithm().calculate(pair.colValue);//RouterUtil.java -line 669

解决意见:
1.不支持like,如果like 分区字段,直接跳过,全节点筛选。
2.支持like , Integer nodeIndex 应该返回 Integer[] nodeIndexs ,这个和路由规则有关。
3.让路由函数子定义自己处理,希望pair.colValue 去掉 %或者_

The dataNode attribute of global table.

A sql for a global table will be executed on all dataNodes of schema, not the table's.
If the value of TableConfig.getDataNodes() is not same as SchemaConfig.getAllDataNodes(), the operation should be executed on the table's dataNodes.

select last_insert_id()返回的值不是刚刚插入的那个

在jdbc标准里,这个select last_insert_id()与insert使用的是同一个连接,直接连mysql,返回的就是刚刚用那个连接插入的记录的主键,如果没有insert只有这个select 返回的是0。但是用mycat,返回的是所有insert之后的最后生成的id
mysql mycat二者的行为不同

直连mysql,自动提交事务也是一样的。自动提交的情况 下,直连mysql,mysql这一次访问是一个事务,就是insert 和后面的select last_insert_id()是在一上事务中的

ERROR 1003 (HY000): Reload config failure

Mcat:Mycat-server-1.3.0.2-20150105144205-linux.tar.gz
OS:CentOS release 6.4 (Final)
Mysql:5.5
Java:java version "1.7.0_67"

问题1:ERROR 1003 (HY000): Reload config failure
error log::
02/12 15:50:51.569 WARN $_NIOREACTOR-0-RW -register error
java.nio.channels.ClosedChannelException
at java.nio.channels.spi.AbstractSelectableChannel.register(AbstractSelectableChannel.java:194)
at org.opencloudb.net.NIOSocketWR.register(NIOSocketWR.java:27)
at org.opencloudb.net.NIOReactor$RW.register(NIOReactor.java:154)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:86)
at java.lang.Thread.run(Thread.java:722)
+++++++++++++++++++++++++++++++++++++++++++++++
问题2:
create table orders (id int not null primary key ,customer_id int not null,sataus int ,note varchar(100) ); //正常执行

insert into orders(id,customer_id) values(1,1); //插入数据失败
insert into orders(id,customer_id) values(2,2); //插入数据失败

select avg(id) from test ;

在没有分库字段情况下,avg函数会发到每个库去执行,解析每个库执行的sql也都是select avg(id) from test ,这样最终的结果是错误的。需要每个库返回count(id),sum(id),最后merge的时候重新计算avg

v1.3.2-druidparser解析,但报另外一个解析的org.opencloudb.parser.SQLParserDelegate.parse异常

1.解析器:druidparser
2.SQL:insert into test01(name,descs) values('sdf','sdf'),values('sdfdf','sdfdsgfdf')
3.备注:id使用db自增序列
5.异常信息:
01/29 16:48:40.105 DEBUG $_NIOREACTOR-3-RW -ServerConnection [id=1, schema=iems, host=192.168.1.208, user=root,txIsolation=3, autocommit=true, schema=tt]insert into test01(name,descs)
values('sdf','sdf'),values('sdfdf','sdfdsgfdf')
01/29 16:48:40.107 ERROR Thread-1 -MyCATSequenceProcessor.executeSeq(SesionSQLPair)
java.sql.SQLSyntaxErrorException: com.foundationdb.sql.StandardException: VALUES is empty
at org.opencloudb.parser.SQLParserDelegate.parse(SQLParserDelegate.java:70)
at org.opencloudb.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:74)
at org.opencloudb.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:22)
at org.opencloudb.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:101)
Caused by: com.foundationdb.sql.StandardException: VALUES is empty
at com.foundationdb.sql.parser.SQLGrammar.rowValueConstructorElement(SQLGrammar.java:8583)
at com.foundationdb.sql.parser.SQLGrammar.rowValueConstructor(SQLGrammar.java:8521)
at com.foundationdb.sql.parser.SQLGrammar.tableValueConstructorList(SQLGrammar.java:9824)
at com.foundationdb.sql.parser.SQLGrammar.tableValueConstructor(SQLGrammar.java:9805)
at com.foundationdb.sql.parser.SQLGrammar.simpleTable(SQLGrammar.java:4598)
at com.foundationdb.sql.parser.SQLGrammar.nonJoinQueryPrimary(SQLGrammar.java:4573)
at com.foundationdb.sql.parser.SQLGrammar.nonJoinQueryTerm(SQLGrammar.java:4462)
at com.foundationdb.sql.parser.SQLGrammar.queryExpression(SQLGrammar.java:4367)
at com.foundationdb.sql.parser.SQLGrammar.insertColumnsAndSource(SQLGrammar.java:8384)
at com.foundationdb.sql.parser.SQLGrammar.insertStatement(SQLGrammar.java:2449)
at com.foundationdb.sql.parser.SQLGrammar.preparableSQLDataStatement(SQLGrammar.java:2208)
at com.foundationdb.sql.parser.SQLGrammar.StatementPart(SQLGrammar.java:1948)
at com.foundationdb.sql.parser.SQLGrammar.Statement(SQLGrammar.java:1846)
at com.foundationdb.sql.parser.SQLGrammar.parseStatement(SQLGrammar.java:1832)
at com.foundationdb.sql.parser.SQLParser.parseStatement(SQLParser.java:104)
at org.opencloudb.parser.SQLParserDelegate.parse(SQLParserDelegate.java:68)
... 3 more

Mycat的使用情况统计汇总

目前非公开的结果,有电信行业、电商、移动互联网、内部企业项目等相关行业使用Mycat,最早的1.0版本截至2015年1月,已经稳定生产系统中使用1年,1.2版本也在生产环境中稳定运行半年以上,由于使用者没有公开使用情况,只能根据QQ群的消息,推测到2014年底,大概有30个项目使用Mycat,欢迎继续跟帖反馈使用情况。

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.