search

MyCATApache / Mycat-Server

GNU General Public License v2.0
9.51k stars 3.85k forks source link

命令行mysql -u* -p* 方式进入mycat客户端，在use db时，mycat.log大量报库表找不到的错误 #2798

Open andrewzhuxu opened 3 years ago

andrewzhuxu commented 3 years ago

1、bug描述 1）命令行方式进入mycat客户端 mysql -uuser -ppwd -P8079 -h192.168.201.14 mycat.log中，出现大量配置的库表不匹配，找不到表的报错信息。 如：executeSql=select @@command bdmc_study_env_interact_stat ]Table 'teach_202103.bdmc_study_env_interact_stat' doesn't exist 其中表bdmc_study_env_interact_stat，配置上属于dataNode name="defaultdn" dataHost="dbhost3306" database="big_screen" ；不属于teach_202103库。 感觉做了dataNode间的交叉查询测试似的。

错误日志1： 2021-06-02 17:20:57.453 WARN [$_NIOREACTOR-26-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:287)) - execute sql err : errno:1146 Table 'teach_202103.bdmc_study_env_interact_stat' doesn't exist con:MySQLConnection@214712518 [id=58, lastTime=1622625657450, user=user_teach, schema=teach_202103, old shema=teach_202103, borrowed=true, fromSlaveDB=false, threadId=394113, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=tdn_202103{select @@command bdmc_study_env_interact_stat}, respHandler=CommandExecResultHandler [node=tdn_202103{select @@command bdmc_study_env_interact_stat}, table=bdmc_study_env_interact_stat], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@1ca91f33, writeQueue=0, modifiedSQLExecuted=true] frontend host:192.168.201.14/4718/user_teach 2021-06-02 17:20:57.454 WARN [$_NIOREACTOR-26-RW] (io.mycat.backend.mysql.nio.MySQLConnection.release(MySQLConnection.java:693)) - can't sure connection syn result,so close it MySQLConnection@214712518 [id=58, lastTime=1622625657450, user=user_teach, schema=teach_202103, old shema=teach_202103, borrowed=true, fromSlaveDB=false, threadId=394113, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=CommandExecResultHandler [node=tdn_202103{select @@command bdmc_study_env_interact_stat}, table=bdmc_study_env_interact_stat], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@1ca91f33, writeQueue=0, modifiedSQLExecuted=true] 2021-06-02 17:20:57.454 INFO [$_NIOREACTOR-26-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:531)) - close connection,reason:syn status unkown ,MySQLConnection@214712518 [id=58, lastTime=1622625657450, user=user_teach, schema=teach_202103, old shema=teach_202103, borrowed=true, fromSlaveDB=false, threadId=394113, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=null, host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@1ca91f33, writeQueue=0, modifiedSQLExecuted=true] 2021-06-02 17:20:57.455 ERROR [$_NIOREACTOR-26-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:231)) - ServerConnection [id=1, schema=teach_bigdata, host=192.168.201.14, user=user_teach,txIsolation=3, autocommit=true, schema=teach_bigdata, executeSql=select @@command bdmc_study_env_interact_stat ]Table 'teach_202103.bdmc_study_env_interact_stat' doesn't existjava.lang.Thread .getStackTrace1552 io.mycat.net.FrontendConnection .getStack245 io.mycat.net.FrontendConnection .writeErrMessage231 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .backConnectionErr314 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .errorResponse275 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleErrorPacket220 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleData111 io.mycat.net.handler.BackendAsyncHandler .offerData36 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handle85 io.mycat.net.AbstractConnection .handle280 io.mycat.net.AbstractConnection .onReadData338 io.mycat.net.NIOSocketWR .asynRead216 io.mycat.net.AbstractConnection .asynRead290 io.mycat.net.NIOReactor$RW .run113 java.lang.Thread .run745 write errorMsg:{} error 2021-06-02 17:20:57.457 WARN [$_NIOREACTOR-28-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:287)) - execute sql err : errno:1146 Table 'teach_202006.bdmc_study_env_interact_stat_county_day' doesn't exist con:MySQLConnection@706441148 [id=28, lastTime=1622625657450, user=user_teach, schema=teach_202006, old shema=teach_202006, borrowed=true, fromSlaveDB=false, threadId=393960, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=tdn_202006{select @@command bdmc_study_env_interact_stat_county_day}, respHandler=CommandExecResultHandler [node=tdn_202006{select @@command bdmc_study_env_interact_stat_county_day}, table=bdmc_study_env_interact_stat_county_day], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@574d711c, writeQueue=0, modifiedSQLExecuted=true] frontend host:192.168.201.14/4718/user_teach 2021-06-02 17:20:57.457 WARN [$_NIOREACTOR-28-RW] (io.mycat.backend.mysql.nio.MySQLConnection.release(MySQLConnection.java:693)) - can't sure connection syn result,so close it MySQLConnection@706441148 [id=28, lastTime=1622625657450, user=user_teach, schema=teach_202006, old shema=teach_202006, borrowed=true, fromSlaveDB=false, threadId=393960, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=CommandExecResultHandler [node=tdn_202006{select @@command bdmc_study_env_interact_stat_county_day}, table=bdmc_study_env_interact_stat_county_day], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@574d711c, writeQueue=0, modifiedSQLExecuted=true] 2021-06-02 17:20:57.457 INFO [$_NIOREACTOR-28-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:531)) - close connection,reason:syn status unkown ,MySQLConnection@706441148 [id=28, lastTime=1622625657450, user=user_teach, schema=teach_202006, old shema=teach_202006, borrowed=true, fromSlaveDB=false, threadId=393960, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=null, host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@574d711c, writeQueue=0, modifiedSQLExecuted=true] 2021-06-02 17:20:57.457 ERROR [$_NIOREACTOR-28-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:231)) - ServerConnection [id=1, schema=teach_bigdata, host=192.168.201.14, user=user_teach,txIsolation=3, autocommit=true, schema=teach_bigdata, executeSql=select @@command bdmc_study_env_interact_stat_county_day ]Table 'teach_202006.bdmc_study_env_interact_stat_county_day' doesn't existjava.lang.Thread .getStackTrace1552 io.mycat.net.FrontendConnection .getStack245 io.mycat.net.FrontendConnection .writeErrMessage231 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .backConnectionErr314 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .errorResponse275 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleErrorPacket220 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleData111 io.mycat.net.handler.BackendAsyncHandler .offerData36 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handle85 io.mycat.net.AbstractConnection .handle280 io.mycat.net.AbstractConnection .onReadData338 io.mycat.net.NIOSocketWR .asynRead216 io.mycat.net.AbstractConnection .asynRead290 io.mycat.net.NIOReactor$RW .run113 java.lang.Thread .run745 write errorMsg:{} error

