mycat常见问题集锦

MyCAT


摘要：MyCAT常见问题与解决过程记录

1.4版本BUG

mycat版本：1.4-RELEASE
运行环境：线上库
报错内容：

04/21 21:50:01.156   WARN [$_MyCatServer] (NIOAcceptor.java:121) -$_MyCatServer
java.lang.ArrayIndexOutOfBoundsException: 9
        at org.opencloudb.net.NIOReactorPool.getNextReactor(NIOReactorPool.java:22)
        at org.opencloudb.net.NIOAcceptor.accept(NIOAcceptor.java:117)
        at org.opencloudb.net.NIOAcceptor.run(NIOAcceptor.java:91)

紧急解决：这个报错导致客户端无法正常连接，重启mycat后恢复正常！
永久解决：这是1.4的BUG，建议升级到1.5。

错误号1105

mycat版本：1.5.1-RELEASE
运行环境：开发库
报错内容：

mysql> select * from e_detail;
ERROR 1105 (HY000): java.lang.ArrayIndexOutOfBoundsException: 80
mycat日志：
04/24 16:14:19.368   WARN [BusinessExecutor3] (MultiNodeQueryHandler.java:484) -caught exception 
java.lang.ArrayIndexOutOfBoundsException
04/24 16:14:19.369   INFO [BusinessExecutor3] (AbstractConnection.java:458) -close connection,reason:java.lang.ArrayIndexOutOfBoundsException ,MySQLConnection [id=27389, lastTime=1493021659154, user=xldev, schema=xueledb125, old shema=xueledb125, borrowed=true, fromSlaveDB=false, threadId=3560685, charset=utf8, txIsolation=3, autocommit=true, attachment=dn249{SELECT *
FROM e_detail

现象补充：当执行select count(*) from e_detail时没报错，只有这个表执行select *的时候会报错。其他表select *都没问题！
造成原因：各个分片中的表结构字段数量不一致！
解决办法：
1、找到不一致的分片加上遗漏的字段
2、直接在mycat上drop掉这个表，重新创建该表（由于开发库数据无所谓，固采用此法）

异常（not support token:IDENTIFIER）

mycat版本：1.4.1-dev
运行环境：线上全站考核库
业务场景：开发反馈将hive输出文件导入mycat时，某个文件导入不了。
查看mycat的wrapper.log报错如下：

INFO   | jvm 1    | 2017/04/28 17:17:51 | Exception in thread "BusinessExecutor18987" com.alibaba.druid.sql.parser.ParserException: not support token:IDENTIFIER
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:698)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:658)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:480)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:555)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:166)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:119)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseLoadDataInFile(MySqlStatementParser.java:1851)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseLoad(MySqlStatementParser.java:1675)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseStatementListDialect(MySqlStatementParser.java:582)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:313)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:121)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:116)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at org.opencloudb.stat.HighFrequencySqlAnalyzer$SQLParser.mergeSql(HighFrequencySqlAnalyzer.java:202)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at org.opencloudb.stat.HighFrequencySqlAnalyzer.onQuery(HighFrequencySqlAnalyzer.java:46)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at org.opencloudb.stat.QueryResultDispatcher$1.run(QueryResultDispatcher.java:51)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
INFO   | jvm 1    | 2017/04/28 17:17:51 |       at java.lang.Thread.run(Thread.java:745)

造成原因：只看这个报错提示可能觉得不够友好，没办法看出是什么问题。通过测试导入的文件发现数据已经存在数据库中了。说明是由于主键id冲突导致这个错。经开发确认确实是delete语句的where条件写错日期范围了。导致部分数据没删掉！
解决办法：删掉重复的数据再次导入即可。

插入数据后无法正常路由查询

开发有个需求，需要向mycat中插入一条数据，insert语句如下：

INSERT INTO t_disk (disk_id,creator,user_id,unit_id,file_pk,file_uri,file_type,NAME,description,extension,ext_type,ext_icon_type,size,add_time,update_time,sticky_status,share_status,share_time,audit_instructions,collect_status,pid,sch_id,STATUS,is_province,resource_id) 
VALUES(  'abcdb0a3233249c59ea463d2ff1d5627',  '1000110004',  '1000110004',  '010003001001001001001',  '156754d9cf9a5dab2fc07ee37043aa72',  '156754d9cf9a5dab2fc07ee37043aa72',  4,  '智慧课堂测试大文件500M',  '',  'mp4',  4,  'video',  '50000000',  NOW(),  NOW(),  0,  0,  NOW(),  '',  0,  '',  '' '10001',  1,  0,  '') ;

开发说已在开发库执行过了，没报错。到线上mycat执行后也提示成功插入一条数据。可是当根据分片字段（sch_id）去查的时候却查不到数据：

SELECT * FROM t_disk WHERE disk_id='abcdb0a3233249c59ea463d2ff1d5627' AND sch_id='10001';
没有数据返回

只根据主键查询的时候可以查到一条数据：

SELECT * FROM t_disk WHERE disk_id='abcdb0a3233249c59ea463d2ff1d5627';
返回一条数据

而且这条数据查询到的sch_id确实是10001，可是为什么通过mycat无法查到呢？
先看看根据分片查询语句的路由信息：

ct_user@ctdb 05:36:55>explain SELECT * FROM t_disk WHERE disk_id='abcdb0a3233249c59ea463d2ff1d5627' AND sch_id='10001';
+-----------+----------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                |
+-----------+----------------------------------------------------------------------------------------------------+
| xldn55    | SELECT * FROM t_disk WHERE disk_id='abcdb0a3233249c59ea463d2ff1d5627' AND sch_id='10001' |
+-----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到该SQL去节点xldn55上查询数据，结果没查到！
再来查看下insert语句的路由信息：

