mycatapache / mycat-server Goto Github PK
View Code? Open in Web Editor NEWLicense: GNU General Public License v2.0
License: GNU General Public License v2.0
select user_id from sam_glucose group by user_id
实际只能查询出两个结果,但是这个语句查询出了3条结果,没有把两个分片的数据合并;
使用 select user_id,count(*) from sam_glucose group by user_id 这样的写法就自动合并为2条记录了。
跨分片的情况下,必须要加count关键字才可以正确分组,这块最好能优化一下。
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)
测试环境:
硬件: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)
如题支持MySQL批处理特性吗?
useServerPrepStmts=true
rewriteBatchedStatements=true
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的问题?
说明:
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 去掉 %或者_
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); //插入数据失败
现在碰到个问题,想请教下:schema.xml 将库里每张表,不管是不是分片,都要每个表都配置下。要是100多张表,只有10来张需要分片,其他90张只是普通表不需要,一一配置太麻烦了。有其他方法将这些90多张表不用配置,这个物理库需要分片的配置,不分片不用配?
我们现在应用较多,有的应用几百张表,有些可能要随时ddl加表什么的,一一个个表配置太麻烦了,有没有有效的方法?比如cobar的schema本身属性有个默认库,普通表不用配置全放在这个默认库里?
如果有这个属性,应用端就可以随意用ddl建普通表了,而schema.xml 无须任何配置。而不是建一张表,就得提前在schema.xml 配置好,这么繁琐了。
如何实现,不分片的表,默认走指定的datanode
ERROR 1003 (HY000): mycat sequnce err.java.lang.RuntimeException: fetch Param Values error.
多个表一起join ,然后group by 汇总,可以查出结果,但如果 追加order by排序 ,则会结果为empty。
两个解析器一样情况,原来v1.2支持。
解析器:druidparser;采用DB 自增序列
错误原因:循环处理多个主键时,命中一个就退出了循环,应该对所有主键都循环一遍,不管是否命中。
测试语句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
问题: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
测试环境:
硬件: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上)
and DB2, SQL Server, etc.
默认的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后端连接的是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
for example: query(), recordSql(), etc.
问题如下:
select * from t1 order by id_ desc limit 5
t1表是2个节点的分片,执行查询后,从两个节点上各取了5条结果,数据也是对的。但是在进行合并后输出的结果是按asc的排序输出的。
mycat是否必须借助于第三方软件才能搭建集群环境?
server.xml中关于cluster的配置是否还可用?
根据《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)
在没有分库字段情况下,avg函数会发到每个库去执行,解析每个库执行的sql也都是select avg(id) from test ,这样最终的结果是错误的。需要每个库返回count(id),sum(id),最后merge的时候重新计算avg
单表查询,如果条件出现or并且此条件非分片字段,那么路由会根据分片字段选择分片,实际应该是路由到所有分片。
增加一条记录是如何解决唯一索引的问题,需要到所有的库去判断吗?mycat是如何实现的
性能比非半同步慢多少?会不会带来其他问题?
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,如系统登录等等。
问题描述:
第一天往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断点调试也不行了。好不稳定啊!求解?
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
"/* 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'"
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格式很少人愿意下载来看。而且版本变更也显示不出来。
望修复该问题。
配置如下:
<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> 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)
命令行连接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)把时间范围条件缩得很短时,数据量少时可以查出来结果,时段很长数据量多时就报如下错误,数据量应该也不是很大,分片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
It's in the executeSQL() method of the JDBCConnection. Why?
自1.2.3版本后,引入了aio,druid等等好多新的内容,但这些内容测试不够充分,问题很多。
希望开发团队在添加新特性的时候,还是要优先保证原有的功能动作正常。
希望能够追加1.2.x的分支,继续维护。目前该版本序列应该是比较稳定可靠的。
系统大概运行了1个礼拜左右,有天晚上凌晨系统突然停止服务,后台所有数据库操作都报错
期望ACTIVE该值 也能够正确标记 mysql当前是否可用状态
我在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)
目前非公开的结果,有电信行业、电商、移动互联网、内部企业项目等相关行业使用Mycat,最早的1.0版本截至2015年1月,已经稳定生产系统中使用1年,1.2版本也在生产环境中稳定运行半年以上,由于使用者没有公开使用情况,只能根据QQ群的消息,推测到2014年底,大概有30个项目使用Mycat,欢迎继续跟帖反馈使用情况。
在jdbc标准里,这个select last_insert_id()与insert使用的是同一个连接,直接连mysql,返回的就是刚刚用那个连接插入的记录的主键,如果没有insert只有这个select 返回的是0。但是用mycat,返回的是所有insert之后的最后生成的id
mysql mycat二者的行为不同
直连mysql,自动提交事务也是一样的。自动提交的情况 下,直连mysql,mysql这一次访问是一个事务,就是insert 和后面的select last_insert_id()是在一上事务中的
在mycat中直接执行mysql中的存储过程,一直处于执行中状态,无法显示结果。
目前可以通过/!mycat: sql=select * from base_user where id=1;_/CALL proc_test(); 这个注解的方式正常执行,但是需要把存储过程中的sql写到注解中,感觉有些不是很方便,不知道还有没有其他方式能正常执行!
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.
关于通配符配置,可否改简单点,如:
< 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
这样不受那么多限制,配置也灵活,也不会隐含很多东西在后面,用起来简单
这两天用mycat-web查看mycat监控,用的是5.1.22 mysql的jdbc驱动包,怎么也连不上,之后换成5.1.12版本的才好使,需注意
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.