oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: luofan.zp
5
# owner group: SQL3
6
# description: OUR GOAL: Make all this simple and effective!
7
# init status (autocommit=1, not_trx)
8
show variables like 'autocommit';
9

10
--echo case 1: (autocommit=1, not_trx) // start transaction
11
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
12
connection conn1;
13
show variables like 'autocommit';
14
--disable_warnings
15
drop table if exists t1;
16
--enable_warnings
17
create table t1 (c1 int primary key, c2 int);
18
start transaction;
19
insert into t1 values (1, 2);
20
select * from t1 where c1 = 1 for update;
21
commit;
22
select * from t1;
23
disconnect conn1;
24

25
--echo case 2: (autocommit=1, not_trx) // commit
26
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
27
connection conn1;
28
show variables like 'autocommit';
29
--disable_warnings
30
drop table if exists t1;
31
--enable_warnings
32
create table t1 (c1 int primary key, c2 int);
33
commit;
34
select * from t1;
35
disconnect conn1;
36

37
--echo case 3: (autocommit=1, not_trx) // autocommit=1
38
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
39
connection conn1;
40
show variables like 'autocommit';
41
--disable_warnings
42
drop table if exists t1;
43
--enable_warnings
44
create table t1 (c1 int primary key, c2 int);
45
set autocommit=1;
46
show variables like 'autocommit';
47
start transaction;
48
insert into t1 values (1, 2);
49
select * from t1 where c1 = 1 for update;
50
commit;
51
select * from t1;
52
disconnect conn1;
53

54
--echo case 4: (autocommit=1, not_trx) // autocommit=0
55
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
56
connection conn1;
57
show variables like 'autocommit';
58
--disable_warnings
59
drop table if exists t1;
60
--enable_warnings
61
create table t1 (c1 int primary key, c2 int);
62
set autocommit=0;
63
show variables like 'autocommit';
64
insert into t1 values (1, 2);
65
select * from t1 where c1 = 1 for update;
66
commit;
67
select * from t1;
68
show variables like 'autocommit';
69
disconnect conn1;
70

71
--echo case 5: (autocommit=1, in_trx) // start transaction
72
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
73
connection conn1;
74
show variables like 'autocommit';
75
--disable_warnings
76
drop table if exists t1;
77
--enable_warnings
78
create table t1 (c1 int primary key, c2 int);
79
start transaction;
80
# (autocommit=1, in_trx)
81
start transaction;
82
insert into t1 values (1, 2);
83
select * from t1 where c1 = 1 for update;
84
commit;
85
select * from t1;
86
disconnect conn1;
87

88
--echo case 6: (autocommit=1, in_trx) // commit
89
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
90
connection conn1;
91
show variables like 'autocommit';
92
--disable_warnings
93
drop table if exists t1;
94
--enable_warnings
95
create table t1 (c1 int primary key, c2 int);
96
start transaction;
97
# (autocommit=1, in_trx)
98
insert into t1 values (1, 2);
99
select * from t1 where c1 = 1 for update;
100
commit;
101
select * from t1;
102
start transaction;
103
show variables like 'autocommit';
104
disconnect conn1;
105

106
--echo case 7: (autocommit=1, in_trx) // set autocommit=1
107
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
108
connection conn1;
109
show variables like 'autocommit';
110
--disable_warnings
111
drop table if exists t1;
112
--enable_warnings
113
create table t1 (c1 int primary key, c2 int);
114
start transaction;
115
# (autocommit=1, in_trx)
116
set autocommit=1;
117
show variables like 'autocommit';
118
insert into t1 values (1, 2);
119
select * from t1 where c1 = 1 for update;
120
commit;
121
select * from t1;
122
disconnect conn1;
123

124
--echo case 8: (autocommit=1, in_trx) // set autocommit=0
125
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
126
connection conn1;
127
show variables like 'autocommit';
128
--disable_warnings
129
drop table if exists t1;
130
--enable_warnings
131
create table t1 (c1 int primary key, c2 int);
132
start transaction;
133
# (autocommit=1, in_trx)
134
set autocommit=0;
135
show variables like 'autocommit';
136
insert into t1 values (1, 2);
137
select * from t1 where c1 = 1 for update;
138
commit;
139
select * from t1;
140
show variables like 'autocommit';
141
disconnect conn1;
142

143
--echo case 9: (autocommit=0, not_trx) // start transaction
144
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
145
connection conn1;
146
show variables like 'autocommit';
147
--disable_warnings
148
drop table if exists t1;
149
--enable_warnings
150
create table t1 (c1 int primary key, c2 int);
151
set autocommit=0;
152
show variables like 'autocommit';
153
# (autocommit=0, not_trx)
154
start transaction;
155
start transaction;
156
insert into t1 values (1, 2);
157
select * from t1 where c1 = 1 for update;
158
commit;
159
select * from t1;
160
show variables like 'autocommit';
161
disconnect conn1;
162