ct_user@ctdb 05:31:25>EXPLAIN
    -> INSERT INTO t_disk (disk_id,creator,user_id,unit_id,file_pk,file_uri,file_type,NAME,description,extension,ext_type,ext_icon_type,size,add_time,update_time,sticky_status,share_status,share_time,audit_instructions,collect_status,pid,sch_id,STATUS,is_province,resource_id) 
    -> VALUES(  'abcdb0a3233249c59ea463d2ff1d5627',  '1000110004',  '1000110004',  '010003001001001001001',  '156754d9cf9a5dab2fc07ee37043aa72',  '156754d9cf9a5dab2fc07ee37043aa72',  4,  '智慧课堂测试大文件500M',  '',  'mp4',  4,  'video',  '50000000',  NOW(),  NOW(),  0,  0,  NOW(),  '',  0,  '',  '' '10001',  1,  0,  '') ;
+-----------+----------------------------------------------------------------------------------------------------
| DATA_NODE | SQL  
----------------------------------------------------------------------------------------------------------------+
| xldn145   | INSERT INTO t_disk (disk_id,creator,user_id,unit_id,file_pk,file_uri,file_type,NAME,description,extension,ext_type,ext_icon_type,size,add_time,update_time,sticky_status,share_status,share_time,audit_instructions,collect_status,pid,sch_id,STATUS,is_province,resource_id)  VALUES(  'abcdb0a3233249c59ea463d2ff1d5627',  '1000110004',  '1000110004',  '010003001001001001001',  '156754d9cf9a5dab2fc07ee37043aa72',  '156754d9cf9a5dab2fc07ee37043aa72',  4,  '智慧课堂测试大文件500M',  '',  'mp4',  4,  'video',  '50000000',  NOW(),  NOW(),  0,  0,  NOW(),  '',  0,  '',  '' '10001',  1,  0,  '')          |
+-----------+----------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

路由显示其存储节点是xldn145，竟然不是同一个分片，这就难怪查询不到了。仔细查看insert语句，发现对应sch_id字段的值是:

'' '10001'

该字符串路由到的分片节点和’10001’肯定是不同的。
解决办法很简单：根据主键删掉该条数据，然后插入正确的值即可！

mycat不支持的SQL写法

开发同事使用开源的Quartz Job Scheduling来做定时任务调度模块，使用过程中发现一些sql写法不支持，如：

mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND NOT (MISFIRE_INSTR = -1); 
+----------------+
| NEXT_FIRE_TIME |
+----------------+
|  1495116120000 |
|  1495292400000 |
+----------------+
2 rows in set (0.00 sec)
mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND NOT (MISFIRE_INSTR = -1) AND NEXT_FIRE_TIME < 1495116120000; 
+----------------+
| NEXT_FIRE_TIME |
+----------------+
|  1495116120000 |
|  1495292400000 |
+----------------+
2 rows in set (0.00 sec)
mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND NOT (MISFIRE_INSTR = -1) AND NEXT_FIRE_TIME > 1495116120000; 
+----------------+
| NEXT_FIRE_TIME |
+----------------+
|  1495116120000 |
|  1495292400000 |
+----------------+
2 rows in set (0.01 sec)

发现无论AND NOT后面跟任何条件都不生效，将NOT (MISFIRE_INSTR = -1)改写为MISFIRE_INSTR != -1查询返回结果正常：

mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME < 1495116120000; 
Empty set (0.00 sec)
mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME = 1495116120000; 
+----------------+
| NEXT_FIRE_TIME |
+----------------+
|  1495116120000 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT NEXT_FIRE_TIME FROM qrtz_TRIGGERS WHERE SCHED_NAME ='XueleJobScheduler' AND MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME > 1495116120000; 
+----------------+
| NEXT_FIRE_TIME |
+----------------+
|  1495292400000 |
+----------------+
1 row in set (0.00 sec)

mycat1.5不支持sqoop工具

使用sqoop将hive数据导入mysql时，sqoop工具会去读取information_schema下面的一些系统表来获取表的字段情况。SQL如下：

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = (SELECT SCHEMA())   AND TABLE_NAME = 'a_table';

mycat1.5环境中报错说mycat中未定义该表：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 158336
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = (SELECT SCHEMA())   AND TABLE_NAME = 'a_table'; 
ERROR 1064 (HY000): can't find table define in schema INFORMATION_SCHEMA.COLUMNS schema:a_db
mysql> 
mysql> exit
Bye

切到mycat1.4环境正常：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.8-mycat-1.4-RELEASE-20150901112004 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = (SELECT SCHEMA())   AND TABLE_NAME = 'a_table'; 
+--------------+
| COLUMN_NAME  |
+--------------+
| accs_id      |
| class_id     |
| score        |
| user_id      |
| user_name    |
| icon         |
| user_score   |
| school_rank  |
| area_rank    |
| global_rank  |
| monthsubject |
| grade        |
+--------------+
12 rows in set (0.03 sec)
mysql> 
mysql>

如果一定要用mycat1.5就需要将INFORMATION_SCHEMA.COLUMNS配置到mycat中才可以。

本文标题:mycat常见问题集锦

文章作者:一条大河

发布时间:2017-04-24, 16:42:55

最后更新:2017-07-13, 16:35:52

原始链接:http://riverdba.github.io/2017/04/24/mycat-faq/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 1.4版本BUG
  2. 2. 错误号1105
  3. 3. 异常（not support token:IDENTIFIER）
  4. 4. 插入数据后无法正常路由查询
  5. 5. mycat不支持的SQL写法
  6. 6. mycat1.5不支持sqoop工具
|