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

最新推荐文章于 2023-04-12 18:30:00 发布
最新推荐文章于 2023-04-12 18:30:00 发布
阅读量1w 收藏 2
点赞数
分类专栏： mycat
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/csdnhsh/article/details/95680704
版权

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关联，配置为ＥＲ分片

2.1 在schema.xml添加如下配置配置文件修改
<table name="ORDER1" dataNode="dn21,dn22" rule="mod-long"> <childTable name="ORDER_DETAIL" primaryKey="ID" joinKey="ORDER_ID" parentKey="ID" /> </table>

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

	<tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
    </tableRule>

然后重启mycat或者重新加载mycat

本blog源地址：http://blog.csdn.net/mchdba/article/details/50655304，不经过原作者mchdba(黄杉)允许，谢绝转载

2.2 先建表， ORDER 和 ORDER_DETAIL 表，有主外键关系
	CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);

	CREATE TABLE ORDER_DETAIL(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID11 FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID));



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

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

	mysql>

2.3 录入数据：

	mysql> 
	mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());
	ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)
	VALUES ('BJ0001', NOW())
	mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());
	ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)
	VALUES ('BJ0001', NOW())
	mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());
	Query OK, 1 row affected (0.03 sec)
	
	mysql>

录入数据，一组组录入，涉及到外键关系：
第一组北京的订单

	mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());
	Query OK, 1 row affected (0.03 sec)
	
	mysql> 
	
	mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,BJ001) ',NOW());
	Query OK, 1 row affected (0.00 sec)
	
	mysql>

第二组上海的订单：

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

第三组广州的订单：

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

第四组 武汉的订单，这里故意将order_id设置成4，看看效果，是否随id为4的广州的那组分片：

	mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(5,'WUHAN005',NOW());
	ERROR 1064 (HY000): Index: 2, Size: 2
	mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(6,'WUHAN006',NOW());
	Query OK, 1 row affected (0.02 sec)
	
	mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,WUHAN005) ',NOW());
