MYCAT Global Serial Number

The database self-increment primary key cannot guarantee the global uniqueness of the self-increment primary key in the case of implementing the Sub-Library table. To do this, MYCAT provides a global sequence and provides a variety of implementations, including local configuration and database configuration.

Local file Mode

Principle: This way mycat the sequence configuration to a file, Mycat will be more conf in sequence_conf.properties file when using the configuration in sequence sequence the current value.

Such as:

#default Global Sequenceglobal.hisids=Global. MiniD=10001GLOBAL. Maxid=20000GLOBAL. Curid=10000

Where Hisids represents used historical segments (typically no special needs are not configurable), MiniD represents the minimum ID value, MAXID represents the maximum ID value, and Curid represents the current ID value.

How to use:

The configuration in Server.xml is as follows:

< system ><  name= "Sequncehandlertype">0</Property></  system>

where 0indicates the use of local file mode.

The test is as follows:

Mysql> Create TableTest (IDint, namevarchar( -)); Query OK,0Rows Affected (0.13sec) MySQL> Insert  intoTest (Id,name)Values(NextValue forMycatseq_global,@ @hostname); Query OK,1Row affected,1Warning (0.03sec) MySQL> Select *  fromtest;+-------+--------------+|Id|Name|+-------+--------------+| 10001 |Mysql-Slave2|+-------+--------------+1Rowinch Set(0.02Sec

At this point, the Global.curid value in Sequence_conf.properties is 10001. Of course, you can use any of the rules defined in sequence_conf.properties, such as:

# self define sequencecompany.hisids= Company. MiniD=1001company. Maxid=company. Curid= +

Mysql> Insert  intoTest (Id,name)Values(NextValue forMycatseq_company,@ @hostname); Query OK,1Row affected,1Warning (0.02sec) MySQL> Select *  fromtest;+-------+--------------+|Id|Name|+-------+--------------+| 10001 |Mysql-Slave1||  1001 |Mysql-Slave1|+-------+--------------+2Rowsinch Set(0.10Sec

This can be customized.

Database mode

Principle

Create a table in the database, hold the sequence name, sequence the current value (current_value), the step (the increment int type reads the number of sequence each time, assuming K) and other information;

Sequence Get steps:

1). When using the sequence for the first time, read current_value from the database, and increment to Mycat, based on the incoming sequence name, and set current_value in the database to the original Current_ Value value +increment (Implementation is based on subsequent storage functions)

2). Mycat will read to current_value+increment as the sequence value for this secondary use, the next time you use, automatically add 1, when using increment times, perform step 1) the same operation.
Mycat is responsible for maintaining this table and what sequence to use, just insert a record in this table. If the sequence of a read is not exhausted, the system stops, and the sequence remaining value of this read is no longer used.

How to use:

1. Configuring the Server.xml File

< system ><  name= "Sequncehandlertype">1</Property></  system>

1 represents the generation of sequence using a database method

2. Configure the database

Create tables and stored procedures in a database that corresponds to one of the Shard points

The configuration information that I datanode in Schem.xml is as follows:

<name= "Dn$1-3"  datahost= "Localhost1"  database= "Db$1-3"/>

For example, I created in DN2, the corresponding database name is DB2 (why this involves datanode, because subsequent sequence_db_conf.properties files will be used), note that you are logging into the database and not creating it in Mycat

1> Creating a mycat_sequence table

Mysql> DROP TABLE IF EXISTSmycat_sequence; Query OK,0Rows affected,1Warning (0.00sec) MySQL> CREATE TABLEMycat_sequence (nameVARCHAR( -) not NULL, Current_valueINT  not NULL, incrementINT  not NULL DEFAULT  -,PRIMARY KEY(name)) ENGINE=InnoDB; Query OK,0Rows Affected (0.07Sec

Where: – name sequence
–current_value Current value
– increment growth step ! You can understand how many sequence mycat read in the database at a time . When these are exhausted , the next time you read from the database .

Note: mycat_sequence must be capitalized.

2> inserting sequence Records

MySQL>INSERTintoVALUES ('mycat' 1  -  1 row affected (0.01 sec)

The delegate inserts a sequence named Mycat with a current value of 1 and a step of 100.

3> Create a storage function.

Note: It must be created in the same database, in this case, DB2. Altogether to create three.

– Gets The value of the current sequence (returns the current value, increment)

    DROP FUNCTION IF EXISTSMycat_seq_currval; DELIMITER $CREATE FUNCTIONMycat_seq_currval (seq_nameVARCHAR( -))RETURNS varchar( -) CHARSET UTF8 deterministicBEGIN    DECLAREretvalVARCHAR( -); SETretval="-999999999,NULL"; SELECTConcatCAST(Current_value as CHAR),",",CAST(Increment as CHAR)) intoretval fromMycat_sequenceWHEREName=Seq_name; RETURNretval; END$ DELIMITER;

– Set the sequence value

    DROP FUNCTION IF EXISTSMycat_seq_setval; DELIMITER $CREATE FUNCTIONMycat_seq_setval (seq_nameVARCHAR( -), ValueINTEGER)RETURNS     varchar( -) CHARSET UTF8 deterministicBEGIN    UPDATEmycat_sequenceSETCurrent_value=valueWHEREName=Seq_name; RETURNMycat_seq_currval (seq_name); END$ DELIMITER;

– Get the next sequence value

    DROP FUNCTION IF EXISTSMycat_seq_nextval; DELIMITER $CREATE FUNCTIONMycat_seq_nextval (seq_nameVARCHAR( -))RETURNS varchar( -) CHARSET UTF8 deterministicBEGIN    UPDATEmycat_sequenceSETCurrent_value=Current_value+IncrementWHEREName=Seq_name; RETURNMycat_seq_currval (seq_name); END$ DELIMITER;

At this point, the database preparation has been completed.

3. Add the following to the Sequence_db_conf.properties file in the Mycat conf directory:

Mycat=dn2

Where the DN2 corresponds to the database DB2 Datanode

Note: The mycat must be uppercase, regardless of whether the data in the table is uppercase or not, in fact, the case of name in Mycat_sequence has no effect on the result.

To start the test:

Login Mycat Client

# MYSQL-H127.0.0.1-UTEST-PTEST-P8066-DTESTDB

Create a test table

MySQL>createtableintvarchar(0 rows Affected (0.22 sec)

Inserting data

Insert the following format:

Insert INTO table1 (id,name) VALUES (next value for Mycatseq_global, ' test ');

Mysql>Mysql> Insert  intoTest (Id,name)Values(NextValue forMycatseq_mycat,'test1'); ERROR1003(HY000): Mycat sequnce Err.org.opencloudb.config.util.ConfigException:can't find definition for sequence:mycatmysql> insert in test (Id,name) values (next value for Mycatseq_mycat,'Test1'); Query OK, 1 row affected (0.11 sec) mysql> select * from test;+------+-------+| ID |  Name |+------+-------+| 101 | Test1 |+------+-------+1 row in Set (0.09 sec)

The following error is reported for the first time:

ERROR 1003 (HY000): Mycat sequnce err.org.opencloudb.config.util.ConfigException:can ' t find definition for SEQUENCE:MYC At

Because sequence_db_conf.properties modification of the current mycat is not known, this time, you can restart the Mycat or login 9066 management port for reload @ @config;

At this point, the test is complete, the key is still two points: Mycat_sequence must be capitalized, sequence_db_conf.properties file mycat=dn2 must be capitalized.

Song Defu

MYCAT Global Serial Number