mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
1094 lines
29 KiB
Text
1094 lines
29 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
connection master;
|
|
create database s_db;
|
|
use s_db;
|
|
grant all on s_db.* to normal_1@'%' identified by 'pass';
|
|
grant all on test.* to normal_2@'%' identified by 'pass';
|
|
grant all on s_db.* to normal_3@'%' identified by 'pass';
|
|
grant all on test.* to normal_4@'%' identified by 'pass';
|
|
connection slave;
|
|
connect m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT;
|
|
connect m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT;
|
|
connect s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT;
|
|
connect s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT;
|
|
connection slave;
|
|
set global read_only=on;
|
|
use s_db;
|
|
###########################################
|
|
master and slave sync sequence.
|
|
###########################################
|
|
connection master;
|
|
create sequence s1;
|
|
show create table s1;
|
|
Table Create Table
|
|
s1 CREATE TABLE `s1` (
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT NULL,
|
|
`maximum_value` bigint(21) NOT NULL,
|
|
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
|
|
`increment` bigint(21) NOT NULL COMMENT 'increment value',
|
|
`cache_size` bigint(21) unsigned NOT NULL,
|
|
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
|
|
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
|
|
) ENGINE=MyISAM SEQUENCE=1
|
|
connection slave;
|
|
show create table s1;
|
|
Table Create Table
|
|
s1 CREATE TABLE `s1` (
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT NULL,
|
|
`maximum_value` bigint(21) NOT NULL,
|
|
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
|
|
`increment` bigint(21) NOT NULL COMMENT 'increment value',
|
|
`cache_size` bigint(21) unsigned NOT NULL,
|
|
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
|
|
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
|
|
) ENGINE=MyISAM SEQUENCE=1
|
|
connection master;
|
|
drop sequence s1;
|
|
###########################################
|
|
not support create table engine=sequence.
|
|
###########################################
|
|
connection master;
|
|
create table t(id int)engine=sequence;
|
|
ERROR 42000: Unknown storage engine 'sequence'
|
|
create table t(id int)engine=innodb;
|
|
alter table t engine=sequence;
|
|
ERROR 42000: Unknown storage engine 'sequence'
|
|
drop table t;
|
|
###########################################
|
|
not support alter sequence table.
|
|
###########################################
|
|
connection master;
|
|
create sequence s2;
|
|
alter table s2 add id int;
|
|
ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns)
|
|
alter table s2 add index ind_x(start_value);
|
|
ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys)
|
|
drop sequence s2;
|
|
###########################################
|
|
support create sequence
|
|
###########################################
|
|
connection master;
|
|
create table t_1(id int);
|
|
show create sequence t_1;
|
|
ERROR 42S02: 's_db.t_1' is not a SEQUENCE
|
|
drop table t_1;
|
|
CREATE SEQUENCE `s2` (
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT NULL,
|
|
`maximum_value` bigint(21) NOT NULL,
|
|
`start_value` bigint(21) NOT NULL,
|
|
`increment` bigint(21) NOT NULL,
|
|
`cache_size` bigint(21) unsigned NOT NULL,
|
|
`cycle_option` tinyint(1) unsigned NOT NULL,
|
|
`cycle_count` bigint(21) NOT NULL
|
|
) ENGINE=InnoDB sequence=1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT...' at line 1
|
|
CREATE TABLE `s2` (
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT NULL,
|
|
`maximum_value` bigint(21) NOT NULL,
|
|
`start_value` bigint(21) NOT NULL,
|
|
`increment` bigint(21) NOT NULL,
|
|
`cache_size` bigint(21) unsigned NOT NULL,
|
|
`cycle_option` tinyint(1) unsigned NOT NULL,
|
|
`cycle_count` bigint(21) NOT NULL
|
|
) ENGINE=InnoDB sequence=1;
|
|
insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0);
|
|
commit;
|
|
select * for s2;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2' at line 1
|
|
select * from s2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 10 1 2 1 1 0
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
1
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
3
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
5
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
7
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
9
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
1
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
3
|
|
select * from s2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 10 1 2 1 1 1
|
|
commit;
|
|
connection master;
|
|
connection slave;
|
|
select * from s2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 10 1 2 1 1 1
|
|
connection master;
|
|
drop sequence s2;
|
|
CREATE TABLE `s2` (
|
|
`next_not_cached_value` bigint(21) NOT NULL,
|
|
`minimum_value` bigint(21) NOT NULL,
|
|
`maximum_value` bigint(21) NOT NULL,
|
|
`start_value` bigint(21) NOT NULL,
|
|
`increment` bigint(21) NOT NULL,
|
|
`cache_size` bigint(21) unsigned NOT NULL,
|
|
`cycle_option` tinyint(1) unsigned NOT NULL,
|
|
`cycle_count` bigint(21) NOT NULL
|
|
) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1;
|
|
show create sequence s2;
|
|
Table Create Table
|
|
s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
|
|
drop sequence s2;
|
|
###########################################
|
|
select sequence syntax test
|
|
###########################################
|
|
connection master;
|
|
create sequence s2;
|
|
create table t2 (id int);
|
|
select * from s2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 9223372036854775806 1 1 1000 0 0
|
|
select * from t2;
|
|
id
|
|
insert into t2 select next value for s2;
|
|
commit;
|
|
select NEXT VALUE for s2;
|
|
NEXT VALUE for s2
|
|
2
|
|
select NEXT VALUE for t2;
|
|
ERROR 42S02: 's_db.t2' is not a SEQUENCE
|
|
select * from s2, t2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count id
|
|
1001 1 9223372036854775806 1 1 1000 0 0 1
|
|
select * for s2;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2' at line 1
|
|
select * for s2, t2;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2, t2' at line 1
|
|
connection master;
|
|
drop sequence s2;
|
|
drop table t2;
|
|
###########################################
|
|
support rename, not support truncate
|
|
###########################################
|
|
connection master;
|
|
create sequence s2;
|
|
alter table s2 rename to s2_1;
|
|
rename table s2_1 to s2_2;
|
|
show create sequence s2_2;
|
|
Table Create Table
|
|
s2_2 CREATE SEQUENCE `s2_2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
|
|
select * from s2_2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 9223372036854775806 1 1 1000 0 0
|
|
truncate table s2_2;
|
|
ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s2_2` doesn't have this option
|
|
rename table s2_2 to s2;
|
|
drop sequence s2;
|
|
###########################################
|
|
all invalid sequence value
|
|
###########################################
|
|
connection master;
|
|
create sequence s2 start with 1
|
|
minvalue 1
|
|
maxvalue 100000
|
|
increment by 1
|
|
cache 10000
|
|
cycle;
|
|
drop sequence s2;
|
|
create sequence s2 start with 1
|
|
minvalue 1
|
|
maxvalue 100000
|
|
increment by 1
|
|
cache 10000
|
|
nocycle;
|
|
drop sequence s2;
|
|
create sequence s2 start with 1
|
|
minvalue 1
|
|
maxvalue 100000
|
|
increment by 1
|
|
nocache
|
|
nocycle;
|
|
drop sequence s2;
|
|
create sequence s2 start with 1
|
|
minvalue 5
|
|
maxvalue 100000
|
|
increment by 1
|
|
nocache
|
|
nocycle;
|
|
ERROR HY000: Sequence 's_db.s2' has out of range value for options
|
|
create sequence s2 start with 1
|
|
minvalue 5
|
|
maxvalue 5
|
|
increment by 1
|
|
nocache
|
|
nocycle;
|
|
ERROR HY000: Sequence 's_db.s2' has out of range value for options
|
|
create sequence s2 start with 1
|
|
minvalue 5
|
|
maxvalue 4
|
|
increment by 1
|
|
nocache
|
|
nocycle;
|
|
ERROR HY000: Sequence 's_db.s2' has out of range value for options
|
|
create sequence s2 start with 1
|
|
minvalue 5
|
|
maxvalue 4
|
|
increment by 0
|
|
nocache
|
|
nocycle;
|
|
ERROR HY000: Sequence 's_db.s2' has out of range value for options
|
|
###########################################
|
|
global read lock prevent query sequence
|
|
###########################################
|
|
connection master;
|
|
create sequence s_db.s1;
|
|
flush table with read lock;
|
|
select NEXT VALUE for s1;
|
|
ERROR HY000: Can't execute the query because you have a conflicting read lock
|
|
unlock tables;
|
|
drop sequence s_db.s1;
|
|
###########################################
|
|
query cache test
|
|
###########################################
|
|
connection master;
|
|
flush status;
|
|
show global variables like 'query_cache_type';
|
|
Variable_name Value
|
|
query_cache_type ON
|
|
show status like 'Qcache_hits';
|
|
Variable_name Value
|
|
Qcache_hits 0
|
|
show status like 'Qcache_inserts';
|
|
Variable_name Value
|
|
Qcache_inserts 0
|
|
###########################################
|
|
priv test
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s_db.s1;
|
|
select NEXT VALUE for s_db.s1;
|
|
NEXT VALUE for s_db.s1
|
|
1
|
|
create sequence s_db.s2;
|
|
drop sequence s_db.s2;
|
|
connection m_normal_2;
|
|
select NEXT VALUE for s_db.s1;
|
|
ERROR 42000: SELECT, INSERT command denied to user 'normal_2'@'localhost' for table `s_db`.`s1`
|
|
create sequence s_db.s2;
|
|
ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table `s_db`.`s2`
|
|
connection m_normal_1;
|
|
drop sequence s_db.s1;
|
|
###########################################
|
|
run out sequence value
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s_t start with 1 cache 2 maxvalue 5;
|
|
create table t(id int);
|
|
insert into t values(1111);
|
|
insert into t select next value for s_t;
|
|
insert into t select next value for s_t;
|
|
insert into t select next value for s_t;
|
|
insert into t select next value for s_t;
|
|
insert into t select next value for s_t;
|
|
insert into t select next value for s_t;
|
|
ERROR HY000: Sequence 's_db.s_t' has run out
|
|
insert into t select next value for s_t;
|
|
ERROR HY000: Sequence 's_db.s_t' has run out
|
|
commit;
|
|
select * from t;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
connection m_normal_1;
|
|
drop sequence s_t;
|
|
drop table t;
|
|
###########################################
|
|
read_only prevent query sequence
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s_db.s1;
|
|
show global variables like 'read_only';
|
|
Variable_name Value
|
|
read_only OFF
|
|
select * from s_db.s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 9223372036854775806 1 1 1000 0 0
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
show global variables like 'read_only';
|
|
Variable_name Value
|
|
read_only ON
|
|
select next value for s_db.s1;
|
|
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
|
|
connection m_normal_1;
|
|
drop sequence s_db.s1;
|
|
###########################################
|
|
update based table
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb;
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 20 1 1 5 1 0
|
|
connection m_normal_1;
|
|
select next value for s_t;
|
|
next value for s_t
|
|
1
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select next_not_cached_value from s_t;
|
|
next_not_cached_value
|
|
6
|
|
------------------------------------------
|
|
master ALTER SEQUENCE
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
select next value for s_t;
|
|
next value for s_t
|
|
2
|
|
alter sequence s_t restart= 11;
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 20 1 1 5 1 0
|
|
connection master;
|
|
connection slave;
|
|
------------------------------------------
|
|
show slave nextval;
|
|
------------------------------------------
|
|
connection s_normal_3;
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 20 1 1 5 1 0
|
|
connection m_normal_1;
|
|
select next value for s_t;
|
|
next value for s_t
|
|
11
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
16 1 20 1 1 5 1 0
|
|
------------------------------------------
|
|
update into invalid sequence
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
select next value for s_t;
|
|
next value for s_t
|
|
12
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
16 1 20 1 1 5 1 0
|
|
alter sequence s_t minvalue=11 maxvalue=9;
|
|
ERROR HY000: Sequence 's_db.s_t' has out of range value for options
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
16 1 20 1 1 5 1 0
|
|
alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20;
|
|
ERROR HY000: Sequence 's_db.s_t' has out of range value for options
|
|
select * from s_t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
16 1 20 1 1 5 1 0
|
|
------------------------------------------
|
|
delete sequence row
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
delete from s_t;
|
|
ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
|
|
commit;
|
|
select next value for s_t;
|
|
next value for s_t
|
|
13
|
|
connection m_normal_1;
|
|
drop sequence s_t;
|
|
###########################################
|
|
test transaction context (innodb)
|
|
###########################################
|
|
------------------------------------------
|
|
transaction table and sequence
|
|
normal transaction commit
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
create sequence s_1 cache 5 engine=innodb;
|
|
create table t_1(id int)engine=innodb;
|
|
begin;
|
|
insert into t_1 values(1111);
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 values(2222);
|
|
commit;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
select * from s_1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
6 1 9223372036854775806 1 1 5 0 0
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
------------------------------------------
|
|
normal transaction rollback
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
begin;
|
|
insert into t_1 values(3333);
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
3333
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
rollback;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
select * from s_1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 5 0 0
|
|
select next value for s_1;
|
|
next value for s_1
|
|
11
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
connection m_normal_1;
|
|
drop sequence s_1;
|
|
drop table t_1;
|
|
###########################################
|
|
test transaction context (myisam)
|
|
###########################################
|
|
------------------------------------------
|
|
transaction table and sequence
|
|
normal transaction commit
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
create sequence s_1 cache 5;
|
|
create table t_1(id int)engine=myisam;
|
|
begin;
|
|
insert into t_1 values(1111);
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 values(2222);
|
|
commit;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
------------------------------------------
|
|
normal transaction rollback
|
|
------------------------------------------
|
|
connection m_normal_1;
|
|
begin;
|
|
insert into t_1 values(3333);
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
insert into t_1 select next value for s_1;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
3333
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
rollback;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
3333
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
select next value for s_1;
|
|
next value for s_1
|
|
11
|
|
connection master;
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t_1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
2222
|
|
3333
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
connection m_normal_1;
|
|
drop sequence s_1;
|
|
drop table t_1;
|
|
###########################################
|
|
close binlog
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s1 cache 2;
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
select next value for s1;
|
|
next value for s1
|
|
2
|
|
select next value for s1;
|
|
next value for s1
|
|
3
|
|
select next value for s1;
|
|
next value for s1
|
|
4
|
|
commit;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 9223372036854775806 1 1 2 0 0
|
|
connection master;
|
|
connection slave;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 9223372036854775806 1 1 2 0 0
|
|
------------------------------------------
|
|
close session binlog.
|
|
------------------------------------------
|
|
connection master;
|
|
set session sql_log_bin=off;
|
|
select next value for s1;
|
|
next value for s1
|
|
5
|
|
select next value for s1;
|
|
next value for s1
|
|
6
|
|
select next value for s1;
|
|
next value for s1
|
|
7
|
|
select next value for s1;
|
|
next value for s1
|
|
8
|
|
set session sql_log_bin=on;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
9 1 9223372036854775806 1 1 2 0 0
|
|
connection master;
|
|
connection slave;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 9223372036854775806 1 1 2 0 0
|
|
connection master;
|
|
select next value for s1;
|
|
next value for s1
|
|
9
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 2 0 0
|
|
connection slave;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 2 0 0
|
|
connection master;
|
|
drop sequence s1;
|
|
###########################################
|
|
statement binlog
|
|
###########################################
|
|
------------------------------------------
|
|
set binlog_format=statement
|
|
------------------------------------------
|
|
connection master;
|
|
set session binlog_format=statement;
|
|
select @@session.binlog_format;
|
|
@@session.binlog_format
|
|
STATEMENT
|
|
create sequence s1 cache 2;
|
|
select next value for s1;
|
|
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
|
|
set session binlog_format=row;
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
connection master;
|
|
connection slave;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
3 1 9223372036854775806 1 1 2 0 0
|
|
connection m_normal_1;
|
|
drop sequence s1;
|
|
------------------------------------------
|
|
set binlog_format=mixed
|
|
------------------------------------------
|
|
connection master;
|
|
set session binlog_format=mixed;
|
|
select @@session.binlog_format;
|
|
@@session.binlog_format
|
|
MIXED
|
|
create sequence s1 cache 2;
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
set session binlog_format=row;
|
|
select next value for s1;
|
|
next value for s1
|
|
2
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
3 1 9223372036854775806 1 1 2 0 0
|
|
connection master;
|
|
connection slave;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
3 1 9223372036854775806 1 1 2 0 0
|
|
connection m_normal_1;
|
|
drop sequence s1;
|
|
connection master;
|
|
connection slave;
|
|
###########################################
|
|
test savepoint
|
|
###########################################
|
|
connection master;
|
|
set session binlog_format=row;
|
|
create sequence s1 cache 2;
|
|
create table t1(id int)engine=innodb;
|
|
begin;
|
|
insert into t1 values(1111);
|
|
savepoint sp1;
|
|
insert into t1 select next value for s1;
|
|
insert into t1 select next value for s1;
|
|
insert into t1 select next value for s1;
|
|
insert into t1 values(2222);
|
|
select * from t1;
|
|
id
|
|
1111
|
|
1
|
|
2
|
|
3
|
|
2222
|
|
rollback to sp1;
|
|
select * from t1;
|
|
id
|
|
1111
|
|
select next value for s1;
|
|
next value for s1
|
|
4
|
|
commit;
|
|
drop sequence s1;
|
|
drop table t1;
|
|
connection master;
|
|
connection slave;
|
|
###########################################
|
|
create as
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s1 cache 2;
|
|
create table t as select * from s1;
|
|
select * from t;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 9223372036854775806 1 1 2 0 0
|
|
drop table t;
|
|
create table t as select next value for s1;
|
|
select * from t;
|
|
next value for s1
|
|
1
|
|
drop table t;
|
|
drop sequence s1;
|
|
connection master;
|
|
connection slave;
|
|
###########################################
|
|
test proc
|
|
###########################################
|
|
connection m_normal_1;
|
|
create table t(id int)engine=innodb;
|
|
create procedure p1()
|
|
begin
|
|
create sequence s1 cache 2;
|
|
end//
|
|
create procedure p2()
|
|
begin
|
|
insert into t select next value for s1;
|
|
commit;
|
|
end//
|
|
call p1();
|
|
call p2();
|
|
call p2();
|
|
call p2();
|
|
call p2();
|
|
select * from t;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
connection master;
|
|
connection slave;
|
|
select * from t;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
connection m_normal_1;
|
|
drop table t;
|
|
drop sequence s1;
|
|
drop procedure p1;
|
|
drop procedure p2;
|
|
###########################################
|
|
test trigger
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s1 cache 2;
|
|
create table t1(id int)engine=innodb;
|
|
create table t2(id int)engine=innodb;
|
|
CREATE TRIGGER tri_1
|
|
before INSERT ON t2 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t1 select next value for s1;
|
|
END//
|
|
begin;
|
|
insert into t2 values(1111);
|
|
insert into t2 values(1111);
|
|
insert into t2 values(1111);
|
|
insert into t2 values(1111);
|
|
select * from t2;
|
|
id
|
|
1111
|
|
1111
|
|
1111
|
|
1111
|
|
select * from t1;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
rollback;
|
|
select * from t2;
|
|
id
|
|
select * from t1;
|
|
id
|
|
select next value for s1;
|
|
next value for s1
|
|
5
|
|
drop trigger tri_1;
|
|
drop table t1;
|
|
drop table t2;
|
|
drop sequence s1;
|
|
###########################################
|
|
test function
|
|
###########################################
|
|
connection m_normal_1;
|
|
create sequence s1 cache 2;
|
|
create table t1(id int)engine=innodb;
|
|
CREATE function f1() returns int
|
|
BEGIN
|
|
INSERT INTO t1 select next value for s1;
|
|
return (1);
|
|
END//
|
|
begin;
|
|
select f1();
|
|
f1()
|
|
1
|
|
select f1();
|
|
f1()
|
|
1
|
|
select f1();
|
|
f1()
|
|
1
|
|
select f1();
|
|
f1()
|
|
1
|
|
select * from t1;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
rollback;
|
|
select * from t1;
|
|
id
|
|
select next value for s1;
|
|
next value for s1
|
|
5
|
|
drop function f1;
|
|
drop table t1;
|
|
drop sequence s1;
|
|
###########################################
|
|
test value boundary
|
|
###########################################
|
|
connection m_normal_1;
|
|
------------------------------------------
|
|
cycle_count increment by cycle_count
|
|
------------------------------------------
|
|
create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle;
|
|
select next value for s1;
|
|
next value for s1
|
|
5
|
|
select next value for s1;
|
|
next value for s1
|
|
6
|
|
select next value for s1;
|
|
next value for s1
|
|
7
|
|
select next value for s1;
|
|
next value for s1
|
|
2
|
|
select next value for s1;
|
|
next value for s1
|
|
3
|
|
drop sequence s1;
|
|
create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle;
|
|
select next value for s1;
|
|
next value for s1
|
|
5
|
|
select next value for s1;
|
|
next value for s1
|
|
6
|
|
select next value for s1;
|
|
next value for s1
|
|
7
|
|
select next value for s1;
|
|
ERROR HY000: Sequence 's_db.s1' has run out
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
8 2 7 5 1 10 0 0
|
|
drop sequence s1;
|
|
create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle;
|
|
select next value for s1;
|
|
next value for s1
|
|
2
|
|
select next_not_cached_value,cycle_count from s1;
|
|
next_not_cached_value cycle_count
|
|
4 0
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
select next_not_cached_value,cycle_count from s1;
|
|
next_not_cached_value cycle_count
|
|
4 1
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
select next_not_cached_value,cycle_count from s1;
|
|
next_not_cached_value cycle_count
|
|
4 2
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
select next_not_cached_value,cycle_count from s1;
|
|
next_not_cached_value cycle_count
|
|
4 3
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
select next_not_cached_value,cycle_count from s1;
|
|
next_not_cached_value cycle_count
|
|
4 4
|
|
drop sequence s1;
|
|
create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle;
|
|
select next value for s1;
|
|
next value for s1
|
|
2
|
|
select next value for s1;
|
|
ERROR HY000: Sequence 's_db.s1' has run out
|
|
drop sequence s1;
|
|
------------------------------------------
|
|
beyond ulonglong maxvalue
|
|
------------------------------------------
|
|
create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle;
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 3
|
|
drop sequence s1;
|
|
create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle;
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 0
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 1
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775806 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775804 2
|
|
select next value for s1, cycle_count from s1;
|
|
next value for s1 cycle_count
|
|
9223372036854775805 3
|
|
drop sequence s1;
|
|
###########################################
|
|
test default()
|
|
###########################################
|
|
connection master;
|
|
CREATE SEQUENCE s1 nocache engine=myisam;
|
|
CREATE table t1 (a int default next value for s1, b int);
|
|
insert into t1 (b) values (1),(2);
|
|
select default(a) from t1;
|
|
default(a)
|
|
3
|
|
4
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 9223372036854775806 1 1 0 0 0
|
|
connection slave;
|
|
connection s_normal_3;
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
5 1 9223372036854775806 1 1 0 0 0
|
|
connection master;
|
|
drop table t1,s1;
|
|
connection master;
|
|
drop database s_db;
|
|
drop user normal_1@'%';
|
|
drop user normal_2@'%';
|
|
drop user normal_3@'%';
|
|
drop user normal_4@'%';
|
|
include/rpl_end.inc
|