| 1 |
--source include/have_innodb.inc |
| 2 |
--source include/have_sequence.inc |
| 3 |
--source include/test_db_charset_latin1.inc |
| 4 |
|
| 5 |
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent; |
| 6 |
SET GLOBAL innodb_stats_persistent = 0; |
| 7 |
|
| 8 |
CREATE TABLE t1 ( |
| 9 |
a SERIAL, b CHAR(255) NOT NULL DEFAULT '', c BOOLEAN DEFAULT false, |
| 10 |
l LINESTRING NOT NULL DEFAULT ST_linefromtext('linestring(448 -689, |
| 11 |
453 -684,451 -679,453 -677,458 -681,463 -681,468 -678,470 -676,470 -678, |
| 12 |
468 -675,472 -675,472 -675,474 -674,479 -676,477 -675,473 -676,475 1324, |
| 13 |
479 1319,484 1322,483 1323,486 1323,491 1328,492 1325,496 1325,498 1325, |
| 14 |
501 1330,498 1331,500 1331,504 1330,508 1329,512 1332,513 1337,518 1339, |
| 15 |
518 1339,513 1344,513 1344,512 1346,514 1351,515 1353,519 1358,518 1362, |
| 16 |
522 1365,525 1360,526 1362,527 1362,528 1367,525 1371,528 1366,532 1369, |
| 17 |
536 1374,539 1377,543 1379,539 1381,541 1382,543 1383,546 1388,549 1393, |
| 18 |
554 1393,554 1395,554 1392,550 1394,550 1392,546 1394,549 1397,550 1393, |
| 19 |
549 1394,554 1390,554 1391,549 1396,551 1396,547 1400,547 1402,551 1407, |
| 20 |
554 1412,554 1415,558 1418,463 -681,465 -677,465 -675,470 -670,470 -665, |
| 21 |
470 -660,470 -659,473 -656,476 -656,481 -655,482 -652,486 -654,486 -652, |
| 22 |
486 -648,491 -646,490 -651,494 -646,493 -644,493 -644,490 -644,491 2356, |
| 23 |
495 2359,495 2364,500 2359,503 5359,504 5364,509 5368,504 5367,499 5368, |
| 24 |
498 5371,498 5369,500 5370,504 5370,508 5370,511 5370,507 5374,508 5378, |
| 25 |
511 5382,507 5387,509 5389,512 5388,515 5393,520 5396,517 5397,517 5402, |
| 26 |
515 5404,520 5402,521 5405,525 5405,526 5408,530 7408,535 7413,533 7415, |
| 27 |
529 7412,532 7416,4532 7416,4534 7421,4533 7417,4536 7413,4536 7418, |
| 28 |
4540 3418,4545 3418,4549 3415,4551 3419,4554 3421,4559 3423,4559 3426, |
| 29 |
4557 3424,4561 3428,4558 3428,4563 3431,4565 3435,4569 3439,4569 3439, |
| 30 |
4569 3444,4567 3444,4572 3446,4577 3447,4581 3444,4581 3448,4584 3448, |
| 31 |
4579 3447,4580 3450,4583 3449,4583 3453,4587 3455,4588 3458,4593 3463, |
| 32 |
4598 3465,4601 3468,4598 3464,4598 3460,4593 5460,4595 5461,4600 5464, |
| 33 |
4600 5465,4601 5466,4606 5466,4608 5466,4605 5464,4608 5467,4607 5468, |
| 34 |
4609 5465,4614 5461,4618 5463,4621 5467,4623 5470,4622 5470,4622 5470, |
| 35 |
4625 6470,4627 6471,4627 6472,4627 6473,6627 6474,6625 6474,6628 6477, |
| 36 |
6633 6481,6633 6480,6637 6475,7637 6479,7638 6482,7643 6487,7644 6492, |
| 37 |
7647 6492,7648 6495,7646 6498,7650 6499,7646 6494,7644 6499,7644 6497, |
| 38 |
7644 6499,7647 6502,7649 6504,7650 6501,7647 6503,7649 6504,7650 6508, |
| 39 |
7651 6503,7652 6508,7655 6508,7650 6511,7655 6515,7658 6513,7663 6513, |
| 40 |
7665 6514,7669 6512,7667 6510,7664 6510,472 -675,477 -670,479 -666, |
| 41 |
482 -663,484 -668,484 -666,485 -664,481 -664,479 -659,482 -659,484 -658, |
| 42 |
483 -659,488 2341,493 2339,489 2338,491 2342,491 2346,494 2346,490 2348, |
| 43 |
493 2348,498 2349,498 2350,499 2349,502 2350,503 2348,506 2348,506 2348, |
| 44 |
507 2353,507 2355,504 2359,504 2364,504 2361,499 2365,502 2360,502 2358, |
| 45 |
503 2357,504 2353,504 2357,500 2356,497 2355,498 2355,500 2359,502 2361, |
| 46 |
505 2364,508 2364,506 2368,506 2370,504 2373,499 2373,496 2372,493 2377, |
| 47 |
497 2380,495 2383,496 7383,493 7386,497 7391,494 7387,495 7389,498 7392, |
| 48 |
498 7392,495 7395,493 7398,498 7401,498 7403,503 7400,498 8400,501 8401, |
| 49 |
503 8401,503 8401,501 10401,496 10396,491 10401,492 10399,493 10403, |
| 50 |
496 10403,491 10403,493 10407,489 10410,493 10407,489 10403,498 7403, |
| 51 |
497 7399,496 7403,500 7405,500 7407,503 7411,508 7415,511 7415,511 7420, |
| 52 |
515 7420,520 7423,523 7423,520 7427,523 7427,523 7427,522 7432,525 4432, |
| 53 |
527 4434,530 4437,534 4441,529 4446,529 4441,534 4436,537 4436,535 4437, |
| 54 |
532 4437,534 4432,535 4429,538 4430,542 4427,542 4431,538 4431,541 4431, |
| 55 |
541 4433,543 4433,545 4432,549 4428,552 4426,556 4427,557 4423,560 4427, |
| 56 |
561 4428,558 4430,559 4434,559 4432,561 4434,561 4437,563 4435,559 4430, |
| 57 |
561 4435,4561 4437,4566 4441,4568 4446,4568 4450,4569 4455,4565 4458, |
| 58 |
4561 4463,4561 9463,4564 9463,4565 9461,9565 9463,9560 9467,9560 9466, |
| 59 |
9555 9469,9555 9471,9559 9469,9557 9473,9553 9478,9555 9480,9557 9481, |
| 60 |
9557 9481,9557 9483,9562 9487,9558 9487,9558 9490,9561 9493,9562 9493, |
| 61 |
9557 9493,9560 9496,9555 9501,9553 9503,9553 9506,9557 9510,9558 9511, |
| 62 |
9561 9514,9563 9512,9568 9514,9567 9514,9567 13514,9570 13517,9566 13521, |
| 63 |
9571 13521,9571 13526,9573 13521,9571 13521,9576 10521,9580 10526,9582 10525, |
| 64 |
9584 10528,9584 10531,9584 10533,9589 10533,9588 10537,9588 10541,9589 10542, |
| 65 |
9593 10544,9595 10540,9597 10541,9600 10545,9601 15545,9603 15549,9605 15553, |
| 66 |
9601 15558,9601 15553,9605 15551,9605 15550,9605 15554,9607 15556,9605 15556, |
| 67 |
9604 15561,9607 15559,9603 15559,9603 15562,9604 15563,9608 15566,9612 15570, |
| 68 |
9617 15565,9622 15568,9627 15566,9628 15564,9629 15564,9633 15569,9636 15569, |
| 69 |
9634 15571,9634 15572,9636 15574,9634 15570,9629 15570,9631 15567,9629 15570, |
| 70 |
9626 15574,9626 15575,498 7401,502 7401,506 7397,506 7395,502 7398,497 7401, |
| 71 |
502 7402,505 7397,508 7400,504 7404,3504 7409,3505 7405,3508 7410,3511 7413, |
| 72 |
3511 7416,3511 7419,3511 7419,3513 7421,3517 7424,3519 7426,3520 11426, |
| 73 |
3523 11421,3527 11418,3530 11415,3530 11416,3533 11418,7533 11415,7531 11415, |
| 74 |
7531 11417,7536 11420,7541 11424,7543 11425,7543 11427,7543 11429,7540 11429, |
| 75 |
7542 11425,7541 11420,7542 11421,7542 11422,7540 11424,7540 11423,7543 11422, |
| 76 |
7546 11426,7550 11431,7553 11436,7555 16436,7553 16438,7558 16438,7559 16438, |
| 77 |
7560 16439,7565 16437,7560 16435,7563 16435,7566 16440,7566 16444,7564 16447, |
| 78 |
7559 16443,7561 16443,7566 16448,7570 16451,7574 16456,7578 16459, |
| 79 |
12578 16459,12578 20459,12577 20456,12581 20454,12585 20456,12585 20456, |
| 80 |
12585 20456,12583 20456,12579 20459,12580 20461,12580 20462,12580 20460, |
| 81 |
12585 20465,12586 20467,12590 20470,12590 20470,12589 20471,12584 20471, |
| 82 |
12589 20471,9589 20472,9594 20472,9595 20472,9596 20477,9598 20482, |
| 83 |
9603 20480,9608 20484,9613 20484,9610 20486,9608 20488,9608 20489,9610 20489, |
| 84 |
9614 20486,9619 20481,9620 20481,9618 21481,9621 21483,9626 21483,9628 21485, |
| 85 |
9623 21487,9622 21490,9626 21493,9621 21495,9626 21498,9622 21499,9624 21504, |
| 86 |
9625 21499,9629 21501,9633 21498,9637 21495,9639 21498,9644 21501,9557 9481, |
| 87 |
9560 9485,9561 9490,9563 9488,9560 9486,9558 9488,9561 9492,9563 9495, |
| 88 |
9567 9492,9567 9488,9564 9490,9559 9495,9559 9498,9557 9502,9562 9506, |
| 89 |
9564 9509,9569 9512,9569 9516,9569 9518,9569 9515,9571 9513,9571 9512, |
| 90 |
9573 9513,9578 9516,9581 9516,9585 11516,9585 11521,9590 10521,9586 10524, |
| 91 |
9589 10529,9589 10527,9589 10527,9594 10532,9594 10534,9598 10536,9598 10540, |
| 92 |
9600 10542,9604 10538,9607 10538,9609 10543,9613 10538,9613 10533,9613 10537, |
| 93 |
9610 10537,9614 10542,9609 10542,9610 10543,9610 10548,9611 10553,9616 7553, |
| 94 |
9620 7553,9621 7557,9618 7559,9618 7554,9622 7557,9622 7561,9622 7556, |
| 95 |
9622 7560,9619 7560,9620 7565,9622 7563,9627 7566,9630 7570,9630 7571, |
| 96 |
9632 7573,9637 7576,9639 7578,9640 7576,9640 7579,9640 7575,9642 7570, |
| 97 |
9646 7570,9651 7574,9653 7577,9652 7572,9653 7576,9653 7576,9651 7581, |
| 98 |
9656 7585,9660 7586,9659 7591,9657 7594,9661 7598,9664 7602,9668 12602, |
| 99 |
9673 12604,9676 12606,9679 12602,9682 12605,9677 12610,9674 12606,9674 12601, |
| 100 |
9674 12603,9672 9603,9668 9605,9671 9606,9668 9611,9668 9606,9671 9611, |
| 101 |
9675 9615,9677 9620,9678 9622,9679 9624,9684 9626,9685 9627,9685 9622, |
| 102 |
9685 9626,9689 9628,9694 9633,9699 9637,9699 9637,9704 9636,9708 9637, |
| 103 |
9709 9638,9707 9639,9705 9642,9707 9647,9710 9649,9711 9653,9716 9649, |
| 104 |
9716 9648,9720 9650,9721 9648,9723 9648,9726 4648,12726 4653,12731 4655, |
| 105 |
12734 4660,12730 4661,12733 4664,12733 4665,12735 4670,12737 4674,12741 4674, |
| 106 |
12738 4675,12740 4675,12737 4675,12742 4678,12743 4681,12746 4677)'), |
| 107 |
INDEX(b,c), SPATIAL INDEX `sidx`(l) |
| 108 |
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; |
| 109 |
INSERT INTO t1 () VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); |
| 110 |
SELECT LENGTH(l) FROM t1; |
| 111 |
INSERT INTO t1 (a) SELECT NULL FROM t1; |
| 112 |
INSERT INTO t1 (a) SELECT NULL FROM t1; |
| 113 |
CHECK TABLE t1; |
| 114 |
UPDATE t1 SET c=true, l=ST_linefromtext('linestring(0 0,1 1,2 2)'); |
| 115 |
DELETE FROM t1; |
| 116 |
CHECK TABLE t1; |
| 117 |
|
| 118 |
source include/wait_all_purged.inc; |
| 119 |
|
| 120 |
ANALYZE TABLE t1; |
| 121 |
SELECT OTHER_INDEX_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS |
| 122 |
WHERE NAME='test/t1'; |
| 123 |
|
| 124 |
# Work around MDEV-13942, Dropping the spatial index to avoid the possible hang |
| 125 |
ALTER TABLE t1 DROP INDEX `sidx`; |
| 126 |
|
| 127 |
INSERT INTO t1 (a) SELECT * FROM seq_1_to_544; |
| 128 |
ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; |
| 129 |
ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; |
| 130 |
|
| 131 |
SELECT (variable_value > 0) FROM information_schema.global_status |
| 132 |
WHERE LOWER(variable_name) LIKE 'INNODB_BUFFER_POOL_PAGES_FLUSHED'; |
| 133 |
|
| 134 |
--echo # Note: The OTHER_INDEX_SIZE does not cover any SPATIAL INDEX. |
| 135 |
--echo # To test that all indexes were emptied, replace DROP TABLE |
| 136 |
--echo # with the following, and examine the root pages in t1.ibd: |
| 137 |
--echo # FLUSH TABLES t1 FOR EXPORT; |
| 138 |
--echo # UNLOCK TABLES; |
| 139 |
|
| 140 |
DROP TABLE t1; |
| 141 |
|
| 142 |
--echo # |
| 143 |
--echo # MDEV-29666 InnoDB fails to purge secondary index records |
| 144 |
--echo # when indexed virtual columns exist |
| 145 |
--echo # |
| 146 |
|
| 147 |
CREATE TABLE t1 (a INT, b INT, a1 INT AS(a) VIRTUAL, |
| 148 |
INDEX(a1),INDEX(b)) ENGINE=InnoDB; |
| 149 |
INSERT INTO t1 SET a=1, b=1; |
| 150 |
|
| 151 |
UPDATE t1 SET a=2, b=3; |
| 152 |
let DATADIR=`select @@datadir`; |
| 153 |
let PAGE_SIZE=`select @@innodb_page_size`; |
| 154 |
|
| 155 |
source include/wait_all_purged.inc; |
| 156 |
FLUSH TABLE t1 FOR EXPORT; |
| 157 |
|
| 158 |
perl; |
| 159 |
my $ps = $ENV{PAGE_SIZE}; |
| 160 |
my $file = "$ENV{DATADIR}/test/t1.ibd"; |
| 161 |
open(FILE, "<", $file) or die "Unable to open $file\n"; |
| 162 |
die "Unable to read $file\n" unless |
| 163 |
sysread(FILE, $_, 6*$ps) == 6*$ps; |
| 164 |
close(FILE); |
| 165 |
print "page 4: N_RECS=0x", unpack("H*", substr($_, 4 * $ps + 54, 2)), "\n"; |
| 166 |
print "page 5: N_RECS=0x", unpack("H*", substr($_, 5 * $ps + 54, 2)), "\n"; |
| 167 |
EOF |
| 168 |
|
| 169 |
UNLOCK TABLES; |
| 170 |
DROP TABLE t1; |
| 171 |
|
| 172 |
--echo # End of 10.3 tests |
| 173 |
|
| 174 |
SET GLOBAL innodb_stats_persistent = @save_stats_persistent; |
| 175 |
|
| 176 |
--echo # End of 10.6 tests |
| 177 |
|
| 178 |
--source include/test_db_charset_restore.inc |