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 ## New connection test_con1 ##
51 ## Value of session & global vairable here should be 10 ##
52 SELECT @@global.auto_increment_offset = 36;
53 @@global.auto_increment_offset = 36
54 1
55 SELECT @@session.auto_increment_offset = 36;
56 @@session.auto_increment_offset = 36
57 1
58 ## Verify global value effect of variable by inserting new rows in table ##
59 INSERT into t1(name) values('Record_5');
60 INSERT into t1(name) values('Record_6');
61 SELECT * from t1;
62 id name
63 5 Record_1
64 15 Record_2
65 24 Record_3
66 34 Record_4
67 36 Record_5
68 51 Record_6
69 ## Setting session value of variable and inserting data in table ##
70 SET @@session.auto_increment_offset = 54;
71 INSERT into t1(name) values('Record_7');
72 INSERT into t1(name) values('Record_8');
73 SELECT * from t1;
74 id name
75 5 Record_1
76 15 Record_2
77 24 Record_3
78 34 Record_4
79 36 Record_5
80 51 Record_6
81 54 Record_7
82 69 Record_8
83 '#--------------------FN_DYNVARS_002_04-------------------------#'
84 ## Setting value of variable less than last insert id ##
85 SET @@session.auto_increment_offset = 5;
86 INSERT into t1(name) values('Record_9');
87 INSERT into t1(name) values('Record_10');
88 INSERT into t1(name) values('Record_11');
89 INSERT into t1(name) values('Record_12');
90 SELECT * from t1;
91 id name
92 5 Record_1
93 15 Record_2
94 24 Record_3
95 34 Record_4
96 36 Record_5
97 51 Record_6
98 54 Record_7
99 69 Record_8
100 80 Record_9
101 95 Record_10
102 110 Record_11
103 125 Record_12
104 '#--------------------FN_DYNVARS_002_05-------------------------#'
105 ## Assigning value to variable greater than auto_increment_incrent value ##
106 SET @@auto_increment_offset = 140;
107 SET @@auto_increment_increment = 10;
108 INSERT into t1(name) values('Record_13');
109 INSERT into t1(name) values('Record_14');
110 SELECT * from t1;
111 id name
112 5 Record_1
113 15 Record_2
114 24 Record_3
115 34 Record_4
116 36 Record_5
117 51 Record_6
118 54 Record_7
119 69 Record_8
120 80 Record_9
121 95 Record_10
122 110 Record_11
123 125 Record_12
124 134 Record_13
125 140 Record_14
126 '#--------------------FN_DYNVARS_002_06-------------------------#'
127 ## Changing datatype of column id with primary key to SmallInt ##
128 ALTER table t1 modify id SMALLINT NOT NULL auto_increment;
129 INSERT into t1(name) values('Record_15');
130 INSERT into t1(name) values('Record_16');
131 SELECT * from t1;
132 id name
133 5 Record_1
134 15 Record_2
135 24 Record_3
136 34 Record_4
137 36 Record_5
138 51 Record_6
139 54 Record_7
140 69 Record_8
141 80 Record_9
142 95 Record_10
143 110 Record_11
144 125 Record_12
145 134 Record_13
146 140 Record_14
147 150 Record_15
148 160 Record_16
149 ## Changing datatype of column id with primary key to BigInt ##
150 ALTER table t1 modify id BIGINT NOT NULL auto_increment;
151 INSERT into t1(name) values('Record_17');
152 INSERT into t1(name) values('Record_18');
153 SELECT * from t1;
154 id name
155 5 Record_1
156 15 Record_2
157 24 Record_3
158 34 Record_4
159 36 Record_5
160 51 Record_6
161 54 Record_7
162 69 Record_8
163 80 Record_9
164 95 Record_10
165 110 Record_11
166 125 Record_12
167 134 Record_13
168 140 Record_14
169 150 Record_15
170 160 Record_16
171 170 Record_17
172 180 Record_18
173 '#--------------------FN_DYNVARS_002_07-------------------------#'
174 ## Assigning -ve value to variable ##
175 SET @@auto_increment_offset = -10;
176 Warnings:
177 Warning 1292 Truncated incorrect auto_increment_offset value: '-10'
178 SELECT @@auto_increment_offset = -10;
179 @@auto_increment_offset = -10
180 0
181 INSERT into t1(name) values('Record_17');
182 INSERT into t1(name) values('Record_18');
183 SELECT * from t1;
184 id name
185 5 Record_1
186 15 Record_2
187 24 Record_3
188 34 Record_4
189 36 Record_5
190 51 Record_6
191 54 Record_7
192 69 Record_8
193 80 Record_9
194 95 Record_10
195 110 Record_11
196 125 Record_12
197 134 Record_13
198 140 Record_14
199 150 Record_15
200 160 Record_16
201 170 Record_17
202 180 Record_18
203 181 Record_17
204 191 Record_18
205 ## Assigning value that is out of range of variable ##
206 SET @@auto_increment_offset = 65536;
207 Warnings:
208 Warning 1292 Truncated incorrect auto_increment_offset value: '65536'
209 SELECT @@auto_increment_offset;
210 @@auto_increment_offset
211 65535
212 INSERT into t1(name) values('Record_17');
213 INSERT into t1(name) values('Record_18');
214 INSERT into t1(name) values('Record_19');
215 INSERT into t1(name) values('Record_20');
216 SELECT * from t1;
217 id name
218 5 Record_1
219 15 Record_2
220 24 Record_3
221 34 Record_4
222 36 Record_5
223 51 Record_6
224 54 Record_7
225 69 Record_8
226 80 Record_9
227 95 Record_10
228 110 Record_11
229 125 Record_12
230 134 Record_13
231 140 Record_14
232 150 Record_15
233 160 Record_16
234 170 Record_17
235 180 Record_18
236 181 Record_17
237 191 Record_18
238 199 Record_17
239 209 Record_18
240 219 Record_19
241 229 Record_20
242 ## No effect of auto_increment_offset since value of this variable is greater ##
243 ## than auto_increment_increment ##
244 ## Dropping table ##
245 DROP table if exists t1;
246 ## Disconnecting connection ##
247 ## switching to default connection ##
248 SET @@global.auto_increment_increment = @global_auto_increment_increment;
249 SET @@session.auto_increment_increment = @session_auto_increment_increment;
250 SET @@global.auto_increment_offset = @global_auto_increment_offset;
251 SET @@session.auto_increment_offset = @session_auto_increment_offset;