Debian Codegraph @

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

raw
1 ############## mysql-test\t\auto_increment_offset_func.test ####################
2 # #
3 # Variable Name: auto_increment_offset #
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_offset" #
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-offset #
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 --disable_warnings
33 drop table if exists t1;
34 --enable_warnings
35
36 #########################
37 # Creating new table #
38 #########################
39
40 --echo ## Creating New Table ##
41 CREATE TABLE t1
42 (
43 id INT NOT NULL auto_increment,
44 PRIMARY KEY (id),
45 name VARCHAR(30)
46 );
47
48 --echo '#--------------------FN_DYNVARS_002_01-------------------------#'
49 #######################################################
50 # Setting initial value of auto_increment_offset #
51 #######################################################
52
53 --echo ## Setting initial value of variable to 5 ##
54 SET @@auto_increment_increment = 10;
55 SET @@auto_increment_offset = 5;
56
57
58 --echo '#--------------------FN_DYNVARS_002_02-------------------------#'
59 ###########################################################################
60 # Inserting first value in table to check auto_increment_offset initial
61 # behavior
62 ###########################################################################
63
64 --echo ## Inserting records in table and verifying variable's behavior ##
65 INSERT into t1(name) values('Record_1');
66 SELECT * from t1;
67 INSERT into t1(name) values('Record_2');
68 SELECT * from t1;
69
70 --echo ## Test behavior of variable after updating value of variable ##
71 SET @@auto_increment_offset = 24;
72 SELECT @@auto_increment_offset;
73 INSERT into t1(name) values('Record_3');
74 SELECT * from t1;
75 INSERT into t1(name) values('Record_4');
76 SELECT * from t1;
77
78
79 --echo '#--------------------FN_DYNVARS_002_03-------------------------#'
80 ##########################################################
81 # Test behavior of variable on new connection # 01 #
82 ##########################################################
83
84 --echo ## Changing value of global scope before opening new connection ##
85
86 SET @@global.auto_increment_increment = 15;
87 SET @@global.auto_increment_offset = 36;
88
89 --echo ## New connection test_con1 ##
90 CONNECT (test_con1,localhost,root,,);
91 CONNECTION test_con1;
92
93 --echo ## Value of session & global vairable here should be 10 ##
94 SELECT @@global.auto_increment_offset = 36;
95 SELECT @@session.auto_increment_offset = 36;
96
97 --echo ## Verify global value effect of variable by inserting new rows in table ##
98 INSERT into t1(name) values('Record_5');
99 INSERT into t1(name) values('Record_6');
100 SELECT * from t1;
101
102 --echo ## Setting session value of variable and inserting data in table ##
103 SET @@session.auto_increment_offset = 54;
104 INSERT into t1(name) values('Record_7');
105 INSERT into t1(name) values('Record_8');
106 SELECT * from t1;
107
108
109 --echo '#--------------------FN_DYNVARS_002_04-------------------------#'
110 ######################################################################
111 # Test behavior of variable on assigning value to variable that is
112 # less than last index id
113 ######################################################################
114
115 --echo ## Setting value of variable less than last insert id ##
116 SET @@session.auto_increment_offset = 5;
117 INSERT into t1(name) values('Record_9');
118 INSERT into t1(name) values('Record_10');
119 INSERT into t1(name) values('Record_11');
120 INSERT into t1(name) values('Record_12');
121 SELECT * from t1;
122
123
124
125 --echo '#--------------------FN_DYNVARS_002_05-------------------------#'
126 #####################################################################
127 # Verify variable's behavior on assigning value greater than
128 # auto_increment_increment value
129 #####################################################################
130
131 --echo ## Assigning value to variable greater than auto_increment_incrent value ##
132 SET @@auto_increment_offset = 140;
133 SET @@auto_increment_increment = 10;
134
135 INSERT into t1(name) values('Record_13');
136 INSERT into t1(name) values('Record_14');
137 SELECT * from t1;
138
139 --echo '#--------------------FN_DYNVARS_002_06-------------------------#'
140 ###############################################################################
141 # Altering table field to different datatypes and checking their behavior #
142 ###############################################################################
143
144 --echo ## Changing datatype of column id with primary key to SmallInt ##
145 ALTER table t1 modify id SMALLINT NOT NULL auto_increment;
146 INSERT into t1(name) values('Record_15');
147 INSERT into t1(name) values('Record_16');
148 SELECT * from t1;
149
150 --echo ## Changing datatype of column id with primary key to BigInt ##
151 ALTER table t1 modify id BIGINT NOT NULL auto_increment;
152 INSERT into t1(name) values('Record_17');
153 INSERT into t1(name) values('Record_18');
154 SELECT * from t1;
155
156 --echo '#--------------------FN_DYNVARS_002_07-------------------------#'
157 ###############################################################################
158 # Check behavior of variable after assigning invalid values to variable #
159 ###############################################################################
160
161 --echo ## Assigning -ve value to variable ##
162 SET @@auto_increment_offset = -10;
163 SELECT @@auto_increment_offset = -10;
164 INSERT into t1(name) values('Record_17');
165 INSERT into t1(name) values('Record_18');
166 SELECT * from t1;
167
168 --echo ## Assigning value that is out of range of variable ##
169 SET @@auto_increment_offset = 65536;
170 SELECT @@auto_increment_offset;
171 INSERT into t1(name) values('Record_17');
172 INSERT into t1(name) values('Record_18');
173 INSERT into t1(name) values('Record_19');
174 INSERT into t1(name) values('Record_20');
175 SELECT * from t1;
176
177 --echo ## No effect of auto_increment_offset since value of this variable is greater ##
178 --echo ## than auto_increment_increment ##
179
180 ############################################################
181 # Disconnecting all connection & dropping table #
182 ############################################################
183
184 --echo ## Dropping table ##
185 DROP table if exists t1;
186
187 --echo ## Disconnecting connection ##
188 DISCONNECT test_con1;
189
190 --echo ## switching to default connection ##
191 connection default;
192
193 # restore vars
194 SET @@global.auto_increment_increment = @global_auto_increment_increment;
195 SET @@session.auto_increment_increment = @session_auto_increment_increment;
196 SET @@global.auto_increment_offset = @global_auto_increment_offset;
197 SET @@session.auto_increment_offset = @session_auto_increment_offset;