mycat错误之 SQLNonTransientException: find no Route:SELECT * FROM `db_xichuan`.`info` LIMIT 0, 1000

本以为mycat基础配置会很简单，却被这个错误搞了两三天了，错误日志：

2018-10-30 14:05:55.128  WARN [$_NIOREACTOR-0-RW] (io.mycat.route.parser.druid.impl.DruidSelectParser.tryRoute(DruidSelectParser.java:436)) -  find no Route:SELECT * FROM `db_xichuan`.`info` LIMIT 0, 1000
2018-10-30 14:05:55.139  WARN [$_NIOREACTOR-0-RW] (io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:281)) - ServerConnection [id=1, schema=db_xichuan, host=12.12.12.12, user=root,txIsolation=3, autocommit=true, schema=db_xichuan]SELECT * FROM `db_xichuan`.`info` LIMIT 0, 1000 err:java.sql.SQLNonTransientException:  find no Route:SELECT * FROM `db_xichuan`.`info` LIMIT 0, 1000
java.sql.SQLNonTransientException:  find no Route:SELECT * FROM `db_xichuan`.`info` LIMIT 0, 1000
	at io.mycat.route.parser.druid.impl.DruidSelectParser.tryRoute(DruidSelectParser.java:437) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.route.parser.druid.impl.DruidSelectParser.changeSql(DruidSelectParser.java:287) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.route.parser.druid.impl.DefaultDruidParser.parser(DefaultDruidParser.java:67) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:84) ~[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.server.ServerConnection.execute(ServerConnection.java:222) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.server.handler.SelectHandler.handle(SelectHandler.java:105) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:79) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.FrontendConnection.query(FrontendConnection.java:317) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.FrontendConnection.query(FrontendConnection.java:337) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.FrontendConnection.rawHandle(FrontendConnection.java:478) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.FrontendConnection.handle(FrontendConnection.java:460) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.AbstractConnection.onReadData(AbstractConnection.java:321) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.NIOSocketWR.asynRead(NIOSocketWR.java:190) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.AbstractConnection.asynRead(AbstractConnection.java:273) ~[Mycat-server-1.6-RELEASE.jar:?]
	at io.mycat.net.NIOReactor$RW.run(NIOReactor.java:102) ~[Mycat-server-1.6-RELEASE.jar:?]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]

解决办法：在schema.xml中将checkSQLschema=“false”,改为true即可。

<schema name="db_xichuan" checkSQLschema="true" sqlMaxLimit="100" >
		<table name="user" primaryKey="id" dataNode="dn1" type="global" />
		<table name="info" primaryKey="id" dataNode="dn2,dn3" rule="mod-long" />
	</schema>

• checkSQLschema 

    当该值为true时，例如我们执行语句select * from TESTDB.company 。mycat会把语句修改为 select * from company 去掉TESTDB

 

参考:

https://blog.csdn.net/qq_38423105/article/details/83241863

https://blog.csdn.net/linlinv3/article/details/51777324