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
22 --disable_warnings
23 drop table if exists t1;
24 --enable_warnings
25
26 #########################
27 # Creating new table #
28 #########################
29
30 --echo ## Creating new table ##
31 CREATE TABLE t1
32 (
33 id INT NOT NULL auto_increment,
34 PRIMARY KEY (id),
35 name varchar(30)
36 ) ENGINE = INNODB;
37
38 --echo '#--------------------FN_DYNVARS_003_01-------------------------#'
39 #####################################################
40 # Setting initial value of auto_commit to zero #
41 #####################################################
42
43 --echo ## Setting variable's value to 0 i.e false ##
44 SET @@autocommit = 0;
45
46
47 --echo '#--------------------FN_DYNVARS_003_02-------------------------#'
48 ######################################################################
49 # Creating 2 different connections & testing behavior of autocommit
50 # after updating record in 1st connection
51 ######################################################################
52
53 --echo ## Creating new connection ##
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 --echo ## New Connection test_con2 ##
71 CONNECT (test_con2,localhost,root,,);
72 CONNECTION test_con2;
73 SELECT * from t1;
74
75
76 --echo '#--------------------FN_DYNVARS_003_03-------------------------#'
77 ######################################################################
78 # Creating 2 different connections & testing behavior of autocommit
79 # after updating record in 1st connection and using COMMIT in first
80 # connection
81 ######################################################################
82
83 --echo ## Verifying behavior of variable by commiting rows in test_con1 ##
84 --echo ## Connecting with connection # 01 ##
85 CONNECTION test_con1;
86 SELECT * from t1;
87 COMMIT;
88
89
90 --echo ## New Connection test_con2 ##
91 --echo ## Now verifying records in table from connection # 02 ##
92 CONNECTION test_con2;
93 SELECT * from t1;
94
95 --echo '#--------------------FN_DYNVARS_003_04-------------------------#'
96 ######################################################################
97 # Creating 2 different connections & testing behavior of autocommit
98 # after updating record in 1st connection and using ROLLBACK in
99 # first connection
100 ######################################################################
101
102 --echo ## Connecting to connection # 01 ##
103 CONNECTION test_con1;
104 SELECT * from t1;
105
106 --echo ## Updating value of first row ##
107 UPDATE t1 set name = 'Record_12' where name = 'Record_1';
108 SELECT * from t1;
109
110 --echo ## Connecting to connecting # 02 and verifying effect of update query ##
111 CONNECTION test_con2;
112 SELECT * from t1;
113
114 --echo ## Now connecting with connection # 01 and using ROLLBACK after it ##
115 CONNECTION test_con1;
116 ROLLBACK;
117 SELECT * from t1;
118
119
120
121 --echo '#--------------------FN_DYNVARS_003_05-------------------------#'
122 ######################################################################
123 # Creating 2 different connections & testing behavior of autocommit
124 # after updating records in 1st connection and setting AUTOCOMMIT
125 # to 1 in second connection
126 ######################################################################
127
128
129 --echo ## Connecting with connection # 01 ##
130 CONNECTION test_con1;
131 INSERT into t1(name) values('Record_3');
132
133
134 --echo ## Connection test_con2 ##
135 --echo ## Now verifying records in table from connection # 02 and changing value ##
136 --echo ## of autocommit to true ##
137 CONNECTION test_con2;
138 SELECT * from t1;
139 SET @@autocommit = 1;
140 INSERT into t1(name) values('Record_4');
141 INSERT into t1(name) values('Record_5');
142 SELECT * from t1;
143
144 --echo ## Connecting with connection # 01 and inserting few records ##
145 CONNECTION test_con1;
146 SELECT * from t1;
147 INSERT into t1(name) values('Record_6');
148 SELECT * from t1;
149
150 --echo ## Now verifying the effect of these new records in second connection ##
151 CONNECTION test_con2;
152 SELECT * from t1;
153
154 --echo ## Commit changes
155 CONNECTION test_con1;
156 COMMIT;
157
158 --echo ## Dropping table t1 ##
159 DROP table t1;
160
161 --echo ## Disconnecting both connections ##
162 DISCONNECT test_con1;
163 DISCONNECT test_con2;
164
165
166