Debian Codegraph @

mysql-test/suite/sys_vars/t/autocommit_func.test

raw
1 ############## mysql-test\t\auto_commit_func.test #############################
2 # #
3 # Variable Name: autocommit #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: Boolean #
7 # Default Value: NA #
8 # Range: NA #
9 # #
10 # #
11 # Creation Date: 2008-03-07 #
12 # Author: Salman Rawala #
13 # #
14 # Description: Test Cases of Dynamic System Variable "autocommit" #
15 # that checks functionality of this variable #
16 # #
17 # Reference: (Not Mentioned on website) #
18 # #
19 ################################################################################
20
21 --source include/have_innodb.inc
22
23 --disable_warnings
24 drop table if exists t1;
25 --enable_warnings
26
27 #########################
28 # Creating new table #
29 #########################
30
31 --echo ## Creating new table ##
32 CREATE TABLE t1
33 (
34 id INT NOT NULL auto_increment,
35 PRIMARY KEY (id),
36 name varchar(30)
37 ) ENGINE = INNODB;
38
39 --echo '#--------------------FN_DYNVARS_003_01-------------------------#'
40 #####################################################
41 # Setting initial value of auto_commit to zero #
42 #####################################################
43
44 --echo ## Setting variable's value to 0 i.e false ##
45 SET @@autocommit = 0;
46
47
48 --echo '#--------------------FN_DYNVARS_003_02-------------------------#'
49 ######################################################################
50 # Creating 2 different connections & testing behavior of autocommit
51 # after updating record in 1st connection
52 ######################################################################
53
54 CONNECT (test_con1,localhost,root,,);
55 CONNECTION test_con1;
56
57 --echo ## Checking value of variable after opening new connection ##
58
59 SELECT @@autocommit;
60
61 --echo ## Setting value of variable to zero and inserting some rows ##
62 SET @@autocommit = 0;
63
64 INSERT into t1(name) values('Record_1');
65 INSERT into t1(name) values('Record_2');
66 SELECT * from t1;
67
68 --echo ## Creating another connection and verifying records in table ##
69
70 CONNECT (test_con2,localhost,root,,);
71 CONNECTION test_con2;
72 SELECT * from t1;
73
74
75 --echo '#--------------------FN_DYNVARS_003_03-------------------------#'
76 ######################################################################
77 # Creating 2 different connections & testing behavior of autocommit
78 # after updating record in 1st connection and using COMMIT in first
79 # connection
80 ######################################################################
81
82 --echo ## Verifying behavior of variable by commiting rows in test_con1 ##
83 CONNECTION test_con1;
84 SELECT * from t1;
85 COMMIT;
86
87
88 --echo ## Now verifying records in table from connection # 02 ##
89 CONNECTION test_con2;
90 SELECT * from t1;
91
92 --echo '#--------------------FN_DYNVARS_003_04-------------------------#'
93 ######################################################################
94 # Creating 2 different connections & testing behavior of autocommit
95 # after updating record in 1st connection and using ROLLBACK in
96 # first connection
97 ######################################################################
98
99 CONNECTION test_con1;
100 SELECT * from t1;
101
102 --echo ## Updating value of first row ##
103 UPDATE t1 set name = 'Record_12' where name = 'Record_1';
104 SELECT * from t1;
105
106 --echo ## Connecting to connecting # 02 and verifying effect of update query ##
107 CONNECTION test_con2;
108 SELECT * from t1;
109
110 --echo ## Now connecting with connection # 01 and using ROLLBACK after it ##
111 CONNECTION test_con1;
112 ROLLBACK;
113 SELECT * from t1;
114
115
116
117 --echo '#--------------------FN_DYNVARS_003_05-------------------------#'
118 ######################################################################
119 # Creating 2 different connections & testing behavior of autocommit
120 # after updating records in 1st connection and setting AUTOCOMMIT
121 # to 1 in second connection
122 ######################################################################
123
124
125 CONNECTION test_con1;
126 INSERT into t1(name) values('Record_3');
127
128
129 --echo ## Now verifying records in table from connection # 02 and changing value ##
130 --echo ## of autocommit to true ##
131 CONNECTION test_con2;
132 SELECT * from t1;
133 SET @@autocommit = 1;
134 INSERT into t1(name) values('Record_4');
135 INSERT into t1(name) values('Record_5');
136 SELECT * from t1;
137
138 --echo ## Connecting with connection # 01 and inserting few records ##
139 CONNECTION test_con1;
140 SELECT * from t1;
141 INSERT into t1(name) values('Record_6');
142 SELECT * from t1;
143
144 --echo ## Now verifying the effect of these new records in second connection ##
145 CONNECTION test_con2;
146 SELECT * from t1;
147
148 --echo ## Commit changes
149 CONNECTION test_con1;
150 COMMIT;
151
152 --echo ## Dropping table t1 ##
153 DROP table t1;
154
155 DISCONNECT test_con1;
156 DISCONNECT test_con2;
157
158
159