Debian Codegraph @

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

raw
1 ############## mysql-test\t\auto_increment_increment_func.test ################
2 # #
3 # Variable Name: auto_increment_increment #
4 # Scope: GLOBAL & SESSION #
5 # Access Type: Dynamic #
6 # Data Type: Numeric #
7 # Default Value: 1 #
8 # Range: 1 - 65536 #
9 # #
10 # #
11 # Creation Date: 2008-03-07 #
12 # Author: Salman Rawala #
13 # #
14 # Description: Test Cases of Dynamic System Variable "auto_increment_increment"#
15 # that checks functionality of this variable #
16 # #
17 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18 # server-system-variables.html#option_mysqld_auto-increment-increment #
19 # #
20 ################################################################################
21 --source include/force_myisam_default.inc
22
23 # Skip the test if MyISAM is not available
24 --source include/have_myisam.inc
25
26 # save vars
27 SET @global_auto_increment_increment = @@global.auto_increment_increment;
28 SET @session_auto_increment_increment = @@session.auto_increment_increment;
29 SET @global_auto_increment_offset = @@global.auto_increment_offset;
30 SET @session_auto_increment_offset = @@session.auto_increment_offset;
31
32
33 --disable_warnings
34 drop table if exists t1;
35 --enable_warnings
36
37 #########################
38 # Creating new table #
39 #########################
40 CREATE TABLE t1
41 (
42 id INT NOT NULL auto_increment,
43 PRIMARY KEY (id),
44 name VARCHAR(30)
45 );
46
47 --echo '#--------------------FN_DYNVARS_001_01-------------------------#'
48 ##########################################################
49 # Setting initial value of auto_increment_increment #
50 ##########################################################
51
52 --echo ## Setting initial value of auto_increment_increment to 5 ##
53 SET @@auto_increment_increment = 5;
54
55
56 --echo '#--------------------FN_DYNVARS_001_02-------------------------#'
57 ###########################################################################
58 # Inserting first value in table to check auto_increment_increment initial
59 # behavior
60 ###########################################################################
61
62 --echo ## Inserting first record in table to check behavior of the variable ##
63 INSERT into t1(name) values('Record_1');
64 SELECT * from t1;
65
66 --echo ## Changing value of variable to 10 ##
67 SET @@global.auto_increment_increment = 10;
68
69 --echo ## Inserting record and verifying value of column id ##
70 INSERT into t1(name) values('Record_2');
71 SELECT * from t1;
72
73
74 --echo ## Test behavior of variable after assigning some larger value to it ##
75 SELECT @@auto_increment_increment;
76 SET @@auto_increment_increment = 100;
77 INSERT into t1(name) values('Record_5');
78 SELECT * from t1;
79
80
81 --echo '#--------------------FN_DYNVARS_001_03-------------------------#'
82 ##########################################################
83 # Test behavior of variable on new connection # 01 #
84 ##########################################################
85
86 --echo ## Creating new connection test_con1 ##
87 CONNECT (test_con1,localhost,root,,);
88 CONNECTION test_con1;
89
90 --echo ## Value of session & global vairable here should be 10 ##
91 SELECT @@global.auto_increment_increment = 10;
92 SELECT @@session.auto_increment_increment = 10;
93
94 --echo ## Setting global value of variable and inserting data in table ##
95 SET @@global.auto_increment_increment = 20;
96 SELECT @@global.auto_increment_increment;
97 INSERT into t1(name) values('Record_6');
98 SELECT * from t1;
99
100 --echo ## Setting session value of variable and inserting data in table ##
101 SET @@session.auto_increment_increment = 2;
102 SELECT @@session.auto_increment_increment;
103 INSERT into t1(name) values('Record_8');
104 INSERT into t1(name) values('Record_9');
105 SELECT * from t1;
106
107
108 --echo '#--------------------FN_DYNVARS_001_04-------------------------#'
109 ##########################################################
110 # Test behavior of variable on new connection # 02 #
111 ##########################################################
112
113 --echo ## Creating another new connection test_con2 ##
114 CONNECT (test_con2,localhost,root,,);
115 connection test_con2;
116
117 --echo ## Verifying initial values of variable in global & session scope ##
118 --echo ## global & session initial value should be 20 ##
119 SELECT @@global.auto_increment_increment = 20;
120 SELECT @@session.auto_increment_increment = 20;
121
122 --echo ## Setting value of session variable to 5 and verifying its behavior ##
123 SET @@session.auto_increment_increment = 5;
124 INSERT into t1(name) values('Record_10');
125 SELECT * from t1;
126
127 SET @@session.auto_increment_increment = 1;
128 SELECT @@auto_increment_increment;
129 SELECT @@global.auto_increment_increment;
130
131
132 --echo '#--------------------FN_DYNVARS_001_05-------------------------#'
133 #####################################################################
134 # Verify variable's value of connection # 01 after processing on
135 # connection # 02
136 #####################################################################
137
138 --echo ## Switching to test_con1 ##
139 connection test_con1;
140
141 --echo ## Verifying values of global & session value of variable ##
142 --echo ## global value should be 20 ##
143 SELECT @@global.auto_increment_increment = 20;
144
145 --echo ## session value should be 2 ##
146 SELECT @@session.auto_increment_increment = 2;
147
148 INSERT into t1(name) values('Record_11');
149 INSERT into t1(name) values('Record_12');
150 SELECT * from t1;
151
152
153 --echo '#--------------------FN_DYNVARS_001_06-------------------------#'
154 ###############################################################################
155 # Altering table field to different datatypes and checking their behavior #
156 ###############################################################################
157
158 --echo ## Changing column's datatype to SmallInt and verifying variable's behavior ##
159 ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
160 INSERT into t1(name) values('Record_13');
161 INSERT into t1(name) values('Record_14');
162 SELECT * from t1;
163
164 --echo ## Changing column's datatype to BigInt and verifying variable's behavior ##
165 ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
166 INSERT into t1(name) values('Record_15');
167 INSERT into t1(name) values('Record_16');
168 SELECT * from t1;
169
170 --echo '#--------------------FN_DYNVARS_001_07-------------------------#'
171 ###############################################################################
172 # Check behavior of variable after assigning invalid value #
173 ###############################################################################
174
175 --echo ## Verifying behavior of variable with negative value ##
176 SET @@auto_increment_increment = -10;
177 INSERT into t1(name) values('Record_17');
178 INSERT into t1(name) values('Record_18');
179 SELECT * from t1;
180
181 ############################################################
182 # Disconnecting all connection & dropping table #
183 ############################################################
184
185 --echo ## Disconnecting test_con2 ##
186 DISCONNECT test_con2;
187
188 --echo ## Dropping table t1 ##
189 DROP table if exists t1;
190
191 --echo ## Disconnecting test_con1 ##
192 DISCONNECT test_con1;
193
194 --echo ## switching to default connection ##
195 connection default;
196
197 # restore vars
198 SET @@global.auto_increment_increment = @global_auto_increment_increment;
199 SET @@session.auto_increment_increment = @session_auto_increment_increment;
200 SET @@global.auto_increment_offset = @global_auto_increment_offset;
201 SET @@session.auto_increment_offset = @session_auto_increment_offset;