Debian Codegraph @

mysql-test/suite/ndb/r/ndb_auto_increment.result

raw
1 DROP TABLE IF EXISTS t1,t2;
2 DROP TABLE IF EXISTS t1;
3 set @old_auto_increment_offset = @@session.auto_increment_offset;
4 set @old_auto_increment_increment = @@session.auto_increment_increment;
5 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
6 set @old_auto_increment_offset = @@session.auto_increment_offset;
7 set @old_auto_increment_increment = @@session.auto_increment_increment;
8 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
9 flush status;
10 create table t1 (a int not null auto_increment primary key) engine ndb;
11 insert into t1 values (NULL);
12 select * from t1 order by a;
13 a
14 1
15 update t1 set a = 5 where a = 1;
16 insert into t1 values (NULL);
17 select * from t1 order by a;
18 a
19 5
20 6
21 insert into t1 values (7);
22 insert into t1 values (NULL);
23 select * from t1 order by a;
24 a
25 5
26 6
27 7
28 8
29 insert into t1 values (2);
30 insert into t1 values (NULL);
31 select * from t1 order by a;
32 a
33 2
34 5
35 6
36 7
37 8
38 9
39 update t1 set a = 4 where a = 2;
40 insert into t1 values (NULL);
41 select * from t1 order by a;
42 a
43 4
44 5
45 6
46 7
47 8
48 9
49 10
50 delete from t1 where a = 10;
51 insert into t1 values (NULL);
52 select * from t1 order by a;
53 a
54 4
55 5
56 6
57 7
58 8
59 9
60 11
61 replace t1 values (NULL);
62 select * from t1 order by a;
63 a
64 4
65 5
66 6
67 7
68 8
69 9
70 11
71 12
72 replace t1 values (15);
73 select * from t1 order by a;
74 a
75 4
76 5
77 6
78 7
79 8
80 9
81 11
82 12
83 15
84 replace into t1 values (NULL);
85 select * from t1 order by a;
86 a
87 4
88 5
89 6
90 7
91 8
92 9
93 11
94 12
95 15
96 16
97 replace t1 values (15);
98 select * from t1 order by a;
99 a
100 4
101 5
102 6
103 7
104 8
105 9
106 11
107 12
108 15
109 16
110 insert ignore into t1 values (NULL);
111 select * from t1 order by a;
112 a
113 4
114 5
115 6
116 7
117 8
118 9
119 11
120 12
121 15
122 16
123 17
124 insert ignore into t1 values (15), (NULL);
125 Warnings:
126 Warning 1062 Duplicate entry '15' for key 't1.PRIMARY'
127 select * from t1 order by a;
128 a
129 4
130 5
131 6
132 7
133 8
134 9
135 11
136 12
137 15
138 16
139 17
140 18
141 insert into t1 values (15)
142 on duplicate key update a = 20;
143 insert into t1 values (NULL);
144 select * from t1 order by a;
145 a
146 4
147 5
148 6
149 7
150 8
151 9
152 11
153 12
154 16
155 17
156 18
157 20
158 21
159 insert into t1 values (NULL) on duplicate key update a = 30;
160 select * from t1 order by a;
161 a
162 4
163 5
164 6
165 7
166 8
167 9
168 11
169 12
170 16
171 17
172 18
173 20
174 21
175 22
176 insert into t1 values (30) on duplicate key update a = 40;
177 select * from t1 order by a;
178 a
179 4
180 5
181 6
182 7
183 8
184 9
185 11
186 12
187 16
188 17
189 18
190 20
191 21
192 22
193 30
194 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
195 select * from t1 order by a;
196 a
197 4
198 5
199 6
200 7
201 8
202 9
203 11
204 12
205 16
206 17
207 18
208 20
209 21
210 22
211 30
212 600
213 601
214 602
215 610
216 611
217 drop table t1;
218 create table t1 (a int not null primary key,
219 b int not null unique auto_increment) engine ndb;
220 insert into t1 values (1, NULL);
221 insert into t1 values (3, NULL);
222 update t1 set b = 3 where a = 3;
223 insert into t1 values (4, NULL);
224 select * from t1 order by a;
225 a b
226 1 1
227 3 3
228 4 4
229 drop table t1;
230 CREATE TABLE t1 (
231 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
232 b INT NOT NULL,
233 c INT NOT NULL UNIQUE
234 ) ENGINE=NDBCLUSTER;
235 CREATE TABLE t2 (
236 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
237 b INT NOT NULL,
238 c INT NOT NULL UNIQUE
239 ) ENGINE=MYISAM;
240 SET @@session.auto_increment_increment=10;
241 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
242 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
243 SELECT * FROM t1 ORDER BY pk;
244 pk b c
245 1 1 0
246 11 2 1
247 21 3 2
248 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
249 COUNT(t1.pk)
250 3
251 TRUNCATE t1;
252 TRUNCATE t2;
253 SET @@session.auto_increment_offset=5;
254 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
255 INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
256 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
257 INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
258 SELECT * FROM t1 ORDER BY pk;
259 pk b c
260 5 1 0
261 15 2 1
262 25 3 2
263 27 4 3
264 35 5 4
265 99 6 5
266 105 7 6
267 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
268 COUNT(t1.pk)
269 7
270 TRUNCATE t1;
271 TRUNCATE t2;
272 SET @@session.auto_increment_increment=2;
273 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
274 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
275 SELECT * FROM t1 ORDER BY pk;
276 pk b c
277 1 1 0
278 3 2 1
279 5 3 2
280 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
281 COUNT(t1.pk)
282 3
283 DROP TABLE t1, t2;
284 CREATE TABLE t1 (
285 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
286 b INT NOT NULL,
287 c INT NOT NULL UNIQUE
288 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
289 CREATE TABLE t2 (
290 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
291 b INT NOT NULL,
292 c INT NOT NULL UNIQUE
293 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
294 SET @@session.auto_increment_offset=1;
295 SET @@session.auto_increment_increment=1;
296 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
297 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
298 SELECT * FROM t1 ORDER BY pk;
299 pk b c
300 7 1 0
301 8 2 1
302 9 3 2
303 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
304 COUNT(t1.pk)
305 3
306 DROP TABLE t1, t2;
307 CREATE TABLE t1 (
308 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
309 b INT NOT NULL,
310 c INT NOT NULL UNIQUE
311 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3;
312 CREATE TABLE t2 (
313 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
314 b INT NOT NULL,
315 c INT NOT NULL UNIQUE
316 ) ENGINE=MYISAM AUTO_INCREMENT = 3;
317 SET @@session.auto_increment_offset=5;
318 SET @@session.auto_increment_increment=10;
319 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
320 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
321 SELECT * FROM t1 ORDER BY pk;
322 pk b c
323 5 1 0
324 15 2 1
325 25 3 2
326 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
327 COUNT(t1.pk)
328 3
329 DROP TABLE t1, t2;
330 CREATE TABLE t1 (
331 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
332 b INT NOT NULL,
333 c INT NOT NULL UNIQUE
334 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
335 CREATE TABLE t2 (
336 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
337 b INT NOT NULL,
338 c INT NOT NULL UNIQUE
339 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
340 SET @@session.auto_increment_offset=5;
341 SET @@session.auto_increment_increment=10;
342 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
343 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
344 SELECT * FROM t1 ORDER BY pk;
345 pk b c
346 15 1 0
347 25 2 1
348 35 3 2
349 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
350 COUNT(t1.pk)
351 3
352 DROP TABLE t1, t2;
353 CREATE TABLE t1 (
354 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
355 b INT NOT NULL,
356 c INT NOT NULL UNIQUE
357 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5;
358 CREATE TABLE t2 (
359 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
360 b INT NOT NULL,
361 c INT NOT NULL UNIQUE
362 ) ENGINE=MYISAM AUTO_INCREMENT = 5;
363 SET @@session.auto_increment_offset=5;
364 SET @@session.auto_increment_increment=10;
365 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
366 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
367 SELECT * FROM t1 ORDER BY pk;
368 pk b c
369 5 1 0
370 15 2 1
371 25 3 2
372 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
373 COUNT(t1.pk)
374 3
375 DROP TABLE t1, t2;
376 CREATE TABLE t1 (
377 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
378 b INT NOT NULL,
379 c INT NOT NULL UNIQUE
380 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100;
381 CREATE TABLE t2 (
382 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
383 b INT NOT NULL,
384 c INT NOT NULL UNIQUE
385 ) ENGINE=MYISAM AUTO_INCREMENT = 100;
386 SET @@session.auto_increment_offset=5;
387 SET @@session.auto_increment_increment=10;
388 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
389 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
390 SELECT * FROM t1 ORDER BY pk;
391 pk b c
392 105 1 0
393 115 2 1
394 125 3 2
395 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
396 COUNT(t1.pk)
397 3
398 DROP TABLE t1, t2;
399 SET @@session.auto_increment_offset=1;
400 SET @@session.auto_increment_increment=1;
401 set ndb_autoincrement_prefetch_sz = 32;
402 drop table if exists t1;
403 SET @@session.auto_increment_offset=1;
404 SET @@session.auto_increment_increment=1;
405 set ndb_autoincrement_prefetch_sz = 32;
406 create table t1 (a int not null auto_increment primary key) engine ndb;
407 insert into t1 values (NULL);
408 insert into t1 values (NULL);
409 select * from t1 order by a;
410 a
411 1
412 33
413 insert into t1 values (20);
414 insert into t1 values (NULL);
415 select * from t1 order by a;
416 a
417 1
418 20
419 33
420 34
421 insert into t1 values (35);
422 insert into t1 values (NULL);
423 insert into t1 values (NULL);
424 ERROR 23000: Duplicate entry '35' for key 't1.PRIMARY'
425 select * from t1 order by a;
426 a
427 1
428 20
429 33
430 34
431 35
432 65
433 insert into t1 values (100);
434 insert into t1 values (NULL);
435 insert into t1 values (NULL);
436 select * from t1 order by a;
437 a
438 1
439 20
440 33
441 34
442 35
443 65
444 66
445 100
446 101
447 set auto_increment_offset = @old_auto_increment_offset;
448 set auto_increment_increment = @old_auto_increment_increment;
449 set ndb_autoincrement_prefetch_sz = 1;
450 drop table t1;
451 set auto_increment_offset = @old_auto_increment_offset;
452 set auto_increment_increment = @old_auto_increment_increment;
453 set ndb_autoincrement_prefetch_sz = 1;
454 CREATE TABLE `t1` (
455 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
456 `k` int(10) unsigned NOT NULL DEFAULT '0',
457 `c` char(120) NOT NULL DEFAULT '',
458 `pad` char(60) NOT NULL DEFAULT '',
459 PRIMARY KEY (`id`),
460 KEY `k` (`k`)
461 ) ENGINE=ndbcluster;
462 Warnings:
463 Warning 1681 Integer display width is deprecated and will be removed in a future release.
464 Warning 1681 Integer display width is deprecated and will be removed in a future release.
465 CREATE TABLE `t2` (
466 `evend_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
467 `timestamp` int(11) NOT NULL,
468 `server_id` int(11) NOT NULL,
469 PRIMARY KEY (`evend_id`)
470 ) ENGINE=ndbcluster;
471 Warnings:
472 Warning 1681 Integer display width is deprecated and will be removed in a future release.
473 Warning 1681 Integer display width is deprecated and will be removed in a future release.
474 Warning 1681 Integer display width is deprecated and will be removed in a future release.
475 insert into t1 values (null,1,'',''),(null,2,'','');
476 CREATE TRIGGER tr1
477 AFTER UPDATE ON t1
478 FOR EACH ROW
479 BEGIN
480 insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id);
481 end;
482 |
483 CREATE TRIGGER tr1
484 AFTER UPDATE ON t1
485 FOR EACH ROW
486 BEGIN
487 insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id);
488 end;
489 |
490 update t1 set c='foobar' where id=1;
491 update t1 set c='foobar' where id=1;
492 update t1 set c='foobar' where id=1;
493 update t1 set c='foobar' where id=1;
494 update t1 set c='foobar' where id=1;
495 update t1 set c='foobar' where id=1;
496 update t1 set c='foobar' where id=1;
497 update t1 set c='foobar' where id=1;
498 select evend_id,server_id from t2 order by evend_id;
499 evend_id server_id
500 1 1
501 2 2
502 3 1
503 4 2
504 5 1
505 6 2
506 7 1
507 8 2
508 drop trigger tr1;
509 drop table t1, t2;
510 drop trigger if exists tr1;
511 create table t1 (a int primary key auto_increment, b int) engine=ndb;
512 insert into t1 values (null,1),(null,2),(null,3);
513 show create table t1;
514 Table Create Table
515 t1 CREATE TABLE `t1` (
516 `a` int NOT NULL AUTO_INCREMENT,
517 `b` int DEFAULT NULL,
518 PRIMARY KEY (`a`)
519 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
520 drop table t1;
521 create table t1 (a int primary key auto_increment, b int) auto_increment=5
522 engine=ndb;
523 alter table t1 auto_increment=32000000;
524 show create table t1;
525 Table Create Table
526 t1 CREATE TABLE `t1` (
527 `a` int NOT NULL AUTO_INCREMENT,
528 `b` int DEFAULT NULL,
529 PRIMARY KEY (`a`)
530 ) ENGINE=ndbcluster AUTO_INCREMENT=32000000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
531 drop table t1;
532 CREATE TABLE t1 (
533 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
534 data binary(16) NOT NULL,
535 PRIMARY KEY (id),
536 UNIQUE KEY uk_t1_data (data)
537 ) ENGINE = NDB;
538 Warnings:
539 Warning 1681 Integer display width is deprecated and will be removed in a future release.
540 INSERT IGNORE INTO t1 (data) VALUES (6),(6),(6),(6),(6),(6),(6);
541 Warnings:
542 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
543 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
544 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
545 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
546 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
547 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
548 SELECT id FROM t1;
549 id
550 1
551 DELETE from t1;
552 INSERT IGNORE INTO t1 (data) VALUES (6),(6),(6),(6),(6),(6),(6);
553 Warnings:
554 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
555 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
556 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
557 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
558 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
559 Warning 1062 Duplicate entry '6' for key 't1.uk_t1_data'
560 SELECT id from t1;
561 id
562 3
563 DROP TABLE t1;
564 create table t1 (a serial) engine ndb;
565 set @@insert_id=1;
566 insert into t1 values(null);
567 insert into t1 values(null);
568 insert into t1 values(null);
569 insert into t1 values(null);
570 set @@insert_id=17;
571 insert into t1 values(null);
572 insert into t1 values(null);
573 insert into t1 values(null);
574 select * from t1 order by a;
575 a
576 1
577 2
578 3
579 4
580 17
581 18
582 19
583 set @@insert_id=1;
584 insert into t1 values(null);
585 ERROR 23000: Duplicate entry '1' for key 't1.a'
586 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
587 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
588 drop table t1;
589 create table t1 (a tinyint not null auto_increment primary key) engine ndb;
590 Warnings:
591 Warning 1063 Max value for column a in table test.t1 is less than autoincrement prefetch size. Please decrease ndb_autoincrement_prefetch_sz
592 drop table t1;