Enumeration of common MyCAT sharding rules

Enumeration of common MyCAT sharding rules

MyCAT supports multiple sharding rules. In the following example, partition enumeration is used. In applicable scenarios, the number of column values is fixed, such as province and month.

Here, three values need to be defined. Rules are defined in rule. xml.

1. tableRule

2. function

3. mapFile

First, define tableRule,

   <tableRule name="sharding-by-intfile-test">                <rule>                        <columns>province</columns>                        <algorithm>hash-int-test</algorithm>                </rule>    </tableRule>

Sharding-by-intfile-test is the rule name and is used in schema. xml.

Columns refers to sharding of provinces.

Algorithm is the algorithm name, which must be defined in the function.

Second, define the function,

      <function name="hash-int-test"                class="org.opencloudb.route.function.PartitionByFileMap">                <property name="mapFile">partition-hash-int-test.txt</property>                <property name="type">1</property>                <property name="defaultNode">0</property>      </function>

MapFile indicates the configuration file name.

The default value of type is 0, 0 indicates Integer, and non-zero indicates String. Because my next test is based on province shards, You need to specify type as 1.

Defanode node default node: smaller than 0 indicates that no default node is set, and greater than or equal to 0 indicates that the default node is set.
Default node: If you encounter an unrecognized enumeration value when enumerating parts, route it to the default node. If the default node is not configured (if the defanode node value is smaller than 0, it indicates that the default node is not configured), an error will be reported when encountering an unrecognized enumeration value. like this: can't find datanode for sharding column: column_name val: ffffffff

Third, create the partition-hash-int-test.txt file in the directory of rule.xml, and define the enumeration rules.

hubei=0tianjin=1guangdong=2

Among them, hubei is distributed to the first node, tianjin is distributed to the second node, and guangdong is in the third node.

Start testing now,

First, define the table sharding rules in shema. xml.

<table name="t_vote" dataNode="dn2,dn1,dn3" rule="sharding-by-intfile-test" />

Log on to MyCAT to create a table

# Mysql-utest-ptest-h127.0.0.1-P8066-DTESTDB

mysql> create table t_vote(id varchar(10),province varchar(10));Query OK, 0 rows affected (0.04 sec)mysql> insert into t_vote(id,province) values(database(),'hubei');Query OK, 1 row affected (0.01 sec)mysql> insert into t_vote(id,province) values(database(),'tianjin');Query OK, 1 row affected (0.01 sec)mysql> insert into t_vote(id,province) values(database(),'guangdong');Query OK, 1 row affected (0.01 sec)mysql> insert into t_vote(id,province) values(database(),'shandong');Query OK, 1 row affected (0.01 sec)mysql> select * from t_vote;+------+-----------+| id   | province  |+------+-----------+| db1  | tianjin   || db2  | hubei     || db2  | shandong  || db3  | guangdong |+------+-----------+4 rows in set (0.03 sec)

Hubei is db2 because it corresponds to the first node. When schema. xml defines the t_vote table, the first node is dn2.

Shandong is in db2 because it is not defined in the sharding rule, so it will be assigned to the default node. The default node defaultNode is 0, that is, the first node.

Summary:

1. At the beginning, no type value is specified in the function, that is, <property name = "type"> 1 </property>. The following error is reported during insert because the default partition type is integer rather than string.

mysql> insert into t_vote(id,province) values(database(),'hubei');ERROR 1064 (HY000): For input string: "hubei"

2. If the defanode node value in the function is not specified, the following error is returned when an unrecognized enumerated value is inserted:

mysql>  insert into t_vote(id,province) values(database(),'shandong');ERROR 1064 (HY000): can't find any valid datanode :T_VOTE -> PROVINCE -> shandong

3. MyCAT also supports dynamic loading.

For example, I set the value of defanodenodeto partition-hash-int-test.txt. This is also supported. Of course, the default node value in functionis removed. The default node value in functionoverwrites the default node value in partition-hash-int-test.txt.

hubei=0tianjin=1guangdong=2DEFAULT_NODE=2

Log on to the MyCAT 9066 Management Port

# Mysql-utest-ptest-h127.0.0.1-P9066

mysql> reload @@config;Query OK, 1 row affected (0.03 sec)Reload config success

Log on to port MyCAT 8066.

mysql> insert into t_vote(id,province) values(database(),'shanxi');Query OK, 1 row affected (0.00 sec)mysql> select * from t_vote;+------+-----------+| id   | province  |+------+-----------+| db1  | tianjin   || db2  | hubei     || db2  | shandong  || db3  | guangdong || db3  | shanxi    |+------+-----------+5 rows in set (0.01 sec)

4. There is a problem with this rule. If the defanode node value is modified midway through the rule, for example, if I set 0 at the beginning and inserted the shandong value, it is now changed to 2.

mysql> select * from t_vote;+------+-----------+| id   | province  |+------+-----------+| db1  | tianjin   || db3  | guangdong || db2  | shanxi    || db2  | hubei     || db2  | shandong  |+------+-----------+5 rows in set (0.01 sec)mysql> select * from t_vote where province='shandong';Empty set (0.00 sec)

The original inserted value cannot be retrieved Based on the where condition.

The information displayed in the log is as follows:

01/24 15:17:50.028  DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect * from t_vote where province='shandong'01/24 15:17:50.029  DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from t_vote where province='shandong' value:select * from t_vote where province='shandong', route={   1 -> dn3{SELECT *FROM t_voteWHERE province = 'shandong'LIMIT 100}}

Dn3 is the node corresponding to defanode node = 2.

This article permanently updates the link address: