走mycat，查询表发生变化

Question

请教下老师，就是我程序访问库本来是cmaoa.g_userinfo不知为啥变成了去找cmaoa.userinfo这个表了。不走mycat是正常的，
mycat报错：

2019-08-09 21:40:52.258  WARN [$_NIOREACTOR-3-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:267)) - execute  sql err : errno:1146 Table 'cmaoa.userinfo' doesn't exist con:MySQLConnection [id=3, lastTime=1565358052244, user=xxx, schema=xxx, old shema=xxx, borrowed=true, fromSlaveDB=false, threadId=206, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SHOW KEYS FROM UserInfo}, respHandler=SingleNodeHandler [node=dn1{SHOW KEYS FROM UserInfo}, packetId=1], host=xxxxx, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] frontend host:xxx/52074/mycat1

tomcat报错

/ezweb/20190809214049:SQL:SELECT A.OrganizeID,B.NAME FROM G_OrgUser A INNER JOIN G_ORGANIZE B ON A.ORGANIZEID=B.ID AND Bjava.sql.SQLException: Transaction error, need to rollback. errno:1146 Table 'cmaoa.userinfo' doesn't exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
        at xsf.data.DataBaseAdapter.queryData(SourceFile:656)
        at xsf.data.DataBaseAdapter.queryDataTable(SourceFile:605)

Answer 1

sqlercn 2019-08-10 13:13:03

schema. xml是怎么配置的？另外使用mycat时最好不要加数据库名前缀

• 提问者 Wl_error #1

  配置文件如下（已或略库名）：
  <?xml version="1.0"?>
  <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  <mycat:schema xmlns:mycat="http://io.mycat/">
  
  	<schema name="cmaoa" checkSQLschema="true"  dataNode="dn1">
  	</schema>
  	<dataNode name="dn1" dataHost="localhost1" database="cmaoa" />
  	<dataHost name="localhost1" maxCon="2000" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  		<heartbeat>select user();</heartbeat>
  		<writeHost host="hostM1" url="xxx:3306" user="root" password="xxx">
  					<readHost host="hostS2" url="xxx:3306" user="root" password="xxx"></readHost>
  
  		</writeHost>
  	</dataHost>
  </mycat:schema>

• sqlercn 回复 提问者 Wl_error #2

  直接select * from guser_info 是否可以，另外打开mycat的debug日志看一下表的路由信息。