Debian Codegraph @

mysql-test/suite/sys_vars/r/auto_increment_offset_func.result

raw
1 SET @global_auto_increment_increment = @@global.auto_increment_increment;
2 SET @session_auto_increment_increment = @@session.auto_increment_increment;
3 SET @global_auto_increment_offset = @@global.auto_increment_offset;
4 SET @session_auto_increment_offset = @@session.auto_increment_offset;
5 drop table if exists t1;
6 ## Creating New Table ##
7 CREATE TABLE t1
8 (
9 id INT NOT NULL auto_increment,
10 PRIMARY KEY (id),
11 name VARCHAR(30)
12 );
13 '#--------------------FN_DYNVARS_002_01-------------------------#'
14 ## Setting initial value of variable to 5 ##
15 SET @@auto_increment_increment = 10;
16 SET @@auto_increment_offset = 5;
17 '#--------------------FN_DYNVARS_002_02-------------------------#'
18 ## Inserting records in table and verifying variable's behavior ##
19 INSERT into t1(name) values('Record_1');
20 SELECT * from t1;
21 id name
22 5 Record_1
23 INSERT into t1(name) values('Record_2');
24 SELECT * from t1;
25 id name
26 5 Record_1
27 15 Record_2
28 ## Test behavior of variable after updating value of variable ##
29 SET @@auto_increment_offset = 24;
30 SELECT @@auto_increment_offset;
31 @@auto_increment_offset
32 24
33 INSERT into t1(name) values('Record_3');
34 SELECT * from t1;
35 id name
36 5 Record_1
37 15 Record_2
38 24 Record_3
39 INSERT into t1(name) values('Record_4');
40 SELECT * from t1;
41 id name
42 5 Record_1
43 15 Record_2
44 24 Record_3
45 34 Record_4
46 '#--------------------FN_DYNVARS_002_03-------------------------#'
47 ## Changing value of global scope before opening new connection ##
48 SET @@global.auto_increment_increment = 15;
49 SET @@global.auto_increment_offset = 36;
50 CONNECT test_con1,localhost,root,,;
51 connection test_con1;
52 ## Value of session & global vairable here should be 10 ##
53 SELECT @@global.auto_increment_offset = 36;
54 @@global.auto_increment_offset = 36
55 1
56 SELECT @@session.auto_increment_offset = 36;
57 @@session.auto_increment_offset = 36
58 1
59 ## Verify global value effect of variable by inserting new rows in table ##
60 INSERT into t1(name) values('Record_5');
61 INSERT into t1(name) values('Record_6');
62 SELECT * from t1;
63 id name
64 5 Record_1
65 15 Record_2
66 24 Record_3
67 34 Record_4
68 36 Record_5
69 51 Record_6
70 ## Setting session value of variable and inserting data in table ##
71 SET @@session.auto_increment_offset = 54;
72 INSERT into t1(name) values('Record_7');
73 INSERT into t1(name) values('Record_8');
74 SELECT * from t1;
75 id name
76 5 Record_1
77 15 Record_2
78 24 Record_3
79 34 Record_4
80 36 Record_5
81 51 Record_6
82 54 Record_7
83 69 Record_8
84 '#--------------------FN_DYNVARS_002_04-------------------------#'
85 ## Setting value of variable less than last insert id ##
86 SET @@session.auto_increment_offset = 5;
87 INSERT into t1(name) values('Record_9');
88 INSERT into t1(name) values('Record_10');
89 INSERT into t1(name) values('Record_11');
90 INSERT into t1(name) values('Record_12');
91 SELECT * from t1;
92 id name
93 5 Record_1
94 15 Record_2
95 24 Record_3
96 34 Record_4
97 36 Record_5
98 51 Record_6
99 54 Record_7
100 69 Record_8
101 80 Record_9
102 95 Record_10
103 110 Record_11
104 125 Record_12
105 '#--------------------FN_DYNVARS_002_05-------------------------#'
106 ## Assigning value to variable greater than auto_increment_incrent value ##
107 SET @@auto_increment_offset = 140;
108 SET @@auto_increment_increment = 10;
109 INSERT into t1(name) values('Record_13');
110 INSERT into t1(name) values('Record_14');
111 SELECT * from t1;
112 id name
113 5 Record_1
114 15 Record_2
115 24 Record_3
116 34 Record_4
117 36 Record_5
118 51 Record_6
119 54 Record_7
120 69 Record_8
121 80 Record_9
122 95 Record_10
123 110 Record_11
124 125 Record_12
125 134 Record_13
126 140 Record_14
127 '#--------------------FN_DYNVARS_002_06-------------------------#'
128 ## Changing datatype of column id with primary key to SmallInt ##
129 ALTER table t1 modify id SMALLINT NOT NULL auto_increment;
130 INSERT into t1(name) values('Record_15');
131 INSERT into t1(name) values('Record_16');
132 SELECT * from t1;
133 id name
134 5 Record_1
135 15 Record_2
136 24 Record_3
137 34 Record_4
138 36 Record_5
139 51 Record_6
140 54 Record_7
141 69 Record_8
142 80 Record_9
143 95 Record_10
144 110 Record_11
145 125 Record_12
146 134 Record_13
147 140 Record_14
148 150 Record_15
149 160 Record_16
150 ## 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 id name
156 5 Record_1
157 15 Record_2
158 24 Record_3
159 34 Record_4
160 36 Record_5
161 51 Record_6
162 54 Record_7
163 69 Record_8
164 80 Record_9
165 95 Record_10
166 110 Record_11
167 125 Record_12
168 134 Record_13
169 140 Record_14
170 150 Record_15
171 160 Record_16
172 170 Record_17
173 180 Record_18
174 '#--------------------FN_DYNVARS_002_07-------------------------#'
175 ## Assigning -ve value to variable ##
176 SET @@auto_increment_offset = -10;
177 Warnings:
178 Warning 1292 Truncated incorrect auto_increment_offset value: '-10'
179 SELECT @@auto_increment_offset = -10;
180 @@auto_increment_offset = -10
181 0
182 INSERT into t1(name) values('Record_17');
183 INSERT into t1(name) values('Record_18');
184 SELECT * from t1;
185 id name
186 5 Record_1
187 15 Record_2
188 24 Record_3
189 34 Record_4
190 36 Record_5
191 51 Record_6
192 54 Record_7
193 69 Record_8
194 80 Record_9
195 95 Record_10
196 110 Record_11
197 125 Record_12
198 134 Record_13
199 140 Record_14
200 150 Record_15
201 160 Record_16
202 170 Record_17
203 180 Record_18
204 181 Record_17
205 191 Record_18
206 ## Assigning value that is out of range of variable ##
207 SET @@auto_increment_offset = 65536;
208 Warnings:
209 Warning 1292 Truncated incorrect auto_increment_offset value: '65536'
210 SELECT @@auto_increment_offset;
211 @@auto_increment_offset
212 65535
213 INSERT into t1(name) values('Record_17');
214 INSERT into t1(name) values('Record_18');
215 INSERT into t1(name) values('Record_19');
216 INSERT into t1(name) values('Record_20');
217 SELECT * from t1;
218 id name
219 5 Record_1
220 15 Record_2
221 24 Record_3
222 34 Record_4
223 36 Record_5
224 51 Record_6
225 54 Record_7
226 69 Record_8
227 80 Record_9
228 95 Record_10
229 110 Record_11
230 125 Record_12
231 134 Record_13
232 140 Record_14
233 150 Record_15
234 160 Record_16
235 170 Record_17
236 180 Record_18
237 181 Record_17
238 191 Record_18
239 199 Record_17
240 209 Record_18
241 219 Record_19
242 229 Record_20
243 ## No effect of auto_increment_offset since value of this variable is greater ##
244 ## than auto_increment_increment ##
245 ## Dropping table ##
246 DROP table if exists t1;
247 disconnect test_con1;
248 connection default;
249 SET @@global.auto_increment_increment = @global_auto_increment_increment;
250 SET @@session.auto_increment_increment = @session_auto_increment_increment;
251 SET @@global.auto_increment_offset = @global_auto_increment_offset;
252 SET @@session.auto_increment_offset = @session_auto_increment_offset;