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
22 # save vars
23 SET @global_auto_increment_increment = @@global.auto_increment_increment;
24 SET @session_auto_increment_increment = @@session.auto_increment_increment;
25 SET @global_auto_increment_offset = @@global.auto_increment_offset;
26 SET @session_auto_increment_offset = @@session.auto_increment_offset;
27
28
29 --disable_warnings
30 drop table if exists t1;
31 --enable_warnings
32
33 #########################
34 # Creating new table #
35 #########################
36 CREATE TABLE t1
37 (
38 id INT NOT NULL auto_increment,
39 PRIMARY KEY (id),
40 name VARCHAR(30)
41 );
42
43 --echo '#--------------------FN_DYNVARS_001_01-------------------------#'
44 ##########################################################
45 # Setting initial value of auto_increment_increment #
46 ##########################################################
47
48 --echo ## Setting initial value of auto_increment_increment to 5 ##
49 SET @@auto_increment_increment = 5;
50
51
52 --echo '#--------------------FN_DYNVARS_001_02-------------------------#'
53 ###########################################################################
54 # Inserting first value in table to check auto_increment_increment initial
55 # behavior
56 ###########################################################################
57
58 --echo ## Inserting first record in table to check behavior of the variable ##
59 INSERT into t1(name) values('Record_1');
60 SELECT * from t1;
61
62 --echo ## Changing value of variable to 10 ##
63 SET @@global.auto_increment_increment = 10;
64
65 --echo ## Inserting record and verifying value of column id ##
66 INSERT into t1(name) values('Record_2');
67 SELECT * from t1;
68
69
70 --echo ## Test behavior of variable after assigning some larger value to it ##
71 SELECT @@auto_increment_increment;
72 SET @@auto_increment_increment = 100;
73 INSERT into t1(name) values('Record_5');
74 SELECT * from t1;
75
76
77 --echo '#--------------------FN_DYNVARS_001_03-------------------------#'
78 ##########################################################
79 # Test behavior of variable on new connection # 01 #
80 ##########################################################
81
82 CONNECT (test_con1,localhost,root,,);
83 CONNECTION test_con1;
84
85 --echo ## Value of session & global vairable here should be 10 ##
86 SELECT @@global.auto_increment_increment = 10;
87 SELECT @@session.auto_increment_increment = 10;
88
89 --echo ## Setting global value of variable and inserting data in table ##
90 SET @@global.auto_increment_increment = 20;
91 SELECT @@global.auto_increment_increment;
92 INSERT into t1(name) values('Record_6');
93 SELECT * from t1;
94
95 --echo ## Setting session value of variable and inserting data in table ##
96 SET @@session.auto_increment_increment = 2;
97 SELECT @@session.auto_increment_increment;
98 INSERT into t1(name) values('Record_8');
99 INSERT into t1(name) values('Record_9');
100 SELECT * from t1;
101
102
103 --echo '#--------------------FN_DYNVARS_001_04-------------------------#'
104 ##########################################################
105 # Test behavior of variable on new connection # 02 #
106 ##########################################################
107
108 CONNECT (test_con2,localhost,root,,);
109 connection test_con2;
110
111 --echo ## Verifying initial values of variable in global & session scope ##
112 --echo ## global & session initial value should be 20 ##
113 SELECT @@global.auto_increment_increment = 20;
114 SELECT @@session.auto_increment_increment = 20;
115
116 --echo ## Setting value of session variable to 5 and verifying its behavior ##
117 SET @@session.auto_increment_increment = 5;
118 INSERT into t1(name) values('Record_10');
119 SELECT * from t1;
120
121 SET @@session.auto_increment_increment = 1;
122 SELECT @@auto_increment_increment;
123 SELECT @@global.auto_increment_increment;
124
125
126 --echo '#--------------------FN_DYNVARS_001_05-------------------------#'
127 #####################################################################
128 # Verify variable's value of connection # 01 after processing on
129 # connection # 02
130 #####################################################################
131
132 connection test_con1;
133
134 --echo ## Verifying values of global & session value of variable ##
135 --echo ## global value should be 20 ##
136 SELECT @@global.auto_increment_increment = 20;
137
138 --echo ## session value should be 2 ##
139 SELECT @@session.auto_increment_increment = 2;
140
141 INSERT into t1(name) values('Record_11');
142 INSERT into t1(name) values('Record_12');
143 SELECT * from t1;
144
145
146 --echo '#--------------------FN_DYNVARS_001_06-------------------------#'
147 ###############################################################################
148 # Altering table field to different datatypes and checking their behavior #
149 ###############################################################################
150
151 --echo ## Changing column's datatype to SmallInt and verifying variable's behavior ##
152 ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
153 INSERT into t1(name) values('Record_13');
154 INSERT into t1(name) values('Record_14');
155 SELECT * from t1;
156
157 --echo ## Changing column's datatype to BigInt and verifying variable's behavior ##
158 ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
159 INSERT into t1(name) values('Record_15');
160 INSERT into t1(name) values('Record_16');
161 SELECT * from t1;
162
163 --echo '#--------------------FN_DYNVARS_001_07-------------------------#'
164 ###############################################################################
165 # Check behavior of variable after assigning invalid value #
166 ###############################################################################
167
168 --echo ## Verifying behavior of variable with negative value ##
169 SET @@auto_increment_increment = -10;
170 INSERT into t1(name) values('Record_17');
171 INSERT into t1(name) values('Record_18');
172 SELECT * from t1;
173
174 ############################################################
175 # Disconnecting all connection & dropping table #
176 ############################################################
177
178 DISCONNECT test_con2;
179
180 --echo ## Dropping table t1 ##
181 DROP table if exists t1;
182
183 DISCONNECT test_con1;
184
185 connection default;
186
187 # restore vars
188 SET @@global.auto_increment_increment = @global_auto_increment_increment;
189 SET @@session.auto_increment_increment = @session_auto_increment_increment;
190 SET @@global.auto_increment_offset = @global_auto_increment_offset;
191 SET @@session.auto_increment_offset = @session_auto_increment_offset;