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
|
############## mysql-test\t\auto_commit_func.test #############################
# #
# Variable Name: autocommit #
# Scope: SESSION #
# Access Type: Dynamic #
# Data Type: Boolean #
# Default Value: NA #
# Range: NA #
# #
# #
# Creation Date: 2008-03-07 #
# Author: Salman Rawala #
# #
# Description: Test Cases of Dynamic System Variable "autocommit" #
# that checks functionality of this variable #
# #
# Reference: (Not Mentioned on website) #
# #
################################################################################
--source include/have_innodb.inc
--disable_warnings
drop table if exists t1;
--enable_warnings
#########################
# Creating new table #
#########################
--echo ## Creating new table ##
CREATE TABLE t1
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name varchar(30)
) ENGINE = INNODB;
--echo '#--------------------FN_DYNVARS_003_01-------------------------#'
#####################################################
# Setting initial value of auto_commit to zero #
#####################################################
--echo ## Setting variable's value to 0 i.e false ##
SET @@autocommit = 0;
--echo '#--------------------FN_DYNVARS_003_02-------------------------#'
######################################################################
# Creating 2 different connections & testing behavior of autocommit
# after updating record in 1st connection
######################################################################
CONNECT (test_con1,localhost,root,,);
CONNECTION test_con1;
--echo ## Checking value of variable after opening new connection ##
SELECT @@autocommit;
--echo ## Setting value of variable to zero and inserting some rows ##
SET @@autocommit = 0;
INSERT into t1(name) values('Record_1');
INSERT into t1(name) values('Record_2');
SELECT * from t1;
--echo ## Creating another connection and verifying records in table ##
CONNECT (test_con2,localhost,root,,);
CONNECTION test_con2;
SELECT * from t1;
--echo '#--------------------FN_DYNVARS_003_03-------------------------#'
######################################################################
# Creating 2 different connections & testing behavior of autocommit
# after updating record in 1st connection and using COMMIT in first
# connection
######################################################################
--echo ## Verifying behavior of variable by commiting rows in test_con1 ##
CONNECTION test_con1;
SELECT * from t1;
COMMIT;
--echo ## Now verifying records in table from connection # 02 ##
CONNECTION test_con2;
SELECT * from t1;
--echo '#--------------------FN_DYNVARS_003_04-------------------------#'
######################################################################
# Creating 2 different connections & testing behavior of autocommit
# after updating record in 1st connection and using ROLLBACK in
# first connection
######################################################################
CONNECTION test_con1;
SELECT * from t1;
--echo ## Updating value of first row ##
UPDATE t1 set name = 'Record_12' where name = 'Record_1';
SELECT * from t1;
--echo ## Connecting to connecting # 02 and verifying effect of update query ##
CONNECTION test_con2;
SELECT * from t1;
--echo ## Now connecting with connection # 01 and using ROLLBACK after it ##
CONNECTION test_con1;
ROLLBACK;
SELECT * from t1;
--echo '#--------------------FN_DYNVARS_003_05-------------------------#'
######################################################################
# Creating 2 different connections & testing behavior of autocommit
# after updating records in 1st connection and setting AUTOCOMMIT
# to 1 in second connection
######################################################################
CONNECTION test_con1;
INSERT into t1(name) values('Record_3');
--echo ## Now verifying records in table from connection # 02 and changing value ##
--echo ## of autocommit to true ##
CONNECTION test_con2;
SELECT * from t1;
SET @@autocommit = 1;
INSERT into t1(name) values('Record_4');
INSERT into t1(name) values('Record_5');
SELECT * from t1;
--echo ## Connecting with connection # 01 and inserting few records ##
CONNECTION test_con1;
SELECT * from t1;
INSERT into t1(name) values('Record_6');
SELECT * from t1;
--echo ## Now verifying the effect of these new records in second connection ##
CONNECTION test_con2;
SELECT * from t1;
--echo ## Commit changes
CONNECTION test_con1;
COMMIT;
--echo ## Dropping table t1 ##
DROP table t1;
DISCONNECT test_con1;
DISCONNECT test_con2;
|
