Debian Codegraph @

mysql-test/suite/rpl/r/rpl_auto_increment.result

raw
1 include/master-slave.inc
2 [connection master]
3 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
4 insert into t1 values (NULL,1),(NULL,2),(NULL,3);
5 select * from t1;
6 a b
7 12 1
8 22 2
9 32 3
10 connection slave;
11 select * from t1;
12 a b
13 12 1
14 22 2
15 32 3
16 connection master;
17 drop table t1;
18 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam;
19 insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4);
20 delete from t1 where b=4;
21 insert into t1 values (NULL,5),(NULL,6);
22 select * from t1;
23 a b
24 1 1
25 2 2
26 3 3
27 22 5
28 32 6
29 connection slave;
30 select * from t1;
31 a b
32 1 1
33 2 2
34 3 3
35 22 5
36 32 6
37 connection master;
38 drop table t1;
39 set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10;
40 show variables like "auto_inc%";
41 Variable_name Value
42 auto_increment_increment 100
43 auto_increment_offset 10
44 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
45 insert into t1 values (NULL),(5),(NULL);
46 insert into t1 values (250),(NULL);
47 select * from t1;
48 a
49 5
50 10
51 110
52 250
53 310
54 insert into t1 values (1000);
55 set @@insert_id=400;
56 insert into t1 values(NULL),(NULL);
57 select * from t1;
58 a
59 5
60 10
61 110
62 250
63 310
64 400
65 410
66 1000
67 connection slave;
68 select * from t1;
69 a
70 5
71 10
72 110
73 250
74 310
75 400
76 410
77 1000
78 connection master;
79 drop table t1;
80 create table t1 (a int not null auto_increment, primary key (a)) engine=innodb;
81 insert into t1 values (NULL),(5),(NULL);
82 insert into t1 values (250),(NULL);
83 select * from t1;
84 a
85 5
86 10
87 110
88 250
89 310
90 insert into t1 values (1000);
91 set @@insert_id=400;
92 insert into t1 values(NULL),(NULL);
93 select * from t1;
94 a
95 5
96 10
97 110
98 250
99 310
100 400
101 410
102 1000
103 connection slave;
104 select * from t1;
105 a
106 5
107 10
108 110
109 250
110 310
111 400
112 410
113 1000
114 connection master;
115 drop table t1;
116 set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
117 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
118 insert into t1 values (NULL),(5),(NULL),(NULL);
119 insert into t1 values (500),(NULL),(502),(NULL),(NULL);
120 select * from t1;
121 a
122 1
123 5
124 6
125 7
126 500
127 501
128 502
129 503
130 504
131 set @@insert_id=600;
132 insert into t1 values(600),(NULL),(NULL);
133 ERROR 23000: Duplicate entry '600' for key 'PRIMARY'
134 set @@insert_id=600;
135 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
136 Warnings:
137 Warning 1062 Duplicate entry '600' for key 'PRIMARY'
138 Warning 1062 Duplicate entry '600' for key 'PRIMARY'
139 Warning 1062 Duplicate entry '600' for key 'PRIMARY'
140 select * from t1;
141 a
142 1
143 5
144 6
145 7
146 500
147 501
148 502
149 503
150 504
151 600
152 610
153 611
154 connection slave;
155 select * from t1;
156 a
157 1
158 5
159 6
160 7
161 500
162 501
163 502
164 503
165 504
166 600
167 610
168 611
169 connection master;
170 drop table t1;
171 set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1;
172 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
173 connection slave;
174 insert into t1 values(2),(12),(22),(32),(42);
175 connection master;
176 insert into t1 values (NULL),(NULL);
177 insert into t1 values (3),(NULL),(NULL);
178 select * from t1;
179 a
180 1
181 3
182 11
183 21
184 31
185 connection slave;
186 select * from t1;
187 a
188 1
189 2
190 3
191 11
192 12
193 21
194 22
195 31
196 32
197 42
198 connection master;
199 drop table t1;
200 create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
201 insert into t1 values(103);
202 set auto_increment_increment=11;
203 set auto_increment_offset=4;
204 insert into t1 values(null);
205 insert into t1 values(null);
206 insert into t1 values(null);
207 ERROR 22003: Out of range value for column 'a' at row 1
208 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
209 a mod(a-@@auto_increment_offset,@@auto_increment_increment)
210 103 0
211 114 0
212 125 0
213 create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam;
214 set auto_increment_increment=10;
215 set auto_increment_offset=1;
216 set insert_id=1000;
217 insert into t2 values(10);
218 insert into t2 values(null);
219 ERROR 22003: Out of range value for column 'a' at row 1
220 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a;
221 a mod(a-@@auto_increment_offset,@@auto_increment_increment)
222 10 9
223 create table t3 like t1;
224 set auto_increment_increment=1000;
225 set auto_increment_offset=700;
226 insert into t3 values(null);
227 ERROR 22003: Out of range value for column 'a' at row 1
228 select * from t3 order by a;
229 a
230 connection slave;
231 select * from t1 order by a;
232 a
233 103
234 114
235 125
236 select * from t2 order by a;
237 a
238 10
239 select * from t3 order by a;
240 a
241 connection master;
242 drop table t1,t2,t3;
243 connection slave;
244 connection master;
245 set auto_increment_increment=1;
246 set auto_increment_offset=1;
247 CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
248 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
249 show create table t1;
250 Table Create Table
251 t1 CREATE TABLE `t1` (
252 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
253 PRIMARY KEY (`id`)
254 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
255 connection slave;
256 show create table t1;
257 Table Create Table
258 t1 CREATE TABLE `t1` (
259 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
260 PRIMARY KEY (`id`)
261 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
262 connection master;
263 drop table t1;
264 include/rpl_reset.inc
265 connection master;
266 DROP TABLE IF EXISTS t1;
267 DROP TABLE IF EXISTS t2;
268 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
269 CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
270 SET SQL_MODE='';
271 INSERT INTO t1 VALUES(NULL);
272 INSERT INTO t2 VALUES(NULL);
273 SELECT * FROM t1;
274 id
275 1
276 SELECT * FROM t2;
277 id
278 1
279 INSERT INTO t1 VALUES();
280 INSERT INTO t2 VALUES();
281 SELECT * FROM t1;
282 id
283 1
284 2
285 SELECT * FROM t2;
286 id
287 1
288 2
289 INSERT INTO t1 VALUES(0);
290 INSERT INTO t2 VALUES(0);
291 SELECT * FROM t1;
292 id
293 1
294 2
295 3
296 SELECT * FROM t2;
297 id
298 1
299 2
300 3
301 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
302 INSERT INTO t1 VALUES(0);
303 INSERT INTO t2 VALUES(0);
304 SELECT * FROM t1;
305 id
306 0
307 1
308 2
309 3
310 SELECT * FROM t2;
311 id
312 0
313 1
314 2
315 3
316 INSERT INTO t1 VALUES(4);
317 INSERT INTO t2 VALUES(4);
318 FLUSH LOGS;
319 connection slave;
320 include/diff_tables.inc [master:t1, slave:t1]
321 include/diff_tables.inc [master:t2, slave:t2]
322 connection master;
323 DROP TABLE t1;
324 DROP TABLE t2;
325 connection slave;
326 connection master;
327 connection slave;
328 include/diff_tables.inc [master:t1, slave:t1]
329 include/diff_tables.inc [master:t2, slave:t2]
330 connection master;
331 DROP TABLE t1;
332 DROP TABLE t2;
333 SET SQL_MODE='';
334 connection slave;
335 connection master;
336 CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam;
337 CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
338 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column");
339 connection slave;
340 connection slave;
341 CREATE TABLE test.slave_only(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
342 INSERT INTO slave_only VALUES(NULL);
343 CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO slave_only VALUES(NULL);
344 connection master;
345 INSERT INTO t_ignored1 VALUES(NULL);
346 INSERT INTO t1 VALUES('s');
347 UPDATE t1 SET s='s1';
348 connection slave;
349 connection slave;
350 SELECT * FROM t1;
351 s
352 s1
353 connection master;
354 CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
355 connection slave;
356 connection slave;
357 STOP SLAVE;
358 SET GLOBAL sql_slave_skip_counter = 2;
359 START SLAVE;
360 connection master;
361 INSERT INTO t_ignored2 VALUES(NULL);
362 UPDATE t1 SET s='s2';
363 connection slave;
364 connection slave;
365 SELECT * FROM t1;
366 s
367 s2
368 SHOW TABLES LIKE 't\_ignored_';
369 Tables_in_test (t\_ignored_)
370 t_ignored2
371 SELECT * FROM t_ignored2;
372 id
373 DROP TABLE slave_only;
374 connection master;
375 DROP TABLE t1;
376 DROP TABLE t_ignored1;
377 DROP TABLE t_ignored2;
378 connection master;
379 CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb;
380 BEGIN;
381 # Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing
382 # zero to fill the auto_increment field.
383 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
384 INSERT INTO t1(id,data) VALUES(0,2);
385 # Resetting sql_mode without NO_AUTO_VALUE_ON_ZERO to
386 # affect the execution of the transaction on slave.
387 SET SQL_MODE=0;
388 COMMIT;
389 SELECT * FROM t1;
390 id data
391 0 2
392 connection slave;
393 SELECT * FROM t1;
394 id data
395 0 2
396 connection master;
397 DROP TABLE t1;
398 connection slave;
399 include/rpl_end.inc