Debian Codegraph @

mysql-test/suite/parts/r/partition_auto_increment_blackhole.result

raw
1 DROP TABLE IF EXISTS t1;
2 # test without partitioning for reference
3 CREATE TABLE t1 (
4 c1 INT NOT NULL AUTO_INCREMENT,
5 PRIMARY KEY (c1))
6 ENGINE='Blackhole';
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `c1` int(11) NOT NULL AUTO_INCREMENT,
11 PRIMARY KEY (`c1`)
12 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
14 AND TABLE_NAME='t1';
15 AUTO_INCREMENT
16 1
17 INSERT INTO t1 VALUES (2);
18 INSERT INTO t1 VALUES (4);
19 INSERT INTO t1 VALUES (NULL);
20 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
21 AND TABLE_NAME='t1';
22 AUTO_INCREMENT
23 1
24 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
25 AND TABLE_NAME='t1';
26 AUTO_INCREMENT
27 1
28 INSERT INTO t1 VALUES (0);
29 INSERT INTO t1 VALUES (5), (16);
30 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
31 # mysql_errno: 0
32 INSERT INTO t1 VALUES (17);
33 INSERT INTO t1 VALUES (19), (NULL);
34 INSERT INTO t1 VALUES (NULL), (10), (NULL);
35 INSERT INTO t1 VALUES (NULL);
36 SET INSERT_ID = 30;
37 INSERT INTO t1 VALUES (NULL);
38 SET INSERT_ID = 29;
39 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
40 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
41 # mysql_errno: 0
42 INSERT INTO t1 VALUES (NULL);
43 UPDATE t1 SET c1 = 50 WHERE c1 = 17;
44 UPDATE t1 SET c1 = 51 WHERE c1 = 19;
45 FLUSH TABLES;
46 UPDATE t1 SET c1 = 40 WHERE c1 = 50;
47 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
48 AND TABLE_NAME='t1';
49 AUTO_INCREMENT
50 1
51 UPDATE t1 SET c1 = NULL WHERE c1 = 4;
52 INSERT INTO t1 VALUES (NULL);
53 INSERT INTO t1 VALUES (NULL);
54 SELECT * FROM t1 ORDER BY c1;
55 c1
56 DROP TABLE t1;
57 CREATE TABLE t1 (
58 c1 INT NOT NULL AUTO_INCREMENT,
59 PRIMARY KEY (c1))
60 ENGINE='Blackhole';
61 SHOW CREATE TABLE t1;
62 Table Create Table
63 t1 CREATE TABLE `t1` (
64 `c1` int(11) NOT NULL AUTO_INCREMENT,
65 PRIMARY KEY (`c1`)
66 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
67 FLUSH TABLE;
68 SHOW CREATE TABLE t1;
69 Table Create Table
70 t1 CREATE TABLE `t1` (
71 `c1` int(11) NOT NULL AUTO_INCREMENT,
72 PRIMARY KEY (`c1`)
73 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
74 INSERT INTO t1 VALUES (4);
75 FLUSH TABLE;
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `c1` int(11) NOT NULL AUTO_INCREMENT,
80 PRIMARY KEY (`c1`)
81 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
82 INSERT INTO t1 VALUES (NULL);
83 FLUSH TABLE;
84 SHOW CREATE TABLE t1;
85 Table Create Table
86 t1 CREATE TABLE `t1` (
87 `c1` int(11) NOT NULL AUTO_INCREMENT,
88 PRIMARY KEY (`c1`)
89 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
90 DELETE FROM t1;
91 INSERT INTO t1 VALUES (NULL);
92 SHOW CREATE TABLE t1;
93 Table Create Table
94 t1 CREATE TABLE `t1` (
95 `c1` int(11) NOT NULL AUTO_INCREMENT,
96 PRIMARY KEY (`c1`)
97 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
98 SELECT * FROM t1 ORDER BY c1;
99 c1
100 TRUNCATE TABLE t1;
101 INSERT INTO t1 VALUES (NULL);
102 SHOW CREATE TABLE t1;
103 Table Create Table
104 t1 CREATE TABLE `t1` (
105 `c1` int(11) NOT NULL AUTO_INCREMENT,
106 PRIMARY KEY (`c1`)
107 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
108 SELECT * FROM t1 ORDER BY c1;
109 c1
110 INSERT INTO t1 VALUES (100);
111 INSERT INTO t1 VALUES (NULL);
112 DELETE FROM t1 WHERE c1 >= 100;
113 OPTIMIZE TABLE t1;
114 Table Op Msg_type Msg_text
115 test.t1 optimize note The storage engine for the table doesn't support optimize
116 SHOW CREATE TABLE t1;
117 Table Create Table
118 t1 CREATE TABLE `t1` (
119 `c1` int(11) NOT NULL AUTO_INCREMENT,
120 PRIMARY KEY (`c1`)
121 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
122 DROP TABLE t1;
123 CREATE TABLE t1
124 (a INT NULL AUTO_INCREMENT,
125 UNIQUE KEY (a))
126 ENGINE='Blackhole';
127 SET LAST_INSERT_ID = 999;
128 SET INSERT_ID = 0;
129 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
130 SELECT LAST_INSERT_ID();
131 LAST_INSERT_ID()
132 999
133 SELECT * FROM t1;
134 a
135 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
136 SELECT LAST_INSERT_ID();
137 LAST_INSERT_ID()
138 999
139 SELECT * FROM t1;
140 a
141 UPDATE t1 SET a = 1 WHERE a IS NULL;
142 SELECT LAST_INSERT_ID();
143 LAST_INSERT_ID()
144 999
145 SELECT * FROM t1;
146 a
147 UPDATE t1 SET a = NULL WHERE a = 1;
148 SELECT LAST_INSERT_ID();
149 LAST_INSERT_ID()
150 999
151 SELECT * FROM t1;
152 a
153 DROP TABLE t1;
154 SET INSERT_ID = 1;
155 # Simple test with NULL
156 CREATE TABLE t1 (
157 c1 INT NOT NULL AUTO_INCREMENT,
158 PRIMARY KEY (c1))
159 ENGINE='Blackhole'
160 PARTITION BY HASH(c1)
161 PARTITIONS 2;
162 INSERT INTO t1 VALUES (NULL);
163 SHOW CREATE TABLE t1;
164 Table Create Table
165 t1 CREATE TABLE `t1` (
166 `c1` int(11) NOT NULL AUTO_INCREMENT,
167 PRIMARY KEY (`c1`)
168 ) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
169 PARTITION BY HASH (`c1`)
170 PARTITIONS 2
171 SELECT * FROM t1;
172 c1
173 DROP TABLE t1;
174 # Test with sql_mode and first insert as 0
175 CREATE TABLE t1 (
176 c1 INT,
177 c2 INT NOT NULL AUTO_INCREMENT,
178 PRIMARY KEY (c2))
179 ENGINE='Blackhole'
180 PARTITION BY HASH(c2)
181 PARTITIONS 2;
182 INSERT INTO t1 VALUES (1, NULL);
183 INSERT INTO t1 VALUES (1, 1), (99, 99);
184 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
185 # mysql_errno: 0
186 INSERT INTO t1 VALUES (1, NULL);
187 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
188 INSERT INTO t1 VALUES (1, 0);
189 SELECT * FROM t1 ORDER BY c1, c2;
190 c1 c2
191 DROP TABLE t1;
192 CREATE TABLE t1 (
193 c1 INT,
194 c2 INT NOT NULL AUTO_INCREMENT,
195 PRIMARY KEY (c2))
196 ENGINE='Blackhole'
197 PARTITION BY HASH(c2)
198 PARTITIONS 2;
199 INSERT INTO t1 VALUES (1, 0);
200 INSERT INTO t1 VALUES (1, 1), (1, NULL);
201 INSERT INTO t1 VALUES (2, NULL), (4, 7);
202 INSERT INTO t1 VALUES (1, NULL);
203 SELECT * FROM t1 ORDER BY c1, c2;
204 c1 c2
205 SET @@session.sql_mode = '';
206 DROP TABLE t1;
207 # Simple test with NULL, 0 and explicit values both incr. and desc.
208 CREATE TABLE t1 (
209 c1 INT NOT NULL AUTO_INCREMENT,
210 PRIMARY KEY (c1))
211 ENGINE='Blackhole'
212 PARTITION BY HASH(c1)
213 PARTITIONS 2;
214 INSERT INTO t1 VALUES (2), (4), (NULL);
215 INSERT INTO t1 VALUES (0);
216 INSERT INTO t1 VALUES (5), (16);
217 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
218 # mysql_errno: 0
219 INSERT INTO t1 VALUES (17), (19), (NULL);
220 INSERT INTO t1 VALUES (NULL), (10), (NULL);
221 INSERT INTO t1 VALUES (NULL), (9);
222 INSERT INTO t1 VALUES (59), (55);
223 INSERT INTO t1 VALUES (NULL), (90);
224 INSERT INTO t1 VALUES (NULL);
225 UPDATE t1 SET c1 = 150 WHERE c1 = 17;
226 UPDATE t1 SET c1 = 151 WHERE c1 = 19;
227 FLUSH TABLES;
228 UPDATE t1 SET c1 = 140 WHERE c1 = 150;
229 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
230 AND TABLE_NAME='t1';
231 AUTO_INCREMENT
232 1
233 UPDATE t1 SET c1 = NULL WHERE c1 = 4;
234 INSERT INTO t1 VALUES (NULL);
235 INSERT INTO t1 VALUES (NULL);
236 SELECT * FROM t1 ORDER BY c1;
237 c1
238 DROP TABLE t1;
239 # Test with auto_increment_increment and auto_increment_offset.
240 CREATE TABLE t1 (
241 c1 INT NOT NULL AUTO_INCREMENT,
242 PRIMARY KEY (c1))
243 ENGINE='Blackhole'
244 PARTITION BY HASH(c1)
245 PARTITIONS 2;
246 SET @@session.auto_increment_increment = 10;
247 SET @@session.auto_increment_offset = 5;
248 INSERT INTO t1 VALUES (1);
249 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
250 SET @@session.auto_increment_increment = 5;
251 SET @@session.auto_increment_offset = 3;
252 INSERT INTO t1 VALUES (NULL);
253 INSERT INTO t1 VALUES (33 + 1);
254 INSERT INTO t1 VALUES (NULL);
255 INSERT INTO t1 VALUES (38 + 2);
256 INSERT INTO t1 VALUES (NULL);
257 INSERT INTO t1 VALUES (43 + 3);
258 INSERT INTO t1 VALUES (NULL);
259 INSERT INTO t1 VALUES (48 + 4);
260 INSERT INTO t1 VALUES (NULL);
261 INSERT INTO t1 VALUES (53 + 5);
262 INSERT INTO t1 VALUES (NULL);
263 INSERT INTO t1 VALUES (63 + 6);
264 INSERT INTO t1 VALUES (NULL);
265 SET @@session.auto_increment_increment = 1;
266 SET @@session.auto_increment_offset = 1;
267 SELECT * FROM t1 ORDER BY c1;
268 c1
269 DROP TABLE t1;
270 # Test reported auto_increment value
271 CREATE TABLE t1 (
272 c1 INT NOT NULL AUTO_INCREMENT,
273 PRIMARY KEY (c1))
274 ENGINE='Blackhole'
275 PARTITION BY HASH (c1)
276 PARTITIONS 2;
277 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
278 AND TABLE_NAME='t1';
279 AUTO_INCREMENT
280 1
281 INSERT INTO t1 VALUES (2);
282 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
283 AND TABLE_NAME='t1';
284 AUTO_INCREMENT
285 3
286 INSERT INTO t1 VALUES (4);
287 INSERT INTO t1 VALUES (NULL);
288 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
289 AND TABLE_NAME='t1';
290 AUTO_INCREMENT
291 6
292 INSERT INTO t1 VALUES (NULL);
293 INSERT INTO t1 VALUES (17);
294 INSERT INTO t1 VALUES (19);
295 INSERT INTO t1 VALUES (NULL);
296 INSERT INTO t1 VALUES (NULL);
297 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
298 AND TABLE_NAME='t1';
299 AUTO_INCREMENT
300 22
301 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
302 AND TABLE_NAME='t1';
303 AUTO_INCREMENT
304 22
305 INSERT INTO t1 VALUES (10);
306 SELECT * FROM t1 ORDER BY c1;
307 c1
308 INSERT INTO t1 VALUES (NULL);
309 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
310 AND TABLE_NAME='t1';
311 AUTO_INCREMENT
312 23
313 INSERT INTO t1 VALUES (NULL);
314 INSERT INTO t1 VALUES (15);
315 INSERT INTO t1 VALUES (NULL);
316 SELECT * FROM t1 ORDER BY c1;
317 c1
318 INSERT INTO t1 VALUES (NULL);
319 DELETE FROM t1;
320 INSERT INTO t1 VALUES (NULL);
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `c1` int(11) NOT NULL AUTO_INCREMENT,
325 PRIMARY KEY (`c1`)
326 ) ENGINE=BLACKHOLE AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
327 PARTITION BY HASH (`c1`)
328 PARTITIONS 2
329 SELECT * FROM t1 ORDER BY c1;
330 c1
331 TRUNCATE TABLE t1;
332 INSERT INTO t1 VALUES (NULL);
333 SHOW CREATE TABLE t1;
334 Table Create Table
335 t1 CREATE TABLE `t1` (
336 `c1` int(11) NOT NULL AUTO_INCREMENT,
337 PRIMARY KEY (`c1`)
338 ) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
339 PARTITION BY HASH (`c1`)
340 PARTITIONS 2
341 SELECT * FROM t1 ORDER BY c1;
342 c1
343 INSERT INTO t1 VALUES (100);
344 INSERT INTO t1 VALUES (NULL);
345 DELETE FROM t1 WHERE c1 >= 100;
346 OPTIMIZE TABLE t1;
347 Table Op Msg_type Msg_text
348 test.t1 optimize note The storage engine for the table doesn't support optimize
349 SHOW CREATE TABLE t1;
350 Table Create Table
351 t1 CREATE TABLE `t1` (
352 `c1` int(11) NOT NULL AUTO_INCREMENT,
353 PRIMARY KEY (`c1`)
354 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
355 PARTITION BY HASH (`c1`)
356 PARTITIONS 2
357 DROP TABLE t1;
358 # Test with two threads
359 connection default;
360 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
361 ENGINE = 'Blackhole'
362 PARTITION BY HASH(c1)
363 PARTITIONS 2;
364 INSERT INTO t1 (c1) VALUES (2);
365 INSERT INTO t1 (c1) VALUES (4);
366 connect con1, localhost, root,,;
367 connection con1;
368 INSERT INTO t1 (c1) VALUES (NULL);
369 INSERT INTO t1 (c1) VALUES (10);
370 connection default;
371 INSERT INTO t1 (c1) VALUES (NULL);
372 INSERT INTO t1 (c1) VALUES (NULL);
373 INSERT INTO t1 (c1) VALUES (19);
374 INSERT INTO t1 (c1) VALUES (21);
375 connection con1;
376 INSERT INTO t1 (c1) VALUES (NULL);
377 connection default;
378 INSERT INTO t1 (c1) VALUES (16);
379 connection con1;
380 INSERT INTO t1 (c1) VALUES (NULL);
381 disconnect con1;
382 connection default;
383 INSERT INTO t1 (c1) VALUES (NULL);
384 SELECT * FROM t1 ORDER BY c1;
385 c1
386 DROP TABLE t1;
387 # Test with two threads + start transaction NO PARTITIONING
388 connect con1, localhost, root,,;
389 connection default;
390 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
391 ENGINE = 'Blackhole';
392 START TRANSACTION;
393 INSERT INTO t1 (c1) VALUES (2);
394 INSERT INTO t1 (c1) VALUES (4);
395 connection con1;
396 START TRANSACTION;
397 INSERT INTO t1 (c1) VALUES (NULL);
398 INSERT INTO t1 (c1) VALUES (10);
399 connection default;
400 INSERT INTO t1 (c1) VALUES (NULL);
401 INSERT INTO t1 (c1) VALUES (NULL);
402 INSERT INTO t1 (c1) VALUES (19);
403 INSERT INTO t1 (c1) VALUES (21);
404 connection con1;
405 INSERT INTO t1 (c1) VALUES (NULL);
406 connection default;
407 INSERT INTO t1 (c1) VALUES (16);
408 connection con1;
409 INSERT INTO t1 (c1) VALUES (NULL);
410 SELECT * FROM t1 ORDER BY c1;
411 c1
412 COMMIT;
413 SELECT * FROM t1 ORDER BY c1;
414 c1
415 disconnect con1;
416 connection default;
417 INSERT INTO t1 (c1) VALUES (NULL);
418 SELECT * FROM t1 ORDER BY c1;
419 c1
420 COMMIT;
421 SELECT * FROM t1 ORDER BY c1;
422 c1
423 DROP TABLE t1;
424 # Test with two threads + start transaction
425 connect con1, localhost, root,,;
426 connection default;
427 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
428 ENGINE = 'Blackhole'
429 PARTITION BY HASH(c1)
430 PARTITIONS 2;
431 START TRANSACTION;
432 INSERT INTO t1 (c1) VALUES (2);
433 INSERT INTO t1 (c1) VALUES (4);
434 connection con1;
435 START TRANSACTION;
436 INSERT INTO t1 (c1) VALUES (NULL), (10);
437 connection default;
438 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
439 INSERT INTO t1 (c1) VALUES (21);
440 connection con1;
441 INSERT INTO t1 (c1) VALUES (NULL);
442 connection default;
443 INSERT INTO t1 (c1) VALUES (16);
444 connection con1;
445 INSERT INTO t1 (c1) VALUES (NULL);
446 SELECT * FROM t1 ORDER BY c1;
447 c1
448 COMMIT;
449 SELECT * FROM t1 ORDER BY c1;
450 c1
451 disconnect con1;
452 connection default;
453 INSERT INTO t1 (c1) VALUES (NULL);
454 SELECT * FROM t1 ORDER BY c1;
455 c1
456 COMMIT;
457 SELECT * FROM t1 ORDER BY c1;
458 c1
459 DROP TABLE t1;
460 # Test with another column after
461 CREATE TABLE t1 (
462 c1 INT NOT NULL AUTO_INCREMENT,
463 c2 INT,
464 PRIMARY KEY (c1,c2))
465 ENGINE = 'Blackhole'
466 PARTITION BY HASH(c2)
467 PARTITIONS 2;
468 INSERT INTO t1 VALUES (1, 0);
469 INSERT INTO t1 VALUES (1, 1);
470 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
471 INSERT INTO t1 VALUES (NULL, 3);
472 INSERT INTO t1 VALUES (2, 0), (NULL, 2);
473 INSERT INTO t1 VALUES (2, 2);
474 INSERT INTO t1 VALUES (2, 22);
475 INSERT INTO t1 VALUES (NULL, 2);
476 SELECT * FROM t1 ORDER BY c1,c2;
477 c1 c2
478 DROP TABLE t1;
479 # Test with another column before
480 CREATE TABLE t1 (
481 c1 INT,
482 c2 INT NOT NULL AUTO_INCREMENT,
483 PRIMARY KEY (c2))
484 ENGINE = 'Blackhole'
485 PARTITION BY HASH(c2)
486 PARTITIONS 2;
487 INSERT INTO t1 VALUES (1, 0);
488 INSERT INTO t1 VALUES (1, 1);
489 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
490 # mysql_errno: 0
491 INSERT INTO t1 VALUES (1, NULL);
492 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
493 INSERT INTO t1 VALUES (2, NULL);
494 INSERT INTO t1 VALUES (2, 2);
495 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
496 # mysql_errno: 0
497 INSERT INTO t1 VALUES (2, 22);
498 INSERT INTO t1 VALUES (2, NULL);
499 SELECT * FROM t1 ORDER BY c1,c2;
500 c1 c2
501 DROP TABLE t1;
502 # Test with auto_increment on secondary column in multi-column-index
503 CREATE TABLE t1 (
504 c1 INT,
505 c2 INT NOT NULL AUTO_INCREMENT,
506 PRIMARY KEY (c1,c2))
507 ENGINE = 'Blackhole'
508 PARTITION BY HASH(c2)
509 PARTITIONS 2;
510 INSERT INTO t1 VALUES (1, 0);
511 INSERT INTO t1 VALUES (1, 1);
512 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
513 # mysql_errno: 0
514 INSERT INTO t1 VALUES (1, NULL);
515 INSERT INTO t1 VALUES (2, NULL);
516 INSERT INTO t1 VALUES (3, NULL);
517 INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
518 INSERT INTO t1 VALUES (2, 2);
519 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
520 # mysql_errno: 0
521 INSERT INTO t1 VALUES (2, 22), (2, NULL);
522 SELECT * FROM t1 ORDER BY c1,c2;
523 c1 c2
524 DROP TABLE t1;
525 # Test AUTO_INCREMENT in CREATE
526 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
527 ENGINE = 'Blackhole'
528 AUTO_INCREMENT = 15
529 PARTITION BY HASH(c1)
530 PARTITIONS 2;
531 SHOW CREATE TABLE t1;
532 Table Create Table
533 t1 CREATE TABLE `t1` (
534 `c1` int(11) NOT NULL AUTO_INCREMENT,
535 PRIMARY KEY (`c1`)
536 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
537 PARTITION BY HASH (`c1`)
538 PARTITIONS 2
539 INSERT INTO t1 (c1) VALUES (4);
540 SHOW CREATE TABLE t1;
541 Table Create Table
542 t1 CREATE TABLE `t1` (
543 `c1` int(11) NOT NULL AUTO_INCREMENT,
544 PRIMARY KEY (`c1`)
545 ) ENGINE=BLACKHOLE AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
546 PARTITION BY HASH (`c1`)
547 PARTITIONS 2
548 INSERT INTO t1 (c1) VALUES (0);
549 SHOW CREATE TABLE t1;
550 Table Create Table
551 t1 CREATE TABLE `t1` (
552 `c1` int(11) NOT NULL AUTO_INCREMENT,
553 PRIMARY KEY (`c1`)
554 ) ENGINE=BLACKHOLE AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
555 PARTITION BY HASH (`c1`)
556 PARTITIONS 2
557 INSERT INTO t1 (c1) VALUES (NULL);
558 SHOW CREATE TABLE t1;
559 Table Create Table
560 t1 CREATE TABLE `t1` (
561 `c1` int(11) NOT NULL AUTO_INCREMENT,
562 PRIMARY KEY (`c1`)
563 ) ENGINE=BLACKHOLE AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
564 PARTITION BY HASH (`c1`)
565 PARTITIONS 2
566 SELECT * FROM t1 ORDER BY c1;
567 c1
568 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
569 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
570 INSERT INTO t1 (c1) VALUES (300);
571 SHOW CREATE TABLE t1;
572 Table Create Table
573 t1 CREATE TABLE `t1` (
574 `c1` int(11) NOT NULL AUTO_INCREMENT,
575 PRIMARY KEY (`c1`)
576 ) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
577 PARTITION BY HASH (`c1`)
578 PARTITIONS 2
579 INSERT INTO t1 (c1) VALUES (0);
580 SHOW CREATE TABLE t1;
581 Table Create Table
582 t1 CREATE TABLE `t1` (
583 `c1` int(11) NOT NULL AUTO_INCREMENT,
584 PRIMARY KEY (`c1`)
585 ) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
586 PARTITION BY HASH (`c1`)
587 PARTITIONS 2
588 INSERT INTO t1 (c1) VALUES (NULL);
589 SHOW CREATE TABLE t1;
590 Table Create Table
591 t1 CREATE TABLE `t1` (
592 `c1` int(11) NOT NULL AUTO_INCREMENT,
593 PRIMARY KEY (`c1`)
594 ) ENGINE=BLACKHOLE AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
595 PARTITION BY HASH (`c1`)
596 PARTITIONS 2
597 SELECT * FROM t1 ORDER BY c1;
598 c1
599 SET @@session.sql_mode = '';
600 DROP TABLE t1;
601 # Test SET INSERT_ID
602 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
603 ENGINE = 'Blackhole'
604 PARTITION BY HASH(c1)
605 PARTITIONS 2;
606 SHOW CREATE TABLE t1;
607 Table Create Table
608 t1 CREATE TABLE `t1` (
609 `c1` int(11) NOT NULL AUTO_INCREMENT,
610 PRIMARY KEY (`c1`)
611 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
612 PARTITION BY HASH (`c1`)
613 PARTITIONS 2
614 INSERT INTO t1 (c1) VALUES (NULL);
615 SHOW CREATE TABLE t1;
616 Table Create Table
617 t1 CREATE TABLE `t1` (
618 `c1` int(11) NOT NULL AUTO_INCREMENT,
619 PRIMARY KEY (`c1`)
620 ) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
621 PARTITION BY HASH (`c1`)
622 PARTITIONS 2
623 SELECT * FROM t1;
624 c1
625 SET INSERT_ID = 23;
626 SHOW CREATE TABLE t1;
627 Table Create Table
628 t1 CREATE TABLE `t1` (
629 `c1` int(11) NOT NULL AUTO_INCREMENT,
630 PRIMARY KEY (`c1`)
631 ) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
632 PARTITION BY HASH (`c1`)
633 PARTITIONS 2
634 INSERT INTO t1 (c1) VALUES (NULL);
635 SHOW CREATE TABLE t1;
636 Table Create Table
637 t1 CREATE TABLE `t1` (
638 `c1` int(11) NOT NULL AUTO_INCREMENT,
639 PRIMARY KEY (`c1`)
640 ) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
641 PARTITION BY HASH (`c1`)
642 PARTITIONS 2
643 SET INSERT_ID = 22;
644 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
645 # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
646 # mysql_errno: 0
647 INSERT INTO t1 VALUES (NULL);
648 SELECT * FROM t1 ORDER BY c1;
649 c1
650 DROP TABLE t1;
651 # Testing with FLUSH TABLE
652 CREATE TABLE t1 (
653 c1 INT NOT NULL AUTO_INCREMENT,
654 PRIMARY KEY (c1))
655 ENGINE='Blackhole'
656 PARTITION BY HASH(c1)
657 PARTITIONS 2;
658 SHOW CREATE TABLE t1;
659 Table Create Table
660 t1 CREATE TABLE `t1` (
661 `c1` int(11) NOT NULL AUTO_INCREMENT,
662 PRIMARY KEY (`c1`)
663 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
664 PARTITION BY HASH (`c1`)
665 PARTITIONS 2
666 FLUSH TABLE;
667 SHOW CREATE TABLE t1;
668 Table Create Table
669 t1 CREATE TABLE `t1` (
670 `c1` int(11) NOT NULL AUTO_INCREMENT,
671 PRIMARY KEY (`c1`)
672 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
673 PARTITION BY HASH (`c1`)
674 PARTITIONS 2
675 INSERT INTO t1 VALUES (4);
676 FLUSH TABLE;
677 SHOW CREATE TABLE t1;
678 Table Create Table
679 t1 CREATE TABLE `t1` (
680 `c1` int(11) NOT NULL AUTO_INCREMENT,
681 PRIMARY KEY (`c1`)
682 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
683 PARTITION BY HASH (`c1`)
684 PARTITIONS 2
685 INSERT INTO t1 VALUES (NULL);
686 FLUSH TABLE;
687 SHOW CREATE TABLE t1;
688 Table Create Table
689 t1 CREATE TABLE `t1` (
690 `c1` int(11) NOT NULL AUTO_INCREMENT,
691 PRIMARY KEY (`c1`)
692 ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
693 PARTITION BY HASH (`c1`)
694 PARTITIONS 2
695 SELECT * FROM t1 ORDER BY c1;
696 c1
697 DROP TABLE t1;
698 #
699 # MDEV-19622 Assertion failures in
700 # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
701 #
702 CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE='Blackhole' PARTITION BY HASH(a);
703 INSERT INTO t1 VALUES (1,1),(2,2);
704 UPDATE t1 SET pk = 0;
705 DROP TABLE t1;
706 #
707 # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
708 # failed in ha_partition::set_auto_increment_if_higher upon REPLACE
709 # with partition pruning
710 #
711 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Blackhole'
712 PARTITION BY RANGE (a) (
713 PARTITION p0 VALUES LESS THAN (10),
714 PARTITION pn VALUES LESS THAN MAXVALUE
715 );
716 REPLACE INTO t1 PARTITION (p0) SELECT 1;
717 DROP TABLE t1;
718 #
719 # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
720 # ha_partition::set_auto_increment_if_higher
721 #
722 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Blackhole' PARTITION BY HASH (a) PARTITIONS 3;
723 REPLACE INTO t1 PARTITION (p0) VALUES (3);
724 DROP TABLE t1;
725 #
726 # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
727 # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
728 #
729 CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE='Blackhole' PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
730 ALTER TABLE t1 TRUNCATE PARTITION p1;
731 INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
732 DROP TABLE t1;