1
############## mysql-test\t\auto_commit_func.test #############################
3
# Variable Name: autocommit #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable "autocommit" #
15
# that checks functionality of this variable #
17
# Reference: (Not Mentioned on website) #
19
################################################################################
21
--source include/have_innodb.inc
24
drop table if exists t1;
27
#########################
28
# Creating new table #
29
#########################
31
--echo ## Creating new table ##
34
id INT NOT NULL auto_increment,
39
--echo '#--------------------FN_DYNVARS_003_01-------------------------#'
40
#####################################################
41
# Setting initial value of auto_commit to zero #
42
#####################################################
44
--echo ## Setting variable's value to 0 i.e false ##
48
--echo '#--------------------FN_DYNVARS_003_02-------------------------#'
49
######################################################################
50
# Creating 2 different connections & testing behavior of autocommit
51
# after updating record in 1st connection
52
######################################################################
54
--echo ## Creating new connection ##
55
CONNECT (test_con1,localhost,root,,);
58
--echo ## Checking value of variable after opening new connection ##
62
--echo ## Setting value of variable to zero and inserting some rows ##
65
INSERT into t1(name) values('Record_1');
66
INSERT into t1(name) values('Record_2');
69
--echo ## Creating another connection and verifying records in table ##
71
--echo ## New Connection test_con2 ##
72
CONNECT (test_con2,localhost,root,,);
77
--echo '#--------------------FN_DYNVARS_003_03-------------------------#'
78
######################################################################
79
# Creating 2 different connections & testing behavior of autocommit
80
# after updating record in 1st connection and using COMMIT in first
82
######################################################################
84
--echo ## Verifying behavior of variable by commiting rows in test_con1 ##
85
--echo ## Connecting with connection # 01 ##
91
--echo ## New Connection test_con2 ##
92
--echo ## Now verifying records in table from connection # 02 ##
96
--echo '#--------------------FN_DYNVARS_003_04-------------------------#'
97
######################################################################
98
# Creating 2 different connections & testing behavior of autocommit
99
# after updating record in 1st connection and using ROLLBACK in
101
######################################################################
103
--echo ## Connecting to connection # 01 ##
104
CONNECTION test_con1;
107
--echo ## Updating value of first row ##
108
UPDATE t1 set name = 'Record_12' where name = 'Record_1';
111
--echo ## Connecting to connecting # 02 and verifying effect of update query ##
112
CONNECTION test_con2;
115
--echo ## Now connecting with connection # 01 and using ROLLBACK after it ##
116
CONNECTION test_con1;
122
--echo '#--------------------FN_DYNVARS_003_05-------------------------#'
123
######################################################################
124
# Creating 2 different connections & testing behavior of autocommit
125
# after updating records in 1st connection and setting AUTOCOMMIT
126
# to 1 in second connection
127
######################################################################
130
--echo ## Connecting with connection # 01 ##
131
CONNECTION test_con1;
132
INSERT into t1(name) values('Record_3');
135
--echo ## Connection test_con2 ##
136
--echo ## Now verifying records in table from connection # 02 and changing value ##
137
--echo ## of autocommit to true ##
138
CONNECTION test_con2;
140
SET @@autocommit = 1;
141
INSERT into t1(name) values('Record_4');
142
INSERT into t1(name) values('Record_5');
145
--echo ## Connecting with connection # 01 and inserting few records ##
146
CONNECTION test_con1;
148
--echo 'Bug#35373: Records donot get committed in transaction on switching connections'
149
INSERT into t1(name) values('Record_6');
152
--echo ## Now verifying the effect of these new records in second connection ##
153
CONNECTION test_con2;
156
--echo ## Dropping table t1 ##
159
--echo ## Disconnecting both connections ##
160
DISCONNECT test_con1;
161
DISCONNECT test_con2;