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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
############## mysql-test/suite/sys_vars/t/completion_type_func.test ###########
# #
# Variable Name: completion_type #
# Scope: GLOBAL & SESSION #
# Access Type: Dynamic #
# Data Type: Numeric #
# Default Value: 0 #
# Valid Values: 0,1 & 2 #
# #
# #
# Creation Date: 2008-03-07 #
# Author: Salman Rawala #
# #
# Description: Test Cases of Dynamic System Variable "completion_type" #
# that checks functionality of this variable #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html#option_mysqld_completion_type #
# #
################################################################################
--source include/have_innodb.inc
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
##############################
# Setup: Table + connections #
##############################
--echo ## Creating new table ##
CREATE TABLE t1
(
id INT NOT NULL,
PRIMARY KEY (id),
name VARCHAR(30)
) ENGINE = INNODB;
connect (test_con1,localhost,root,,);
connect (test_con2,localhost,root,,);
connection default;
--echo #########################################################
--echo # Setting initial value of completion_type to zero #
--echo #########################################################
INSERT INTO t1 VALUES(1,'Record_1');
SELECT * FROM t1;
--echo ## Setting value of variable to 0 ##
SET @@session.completion_type = 0;
--echo ## Here commit & rollback should work normally ##
--echo ## test commit ##
START TRANSACTION;
INSERT INTO t1 VALUES(2,'Record_2');
INSERT INTO t1 VALUES(3,'Record_3');
SELECT * FROM t1;
connection test_con1;
--echo ## Don't expect to see id's 2 and 3 in the table w/o COMMIT ##
SELECT * FROM t1;
connection default;
COMMIT;
--echo ## test rollback ##
START TRANSACTION;
INSERT INTO t1 VALUES(4,'Record_4');
INSERT INTO t1 VALUES(5,'Record_5');
SELECT * FROM t1;
connection test_con1;
--echo ## Don't expect to see id's 4 and 5 here ##
--echo ## Expect to see 3, Record_3 ##
SELECT * FROM t1;
connection default;
ROLLBACK;
--echo ## Don't expect to see id's 4 and 5 now ##
SELECT * FROM t1;
--echo
--echo #########################################################
--echo # Setting initial value of completion_type to one #
--echo #########################################################
connection test_con1;
SET @@session.completion_type = 1;
START TRANSACTION;
SELECT * FROM t1;
INSERT INTO t1 VALUES(6,'Record_6');
INSERT INTO t1 VALUES(7,'Record_7');
COMMIT;
--echo ## Expect to immediately have a new transaction ##
INSERT INTO t1 VALUES(8,'Record_8');
SELECT * FROM t1;
connection test_con2;
--echo ## Do not expect to see 8, Record_8 as no COMMIT has occurred ##
SELECT * FROM t1;
connection test_con1;
--echo ## Testing ROLLBACK behavior
START TRANSACTION;
INSERT INTO t1 VALUES(9, 'Record_9');
INSERT INTO t1 VALUES(10, 'Record_10');
--echo ## Expect to see id's 8, 9, 10 here ##
--echo ## 8, Record_8 COMMITted with the start of this transaction ##
SELECT * FROM t1;
ROLLBACK;
--echo ## id's 9 and 10 are gone now due to ROLLBACK ##
SELECT * FROM t1;
--echo ## Expect a new transaction ##
INSERT INTO t1 VALUES(9, 'Record_9');
connection test_con2;
--echo ## Don't expect to see 9, Record_9 due to no COMMIT yet ##
SELECT * FROM t1;
connection test_con1;
ROLLBACK;
--echo ## Don't expect to see 9, Record_9
SELECT * FROM t1;
--echo #########################################################
--echo # Setting initial value of completion_type to 2 #
--echo #########################################################
SET @@session.completion_type = 2;
--echo ## Here commit should work as COMMIT RELEASE ##
START TRANSACTION;
SELECT * FROM t1;
INSERT INTO t1 VALUES(9,'Record_9');
INSERT INTO t1 VALUES(10,'Record_10');
COMMIT;
--echo ## Inserting rows should give error here because connection should ##
--echo ## disconnect after using COMMIT ##
--Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED
INSERT INTO t1 VALUES(4,'Record_4');
connection test_con2;
SET @@session.completion_type = 2;
--echo ## Inserting rows and using Rollback which should Rollback & release ##
START TRANSACTION;
SELECT * FROM t1;
INSERT INTO t1 VALUES(11,'Record_11');
INSERT INTO t1 VALUES(12,'Record_12');
ROLLBACK;
--echo ## Expect a failure due to COMMIT/ROLLBACK AND RELEASE behavior ##
--Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED
INSERT INTO t1 VALUES(4,'Record_4');
connection default;
disconnect test_con1;
disconnect test_con2;
DROP TABLE t1;
|
