Mycat's database primary key self growth (local timestamp) - yellow Tong

Keywords: mycat xml Database vim

In the local time mode, the policy ID automatically generated by ID= 64 bit binary (42 (MS) + 5 (machine ID) + 5 (business code) + 12 (repeated accumulation). The local time stamp mode is mycat's default, so when you configure it, you need to confirm whether the sequnceHandlerType configuration in your server.xml is 2. To realize the local timestamp, the automatically generated ID is 18 bits, so when you create a table, you need to pay attention to the size of the primary key.

Create database

#Create database
create database mycat;

#add table
use mycat 
#Create table test
create table test5(id varchar(18) primary key,name varchar(32)) ;

#Create database
create database mycat3;

#add table
use mycat2
#Create table test
create table test5(id varchar(18) primary key,name varchar(32)) ;

Configure MAC at

1. Configure schema.xml

#Edit the configuration document of schema.xml
vim ./conf/schema.xml

#Add virtual table configuration to this place.
 <table name="test5" dataNode="jdbc_node1,jdbc_node2" primaryKey="id" autoIncrement="true" type="global"/>

Here is the complete configuration

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="yellowcong" checkSQLschema="true" sqlMaxLimit="1000">    
                 <table name="test5" dataNode="jdbc_node1,jdbc_node2" primaryKey="id" autoIncrement="true" type="global"/>

        <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="" user="root" password="root" />



Configure server.xml

#Modify server.xml of mycat
vim ./conf/server.xml

#Modify sequnceHandlerType to 2 to indicate the local timestamp method
<property name="sequnceHandlerType">2</property>

Here is the complete configuration

<?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 - -
        - - 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/">
        <property name="serverPort">8066</property>
        <property name="useSqlStat">0</property>  <!-- 1 To turn on real-time statistics, 0 to turn off -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1 To enable full overtime consistency detection, 0 to close -->

                <property name="sequnceHandlerType">2</property>

                <property name="processorBufferPoolType">0</property>

                <!--Distributed transaction switch, 0 for unfiltered distributed transactions, 1 for filtered distributed transactions (if only global tables are involved in distributed transactions, then no filtering), 2 for unfiltered distributed transactions,But log distributed transactions-->
                <property name="handleDistributedTransactions">0</property>

                        off heap for merge/order/group/limit      1 On 0 off
                <property name="useOffHeapForMerge">1</property>

                        Unit is m
                <property name="memoryPageSize">1m</property>

                        Unit is k
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>

                        Unit is m
                <property name="systemReserveMemorySize">384m</property>

                <!--Whether to adopt zookeeper Coordinated switching  -->
                <property name="useZKSwitch">true</property>


        <user name="root">
                <property name="password">root</property>
                <property name="schemas">yellowcong</property>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">yellowcong</property>
                <property name="readOnly">true</property>


Configure sequence? Time?

The following is to configure the machine ID. It is not modified by default.

vim ./conf/

#sequence depend on TIME

Test insert data

Restart mycat server

#Connect mycat
mysql -h -P 8066 -u root -proot

#Using yellowocng database
use yellowcong;

#insert data
insert into test5 (name) values ('doubi'),('yellowcong');

ID generation rule ID= 64 bit binary (42 (MS) + 5 (machine ID) + 5 (business code) + 12 (repeated accumulation)

Problem set

Column count doesn't match value count at row 1

The problem is that I wrote NIMA sql wrong. It's embarrassing

Field 'id' doesn't have a default value

This problem means that there is no default id specified. The solution is to configure schema.xml and add autoIncrement="true"

Reference article

Posted by amitkrathi on Sat, 02 May 2020 13:08:23 -0700