2）在mycat-1.6.7.4-release测试时，在上面的报错信息之后，会持续报receive FieldEofPacket but no handler的错误，之后在命令行查询数据时，经常会卡在语句不动，后台mycat.log中无其他日志。 错误日志2： 2021-06-01 10:49:39.422 WARN [$_NIOREACTOR-23-RW] (io.mycat.backend.mysql.nio.MySQLConnectionHandler.closeNoHandler(MySQLConnectionHandler.java:219)) - no handler bind in this con io.mycat.backend.mysql.nio.MySQLConnectionHandler@706813d5 client:MySQLConnection@504964601 [id=855, lastTime=1622558979419, user=user_teach, schema=teach_202001, old shema=teach_202001, borrowed=false, fromSlaveDB=false, threadId=925614, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=10.1.13.13, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 2021-06-01 10:49:49.421 ERROR [$_NIOREACTOR-5-RW] (io.mycat.backend.mysql.nio.MySQLConnectionHandler.handleFieldEofPacket(MySQLConnectionHandler.java:197)) - receive FieldEofPacket but no handler

2、版本号(非常重要) 在两个版本下测试，都出现此问题。 mycat-1.6.7.6-release-20210303094759 MyCat Server mycat-1.6.7.4-release

3、相关表的配置信息 schema.xml (需包含表的配置信息，mysql的连接驱动是JDBC还是native方式) <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

select user() select user() select user() rule.xml (涉及到的路由函数) date_period partbymonth yyyy-MM-dd 2020-01-01 2021-12-01 server.xml(可选) druidparser 40960 204800000 32 16 60000 86400000 0.0.0.0 1 utf8mb4 8079 9079 134217728 1 1 1 0 4096 64k 1k 0 384m 1 (?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+ false io.mycat.route.sequence.handler.HttpIncrSequenceHandler 0 0 0 0 1 **4、操作步骤** 1. 如bug描述 2. '....' 3. ... **5、期望结果** 不打印ERROR信息 **6、实际结果** A clear and concise description of what actually happened. **7、额外信息** mysql-5.7.14
funnyAnt commented 3 years ago

执行下 "explain select @@command **" 语句,看路由到哪个节点上面执行了，

这种命令类的语句默认是到schema.xml上面配置的默认dataNode上面执行，即 “<schema name="TESTDB" checkSQLschema="true" dataNode="xxxxx" sqlMaxLimit="100" randomDataNode="dn1">”里面的dataNode，请检查你配置的dataNode值。

andrewzhuxu commented 3 years ago

测试了这个路由策略，确实是有问题的。 我的目的是设置一个逻辑库，下面既有分片表对应的分片库，也有单片库。 schema配置为（两种配置都尝试过）： schema name="teach_bigdata" checkSQLschema="true" sqlMaxLimit="1000" schema name="teach_bigdata" checkSQLschema="true" sqlMaxLimit="1000" dataNode="defaultdn" 看执行计划节点： explain select @@command bdmc_study_env_interact_stat; 结果是路由到随机的dataNode节点上。 见截图： image

andrewzhuxu commented 3 years ago

修改schema配置为： schema name="teach_bigdata" checkSQLschema="true" sqlMaxLimit="1000" dataNode="tdn_202001" randomDataNode="defaultdn" 此时，指定了randomDataNode="defaultdn" 再查询执行计划时，结果就都指定 defaultdn，分片datanode的表执行计划也路由到defaultdn上了。 见截图： image

此时，在use db后，mycat.log中，仍然刷出doesn't exist不匹配库表的ERROR信息。 executeSql=select @@command user_role_county_total]Table 'big_screen.user_role_county_total' doesn't exist

错误日志见： 2021-06-03 10:59:18.117 WARN [$_NIOREACTOR-16-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:287)) - execute sql err : errno:1146 Table 'big_screen.user_role_county_total' doesn't exist con:MySQLConnection@870686030 [id=112, lastTime=1622689158115, user=user_teach, schema=big_screen, old shema=big_screen, borrowed=true, fromSlaveDB=false, threadId=396188, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=defaultdn{select @@command user_role_county_total}, respHandler=CommandExecResultHandler [node=defaultdn{select @@command user_role_county_total}, table=user_role_county_total], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@752576c1, writeQueue=0, modifiedSQLExecuted=true] frontend host:192.168.201.14/57766/user_teach 2021-06-03 10:59:18.117 WARN [$_NIOREACTOR-16-RW] (io.mycat.backend.mysql.nio.MySQLConnection.release(MySQLConnection.java:693)) - can't sure connection syn result,so close it MySQLConnection@870686030 [id=112, lastTime=1622689158115, user=user_teach, schema=big_screen, old shema=big_screen, borrowed=true, fromSlaveDB=false, threadId=396188, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=CommandExecResultHandler [node=defaultdn{select @@command user_role_county_total}, table=user_role_county_total], host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@752576c1, writeQueue=0, modifiedSQLExecuted=true] 2021-06-03 10:59:18.117 INFO [$_NIOREACTOR-16-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:531)) - close connection,reason:syn status unkown ,MySQLConnection@870686030 [id=112, lastTime=1622689158115, user=user_teach, schema=big_screen, old shema=big_screen, borrowed=true, fromSlaveDB=false, threadId=396188, charset=utf8, txIsolation=3, autocommit=true, txReadonly=false, attachment=null, respHandler=null, host=192.168.201.14, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@752576c1, writeQueue=0, modifiedSQLExecuted=true] 2021-06-03 10:59:18.118 ERROR [$_NIOREACTOR-16-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:231)) - ServerConnection [id=1, schema=teach_bigdata, host=192.168.201.14, user=user_teach,txIsolation=3, autocommit=true, schema=teach_bigdata, executeSql=select @@command user_role_county_total]Table 'big_screen.user_role_county_total' doesn't existjava.lang.Thread .getStackTrace1552 io.mycat.net.FrontendConnection .getStack245 io.mycat.net.FrontendConnection .writeErrMessage231 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .backConnectionErr314 io.mycat.backend.mysql.nio.handler.SingleNodeHandler .errorResponse275 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleErrorPacket220 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleData111 io.mycat.net.handler.BackendAsyncHandler .offerData36 io.mycat.backend.mysql.nio.MySQLConnectionHandler .handle85 io.mycat.net.AbstractConnection .handle280 io.mycat.net.AbstractConnection .onReadData338 io.mycat.net.NIOSocketWR .asynRead216 io.mycat.net.AbstractConnection .asynRead290 io.mycat.net.NIOReactor$RW .run113 java.lang.Thread .run745 write errorMsg:{} error

andrewzhuxu commented 3 years ago

目前这个错误，仅在命令行维护 use db时，出现，但也不影响后续查询等操作（1.6.7.6查询没问题，1.6.7.4偶尔会卡主查询语句）。JDBC方式的查询访问，没有此类问题。

funnyAnt commented 3 years ago

use db后，mycat.log中，仍然刷出doesn't exist不匹配库表的ERROR信息。executeSql=select @@command user_role_county_total

  • use db不会主动发送select @@command 这样的命令，还是检查下你们的业务逻辑。
  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.