此版本Mycat-server-1.6.7.6-release.jar不支持MySQL MAX()函数

[3721518]

在Mycat查询后端MySQL某个库的某张表并且根据id使用max()函数计算时，前端返回异常如下---> 2013 - Lost connection to MySQL server during query 使用的版本： Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz mycat.log日志报错如下：

2021-08-30 15:47:06.575 WARN [$_NIOREACTOR-1-RW] (io.mycat.net.NIOReactor$RW.run(NIOReactor.java:118)) - caught err: java.lang.ArrayIndexOutOfBoundsException: 11 at com.alibaba.druid.sql.parser.SQLExprParser.getAggregateFunction(SQLExprParser.java:2131) ~[druid-1.2.6.jar:1.2.6] at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:1565) ~[druid-1.2.6.jar:1.2.6] at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:5553) ~[druid-1.2.6.jar:1.2.6] at io.mycat.route.parser.druid.MycatExprParser.parseSelectItem(MycatExprParser.java:31) ~[Mycat-server-1.6.7.6-release.jar:?] at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:982) ~[druid-1.2.6.jar:1.2.6] at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:239) ~[druid-1.2.6.jar:1.2.6] at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:378) ~[druid-1.2.6.jar:1.2.6] at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:61) ~[druid-1.2.6.jar:1.2.6] at io.mycat.route.parser.druid.MycatStatementParser.parseSelect(MycatStatementParser.java:39) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.route.parser.druid.MycatStatementParser.parseSelect(MycatStatementParser.java:15) ~[Mycat-server-1.6.7.6-release.jar:?] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:4518) ~[druid-1.2.6.jar:1.2.6] at io.mycat.server.util.SchemaUtil.parseSchema(SchemaUtil.java:24) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.server.ServerConnection.execute(ServerConnection.java:275) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.server.handler.SelectHandler.handle(SelectHandler.java:129) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:95) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.FrontendConnection.query(FrontendConnection.java:397) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.FrontendConnection.query(FrontendConnection.java:417) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:76) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.FrontendConnection.rawHandle(FrontendConnection.java:591) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.FrontendConnection.handle(FrontendConnection.java:573) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.AbstractConnection.onReadData(AbstractConnection.java:338) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.NIOSocketWR.asynRead(NIOSocketWR.java:216) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.AbstractConnection.asynRead(AbstractConnection.java:290) ~[Mycat-server-1.6.7.6-release.jar:?] at io.mycat.net.NIOReactor$RW.run(NIOReactor.java:113) [Mycat-server-1.6.7.6-release.jar:?] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_162] 2021-08-30 15:47:06.579 INFO [$_NIOREACTOR-1-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:531)) - close connection,reason:program err:java.lang.ArrayIndexOutOfBoundsException: 11 ,ServerConnection [id=3, schema=ali_cloud, host=10.10.81.249, user=mycat_user,txIsolation=3, autocommit=true, schema=ali_cloud, executeSql=SELECT a.*, sum( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.reruns_number, 0 ) ELSE 0 END ) AS rerunsNumber, sum( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.reruns_weight, 0 ) ELSE 0 END ) AS rerunsWeight, max( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.if_check_reruns, 0 ) ELSE 0 END ) AS ifCheckreruns, sum( ifnull( t_reces_detial_rewords.rewirks_num, 0 ) ) AS rewirksNum, sum( ifnull( t_reces_detial_rewords.rewirks_weight, 0 ) ) AS rewirksWeight, sum( ifnull( t_reces_detial_rewords.no_rewirks_num, 0 ) ) AS norewirksNum, sum( ifnull( t_reces_detial_rewords.no_rewirks_weight, 0 ) ) AS norewirksWeight FROM .......

使用的SQL语句 select max(id) from t_area_reruns;

[junwen12221]

SELECT
a.*,
sum( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.reruns_number, 0 ) ELSE 0 END ) AS rerunsNumber,
sum( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.reruns_weight, 0 ) ELSE 0 END ) AS rerunsWeight,
max( CASE WHEN t_area_reruns.status = 0 THEN ifnull( t_area_reruns_detial.if_check_reruns, 0 ) ELSE 0 END ) AS ifCheckreruns,
sum( ifnull( t_reces_detial_rewords.rewirks_num, 0 ) ) AS rewirksNum,
sum( ifnull( t_reces_detial_rewords.rewirks_weight, 0 ) ) AS rewirksWeight,
sum( ifnull( t_reces_detial_rewords.no_rewirks_num, 0 ) ) AS norewirksNum,
sum( ifnull( t_reces_detial_rewords.no_rewirks_weight, 0 ) ) AS norewirksWeight
FROM

这种复杂sql支持不了

[3721518]

@junwen12221 我执行简单的select 带上max函数也是不支持的，select max(id) from t_area_reruns。这很简单了

[3721518]

@junwen12221 我又使用了此版本：Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz，是支持我上面的复杂SQL的。这是真实业务场景，还请见谅。 mycat jdbc driver：mysql-connector-java-5.1.47.jar MySQL Replication Version：MySQL-8.0.20

[junwen12221]

可能是升级druid版本导致的bug

[3721518]

@junwen12221 @funnyAnt 我特别需要使用Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz这个版本，因为它解决了#2733和#2749向这样的多个bug 请问开发者那边能尽快解决这个问题吗，或者可以提供更新的修复版本吗？

[junwen12221]

http://dl.mycat.org.cn/1.6.7.6/20210831141727/

测试一下

[junwen12221]

怀疑是其他sql影响了,并不是不支持max.我的环境没有问题

[3721518]

已经测试了此版本http://dl.mycat.org.cn/1.6.7.6/20210831141727并且已经修复，非常感谢您的及时回复和修复，谢谢！！！

[3721518]

我把mycat日志输出为debug模式，把上面的稍微复杂的sql给单独拎出来执行是没问题的，只要查询带max的就报错，起初我们以为数组索引或某个字段的类型不支持，但我们改了并没有作用！

[junwen12221]

应该是这个sql执行的时候,客户端发出的额外的sql影响的

[junwen12221]

现在已经知道原因了,大概是druid前后版本不兼容.在没有use schema的时候会尝试解析schema导致的异常

[junwen12221]

使用mycat要总是设置schema

[junwen12221]

http://dl.mycat.org.cn/1.6.7.6/20210831141727/Mycat-server-1.6.7.6-release-20210831170644-linux.tar.gz 这个进一步修复了

[3721518]

@junwen12221 已经在第一时间使用了最新修复版本20210831170644，目前使用没什么问题。

[3721518]

设置此参数fakeMySQLVersion 为8.0.20，writeHost里定义的用户加密后无法解密；

[junwen12221]

这个可能与客户端的连接协议有关系