mycat高可用ER分片的2种场景详细分析

页面导航:首页 > 数据库 > Mysql > mycat高可用ER分片的2种场景详细分析

mycat高可用ER分片的2种场景详细分析

来源: 作者: 时间:2016-02-17 10:07 【

1,ER分片关系简介有一类业务,例如订单(ORDER)跟订单明细表(ORDER_DETAIL),明细表会依赖二订单,就是该会存在表的主从关系,这类似业务的切分可以抽象出合适的切分规则,比如根

1,ER分片关系简介

有一类业务,例如订单(ORDER)跟订单明细表(ORDER_DETAIL),明细表会依赖二订单,就是该会存在表的主从关系,这类似业务的切分可以抽象出合适的切分规则,比如根据用户ID切分,其它相关的表都依赖于用户ID,再或者根据订单ID进行切分,总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表,子表的记录与所关联的父表记录存放在同一个数据分片上,避免数据Join跨库操作,以order与order_detail例子为例,schema.xml中定义合适的分片配置,order,order_detail 根据order_id迕行数据切分,保证相同order_id的数据分到同一个分片上,在进行数据插入操作时,Mycat会获取order所在的分片,然后将order_detail也插入到order所在的分片。


2, 父表按照主键ID分片,字表的分片字段与主表ID关联,配置为ER分片

2.1 在schema.xml添加如下配置配置文件修改

 

在rule.xml里面设定分片规则

    
                
                        id
                        D4NCjxociAvPg0KPGg0IGlkPQ=="27-总结当子表与父表的关联字段正好是父表的分片字段时子表直接根据父表规则进行分片在数据录入的时候子表直接放在父表的分片上面在进行关联查询join的时候走的是">2.7 总结:当子表与父表的关联字段正好是父表的分片字段时,子表直接根据父表规则进行分片,在数据录入的时候子表直接放在父表的分片上面,在进行关联查询join的时候,走的是

3 .父表的分片字段为其他字段,以PROVINCE字段分片,字表的分片字段与主表ID关联,配置为ER分片

3.1 各种配置

在schema.xml里面添加表配置

      

在rule.xml里面设定分片规则

        
                 
                         PROVINCE
                          > CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);
    Query OK, 0 rows affected (0.05 sec)

    mysql> CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));
    Query OK, 0 rows affected (0.02 sec)

    mysql> 

3.2 录入数据,一组组录入,涉及到外键关系:

    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    Query OK, 1 row affected (0.21 sec)

    mysql>  
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW());
    Query OK, 1 row affected (0.41 sec)

    mysql> 
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,2GZH004) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,2GZH004) ',NOW());
    Query OK, 1 row affected (0.21 sec)

    mysql>
    INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());
    INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());
    Query OK, 1 row affected (0.02 sec)

    mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
    Query OK, 1 row affected (0.22 sec)

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

3.3 数据验证

    [[email protected]_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3327 |
    +--------+
    +----+----------+-----------+---------------------+
    | ID | PROVINCE | SN        | CREATE_TIME         |
    +----+----------+-----------+---------------------+
    |  3 | shanghai | 2SHH001   | 2016-02-12 17:24:00 |
    |  6 | shanghai | 2WUHAN006 | 2016-02-12 17:24:14 |
    +----+----------+-----------+---------------------+
    +----+----------+------------+-------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                             | CREATE_TIME         |
    +----+----------+------------+-------------------------------------+---------------------+
    |  3 |        3 | 1          | test data  of ORDER1(ID=3,2SHH001)  | 2016-02-12 17:24:04 |
    +----+----------+------------+-------------------------------------+---------------------+
    [[email protected]_1_11~]#     
    [[email protected]_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";
    Enter password: 
    +--------+
    | @@port |
    +--------+
    |   3317 |
    +--------+
    +----+----------+---------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         |
    +----+----------+---------+---------------------+
    |  1 | beijing  | 2BJ0001 | 2016-02-12 17:23:46 |
    |  4 | beijing  | 2GZH004 | 2016-02-12 17:24:07 |
    +----+----------+---------+---------------------+
    +----+----------+------------+---------------------------------------+---------------------+
    | ID | ORDER_ID | ORD_STATUS | ADDRESS                               | CREATE_TIME         |
    +----+----------+------------+---------------------------------------+---------------------+
    |  1 |        1 | 1          | test data  of ORDER1(ID=1,2BJ0001)    | 2016-02-12 17:23:50 |
    |  4 |        4 | 1          | test data  of ORDER1(ID=4,2GZH004)    | 2016-02-12 17:24:11 |
    |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    +----+----------+------------+---------------------------------------+---------------------+
    [[email protected]_1_11~]# 

3.4 mycat.log中debug日志显示,通过关键字2BJ0001来检索父子数据记录的录入情况

vim mycat.log

    02/12 17:23:50.530  DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:23:50.531  DEBUG [BusinessExecutor3] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:23:50.532  DEBUG [$_NIOREACTOR-0-RW] (FetchStoreNodeOfChildTableHandler.java:154) -received rowResponse response,1 from  MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=dn21, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChil[email protected], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:23:50.533  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:23:50.732  DEBUG [BusinessExecutor4] (RouterUtil.java:1213) -found partion node for child table to insert dn21 sql :INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 17:23:50.733  DEBUG [BusinessExecutor4] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW()), route={
       1 -> dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}
    } rrs
    02/12 17:23:50.735  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
    02/12 17:23:50.735  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