2.4 验证子表是否随父表分片，果然验证如下，大家看到订单详细表ORDER_DETAIL的数据的分片都是随ORDER_ID所在外键的父表的分片：
	[root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3317 --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";
	Enter password: 
	+--------+
	| @@port |
	+--------+
	|   3327 |
	+--------+
	+----+--------+---------------------+
	| ID | SN     | CREATE_TIME         |
	+----+--------+---------------------+
	|  1 | BJ0001 | 2016-02-11 22:54:26 |
	|  4 | GZH004 | 2016-02-11 22:57:49 |
	+----+--------+---------------------+
	+----+----------+------------+--------------------------------------+---------------------+
	| ID | ORDER_ID | ORD_STATUS | ADDRESS                              | CREATE_TIME         |
	+----+----------+------------+--------------------------------------+---------------------+
	|  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)     | 2016-02-11 23:07:05 |
	|  4 |        4 | 1          | test data  of ORDER1(ID=4,GZH004)    | 2016-02-11 23:09:48 |
	|  6 |        4 | 1          | test data  of ORDER1(ID=6,WUHAN005)  | 2016-02-11 23:10:18 |
	+----+----------+------------+--------------------------------------+---------------------+
	[root@test_1_11logs]# 
	[root@test_1_11logs]# 
	[root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3327 --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";
	Enter password: 
	+--------+
	| @@port |
	+--------+
	|   3317 |
	+--------+
	+----+----------+---------------------+
	| ID | SN       | CREATE_TIME         |
	+----+----------+---------------------+
	|  3 | SHH001   | 2016-02-11 22:57:26 |
	|  6 | WUHAN006 | 2016-02-11 23:04:39 |
	+----+----------+---------------------+
	+----+----------+------------+------------------------------------+---------------------+
	| ID | ORDER_ID | ORD_STATUS | ADDRESS                            | CREATE_TIME         |
	+----+----------+------------+------------------------------------+---------------------+
	|  3 |        3 | 1          | test data  of ORDER1(ID=3,SHH001)  | 2016-02-11 23:09:02 |
	+----+----------+------------+------------------------------------+---------------------+
	[root@test_1_11logs]#
2.5 有日志为列，看到SHH001这个订单的数据录入都在端口为3317的分片mysql实例上，如下验证：
[root@test_1_11logs]# more mycat.log|grep SHH001
	02/11 22:56:58.477  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 ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())
	02/11 22:56:58.478   WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
	02/11 22:57:22.476  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 ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())
	02/11 22:57:22.477   WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
	02/11 22:57:26.988  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 ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())
	02/11 22:57:26.989  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()), route={
	   1 -> dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}
	02/11 22:57:26.999  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1455202646984, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=91, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
	02/11 23:09:02.690  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_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,SHH001) ',NOW())
	VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())
	02/11 23:09:02.692  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,SHH001) ',NOW()), route={
	VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}
	VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
	VALUES (3, 3, '1', 'test data  of ORDER1(ID=3,SHH001) ', NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
	[root@test_1_11logs]#

【】看到直接22:57:26.988时间点处有数据录入，22:57:26.989处有路由分配 route={dn21了，直接走父表的路由了**

2.6 查询路由

mycat命令行里面执行：

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

	mysql>  select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID;
	+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
	| ID | SN     | CREATE_TIME         | ID | ORDER_ID | ORD_STATUS | ADDRESS                           | CREATE_TIME         |
	+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
	|  1 | BJ0001 | 2016-02-12 17:51:24 |  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)  | 2016-02-12 17:51:30 |
	+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
	1 row in set (0.01 sec)

	mysql>

【】mycat.log后台日志debug信息，看到走的route路由是dn21和dn22，所有的节点路由都走了：

	02/12 18:22:51.444  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]explain select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
	02/12 18:22:51.444  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
	02/12 18:22:51.448  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID value:select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID, route={
	   1 -> dn21{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
	   2 -> dn22{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
	}
	02/12 18:23:01.214  DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=18, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=18, 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 18:23:01.215  DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=22, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=21, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

这里写图片描述
图片1.jpg

2.7 总结：当子表与父表的关联字段正好是父表的分片字段时，子表直接根据父表规则进行分片，在数据录入的时候子表直接放在父表的分片上面，在进行关联查询join的时候，走的是

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

3.1 各种配置

在schema.xml里面添加表配置

	 <table name="ORDER2" primaryKey="ID" dataNode="dn21,dn22" rule="province-str"> <childTable name="ORDER_DETAIL2" primaryKey="ID" joinKey="ORDER_ID" parentKey="ID" /> </table>

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

        <tableRule name="province-str">
                 <rule>
                         <columns>PROVINCE</columns>
                          <algorithm>province-str-split</algorithm>
                </rule>
         </tableRule>    </tableRule>
        <function name="province-str-split" class="org.opencloudb.route.function.PartitionByFileMap">
                <property name="type">1</property>
                <property name="mapFile">partition-hash-str-pro.txt</property>
                <property name="defaultNode">0</property>
        </function>

新添加partition-hash-str-pro.txt

	[root@test_1_11conf]# more partition-hash-str-pro.txt
	beijing=0
	shanghai=1
	tianjing=2
	[root@test_1_11conf]#

然后重启mycat或者重新加载mycat

3.2 建表
	CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
	
	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));
	mysql> 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.3 录入数据，一组组录入，涉及到外键关系：
	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.4 数据验证
	[root@test_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 |
	+----+----------+------------+-------------------------------------+---------------------+
	[root@test_1_11~]# 	
	[root@test_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 |
	+----+----------+------------+---------------------------------------+---------------------+
	[root@test_1_11~]#
3.5 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.FetchStoreNodeOfChildTableHandler@dde0e41, 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.6 如果录入不存在的父表数据的时候路由情况

数据录入：

	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) -org.opencloudb.server.NonBlockingSession@634984c8INSERT 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.7 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.8 【】总结：当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在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。

林震南
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mycat源码篇 : MyCat事务管理机制分析
CrazyPig的技术博客
08-26 1万+
针对MyCat1.5版本，对MyCat的事务管理机制进行简要分析
mycat:can't find (root) parent sharding node for sql
stephen
04-30 1392
插入子表的数据时，报的以上错误。 INSERT INTO `company3` (`id`, `name`, `c2id`, `num`) VALUES (7, '222', 1, 224); 而当我改成： INSERT INTO `company3` (`id`, `name`, `c2id`, `num`) VALUES (7, '222', 4, 224); 的时候就不会了，之所...
MyCat1.6 RE 子表数据插入 cant find (root) parent sharding node for sql ERRO
weixin_44560583的博客
08-06 599
MyCat1.6 RE 子表数据插入 cant find (root) parent sharding node for sql ERRO 问题描述 使用MyCat1.6.7.6 + MySQL 8.0.25 部署 水平分表 ER 分表时，子表插入数据，抛出如下异常： ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO.... 上面抛出异常为 找不到父节点，GitHub有类似的文章GitHub，但
MyCat分片&读写分离
MyNine_的博客
04-20 317
MyCat简介 MyCat来自阿里曾经开源知名产品–Cobar。Cobar核心功能和优势是MySQL数据库分片，此产品曾经广为流传，据说最早的发起者对 Mysql 很精通，后来从阿里跳槽了，阿里随后开源的 Cobar，并维持到 2013 年年初。 Cobar 的思路和实现路径的确不错。基于 Java 开发的，实现了 MySQL 公开的二进制传输协议，巧妙地将自己伪装成一个 MySQL Server...
大规模MySQL运维陷阱之基于MyCat的伪分布式架构
渡江客涂鸦板
04-12 1923
分布式数据库，已经进入了全面快速发展阶段，这种发展，是与时俱进的，与人的需求是分不开的，因为现在信息时代的高速发展，导致数据量和交易量越来越大。这种现象首先导致的就是存储瓶颈，因为MySQL数据库，实质上，还是一个单机版本的数据库，而只要是单机，就必然会遇到的一个问题就是存储问题，因为存储是硬需求，而CPU和内存如果不够的话，只是性能不好，并不会直接否定方案或者架构。
Mycat+Mysql实现数据分片存储高可用集群
最新发布
05-21
技术解析：深入解析了Mycat的数据分片原理、负载均衡策略以及高可用机制，帮助用户理解其背后的技术原理。 实践指导：文档不仅提供了理论知识，还结合了丰富的实践经验，给出了各种可能出现的问题的解决方案。 性能...
mycat字符串枚举分片示例.txt
08-26
org.opencloudb.route.function.PartitionByFileMap可以支持字符串枚举、...枚举分片的适用场景：记录数可枚举（记录数基本固定或变化很小），比如省份、地区、商业区域（华东区、华中区、华北区、东南区、西南区等）。
mycat分片规则和算法.docx
07-30
本文将深入探讨Mycat中的分片规则和算法，包括枚举法、固定分片Hash算法、范围约定、求模法、日期列分区法、通配取模、ASCII码求模通配、编程指定、字符串拆分Hash解析以及一致性Hash等。 首先，让我们来看看枚举法...
Mycat取模分片示例
10-17
【Mycat取模分片示例】 Mycat 是一个开源的分布式数据库中间件，它支持水平扩展，能够处理海量数据。在大型系统中，为了提高数据处理能力，我们通常会将数据分散到多个数据库节点（也称为分片）上。取模分片是一种...
Mycat从入门到精通之分片规则、场景、策略.zip
04-25
《Mycat从入门到精通之分片规则、场景、策略》是一份全面介绍Mycat数据库中间件的教程资源，包含PPT讲义和相关的练习题，旨在帮助学习者深入理解和掌握Mycat的核心功能——数据分片。在大数据时代，面对海量的数据...
MyCat高可用负载均衡集群实现（HAProxy+Keepalived+MyCat）.pdf
12-14
MyCat高可用负载均衡集群实现（HAProxy+Keepalived+MyCat）.pdf
Mycat1.6 release版本报错： can't find (root) parent sharding node for sql
ZRBRZB的专栏
11-16 1877
2017年11月16 Mycat1.6 release 是目前最新的Mycat的发布版本。同事在使用ER分片时遇到了can't find (root) parent sharding node for sql的错误。 具体情况如下： 在同一个事务中，插入父表及子表记录，父表按某个日期类型的字段用sharding-by-month算法进行分片，然后出现上述错误信息，上网查找资料无果。然
MyCat实战讲解（MySQL集群）
01-01
Mycat是阿里推广的一个开源组件，其核心的概念为库表分离实现操作，利用合理的库表分离可以用更高效的方式来进行SQL数据库的开发与处理，适合高并发的数据访问处理。 在本课程中将为读者讲解库表分离的设计思想，以及数据库的垂直水平分割概念，同时后将为读者讲解高可用的MYSQL集群机构的设计实现。
mysql er分片_MyCat 学习笔记 第十篇.数据分片 之 ER分片
weixin_35813719的博客
02-07 288
1 应用场景这篇来说下mycat中自带的er关系分片，所谓er关系分片即可以理解为有关联关系表之间数据分片。类似于订单主表与订单详情表间的分片存储规则。本文所说的er分片分为两种：a. 依据主键进行数据分片，验证发现主表数据保存在第1个datanode中，子表数据根据分片规则存储。b. 依据分片关键字段进行分片，验证发现主表与子表根据分片规则存储，且保存在相同的分片内。接下来，可以下实际配置与数据...
Mycat常见问题与解决方案---宜将剩勇追穷寇，不可沽名学霸王
热门推荐
wangshuang1631的博客
03-17 2万+
1 Mycat目前有哪些功能与特性？答: • 支持 SQL 92标准 • 支持Mysql集群，可以作为Proxy使用 • 支持JDBC连接多数据库 • 支持NoSQL数据库 • 支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群 • 自动故障切换，高可用性 • 支持读写分离，支持Mysql双主多从，以及一
ER分片表
weixin_43356218的博客
10-29 928
ER分片表 1.ER分片表概念 问题描述： 例如有订单表和订单商品表，两个表都需要分片 如果两个表的关联查询数据分到不同分片，则mycat关联查询时，无法拿到完整数据（不支持分库查） 解决思路：将关联的商品和订单数据放入同一个分片，如何保证？ 可以利用外键字段（两个表的外键和主键字段内容相同）进行hash一致性计算，计算结果一致。 ER分片表逻辑： er分片表是mycat首创 先定义父级表...
MyCat分片规则(全局表，ER分片表，多对多关联，主键分片VS非主键分片)，MyCat常用的分片规则（15中分片规则），自定义MyCat分片规则，其它术语
涂作权的博客
05-18 6636
1 MyCat分片规则 数据切分中重要的几条原则，其中有几条数据冗余，表分组（Table Group）。 1.1全局表 如果你的业务中有些数据类似于数据字典，比如配置文件的配置，常用业务的配置或数据量不是很大，很少变动的表，这些表往往不是特别大，而且大部分的业务场景都会用到，那么这种表适合于MyCat全局表，无须对数据进行切分。只要在所有的分片上保存一份数据即可，MyCat在Join操...
mycat 取模分片，ER分片
convict_EVA的专栏
07-25 2505
1、业务场景：用户购物下单。 要求：把订单分片，商家要查询购买在订单，用户要查询自己的订单。 表设计：用户，商家订单，订单明细，买方订单 分片规则： “用户表”---user_id 取模，“商家订单”----seller_user_id 取模 “订单明细”----“商家订单”ER 分片，“买家订单”----buyer_user_id 取模 2、表
Mycat之——实现ER分片
冰河的专栏
03-04 1278
【前言】引出ER分片 在《Mycat之——设置全局自增id》一文中，我们为水平分片的数据表设置了全局自增的id值，解决了由于分片后各自数据节点的数据表自增，导致Mycat查询数据时，出现的id值重复的问题。 此时，我们继续在Mycat下执行如下SQL语句来关联查询order_master数据表和order_detail数据表的数据。 mysql> select * from order_ma...
mycat高可用数据库集群实战(2)
05-23
在第一篇文章中，我们介绍了如何使用Mycat实现高可用数据库集群，包括Mycat的安装和配置，以及MySQL主从复制的设置。在本文中，我们将继续介绍如何使用Mycat来提高数据库的可用性。 1. 使用Mycat实现读写分离 在上一篇文章中，我们已经将MySQL数据库设置成了主从复制，主库用于写操作，从库用于读操作。在Mycat中，我们可以使用读写分离来进一步提高数据库的性能和可用性。 首先，需要在Mycat的server.xml配置文件中添加读写分离的配置： ``` <system> <property name="balanceType">1</property> <property name="writeType">0</property> <property name="readType">1</property> </system> ``` 其中，balanceType表示负载均衡的方式，1表示使用轮询算法；writeType表示写操作的路由方式，0表示直接路由到主库；readType表示读操作的路由方式，1表示路由到从库。 然后，在Mycat的schema.xml配置文件中，需要为每个表指定读写分离的规则，例如： ``` <table name="user"> <rule> <columns>id</columns> <algorithm>modula(#id, 2)</algorithm> </rule> <childTable /> <rule> <columns>id</columns> <algorithm>modula(#id, 2)</algorithm> </rule> </table> ``` 其中，<columns>指定用于路由的列，<algorithm>指定路由算法。在上面的例子中，我们使用modula算法将id为奇数的记录路由到从库1，将id为偶数的记录路由到从库2。 2. 使用Mycat实现分库分表 当单个数据库无法满足应用程序的需求时，我们可以通过分库分表的方式来扩展数据库的容量和性能。在Mycat中，可以通过配置schema.xml文件来实现分库分表。 首先，需要在Mycat的server.xml文件中添加数据源的配置： ``` <dataHost name="ds0" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host1" url="jdbc:mysql://host1:3306/test" user="root" password="123456"/> <readHost host="host2" url="jdbc:mysql://host2:3306/test" user="root" password="123456"/> <readHost host="host3" url="jdbc:mysql://host3:3306/test" user="root" password="123456"/> </dataHost> ``` 其中，name属性指定数据源的名称，maxCon和minCon指定连接池的大小，writeType和readType指定路由方式，dbType指定数据库类型，dbDriver指定数据库驱动，switchType指定连接池的切换方式，slaveThreshold指定从库的阈值，heartbeat指定心跳SQL。 然后，在schema.xml文件中，需要为每个表设置分库分表的规则，例如： ``` <table name="user" dataNode="dn1,dn2"> <rule> <columns>id</columns> <algorithm>modula(#id, 2)</algorithm> </rule> <childTable /> <rule> <columns>id</columns> <algorithm>modula(#id, 2)</algorithm> </rule> </table> <dataNode name="dn1" dataHost="ds0" database="test1" /> <dataNode name="dn2" dataHost="ds0" database="test2" /> ``` 其中，<dataNode>指定数据节点的配置，name属性指定节点名称，dataHost属性指定数据源名称，database属性指定数据库名称。在上面的例子中，我们使用modula算法将id为奇数的记录路由到dn1节点，将id为偶数的记录路由到dn2节点。 3. 总结 通过使用Mycat实现读写分离和分库分表，我们可以进一步提高数据库的可用性和性能。除此之外，Mycat还支持水平和垂直扩展、数据备份和恢复等功能，可以满足各种应用场景的需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值