Mycat之——错误处理总结

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/77140801

1、报错1

mysql>  INSERT INTO t_order(ID,SN,CREATE_TIME) VALUES(1,'2BJ0001001',NOW());
    ERROR 1064 (HY000): For input string: "2BJ0001001"
mysql> 

报错是因为分片字段是str字符串，所以需要修改分片规则1中type从0改成1，0是数字型分片，1是字符串分片。

2、报错2

mysql>  INSERT INTO t_order_detail(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001001) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO t_order_detail(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001001) ',NOW())
mysql> 

解决方案1：此类ER表的插入操作不能作为一个事务进行数据提交，如果父子表在一个事务中进行提交，显然在事务没有提交前子表是无法查询附表的数据的，因此就无法确定分片节点，如果是ER关系的表在插入数据时不能再同一个事务中提交数据，需要分开提交。

3、报错3

解决方案2：上面的方案2个事务搞不定，所以查看后台Mycat日志

08/13 10:52:09.378  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO t_order_detail(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001001) ',NOW())
08/13 10:52:09.506  DEBUG [$_NIOREACTOR-3-RW] (RouterUtil.java:1188) -find root parent's node sql select t_order.id from t_order where  t_order.id=1
08/13 10:52:09.513  DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select t_order.id from t_order where  t_order.id=1
08/13 10:52:09.513  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select t_order.id from t_order where  t_order.id=1
08/13 10:52:09.514  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
08/13 10:52:09.514  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
08/13 10:52:09.535   WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.t_order' doesn't exist
08/13 10:52:09.535  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1455258729497, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=290, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.209.137, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/13 10:52:09.715  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22
08/13 10:52:09.715  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
08/13 10:52:09.716   WARN [$_NIOREACTOR-1-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.t_order' doesn't exist

后台执行sql确实找不到

mysql> select t_order.id from t_order where  t_order.id=1;
    ERROR 1105 (HY000): Table 'db3.t_order' doesn't exist
mysql

原因是2个datanode是互为主从的关系，dn1和dn2是mm结构，然后停止mm结构，2个都是单独的MySQL，然后重建t_order和t_order_detail表，再执行insert操作，问题解决了。

4、报错4

mysql> INSERT INTO t_order_detail(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of t_order(ID=1,2BJ0001001) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO t_order_detail(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of t_order(ID=1,2BJ0001001) ',NOW())
mysql> 

原因：表名字大小写的问题导致

解决方案：

打开MySQL的my.cnf配置文件，在[mysqld]节点下加上如下配置：

[mysqld]
lower_case_table_names = 1

问题解决。