五分钟带你玩转mycat（三）mycat异常集合

#创建数据库
create database mycat;
#添加表
use mycat 
#创建表test
create table test6(id varchar(18) primary key,name varchar(32)) ;
#创建数据库
create database mycat2;
#添加表
use mycat 2
#创建表test
create table test6(id varchar(18) primary key,name varchar(32)) ;

#编辑添加table
vim conf/schemal.xml
#添加表,设定rule="auto-sharding-long" ,自动根据范围分片
<table name="test6" dataNode="jdbc_node1,jdbc_node2" primaryKey="id" autoIncrement="true" rule="auto-sharding-long"/>

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="yellowcong" checkSQLschema="false" sqlMaxLimit="1000">
                <table name="test6" dataNode="jdbc_node1,jdbc_node2" primaryKey="id" autoIncrement="true" rule="auto-sharding-long"/>
        </schema>
    <!--多个数据库-->
        <dataNode name="jdbc_node1" dataHost="localhost" database="mycat" />
        <dataNode name="jdbc_node2" dataHost="localhost" database="mycat2" />
        <!-- 快递员表，非分片表 -->
        <dataHost name="localhost" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root" />
        </dataHost>   
</mycat:schema>

#编辑
vim ./conf/server.xml
#确保这个id的生成策略为2 ，表示为根据时间戳
<property name="sequnceHandlerType">2</property>

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
        - you may not use this file except in compliance with the License. - You
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
        - - Unless required by applicable law or agreed to in writing, software -
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
        License for the specific language governing permissions and - limitations
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="serverPort">8066</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
                <property name="sequnceHandlerType">2</property>
                <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</property>
                <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
                <property name="handleDistributedTransactions">0</property>
                        <!--
                        off heap for merge/order/group/limit      1开启   0关闭
                -->
                <property name="useOffHeapForMerge">1</property>
                <!--
                        单位为m
                -->
                <property name="memoryPageSize">1m</property>
                <!--
                        单位为k
                -->
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <!--
                        单位为m
                -->
                <property name="systemReserveMemorySize">384m</property>
                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">true</property>
        </system>
        <user name="root">
                <property name="password">root</property>
                <property name="schemas">yellowcong</property>             
        </user>
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">yellowcong</property>
                <property name="readOnly">true</property>
        </user>
</mycat:server>

vim ./conf/rule.xml
#添加defaultNode ，设定默认节点为0 ，不设定就会报错
<function name="rang-long"
         class="io.mycat.route.function.AutoPartitionByLong">
         <property name="mapFile">autopartition-long.txt</property>
         <property name="defaultNode">0</property>
 </function>

vim ./conf/autopartition-long.txt
#默认是三个，我们需要删除最后一个，不然就会报错，说节点少了
#K=1000条记录，M=10000条记录，那么下面三个配置就是0~500万的记录会存在数据库节点1的表中，500万~1000万会存在节点2的表中
0-500M=0
500M-1000M=1

#进入mycat
mysql -h 127.0.0.1 -P 8066 -u root -proot
#使用yellowcong数据库
use yellowcong;
#插入多条数据
insert into test6 (name) values ('doubi1'),('doubi2'),('doubi3'),('yellowcong');

#进入物理节点
mysql -h 127.0.0.1 -P 3306 -u root -proot
#查看节点1，也就是默认节点
select * from mycat.test6;
#查看节点2
select * from mycat2.test6;

<span style="color:#000000"><code> Caused by: io.mycat.config.util.ConfigException: Illegal table conf : table [ TEST2 ] rule function [ rang-long ] <span style="color:#000088 !important">partition</span> <span style="color:#000088 !important">size</span> : <span style="color:#006666 !important">3</span> > table datanode <span style="color:#000088 !important">size</span> : <span style="color:#006666 !important">2</span>, please make sure table datanode <span style="color:#000088 !important">size</span> = function <span style="color:#000088 !important">partition</span> <span style="color:#000088 !important">size</span></code></span>

#修改后的语句
insert into test2(name) values('doubi1'),('doubi2'),('doubi3'),('yellowcong');

#原来语句
insert into test2 (null,'doubi1'),(null,'doubi2'),(null,'doubi3'),(null,'yellowcong');
#修改后的语句
insert into test2(name) values('doubi1'),('doubi2'),('doubi3'),('yellowcong');

ERROR 1064 (HY000): can't find any valid datanode :TEST2 -> ID -> 953095995447709696

vim conf/server.xml
0 表示是表示使用本地文件方式。
1 表示的是根据数据库来生成
2 表示时间戳的方式 ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
<property name="sequnceHandlerType">2</property>