1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
--source include/have_sequence.inc
--source include/have_innodb.inc
#
# Test various combinations of operations on sequence
#
--echo #
--echo # Create and check
--echo #
create sequence s1 engine=innodb;
check table s1;
select next value for s1;
flush tables;
check table s1;
select next value for s1;
flush tables;
repair table s1;
select next value for s1;
drop sequence s1;
create or replace sequence s1 engine=innodb;
select next value for s1;
repair table s1;
check table s1;
select next value for s1;
select * from s1;
drop sequence s1;
--echo #
--echo # INSERT
--echo #
create sequence s1;
create sequence s2;
--error ER_NO_DEFAULT_FOR_FIELD
insert into s1 (next_not_cached_value, minimum_value) values (100,1000);
--error ER_UPDATE_TABLE_USED
insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
--error ER_WRONG_INSERT_INTO_SEQUENCE
insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
--error ER_WRONG_INSERT_INTO_SEQUENCE
insert into s1 select * from s2;
--error ER_SEQUENCE_INVALID_DATA
insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
--error ER_SEQUENCE_INVALID_DATA
insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0);
select * from s1;
insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0);
select * from s1;
select next value for s1;
select * from s1;
--error ER_SEQUENCE_INVALID_DATA
insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
drop sequence s1,s2;
--echo #
--echo # UPDATE and DELETE
--echo #
create sequence s1;
--error ER_ILLEGAL_HA
update s1 set next_not_cached_value=100;
--error ER_ILLEGAL_HA
delete from s1 where next_not_cached_value > 0;
drop sequence s1;
--echo #
--echo # SHOW TABLES
--echo #
create sequence s1;
create table t1 (a int);
create view v1 as select * from s1;
show full tables;
SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test";
drop table t1,s1;
drop view v1;
--echo #
--echo # LOCK TABLES (as in mysqldump)
--echo #
create sequence s1 engine=innodb;
LOCK TABLES s1 READ;
SELECT * from s1;
UNLOCK TABLES;
LOCK TABLES s1 WRITE;
insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0);
UNLOCK TABLES;
drop table s1;
--echo #
--echo # Many sequence calls with innodb
--echo #
create sequence s1 cache=1000 engine=innodb;
start transaction;
select count(nextval(s1)) from seq_1_to_2000;
commit;
select * from s1;
drop sequence s1;
create sequence s1 cache=1000 engine=innodb;
start transaction;
select count(nextval(s1)) from seq_1_to_2000;
connect (addconroot, localhost, root,,);
connection addconroot;
start transaction;
select count(nextval(s1)) from seq_1_to_2000;
select * from s1;
commit;
disconnect addconroot;
connection default;
select * from s1;
commit;
drop sequence s1;
--echo #
--echo # Flush tables with read lock
--echo #
create sequence s1;
select next value for s1;
flush tables with read lock;
--error 1223
create sequence s2;
--error 1223
select next value for s1;
unlock tables;
drop sequence s1;
--echo #
--echo # MDEV-14761
--echo # Assertion `!mysql_parse_status || thd->is_error() ||
--echo # thd->get_internal_handler()' failed in parse_sql
--echo #
CREATE SEQUENCE s1;
--error ER_DUP_ARGUMENT
ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE;
DROP SEQUENCE s1;
|
