Debian Codegraph @

mysql-test/suite/innodb/t/autoinc_persist.test

raw
1 --source include/have_innodb.inc
2 # Restarting is not supported when testing the embedded server.
3 --source include/not_embedded.inc
4 --source include/no_valgrind_without_big.inc
5 --source include/test_db_charset_latin1.inc
6
7 if ($MTR_COMBINATION_DESC) {
8 let $AUTO_INCREMENT_KEY_a= AUTO_INCREMENT, PRIMARY KEY(a DESC);
9 let $AUTO_INCREMENT_KEY_b= AUTO_INCREMENT, INDEX(b DESC);
10 let $AUTO_INCREMENT_PRIMARY_KEY_a= AUTO_INCREMENT, PRIMARY KEY(a DESC);
11 let $AUTO_INCREMENT_PRIMARY_KEY_b= AUTO_INCREMENT, ADD PRIMARY KEY(b DESC);
12 }
13 if (!$MTR_COMBINATION_DESC) {
14 let $AUTO_INCREMENT_KEY_a= AUTO_INCREMENT KEY;
15 let $AUTO_INCREMENT_KEY_b= AUTO_INCREMENT, KEY(b);
16 let $AUTO_INCREMENT_PRIMARY_KEY_a= AUTO_INCREMENT PRIMARY KEY;
17 let $AUTO_INCREMENT_PRIMARY_KEY_b= AUTO_INCREMENT PRIMARY KEY;
18 }
19
20 --echo #
21 --echo # MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
22 --echo #
23 --echo # WL#6204 InnoDB persistent max value for autoinc columns
24 --echo #
25 --echo # Most of this test case is copied from the test innodb.autoinc_persist
26 --echo # that was introduced in MySQL 8.0.0. The observable behaviour
27 --echo # of MDEV-6076 is equivalent to WL#6204, with the exception that
28 --echo # there is less buffering taking place and redo log checkpoints
29 --echo # are not being treated specially.
30 --echo # Due to less buffering, there is no debug instrumentation testing
31 --echo # for MDEV-6076.
32 --echo #
33
34 --echo # Pre-create several tables
35
36 SET SQL_MODE='STRICT_ALL_TABLES';
37
38 eval CREATE TABLE t1(a TINYINT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
39 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
40 (20), (30), (31);
41 SELECT * FROM t1;
42
43 eval CREATE TABLE t2(a TINYINT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
44 --error ER_WARN_DATA_OUT_OF_RANGE
45 INSERT INTO t2 VALUES(-5);
46 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
47 (20), (30), (31);
48 SELECT * FROM t2;
49
50 eval CREATE TABLE t3(a SMALLINT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
51 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
52 (20), (30), (31), (1024), (4096);
53 SELECT * FROM t3;
54
55 eval CREATE TABLE t4(a SMALLINT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
56 --error ER_WARN_DATA_OUT_OF_RANGE
57 INSERT INTO t4 VALUES(-5);
58 INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
59 (20), (30), (31), (1024), (4096);
60 SELECT * FROM t4;
61
62 eval CREATE TABLE t5(a MEDIUMINT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
63 INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
64 (20), (30), (31), (1000000), (1000005);
65 SELECT * FROM t5;
66
67 eval CREATE TABLE t6(a MEDIUMINT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
68 --error ER_WARN_DATA_OUT_OF_RANGE
69 INSERT INTO t6 VALUES(-5);
70 INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
71 (20), (30), (31), (1000000), (1000005);
72 SELECT * FROM t6;
73
74 eval CREATE TABLE t7(a INT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
75 INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
76 (20), (30), (31), (100000000), (100000008);
77 SELECT * FROM t7;
78
79 eval CREATE TABLE t8(a INT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
80 --error ER_WARN_DATA_OUT_OF_RANGE
81 INSERT INTO t8 VALUES(-5);
82 INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
83 (20), (30), (31), (100000000), (100000008);
84 SELECT * FROM t8;
85
86 eval CREATE TABLE t9(a BIGINT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
87 INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
88 (20), (30), (31), (100000000000), (100000000006);
89 SELECT * FROM t9;
90
91 eval CREATE TABLE t10(a BIGINT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
92 --error ER_WARN_DATA_OUT_OF_RANGE
93 INSERT INTO t10 VALUES(-5);
94 INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
95 (20), (30), (31), (100000000000), (100000000006);
96 SELECT * FROM t10;
97
98 eval CREATE TABLE t11(a FLOAT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
99 INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
100 SELECT * FROM t11;
101
102 eval CREATE TABLE t11u(a FLOAT UNSIGNED $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
103 --error ER_WARN_DATA_OUT_OF_RANGE
104 INSERT INTO t11u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
105 INSERT INTO t11u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
106 SELECT * FROM t11u;
107
108 eval CREATE TABLE t12(a DOUBLE $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB;
109 INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
110 SELECT * FROM t12;
111
112 CREATE TABLE t12u(a DOUBLE UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
113 --error ER_WARN_DATA_OUT_OF_RANGE
114 INSERT INTO t12u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
115 INSERT INTO t12u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
116 SELECT * FROM t12u;
117
118 --echo # Scenario 1: Normal restart, to test if the counters are persisted
119 --echo # Scenario 2: Delete some values, to test the counters should not be the
120 --echo # one which is the largest in current table
121
122 DELETE FROM t1 WHERE a > 30;
123 SELECT MAX(a) AS `Expect 30` FROM t1;
124 DELETE FROM t3 WHERE a > 2000;
125 SELECT MAX(a) AS `Expect 2000` FROM t3;
126 DELETE FROM t5 WHERE a > 1000000;
127 SELECT MAX(a) AS `Expect 1000000` FROM t5;
128 DELETE FROM t7 WHERE a > 100000000;
129 SELECT MAX(a) AS `Expect 100000000` FROM t7;
130 DELETE FROM t9 WHERE a > 100000000000;
131 SELECT MAX(a) AS `Expect 100000000000` FROM t9;
132
133 eval CREATE TABLE t13(a INT $AUTO_INCREMENT_KEY_a) ENGINE = InnoDB,
134 AUTO_INCREMENT = 1234;
135
136 --source include/restart_mysqld.inc
137
138 SHOW CREATE TABLE t13;
139 INSERT INTO t13 VALUES(0);
140 SELECT a AS `Expect 1234` FROM t13;
141 DROP TABLE t13;
142
143 INSERT INTO t1 VALUES(0), (0);
144 SELECT MAX(a) AS `Expect 33` FROM t1;
145 INSERT INTO t3 VALUES(0), (0);
146 SELECT MAX(a) AS `Expect 4098` FROM t3;
147 INSERT INTO t5 VALUES(0), (0);
148 SELECT MAX(a) AS `Expect 1000007` FROM t5;
149 INSERT INTO t7 VALUES(0), (0);
150 SELECT MAX(a) AS `Expect 100000010` FROM t7;
151 INSERT INTO t9 VALUES(0), (0);
152 SELECT MAX(a) AS `Expect 100000000008` FROM t9;
153
154 --echo # Scenario 3: Insert some bigger counters, the next counter should start
155 --echo # from there
156
157 INSERT INTO t1 VALUES(40), (0);
158 INSERT INTO t1 VALUES(42), (0);
159 SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
160 INSERT INTO t3 VALUES(5000), (0);
161 INSERT INTO t3 VALUES(5010), (0);
162 SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
163 INSERT INTO t5 VALUES(1000010), (0);
164 INSERT INTO t5 VALUES(1000020), (0);
165 SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
166 INSERT INTO t7 VALUES(100000020), (0);
167 INSERT INTO t7 VALUES(100000030), (0);
168 SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
169 INSERT INTO t9 VALUES(100000000010), (0);
170 INSERT INTO t9 VALUES(100000000020), (0);
171 SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
172
173 --echo # Scenario 4: Update some values, to test the counters should be updated
174 --echo # to the bigger value, but not smaller value.
175
176 INSERT INTO t1 VALUES(50), (55);
177 # Updating to bigger value will update the auto-increment counter
178 UPDATE t1 SET a = 105 WHERE a = 5;
179 # Updating to smaller value will not update the counter
180 UPDATE t1 SET a = 100 WHERE a = 55;
181 --echo # This should insert 102, 106, 107, and make next counter 109.
182 INSERT INTO t1 VALUES(102), (0), (0);
183 SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
184 DELETE FROM t1 WHERE a > 105;
185 INSERT INTO t1 VALUES(0);
186 SELECT MAX(a) AS `Expect 109` FROM t1;
187
188 --echo # Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
189 --echo # be updated accordingly
190
191 INSERT INTO t3 VALUES(60), (65);
192 # Updating to bigger value will update the auto-increment counter
193 UPDATE t3 SET a = 6005 WHERE a = 5;
194 # Updating to smaller value will not update the counter
195 UPDATE t3 SET a = 6000 WHERE a = 60;
196 --echo # This should insert 6002, 6006, 6007, and make next counter 6009.
197 INSERT INTO t3 VALUES(6002), (0), (0);
198 SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
199 DELETE FROM t3 WHERE a > 6005;
200 INSERT INTO t3 VALUES(0);
201 SELECT MAX(a) AS `Expect 6009` FROM t3;
202
203 INSERT INTO t5 VALUES(100), (200);
204 # Updating to bigger value will update the auto-increment counter
205 UPDATE t5 SET a = 1000105 WHERE a = 5;
206 # Updating to smaller value will not update the counter
207 UPDATE t5 SET a = 1000100 WHERE a = 100;
208 --echo # This should insert 1000102, 1000106, 1000107, and make next counter
209 --echo # 1000109.
210 INSERT INTO t5 VALUES(1000102), (0), (0);
211 SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
212 DELETE FROM t5 WHERE a > 1000105;
213 INSERT INTO t5 VALUES(0);
214 SELECT MAX(a) AS `Expect 1000109` FROM t5;
215
216 INSERT INTO t7 VALUES(100), (200);
217 # Updating to bigger value will update the auto-increment counter
218 UPDATE t7 SET a = 100000105 WHERE a = 5;
219 # Updating to smaller value will not update the counter
220 UPDATE t7 SET a = 100000100 WHERE a = 100;
221 --echo # This should insert 100000102, 1100000106, 100000107, and make next
222 --echo # counter 100000109.
223 INSERT INTO t7 VALUES(100000102), (0), (0);
224 SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
225 DELETE FROM t7 WHERE a > 100000105;
226 INSERT INTO t7 VALUES(0);
227 SELECT MAX(a) AS `Expect 100000109` FROM t7;
228
229 INSERT INTO t9 VALUES(100), (200);
230 # Updating to bigger value will update the auto-increment counter
231 UPDATE t9 SET a = 100000000105 WHERE a = 5;
232 # Updating to smaller value will not update the counter
233 UPDATE t9 SET a = 100000000100 WHERE a = 100;
234 --echo # This should insert 100000000102, 100000000106, 100000000107, and make
235 --echo # next counter 100000000109.
236 INSERT INTO t9 VALUES(100000000102), (0), (0);
237 SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
238 DELETE FROM t9 WHERE a > 100000000105;
239 INSERT INTO t9 VALUES(0);
240 SELECT MAX(a) AS `Expect 100000000109` FROM t9;
241
242 --source include/restart_mysqld.inc
243
244 INSERT INTO t1 VALUES(0), (0);
245 SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
246
247 INSERT INTO t3 VALUES(0), (0);
248 SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
249
250 INSERT INTO t5 VALUES(0), (0);
251 SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
252
253 INSERT INTO t7 VALUES(0), (0);
254 SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
255
256 INSERT INTO t9 VALUES(0), (0);
257 SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
258
259 --echo # Scenario 5: Test kill the server
260
261 INSERT INTO t1 VALUES(125);
262 DELETE FROM t1 WHERE a = 125;
263
264 INSERT INTO t3 VALUES(6100);
265 DELETE FROM t3 WHERE a = 6100;
266
267 INSERT INTO t5 VALUES(1100200);
268 DELETE FROM t5 WHERE a = 1100200;
269
270 INSERT INTO t7 VALUES(100000200);
271 DELETE FROM t7 WHERE a = 100000200;
272
273 --echo # Ensure that all changes before the server is killed are persisted.
274 set global innodb_flush_log_at_trx_commit=1;
275
276 INSERT INTO t9 VALUES(100000000200);
277 DELETE FROM t9 WHERE a = 100000000200;
278
279 --let $shutdown_timeout=0
280 --source include/restart_mysqld.inc
281
282 INSERT INTO t1 VALUES(0);
283 SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
284
285 INSERT INTO t3 VALUES(0);
286 SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
287
288 INSERT INTO t5 VALUES(0);
289 SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
290
291 INSERT INTO t7 VALUES(0);
292 SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
293
294 INSERT INTO t9 VALUES(0);
295 SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
296
297 --echo # Scenario 6: Test truncate will reset the counters to 0
298
299 TRUNCATE TABLE t1;
300 TRUNCATE TABLE t3;
301 TRUNCATE TABLE t5;
302 TRUNCATE TABLE t7;
303 TRUNCATE TABLE t9;
304
305 INSERT INTO t1 VALUES(0), (0);
306 SELECT * FROM t1;
307
308 INSERT INTO t3 VALUES(0), (0);
309 SELECT * FROM t3;
310
311 INSERT INTO t5 VALUES(0), (0);
312 SELECT * FROM t5;
313
314 INSERT INTO t7 VALUES(0), (0);
315 SELECT * FROM t7;
316
317 INSERT INTO t9 VALUES(0), (0);
318 SELECT * FROM t9;
319
320 --echo # Ensure that all changes before the server is killed are persisted.
321 set global innodb_flush_log_at_trx_commit=1;
322
323 TRUNCATE TABLE t1;
324 TRUNCATE TABLE t3;
325 TRUNCATE TABLE t5;
326 TRUNCATE TABLE t7;
327 TRUNCATE TABLE t9;
328
329 --echo # Scenario 7: Test explicit rename table won't change the counter
330
331 RENAME TABLE t9 to t19;
332 INSERT INTO t19 VALUES(0), (0);
333 SELECT * FROM t19;
334
335 --source include/restart_mysqld.inc
336
337 INSERT INTO t1 VALUES(0), (0);
338 SELECT * FROM t1;
339
340 INSERT INTO t3 VALUES(0), (0);
341 SELECT * FROM t3;
342
343 INSERT INTO t5 VALUES(0), (0);
344 SELECT * FROM t5;
345
346 INSERT INTO t7 VALUES(0), (0);
347 SELECT * FROM t7;
348
349 INSERT INTO t19 VALUES(0), (0);
350 SELECT * FROM t19;
351 DELETE FROM t19 WHERE a = 4;
352
353 RENAME TABLE t19 to t9;
354 INSERT INTO t9 VALUES(0), (0);
355 SELECT * FROM t9;
356
357 TRUNCATE TABLE t9;
358
359 INSERT INTO t9 VALUES(0), (0);
360 SELECT * FROM t9;
361
362 --echo # Scenario 8: Test ALTER TABLE operations
363
364 INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
365 SELECT * FROM t3;
366 DELETE FROM t3 WHERE a > 300;
367 SELECT MAX(a) AS `Expect 200` FROM t3;
368 --echo # This will not change the counter to 150, but to 201, which is the next
369 --echo # of current max counter in the table
370 ALTER TABLE t3 AUTO_INCREMENT = 150;
371 SHOW CREATE TABLE t3;
372 INSERT INTO t3 VALUES(0);
373 SELECT MAX(a) AS `Expect 201` FROM t3;
374 --echo # This will change the counter to 500, which is bigger than any counter
375 --echo # in the table
376 ALTER TABLE t3 AUTO_INCREMENT = 500;
377 SHOW CREATE TABLE t3;
378 INSERT INTO t3 VALUES(0);
379 SELECT MAX(a) AS `Expect 500` FROM t3;
380
381 TRUNCATE TABLE t3;
382 ALTER TABLE t3 AUTO_INCREMENT = 100;
383 SHOW CREATE TABLE t3;
384 INSERT INTO t3 VALUES(0), (0);
385 SELECT * FROM t3;
386
387 INSERT INTO t3 VALUES(150), (180);
388 UPDATE t3 SET a = 200 WHERE a = 150;
389 INSERT INTO t3 VALUES(220);
390 --echo # This still fails to set to 120, but just 221
391 ALTER TABLE t3 AUTO_INCREMENT = 120;
392 SHOW CREATE TABLE t3;
393 INSERT INTO t3 VALUES(0);
394 SELECT MAX(a) AS `Expect 221` FROM t3;
395
396 DELETE FROM t3 WHERE a > 120;
397
398 ALTER TABLE t3 AUTO_INCREMENT = 120;
399 SHOW CREATE TABLE t3;
400
401 --echo # MDEV-6076: Test adding an AUTO_INCREMENT COLUMN
402 CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB;
403 INSERT INTO mdev6076a VALUES(2),(1);
404 CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB;
405 INSERT INTO mdev6076b VALUES(2),(1);
406 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
407 ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE;
408 ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE;
409 ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100,
410 ALGORITHM=INPLACE;
411 --echo # MDEV-6076: Test root page split and page_create_empty()
412 CREATE TABLE mdev6076empty (b SERIAL, pad CHAR(255) NOT NULL DEFAULT '')
413 ENGINE=InnoDB;
414 BEGIN;
415 --echo # Insert records in descending order of AUTO_INCREMENT,
416 --echo # causing a page split on the very last insert.
417 --echo # Without the fix in btr_page_empty() this would lose the counter value.
418 --echo # Without the fix in page_create_empty() the counter value would be lost
419 --echo # when ROLLBACK deletes the last row.
420 --disable_query_log
421 let $i= 55;
422 while ($i) {
423 eval INSERT INTO mdev6076empty SET b=$i;
424 dec $i;
425 }
426 --enable_query_log
427 ROLLBACK;
428
429 --source include/restart_mysqld.inc
430
431 INSERT INTO t3 VALUES(0);
432 SELECT MAX(a) AS `Expect 120` FROM t3;
433
434 INSERT INTO mdev6076a SET b=NULL;
435 SELECT * FROM mdev6076a;
436 INSERT INTO mdev6076b SET b=NULL;
437 SELECT * FROM mdev6076b;
438 # MDEV-515 resets the PAGE_ROOT_AUTOINC field in
439 # root page during rollback.
440 --disable_query_log
441 BEGIN;
442 let $i = 55;
443 WHILE ($i) {
444 eval INSERT INTO mdev6076empty SET b=$i;
445 dec $i;
446 }
447 ROLLBACK;
448 --enable_query_log
449 INSERT INTO mdev6076empty SET b=NULL;
450 SELECT * FROM mdev6076empty;
451 DROP TABLE mdev6076a, mdev6076b, mdev6076empty;
452
453 INSERT INTO t3 VALUES(0), (0), (200), (210);
454
455 --echo # Test the different algorithms in ALTER TABLE
456
457 --let $template = t3
458 --let $algorithm = INPLACE
459 --let $table = t_inplace
460 --source suite/innodb/include/autoinc_persist_alter.inc
461 --let $algorithm = COPY
462 --let $table = t_copy
463 --source suite/innodb/include/autoinc_persist_alter.inc
464
465 --echo # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
466
467 eval CREATE TABLE t30 (a INT NOT NULL $AUTO_INCREMENT_PRIMARY_KEY_a, b INT, key(b)) ENGINE = InnoDB;
468
469 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
470
471 INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
472 INSERT INTO t30(b) VALUES(4), (5), (6), (7);
473 SELECT * FROM t30 ORDER BY b;
474 ALTER TABLE t30 MODIFY b MEDIUMINT;
475 SELECT * FROM t30 ORDER BY b;
476
477 --echo # Ensure that all changes before the server is killed are persisted.
478 set global innodb_flush_log_at_trx_commit=1;
479
480 CREATE TABLE t31 (a INT) ENGINE = InnoDB;
481 INSERT INTO t31 VALUES(1), (2);
482 eval ALTER TABLE t31 ADD b INT $AUTO_INCREMENT_PRIMARY_KEY_b;
483 INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
484 --error ER_DUP_ENTRY
485 INSERT INTO t31 VALUES(6, 0);
486 SELECT * FROM t31;
487
488 SET SQL_MODE = 0;
489
490 --echo # Scenario 10: Rollback would not rollback the counter
491 eval CREATE TABLE t32 (a BIGINT $AUTO_INCREMENT_PRIMARY_KEY_a) ENGINE=InnoDB;
492
493 INSERT INTO t32 VALUES(0), (0);
494
495 --echo # Ensure that all changes before the server is killed are persisted.
496 set global innodb_flush_log_at_trx_commit=1;
497
498 START TRANSACTION;
499 INSERT INTO t32 VALUES(0), (0);
500 SELECT MAX(a) AS `Expect 4` FROM t32;
501 DELETE FROM t32 WHERE a >= 2;
502 ROLLBACK;
503
504 --echo # Scenario 11: Test duplicate primary key/secondary key will not stop
505 --echo # increasing the counter
506
507 eval CREATE TABLE t33 (
508 a BIGINT NOT NULL PRIMARY KEY,
509 b BIGINT NOT NULL $AUTO_INCREMENT_KEY_b) ENGINE = InnoDB;
510
511 INSERT INTO t33 VALUES(1, NULL);
512 INSERT INTO t33 VALUES(2, NULL);
513 --error ER_DUP_ENTRY
514 INSERT INTO t33 VALUES(2, NULL);
515
516 INSERT INTO t33 VALUES(3, NULL);
517 SELECT MAX(b) AS `Expect 4` FROM t33;
518
519 TRUNCATE TABLE t33;
520
521 INSERT INTO t33 VALUES(1, NULL);
522 INSERT INTO t33 VALUES(2, NULL);
523
524 set global innodb_flush_log_at_trx_commit=1;
525
526 START TRANSACTION;
527 UPDATE t33 SET a = 10 WHERE a = 1;
528 --error ER_DUP_ENTRY
529 INSERT INTO t33 VALUES(2, NULL);
530 COMMIT;
531
532 --source include/restart_mysqld.inc
533
534 --echo # This will not insert 0
535 INSERT INTO t31(a) VALUES(6), (0);
536 SELECT * FROM t31;
537 DROP TABLE t31;
538
539 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
540
541 DELETE FROM t30 WHERE a = 0;
542 UPDATE t30 set a = 0 where b = 5;
543 SELECT * FROM t30 ORDER BY b;
544 DELETE FROM t30 WHERE a = 0;
545
546 UPDATE t30 SET a = NULL WHERE b = 6;
547 UPDATE t30 SET a = 300 WHERE b = 7;
548
549 SELECT * FROM t30 ORDER BY b;
550
551 SET SQL_MODE = 0;
552
553 SELECT MAX(a) AS `Expect 2` FROM t32;
554 INSERT INTO t32 VALUES(0), (0);
555 SELECT MAX(a) AS `Expect 6` FROM t32;
556
557 FLUSH TABLES t33 FOR EXPORT;
558 let MYSQLD_DATADIR=`select @@datadir`;
559 perl;
560 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
561 ib_backup_tablespaces("test", "t33");
562 EOF
563 UNLOCK TABLES;
564 DROP TABLE t33;
565 eval CREATE TABLE t33 (
566 a BIGINT NOT NULL PRIMARY KEY,
567 b BIGINT NOT NULL $AUTO_INCREMENT_KEY_b) ENGINE = InnoDB;
568 ALTER TABLE t33 DISCARD TABLESPACE;
569 perl;
570 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
571 ib_discard_tablespaces("test", "t33");
572 ib_restore_tablespaces("test", "t33");
573 EOF
574 ALTER TABLE t33 IMPORT TABLESPACE;
575
576 INSERT INTO t33 VALUES(3, NULL);
577 SELECT MAX(b) AS `Expect 4` FROM t33;
578 SELECT * FROM t33;
579
580 DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t11u, t12u,
581 t30, t32, t33;
582 --source include/test_db_charset_restore.inc