163

164
--echo case 10: (autocommit=0, not_trx) // commit
165
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
166
connection conn1;
167
show variables like 'autocommit';
168
--disable_warnings
169
drop table if exists t1;
170
--enable_warnings
171
create table t1 (c1 int primary key, c2 int);
172
set autocommit=0;
173
show variables like 'autocommit';
174
# (autocommit=0, not_trx)
175
insert into t1 values (1, 2);
176
select * from t1 where c1 = 1 for update;
177
commit;
178
insert into t1 values (2, 3);
179
select * from t1 where c1 = 2 for update;
180
rollback;
181
select * from t1;
182
show variables like 'autocommit';
183
disconnect conn1;
184

185
--echo case 11: (autocommit=0, not_trx) // set autocommit=1
186
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
187
connection conn1;
188
show variables like 'autocommit';
189
--disable_warnings
190
drop table if exists t1;
191
--enable_warnings
192
create table t1 (c1 int primary key, c2 int);
193
set autocommit=0;
194
show variables like 'autocommit';
195
# (autocommit=0, not_trx)
196
set autocommit=1;
197
show variables like 'autocommit';
198
commit;
199
rollback;
200
select * from t1;
201
show variables like 'autocommit';
202
disconnect conn1;
203

204
--echo case 12: (autocommit=0, not_trx) // commit
205
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
206
connection conn1;
207
show variables like 'autocommit';
208
--disable_warnings
209
drop table if exists t1;
210
--enable_warnings
211
create table t1 (c1 int primary key, c2 int);
212
set autocommit=0;
213
show variables like 'autocommit';
214
# (autocommit=0, not_trx)
215
set autocommit=0;
216
show variables like 'autocommit';
217
insert into t1 values (1, 2);
218
select * from t1 where c1 = 1 for update;
219
commit;
220
insert into t1 values (2, 3);
221
select * from t1 where c1 = 2 for update;
222
commit;
223
select * from t1;
224
show variables like 'autocommit';
225
disconnect conn1;
226

227
--echo case 13: (autocommit=0, in_trx) // start transaction
228
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
229
connection conn1;
230
show variables like 'autocommit';
231
--disable_warnings
232
drop table if exists t1;
233
--enable_warnings
234
create table t1 (c1 int primary key, c2 int);
235
set autocommit=0;
236
start transaction;
237
show variables like 'autocommit';
238
# (autocommit=0, in_trx)
239
start transaction;
240
commit;
241
show variables like 'autocommit';
242
disconnect conn1;
243

244
--echo case 14: (autocommit=0, in_trx) // commit
245
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
246
connection conn1;
247
show variables like 'autocommit';
248
--disable_warnings
249
drop table if exists t1;
250
--enable_warnings
251
create table t1 (c1 int primary key, c2 int);
252
set autocommit=0;
253
start transaction;
254
show variables like 'autocommit';
255
# (autocommit=0, in_trx)
256
insert into t1 values (1, 2);
257
select * from t1 where c1 = 1 for update;
258
commit;
259
select * from t1;
260
show variables like 'autocommit';
261
disconnect conn1;
262

263
--echo case 15: (autocommit=0, in_trx) // set autocommit=1
264
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
265
connection conn1;
266
show variables like 'autocommit';
267
--disable_warnings
268
drop table if exists t1;
269
--enable_warnings
270
create table t1 (c1 int primary key, c2 int);
271
set autocommit=0;
272
start transaction;
273
show variables like 'autocommit';
274
# (autocommit=0, in_trx)
275
insert into t1 values (1, 2);
276
select * from t1 where c1 = 1 for update;
277
set autocommit=1;
278
select * from t1;
279
show variables like 'autocommit';
280
# are we in (autocommit=1, not_trx) ?
281
commit;
282
disconnect conn1;
283

284
--echo case 16: (autocommit=0, in_trx) // set autocommit=0
285
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
286
connection conn1;
287
show variables like 'autocommit';
288
--disable_warnings
289
drop table if exists t1;
290
--enable_warnings
291
create table t1 (c1 int primary key, c2 int);
292
set autocommit=0;
293
start transaction;
294
show variables like 'autocommit';
295
# (autocommit=0, in_trx)
296
set autocommit=0;
297
insert into t1 values (1, 2);
298
select * from t1 where c1 = 1 for update;
299
commit;
300
select * from t1;
301
show variables like 'autocommit';
302
commit;
303
disconnect conn1;
304

305