【】可以看到在17:23:50.532处,看到find root parent’s node sql select order2.id from order2 where order2.id=1这样的日志记录,就是通过外键锁涉及的父表的关联字段id去找所在分片记录,然后在17:23:50.732看到已经路由出来了分片地址route={dn21。


3.5 如果录入不存在的父表数据的时候路由情况

数据录入:

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

查看日志mycat.log情况:

    02/12 17:54:43.351  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
    02/12 17:54:43.352  DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=9
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:54:43.353  DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:54:43.354  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455270883346, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:54:43.554  DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22
    02/12 17:54:43.554  DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
    02/12 17:54:43.554  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=15, lastTime=1455270883547, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 17:54:43.805   WARN [BusinessExecutor3] (RouterUtil.java:1206) [email protected] INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW()) err:can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())

看到,遍历了所有节点dn21和dn22之后,select order2.id from order2 where order2.id=9执行后没有找到记录,就报错了 can’t find (root) parent sharding node for sql:


3.6 join关联查询涉及到的路由,是全节点遍历查询

    mysql> explain select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;
    +-----------+--------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                  |
    +-----------+--------------------------------------------------------------------------------------+
    | dn21      | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
    | dn22      | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
    +-----------+--------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    mysql> 
    mysql> select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         | ID | ORDER_ID | ORD_STATUS | ADDRESS                               | CREATE_TIME         |
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    |  1 | beijing  | 2BJ0001 | 2016-02-12 17:23:46 |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    |  4 | beijing  | 2GZH004 | 2016-02-12 17:24:07 |  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-02-12 17:24:17 |
    +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+
    2 rows in set (0.01 sec)

    mysql>

    02/12 18:02:23.370  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
    02/12 18:02:23.371  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
    02/12 18:02:23.373  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 value:select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
       1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
       2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
    }
    02/12 18:02:23.373  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
       1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
       2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
    } rrs       

3.7 【】总结:当子表与父表的关联字段不是父表的分片字段时,必须通过查找对应的父表记录来确认子表所在分片,如果找不到则会抛出错误,在join查询的时候,路由走的是所有分片节点。


4,报错处理总结

4.1 报错1:

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

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

4.2 报错2:

    mysql>  INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());
    ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    mysql>  
解决方案1:此类ER表癿揑入操作丌能做为一个亊务迕行数捤提交,如果父子表在一个亊务中迕行提交,显然在亊务没有提交前子表是无法查刡父表癿数捤癿,因此就无法确定sharding node。如果是ER关系癿表在揑入数捤时丌能在同一个亊务中提交数捤,叧能分开提交。

4.3 报错3

解决方案2:上面的方案2个事务搞不定,所以查看后台mycat.log,日志

    02/12 14:32: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 ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 14:32:09.506  DEBUG [$_NIOREACTOR-3-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 14:32:09.513  DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 14:32:09.513  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 14:32:09.514  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 14:32:09.514  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 14:32:09.535   WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist
    02/12 14:32: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=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/12 14:32:09.715  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22
    02/12 14:32:09.715  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
    02/12 14:32:09.716   WARN [$_NIOREACTOR-1-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist

后台执行sql确实找不到

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

原因是2个datanode是互为主从的关系,dn21和dn22是mm结构,然后停止mm结构,2个都是单独的mysql,然后重建ORDER2和ORDER_DETAIL2表,再执行insert操作,ok,问题解决了。

4.4 报错4

解决方案3:表名字大小写的问题导致

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

后台报错:

    02/12 17:04:24.583  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW())
    02/12 17:04:24.584  DEBUG [$_NIOREACTOR-0-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=1
    02/12 17:04:24.584  DEBUG [BusinessExecutor6] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=1
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=1
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21
    02/12 17:04:24.585  DEBUG [BusinessExecutor6] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
    02/12 17:04:24.585   WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist
    mysql> select * from db3.order2;
    ERROR 1146 (42S02): Table 'db3.order2' doesn't exist
    mysql> select * from db3.ORDER2;
    Empty set (0.01 sec)

    mysql> 
    mysql> 
    [mysqld]
    lower_case_table_names = 1

然后重启mysql已经mycat服务,再次进行check。

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<