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