mycatapache / mycat-doc Goto Github PK
View Code? Open in Web Editor NEWLicense: Apache License 2.0
License: Apache License 2.0
如题
使用druid解析器,发现explain节点数据有问题:
127.0.0.1/root:[TESTDB> EXPLAIN SELECT * FROM test WHERE id >= 11000 AND id <= 1111100;
+-----------+--------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------+
| dn1 | SELECT * FROM test WHERE id >= 11000 AND id <= 1111100 |
+-----------+--------------------------------------------------------+
1 row in set (0.12 sec)
5个节点,只定位到一个节点。
注释掉druid解析器后:
127.0.0.1/root:[TESTDB> EXPLAIN SELECT * FROM test WHERE id >= 11000 AND id <= 1111100;
+-----------+----------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------+
| dn1 | SELECT * FROM test WHERE (id >= 11000) AND (id <= 1111100) LIMIT 100 |
| dn2 | SELECT * FROM test WHERE (id >= 11000) AND (id <= 1111100) LIMIT 100 |
| dn3 | SELECT * FROM test WHERE (id >= 11000) AND (id <= 1111100) LIMIT 100 |
| dn4 | SELECT * FROM test WHERE (id >= 11000) AND (id <= 1111100) LIMIT 100 |
| dn5 | SELECT * FROM test WHERE (id >= 11000) AND (id <= 1111100) LIMIT 100 |
+-----------+----------------------------------------------------------------------+
5 rows in set (0.00 sec)
mycat版本1.3.0.1,linux-x64
MyCAT是一个不错的项目 我看到官方支持的特性太多了,建议模块化,例如core支持jdbc mongodb bigdata特性都分开 这样项目更好维护依赖。。。
说明:mycat 使用的是主从结构
schema.xml内容如下
<mycat:schema xmlns:mycat="http://io.mycat/">
select user()
</mycat:schema>
mycat 1.5.1
11/16 15:49:34.982 ERROR [$_NIOREACTOR-2-RW] (DruidMycatRouteStrategy.java:53) -DruidMycatRouteStrategyError
com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual ERROR DURATION
at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:1471)
at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:693)
at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:658)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:480)
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:555)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:166)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:119)
at com.alibaba.druid.sql.parser.SQLExprParser.exprList(SQLExprParser.java:789)
at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:690)
at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:658)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:480)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectItem(SQLSelectParser.java:298)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:275)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:127)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:60)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:196)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:145)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:1658)
at org.opencloudb.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:50)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:76)
at org.opencloudb.route.RouteService.route(RouteService.java:133)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:224)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:175)
at org.opencloudb.server.handler.SelectHandler.handle(SelectHandler.java:106)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:77)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:308)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71)
at org.opencloudb.net.FrontendConnection.rawHandle(FrontendConnection.java:435)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:417)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:313)
at org.opencloudb.net.NIOSocketWR.asynRead(NIOSocketWR.java:186)
at org.opencloudb.net.AbstractConnection.asynRead(AbstractConnection.java:274)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:100)
at java.lang.Thread.run(Thread.java:744)
11/16 15:49:34.984 WARN [$_NIOREACTOR-2-RW] (ServerConnection.java:232) -ServerConnection [id=14, schema=TESTDB, host=10.20.37.67, user=test,txIsolation=3, autocommit=true, schema=TESTDB]SELECT STATE AS 状态
, ROUND(SUM(DURATION),7) AS 期间
, CONCAT(ROUND(SUM(DURATION)/*100,3), '%') AS 百分比
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID= GROUP BY STATE ORDER BY SEQ err:java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual ERROR DURATION
java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual ERROR DURATION
at org.opencloudb.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:54)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:76)
at org.opencloudb.route.RouteService.route(RouteService.java:133)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:224)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:175)
at org.opencloudb.server.handler.SelectHandler.handle(SelectHandler.java:106)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:77)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:308)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71)
at org.opencloudb.net.FrontendConnection.rawHandle(FrontendConnection.java:435)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:417)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:313)
at org.opencloudb.net.NIOSocketWR.asynRead(NIOSocketWR.java:186)
at org.opencloudb.net.AbstractConnection.asynRead(AbstractConnection.java:274)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:100)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual ERROR DURATION
at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:1471)
at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:693)
at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:658)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:480)
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:555)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:166)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:119)
at com.alibaba.druid.sql.parser.SQLExprParser.exprList(SQLExprParser.java:789)
at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:690)
at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:658)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:480)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectItem(SQLSelectParser.java:298)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:275)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:127)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:60)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:196)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:145)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:1658)
at org.opencloudb.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:50)
... 15 more
11/16 15:49:35.067 WARN [$_NIOREACTOR-2-RW] (DruidSelectParser.java:393) - find no Route:SELECT * FROM testdb1
.goods
LIMIT 0
11/16 15:49:35.067 WARN [$_NIOREACTOR-2-RW] (ServerConnection.java:232) -ServerConnection [id=14, schema=TESTDB, host=10.20.37.67, user=test,txIsolation=3, autocommit=true, schema=TESTDB]SELECT * FROM testdb1
.goods
LIMIT 0 err:java.sql.SQLNonTransientException: find no Route:SELECT * FROM testdb1
.goods
LIMIT 0
java.sql.SQLNonTransientException: find no Route:SELECT * FROM testdb1
.goods
LIMIT 0
at org.opencloudb.parser.druid.impl.DruidSelectParser.tryRoute(DruidSelectParser.java:394)
at org.opencloudb.parser.druid.impl.DruidSelectParser.changeSql(DruidSelectParser.java:257)
at org.opencloudb.parser.druid.impl.DefaultDruidParser.parser(DefaultDruidParser.java:66)
at org.opencloudb.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:64)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:76)
at org.opencloudb.route.RouteService.route(RouteService.java:133)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:224)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:175)
at org.opencloudb.server.handler.SelectHandler.handle(SelectHandler.java:106)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:77)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:308)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71)
at org.opencloudb.net.FrontendConnection.rawHandle(FrontendConnection.java:435)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:417)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:313)
at org.opencloudb.net.NIOSocketWR.asynRead(NIOSocketWR.java:186)
at org.opencloudb.net.AbstractConnection.asynRead(AbstractConnection.java:274)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:100)
at java.lang.Thread.run(Thread.java:744)
[root@localhost mycat]# cat version.txt
BuildTime 2015-05-27 01:55:20
GitVersion e3bf75fcd5ce5b12a40c1ce30805aed6c30f2c04
MavenVersion 1.3.0.3-release
GitUrl https://github.com/MyCATApache/Mycat-Server.git
MyCatSite http://www.mycat.org.cn
QQGroup 106088787
请问下版本几支持预处理呢?
1.示例mysql
mysql上有test.OneTest 这样子包含大写字母的表,在映射到mycat之后会自动变成onetest
导致在mycat上找不到这个表的数据
2.示例mongo
在mongo的表关系映射上也存在这个问题。
请问这个如何解决?
在权威指南“入门篇-第四章-服务启动与配置”这里说是可以支持集群的,但是在“第11章常见问题与解决方案有说不支持集群”,刚刚接触,还有实践,不知道是否支持集群,另外“第10章--权限控制和多租户”这两个地方没明白说些什么
入门指南\sequnce-sql.txt第13行 SET retval="-999999999,null"; 步进值不能是null 如果MYCAT_SEQUENCE中没有对应的记录 返回null 程序解析为int类型是会抛出NumberFormatException异常
版本 - Mycat-server-1.4-RELEASE-20150901112004-linux.tar.gz
schema.xml配置文件内容:
<schema name="test_sns" checkSQLschema="false" sqlMaxLimit="100">
<table name="sns_user_relation" primaryKey="_id" autoIncrement="true" dataNode="dn$1-8" rule="rule2" />
</schema>
<dataNode name="dn$1-4" dataHost="host1" database="db$1-4" />
<dataNode name="dn$5-8" dataHost="host2" database="db$5-8" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1M1" url="192.168.6.211:3306" user="root"
password="123456">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host2M1" url="192.168.6.210:3306" user="root"
password="123456">
</writeHost>
</dataHost>
在运行insert into语句的时候报错:
mysql> insert into sns_user_relation (user_id, rel_id, rel_type, is_valid, create_time, update_time, group_id, uid) values (149,145,3,1,NOW(),NOW(),0,7001317);
ERROR 1003 (HY000): mycat sequnce err.java.lang.NumberFormatException: null
日志错误:
09/23 23:14:54.712 ERROR Thread-1 -MyCATSequenceProcessor.executeSeq(SesionSQLPair)
java.lang.NumberFormatException: null
at java.lang.Long.parseLong(Long.java:404)
at java.lang.Long.parseLong(Long.java:483)
at org.opencloudb.sequence.handler.IncrSequenceHandler.nextId(IncrSequenceHandler.java:62)
at org.opencloudb.parser.druid.DruidSequenceHandler.getExecuteSql(DruidSequenceHandler.java:58)
at org.opencloudb.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:84)
at org.opencloudb.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:18)
at org.opencloudb.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:104)
Caused by: java.lang.ClassNotFoundException: io.mycat.route.function.PartitionByString
为什么会找不到呢 ???不是1.6版本 是1.5.1版本
系统:ubuntu16.04,mysql 5.7
按照mycat-doc下的入门两节点配置,1.4-release和1.5-release配置完成,可以正常启动,但是总是出现
ERROR 1003 (HY000): Unsupported statement
jdk环境
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)
<schema name="xhg_mycat" checkSQLschema="false" sqlMaxLimit="100">
<!-- 表分片配置在这些 -->
<table name="t_consumer" primaryKey="id" autoIncrement="true" dataNode="dn1"/>
<table name="t_event_error" primaryKey="id" dataNode="dn1"/>
<table name="t_event_receive" primaryKey="id" dataNode="dn1"/>
<table name="t_event_source" primaryKey="id" dataNode="dn1"/>
</schema>
t_consumer 配置了 autoIncrement="true" ,插入的时候报错,不配置这项就可以正常运行
mysql> insert into t_consumer(created_time,name
,name2) values(now(),'aa','aa');
ERROR 1064 (HY000): com.alibaba.druid.sql.parser.ParserException: syntax error
mycat 日志
2018-07-20 17:50:10.414 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.net.FrontendConnection.query(FrontendConnection.java:288)) - ServerConnection [id=1, schema=xhg_mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=xhg_mycat] insert into t_consumer(created_time,
name,name2) values(now(),'aa','aa') 2018-07-20 17:50:10.414 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:57)) - ServerConnection [id=1, schema=xhg_mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=xhg_mycat]insert into t_consumer(created_time,
name,name2) values(now(),'aa','aa') 2018-07-20 17:50:10.415 ERROR [Thread-1] (io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:73)) - DruidMycatRouteStrategyError com.alibaba.druid.sql.parser.ParserException: syntax error at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseValueClause(MySqlStatementParser.java:2132) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:2022) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:193) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:199) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:2065) ~[druid-1.0.26.jar:1.0.26] at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:70) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:81) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.RouteService.route(RouteService.java:133) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:273) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:87) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:19) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:110) ~[Mycat-server-1.6-RELEASE.jar:?] 2018-07-20 17:50:10.415 WARN [Thread-1] (io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:281)) - ServerConnection [id=1, schema=xhg_mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=xhg_mycat]insert into t_consumer(ID,created_time,
name,name2) values( 10002,now() err:java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: syntax error java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: syntax error at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:74) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:81) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.RouteService.route(RouteService.java:133) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:273) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:87) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:19) ~[Mycat-server-1.6-RELEASE.jar:?] at io.mycat.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:110) ~[Mycat-server-1.6-RELEASE.jar:?] Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseValueClause(MySqlStatementParser.java:2132) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:2022) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:193) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:199) ~[druid-1.0.26.jar:1.0.26] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:2065) ~[druid-1.0.26.jar:1.0.26] at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:70) ~[Mycat-server-1.6-RELEASE.jar:?]
表结构
CREATE TABLE `t_consumer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(105) NOT NULL DEFAULT '' COMMENT '名字',
`name2` varchar(106) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10019 DEFAULT CHARSET=utf8mb4;
我在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中增加了
sql:
select id,user,age,name from users having id>10 and age>20;
error result:
java.lang.NullPointerException;
sql:
select id,user,age,name from users having id>10;
result is fine
全局表left join 关联分片表,在以下情况时会出现重复数据:全局表有记录,对应的分片表中没有记录。
按照一般情况,会输出全局表的数据,分片表字段均为null,但在mycat 1.5GA版本中,会出现N条重复记录,N=全局表存在的库的数目。
加入注解,可以解决数据重复问题。但是只是临时方案,请在以后版本中予以解决。感谢
现在阿里云的同步节点只能支持这个版本,造成无法直接使用mycat进行同步
1.版本信息:1.3.0.2
2.解析器:fdbparser
3.问题sql语句:(ps:select的是单表的全部列名)
select
id,
create_time,
update_time,
version,
des_url,
duration,
file_id,
header,
segment_index,
location,
md5,
size,
status
from
mams_video_file_segment
where
file_id=2006267423
and status=1
order by
segment_index asc;
4.异常信息:
WARN BusinessExecutor1 -data Merge error:
java.lang.ArrayIndexOutOfBoundsException: 0 >= 0
at java.util.Vector.elementAt(Vector.java:470)
at org.opencloudb.mpp.tmp.MaxHeap.getRoot(MaxHeap.java:61)
at org.opencloudb.mpp.tmp.MaxHeap.addIfRequired(MaxHeap.java:83)
at org.opencloudb.mpp.tmp.RowDataSorter.addRow(RowDataSorter.java:54)
at org.opencloudb.mpp.DataMergeService.handleRowData(DataMergeService.java:237)
at org.opencloudb.mpp.DataMergeService.access$200(DataMergeService.java:55)
at org.opencloudb.mpp.DataMergeService$2.run(DataMergeService.java:263)
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 1.5 加字段mysql> alter table orders add msg varchar(50);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
影响的记录不显示 ,
单独在MYSQL操作会显示影响的记录数
mysql> alter table orders add msg varchar(50);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0 情况本来就是这样的吗
192.168.162.46 manager datanode3 --也是mycat 安装节点
192.168.162.25 test-db1-huaj datanode1
192.168.162.26 test-db2-huaj datanode2
11/15 11:41:40.295 WARN [$_NIOREACTOR-5-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
11/15 11:41:40.295 INFO [$_NIOREACTOR-5-RW] (SQLJob.java:111) -can't get connection for sql :select user()
11/15 11:41:40.296 INFO [$_NIOREACTOR-5-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
11/15 11:41:40.296 WARN [$_NIOREACTOR-6-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
11/15 11:41:40.296 INFO [$_NIOREACTOR-6-RW] (SQLJob.java:111) -can't get connection for sql :select user()
11/15 11:41:40.296 INFO [$_NIOREACTOR-6-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for test-db1-huaj of schema testdb2
11/15 11:41:40.297 INFO [$_NIOREACTOR-6-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
11/15 11:41:40.298 WARN [$_NIOREACTOR-7-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
11/15 11:41:40.299 INFO [$_NIOREACTOR-7-RW] (SQLJob.java:111) -can't get connection for sql :select user()
11/15 11:41:40.299 INFO [$_NIOREACTOR-7-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
[DEBUG][Thread-1] get next segement of sequence from db for sequnce:USER_INFO curVal 0 (io.mycat.route.sequence.handler.IncrSequenceMySQLHandler:IncrSequenceMySQLHandler.java:113)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:19,953 [DEBUG][Thread-1] execute in datanode cashtoutiaoDn1 for fetch sequnce sql SELECT mycat_seq_nextval('USER_INFO') (io.mycat.route.sequence.handler.FetchMySQLSequnceHandler:IncrSequenceMySQLHandler.java:151)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:19,953 [DEBUG][Thread-1] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:96)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:19,953 [DEBUG][Thread-1] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:127)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:19,953 [WARN ][Thread-1] executeException java.io.UnsupportedEncodingException: unsupported yet (io.mycat.route.sequence.handler.FetchMySQLSequnceHandler:IncrSequenceMySQLHandler.java:237)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:20,355 [DEBUG][Timer0] JDBCHeartBeat con query sql: select user() (io.mycat.backend.jdbc.JDBCHeartbeat:JDBCHeartbeat.java:109)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:20,358 [DEBUG][Timer0] JDBCHeartBeat con query sql: select user() (io.mycat.backend.jdbc.JDBCHeartbeat:JDBCHeartbeat.java:109)
INFO | jvm 1 | 2018/03/12 19:06:20 | 2018-03-12 19:06:20,361 [DEBUG][Timer0] JDBCHeartBeat con query sql: select user() (io.mycat.backend.jdbc.JDBCHeartbeat:JDBCHeartbeat.java:109)
INFO | jvm 1 | 2018/03/12 19:06:30 | 2018-03-12 19:06:29,965 [ERROR][Thread-1] MyCATSequenceProcessor.executeSeq(SesionSQLPair) java.lang.RuntimeException: can't fetch sequnce in db,sequnce :USER_INFO detail:java.io.UnsupportedEncodingException: unsupported yet
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.sequence.handler.IncrSequenceMySQLHandler.getSeqValueFromDB(IncrSequenceMySQLHandler.java:125)
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.sequence.handler.IncrSequenceMySQLHandler.nextId(IncrSequenceMySQLHandler.java:94)
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.parser.druid.DruidSequenceHandler.getExecuteSql(DruidSequenceHandler.java:64)
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:85)
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:19)
INFO | jvm 1 | 2018/03/12 19:06:30 | at io.mycat.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:110)
INFO | jvm 1 | 2018/03/12 19:06:30 | (io.mycat.route.MyCATSequnceProcessor:MyCATSequnceProcessor.java:89)
server.xml 里面配置了1
表user_info配置了主键自增,schema.xml 中配置了
一直报上面的错误,希望给予指导
走了一些debug的流程,代码有点多,而且貌似跟8.3的方法有重叠交叉的部分,比较困惑
我现在有3个分区,
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
现在想指定MyCat查询的时候走第0个分区,下面的语句不起作用
explain /*!mycat:sql=select 0 */select * from t_ticket_detail where PK_SID >= 1000
请问要怎么实现呢?
rt
<dataHost name="shard1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
表结构
CREATE TABLE `t_consumer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(105) NOT NULL DEFAULT '' COMMENT '名字',
`name2` varchar(106) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10019 DEFAULT CHARSET=utf8mb4;
当配置 dbDriver="jdbc" 时,varchar 类型长度小于106 ,查询结果会显示不全
Navicat 查询
mycat控制台查询
mysql> select * from t_consumer;
mysql> select * from t_consumer;
+-------+-----------------------+------------------------------------------------------------------+--------------------------------------+
| id | created_time | name | name2 |
+-------+-----------------------+------------------------------------------------------------------+--------------------------------------+
| 1 | NULL | 7febf94428054cd9a3163c27368e90067febf94428054cd9a3163c27368e9006 | 7febf944-2805-4cd9-a316-3c27368e9006 |
| 2 | NULL | 7febf944-2805-4cd9-a316-3c27368e9006 | 7febf944-2805-4cd9-a316-3c27368e9006 |
name 那列显示会有问题
将 dbDriver="jdbc" 时 修改成 dbDriver="native" 就可以正常显示
在生产部署或者相关的数据库迁移指南的地方加上这些友情提醒吧,能避免一些错误的发生。
mycat不支持lock tables,导入的时候必须要指定列名。
而mysqldump导出的数据中默认没有列名,且在写入前lock tables,写入后unlock tables。
链接
文档2.2解压中mkdir写成了mkdri
我现在用最新server源码, mvn package报错。。。
[ERROR] Failed to execute goal org.codehaus.mojo:buildnumber-maven-plugin:1.3:create (default) on project Mycat-server: Execution default of goal org.codehaus.mojo:buildnumber-maven-plugin:1.3:create failed: Plugin org.codehaus.mojo:buildnumber-maven-plugin:1.3 or one of its dependencies could not be resolved: Failed to collect dependencies at org.codehaus.mojo:buildnumber-maven-plugin:jar:1.3 -> org.apache.maven.scm:maven-scm-provider-gitexe:jar:RELEASE: Failed to read artifact descriptor for org.apache.maven.scm:maven-scm-provider-gitexe:jar:RELEASE: Failed to resolve version for org.apache.maven.scm:maven-scm-provider-gitexe:jar:RELEASE: Could not find metadata org.apache.maven.scm:maven-scm-provider-gitexe/maven-metadata.xml in local (C:\Users\Administrator.m2\repository) -> [Help 1]
大佬 怎么加群啊 文档提供的 QQ 群 加不进去了撒
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.