diff options
20 files changed, 236 insertions, 246 deletions
diff --git a/mysql-test/suite/encryption/r/innodb-discard-import-change.result b/mysql-test/suite/encryption/r/innodb-discard-import-change.result index d13c730c7e7..cafdbef4b69 100644 --- a/mysql-test/suite/encryption/r/innodb-discard-import-change.result +++ b/mysql-test/suite/encryption/r/innodb-discard-import-change.result @@ -100,5 +100,5 @@ NOT FOUND /verysecretmessage/ in t3.ibd # t4 page compressed and encrypted expecting NOT FOUND NOT FOUND /verysecretmessage/ in t4.ibd # t5 normal expecting FOUND -FOUND 256 /verysecretmessage/ in t5.ibd +FOUND 289 /verysecretmessage/ in t5.ibd DROP TABLE t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/suite/innodb/disabled.def b/mysql-test/suite/innodb/disabled.def index 3c2d32765d8..35c941f8af7 100644 --- a/mysql-test/suite/innodb/disabled.def +++ b/mysql-test/suite/innodb/disabled.def @@ -11,4 +11,3 @@ ############################################################################## create-index-debug : MDEV-13680 InnoDB may crash when btr_page_alloc() fails -innodb_wl6326 : MDEV-21535 Too broad ha_innobase::records_in_range() diff --git a/mysql-test/suite/innodb/r/index_merge_threshold.result b/mysql-test/suite/innodb/r/index_merge_threshold.result index b8a6d81aecc..35cb82e6f1e 100644 --- a/mysql-test/suite/innodb/r/index_merge_threshold.result +++ b/mysql-test/suite/innodb/r/index_merge_threshold.result @@ -204,10 +204,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -221,8 +220,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -231,32 +230,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 2 -6 4 +5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -287,10 +285,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -304,8 +301,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -314,32 +311,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 2 -6 4 +5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=25) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -370,10 +366,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -387,8 +382,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -397,32 +392,31 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 1 -6 4 +5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # test to confirm partitioned table (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) @@ -458,10 +452,9 @@ where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 0 -3 3 +3 2 4 7 -5 3 -6 4 +5 7 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -475,8 +468,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -486,32 +479,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 0 3 2 4 4 -5 2 -6 4 +5 4 delete from tab1 where a = 11; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 10; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 9; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # # behavior for updating to smaller records @@ -544,10 +536,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -558,8 +549,8 @@ update tab1 set b='' where a = 7; SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -568,29 +559,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 7 +5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -621,10 +611,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -635,8 +624,8 @@ update tab1 set b='' where a = 7; SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -645,29 +634,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 7 +5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=25) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -698,10 +686,9 @@ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS -3 3 +3 2 4 7 -5 3 -6 4 +5 7 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -712,8 +699,8 @@ update tab1 set b='' where a = 7; SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -722,29 +709,28 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 3 -6 7 +5 7 update tab1 set b='' where a = 11; # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # test to confirm explicit temporary table (MERGE_THRESHOLD=35) # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly) @@ -785,8 +771,8 @@ update tab1 set b='' where a = 7; SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -798,22 +784,22 @@ update tab1 set b='' where a = 11; SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 update tab1 set b='' where a = 10; # check page merge happens (MERGE_THRESHOLD=35 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 update tab1 set b='' where a = 9; # check page merge happens (MERGE_THRESHOLD=25 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # # behavior for secondary index with blob @@ -877,10 +863,9 @@ where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 -4 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -908,8 +893,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -919,32 +904,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 11 +28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; @@ -1005,10 +989,9 @@ where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 -4 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1036,8 +1019,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1047,32 +1030,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 11 +28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=40) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; @@ -1133,10 +1115,9 @@ where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 -4 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1164,8 +1145,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1175,32 +1156,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 8 -30 11 +28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 DROP TABLE tab1; # compressed table behaves same (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB @@ -1262,10 +1242,9 @@ where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 42 -4 3 +4 2 27 21 -28 10 -30 11 +28 21 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1293,8 +1272,8 @@ InnoDB 0 transactions not purged SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1304,31 +1283,30 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 9 -30 11 +28 11 delete from tab1 where a = 32; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=50 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 1 -index_page_merge_successful 1 +index_page_merge_attempts 0 +index_page_merge_successful 0 delete from tab1 where a = 31; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=45 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 2 -index_page_merge_successful 2 +index_page_merge_attempts 1 +index_page_merge_successful 1 delete from tab1 where a = 30; InnoDB 0 transactions not purged # check page merge happens (MERGE_THRESHOLD=40 causes merge here) SELECT name,count_reset FROM information_schema.innodb_metrics WHERE name like 'index_page_merge_%'; name count_reset -index_page_merge_attempts 3 -index_page_merge_successful 3 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/r/index_tree_operation.result b/mysql-test/suite/innodb/r/index_tree_operation.result index 18890d99750..29660962e0c 100644 --- a/mysql-test/suite/innodb/r/index_tree_operation.result +++ b/mysql-test/suite/innodb/r/index_tree_operation.result @@ -16,10 +16,9 @@ information_schema.innodb_buffer_page s2 WHERE s1.space = s2.space AND name = 'test/t1' AND page_type = "INDEX" ORDER BY page_number; page_number number_records -3 3 +3 2 4 3 -5 1 -6 2 +5 3 INSERT INTO t1 VALUES (999, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -29,8 +28,8 @@ AND page_type = "INDEX" ORDER BY page_number; page_number number_records 3 3 4 3 -5 1 -6 3 +5 3 +6 1 INSERT INTO t1 VALUES (998, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -38,11 +37,10 @@ information_schema.innodb_buffer_page s2 WHERE s1.space = s2.space AND name = 'test/t1' AND page_type = "INDEX" ORDER BY page_number; page_number number_records -3 4 +3 3 4 3 -5 1 -6 3 -7 1 +5 3 +6 2 INSERT INTO t1 VALUES (997, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -50,9 +48,8 @@ information_schema.innodb_buffer_page s2 WHERE s1.space = s2.space AND name = 'test/t1' AND page_type = "INDEX" ORDER BY page_number; page_number number_records -3 4 +3 3 4 3 -5 1 +5 3 6 3 -7 2 DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result index f8bf417969e..d795b516d5e 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -13,7 +13,6 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; -SET GLOBAL innodb_limit_optimistic_insert_debug=700; INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; BEGIN; SELECT b FROM t1 LIMIT 3; @@ -51,5 +50,5 @@ Table Op Msg_type Msg_text test.t1 check status OK SHOW ENGINE INNODB STATUS; Type Name Status -InnoDB insert 139, delete mark 1 +InnoDB insert 79, delete mark 1 DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-page_compression_default.result b/mysql-test/suite/innodb/r/innodb-page_compression_default.result index 23aa0a28c90..4610d251fc0 100644 --- a/mysql-test/suite/innodb/r/innodb-page_compression_default.result +++ b/mysql-test/suite/innodb/r/innodb-page_compression_default.result @@ -36,7 +36,7 @@ select count(*) from innodb_page_compressed9; count(*) 10000 # innodb_normal expected FOUND -FOUND 24000 /AaAaAaAa/ in innodb_normal.ibd +FOUND 24084 /AaAaAaAa/ in innodb_normal.ibd # innodb_page_compressed1 page compressed expected NOT FOUND NOT FOUND /AaAaAaAa/ in innodb_page_compressed1.ibd # innodb_page_compressed2 page compressed expected NOT FOUND diff --git a/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result b/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result index ea3b18011df..2f675bcf1b4 100644 --- a/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result +++ b/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result @@ -37,7 +37,7 @@ select count(*) from innodb_page_compressed9; count(*) 10000 # innodb_normal expected FOUND -FOUND 24000 /AaAaAaAa/ in innodb_normal.ibd +FOUND 24084 /AaAaAaAa/ in innodb_normal.ibd # innodb_page_compressed1 page compressed expected NOT FOUND NOT FOUND /AaAaAaAa/ in innodb_page_compressed1.ibd # innodb_page_compressed2 page compressed expected NOT FOUND diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_load_now.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_load_now.result index 30812b1d198..6ecf6035329 100644 --- a/mysql-test/suite/innodb/r/innodb_buffer_pool_load_now.result +++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_load_now.result @@ -14,7 +14,7 @@ SELECT NULL, REPEAT('b', 64), REPEAT('c', 256) FROM seq_1_to_16382; SELECT COUNT(*) FROM information_schema.innodb_buffer_page_lru WHERE table_name = '`test`.`ib_bp_test`'; COUNT(*) -594 +596 SET GLOBAL innodb_buffer_pool_dump_now = ON; SET GLOBAL innodb_fast_shutdown=0; # restart @@ -32,7 +32,7 @@ Buffer pool(s) load completed at TIMESTAMP_NOW SELECT COUNT(*) FROM information_schema.innodb_buffer_page_lru WHERE table_name = '`test`.`ib_bp_test`'; COUNT(*) -594 +596 call mtr.add_suppression("InnoDB: Error parsing"); SET GLOBAL innodb_buffer_pool_load_now = ON; # Re-write some valid pages to the dump file, make sure the space diff --git a/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result b/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result index fcd73caf978..8453050a92a 100644 --- a/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result +++ b/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result @@ -115,5 +115,4 @@ insert into t2 values (201, REPEAT('A', 16), REPEAT('B', 32)); insert into t2 values (202, REPEAT('A', 16), REPEAT('B', 32)); insert into t2 values (203, REPEAT('A', 16), REPEAT('B', 32)); insert into t2 values (204, REPEAT('A', 16), REPEAT('B', 32)); -Too little space is reserved on second index. DROP TABLE t2; diff --git a/mysql-test/suite/innodb/r/innodb_wl6326.result b/mysql-test/suite/innodb/r/innodb_wl6326.result index 407a81715f6..fcd58aedafe 100644 --- a/mysql-test/suite/innodb/r/innodb_wl6326.result +++ b/mysql-test/suite/innodb/r/innodb_wl6326.result @@ -30,6 +30,7 @@ SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME CLUST_INDEX_SIZE 1 SET GLOBAL innodb_limit_optimistic_insert_debug = 7; +BEGIN; INSERT INTO t1 (a00) VALUES ('aa'); INSERT INTO t1 (a00) VALUES ('ab'); INSERT INTO t1 (a00) VALUES ('ac'); @@ -38,6 +39,7 @@ INSERT INTO t1 (a00) VALUES ('ae'); INSERT INTO t1 (a00) VALUES ('af'); INSERT INTO t1 (a00) VALUES ('ag'); INSERT INTO t1 (a00) VALUES ('ah'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -45,16 +47,19 @@ test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE 3 +BEGIN; INSERT INTO t1 (a00) VALUES ('ai'); INSERT INTO t1 (a00) VALUES ('aj'); INSERT INTO t1 (a00) VALUES ('ak'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -3 +4 +BEGIN; INSERT INTO t1 (a00) VALUES ('al'); INSERT INTO t1 (a00) VALUES ('am'); INSERT INTO t1 (a00) VALUES ('an'); @@ -62,13 +67,15 @@ INSERT INTO t1 (a00) VALUES ('ao'); INSERT INTO t1 (a00) VALUES ('ap'); INSERT INTO t1 (a00) VALUES ('aq'); INSERT INTO t1 (a00) VALUES ('ar'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -4 +5 +BEGIN; INSERT INTO t1 (a00) VALUES ('as'); INSERT INTO t1 (a00) VALUES ('at'); INSERT INTO t1 (a00) VALUES ('au'); @@ -76,13 +83,15 @@ INSERT INTO t1 (a00) VALUES ('av'); INSERT INTO t1 (a00) VALUES ('aw'); INSERT INTO t1 (a00) VALUES ('ax'); INSERT INTO t1 (a00) VALUES ('ay'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -5 +6 +BEGIN; INSERT INTO t1 (a00) VALUES ('az'); INSERT INTO t1 (a00) VALUES ('ba'); INSERT INTO t1 (a00) VALUES ('bb'); @@ -90,13 +99,15 @@ INSERT INTO t1 (a00) VALUES ('bc'); INSERT INTO t1 (a00) VALUES ('bd'); INSERT INTO t1 (a00) VALUES ('be'); INSERT INTO t1 (a00) VALUES ('bf'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -6 +7 +BEGIN; INSERT INTO t1 (a00) VALUES ('bg'); INSERT INTO t1 (a00) VALUES ('bh'); INSERT INTO t1 (a00) VALUES ('bi'); @@ -104,13 +115,15 @@ INSERT INTO t1 (a00) VALUES ('bj'); INSERT INTO t1 (a00) VALUES ('bk'); INSERT INTO t1 (a00) VALUES ('bl'); INSERT INTO t1 (a00) VALUES ('bm'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -7 +8 +BEGIN; INSERT INTO t1 (a00) VALUES ('bn'); INSERT INTO t1 (a00) VALUES ('bo'); INSERT INTO t1 (a00) VALUES ('bp'); @@ -118,13 +131,15 @@ INSERT INTO t1 (a00) VALUES ('bq'); INSERT INTO t1 (a00) VALUES ('br'); INSERT INTO t1 (a00) VALUES ('bs'); INSERT INTO t1 (a00) VALUES ('bt'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -8 +11 +BEGIN; INSERT INTO t1 (a00) VALUES ('bu'); INSERT INTO t1 (a00) VALUES ('bv'); INSERT INTO t1 (a00) VALUES ('bw'); @@ -146,13 +161,15 @@ INSERT INTO t1 (a00) VALUES ('cl'); INSERT INTO t1 (a00) VALUES ('cm'); INSERT INTO t1 (a00) VALUES ('cn'); INSERT INTO t1 (a00) VALUES ('co'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -13 +15 +BEGIN; INSERT INTO t1 (a00) VALUES ('cp'); INSERT INTO t1 (a00) VALUES ('cq'); INSERT INTO t1 (a00) VALUES ('cr'); @@ -202,13 +219,15 @@ INSERT INTO t1 (a00) VALUES ('ei'); INSERT INTO t1 (a00) VALUES ('ej'); INSERT INTO t1 (a00) VALUES ('ek'); INSERT INTO t1 (a00) VALUES ('el'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; CLUST_INDEX_SIZE -21 +23 +BEGIN; INSERT INTO t1 (a00) VALUES ('em'); INSERT INTO t1 (a00) VALUES ('en'); INSERT INTO t1 (a00) VALUES ('eo'); @@ -257,6 +276,7 @@ INSERT INTO t1 (a00) VALUES ('ge'); INSERT INTO t1 (a00) VALUES ('gf'); INSERT INTO t1 (a00) VALUES ('gg'); INSERT INTO t1 (a00) VALUES ('gh'); +COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected diff --git a/mysql-test/suite/innodb/r/instant_alter,32k.rdiff b/mysql-test/suite/innodb/r/instant_alter,32k.rdiff index 14a19856be5..37c3c479e68 100644 --- a/mysql-test/suite/innodb/r/instant_alter,32k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,32k.rdiff @@ -44,7 +44,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+4 ++5 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -71,7 +71,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+4 ++5 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -98,7 +98,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+4 ++5 connection default; ROLLBACK; CHECKSUM TABLE big; diff --git a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff index f78615ea3a6..71394d4b43b 100644 --- a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff @@ -117,7 +117,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+27 ++28 connection default; ROLLBACK; CHECKSUM TABLE big; diff --git a/mysql-test/suite/innodb/r/instant_alter,8k.rdiff b/mysql-test/suite/innodb/r/instant_alter,8k.rdiff index 8f4294b56d2..b96262866e5 100644 --- a/mysql-test/suite/innodb/r/instant_alter,8k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,8k.rdiff @@ -41,7 +41,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', @@ -50,7 +50,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+12 ++13 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -59,7 +59,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,big; @@ -104,7 +104,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', @@ -113,7 +113,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+12 ++13 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -122,7 +122,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,big; @@ -167,7 +167,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; ALTER TABLE big ADD COLUMN (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde', @@ -176,7 +176,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+12 ++13 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -185,7 +185,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+4 ++5 connection default; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,big; diff --git a/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result b/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result index 6e9cf71925a..37ddb0a9348 100644 --- a/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result +++ b/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result @@ -4,23 +4,17 @@ INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), (4, 'mariadb'), (5, 'test1'), (6, 'test2'), (7, 'test3'), (8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), (12, 'test8'); -FLUSH TABLE t1 FOR EXPORT; -UNLOCK TABLES; -SET GLOBAL innodb_log_checkpoint_now=ON; SELECT COUNT(*) FROM t1; COUNT(*) 12 UPDATE t1 SET c='best8' WHERE pk=12; # Kill the server # Corrupt the pages -# restart SELECT * FROM t1 WHERE PK = 1; ERROR 42000: Unknown storage engine 'InnoDB' -# restart: --innodb-force-recovery=1 SELECT * FROM t1 WHERE PK = 1; pk c 1 sql SELECT * FROM t1 WHERE pk = 12; -ERROR HY000: Got error 168 "Unknown (generic) error from engine" from storage engine InnoDB +ERROR HY000: Index for table 't1' is corrupt; try to repair it DROP TABLE t1; -# restart diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test index 9c5acedb620..79d9cc814a0 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -35,16 +35,9 @@ ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; -# The removed function page_copy_rec_list_end_to_created_page() would create -# less dense pages than the remaining part of page_copy_rec_list_end(). -# For the INSERT after the DELETE to be buffered, the leftmost page -# must not be too densely packed. -SET GLOBAL innodb_limit_optimistic_insert_debug=700; - # Create enough rows for the table, so that the change buffer will be # used for modifying the secondary index page. There must be multiple # index pages, because changes to the root page are never buffered. - INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; BEGIN; diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.test b/mysql-test/suite/innodb/t/innodb_wl6326.test index 71eba94be04..1cf98cd1c7b 100644 --- a/mysql-test/suite/innodb/t/innodb_wl6326.test +++ b/mysql-test/suite/innodb/t/innodb_wl6326.test @@ -59,6 +59,7 @@ SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME # (above t1 definition is already adjusted) SET GLOBAL innodb_limit_optimistic_insert_debug = 7; +BEGIN; INSERT INTO t1 (a00) VALUES ('aa'); INSERT INTO t1 (a00) VALUES ('ab'); INSERT INTO t1 (a00) VALUES ('ac'); @@ -67,21 +68,25 @@ INSERT INTO t1 (a00) VALUES ('ae'); INSERT INTO t1 (a00) VALUES ('af'); INSERT INTO t1 (a00) VALUES ('ag'); INSERT INTO t1 (a00) VALUES ('ah'); +COMMIT; # Raise root (1-2) # (aa,ad) # (aa,ab,ac)(ad,ae,af,ag,ah) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('ai'); INSERT INTO t1 (a00) VALUES ('aj'); INSERT INTO t1 (a00) VALUES ('ak'); +COMMIT; # Split leaf (1-3) # (aa,ad,ak) # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('al'); INSERT INTO t1 (a00) VALUES ('am'); INSERT INTO t1 (a00) VALUES ('an'); @@ -89,12 +94,14 @@ INSERT INTO t1 (a00) VALUES ('ao'); INSERT INTO t1 (a00) VALUES ('ap'); INSERT INTO t1 (a00) VALUES ('aq'); INSERT INTO t1 (a00) VALUES ('ar'); +COMMIT; # Split leaf (1-4) # (aa,ad,ak,ar) # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('as'); INSERT INTO t1 (a00) VALUES ('at'); INSERT INTO t1 (a00) VALUES ('au'); @@ -102,12 +109,14 @@ INSERT INTO t1 (a00) VALUES ('av'); INSERT INTO t1 (a00) VALUES ('aw'); INSERT INTO t1 (a00) VALUES ('ax'); INSERT INTO t1 (a00) VALUES ('ay'); +COMMIT; # Split leaf (1-5) # (aa,ad,ak,ar,ay) # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar,as,at,au,av,aw,ax)(ay) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('az'); INSERT INTO t1 (a00) VALUES ('ba'); INSERT INTO t1 (a00) VALUES ('bb'); @@ -115,13 +124,14 @@ INSERT INTO t1 (a00) VALUES ('bc'); INSERT INTO t1 (a00) VALUES ('bd'); INSERT INTO t1 (a00) VALUES ('be'); INSERT INTO t1 (a00) VALUES ('bf'); +COMMIT; # Split leaf (1-6) # (aa,ad,ak,ar,ay,bf) # (aa,ab,ac)(ad..)(ak..)(ar,as,at,au,av,aw,ax)(ay,az,ba,bb,bc,bd,be)(bf) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - +BEGIN; INSERT INTO t1 (a00) VALUES ('bg'); INSERT INTO t1 (a00) VALUES ('bh'); INSERT INTO t1 (a00) VALUES ('bi'); @@ -129,12 +139,14 @@ INSERT INTO t1 (a00) VALUES ('bj'); INSERT INTO t1 (a00) VALUES ('bk'); INSERT INTO t1 (a00) VALUES ('bl'); INSERT INTO t1 (a00) VALUES ('bm'); +COMMIT; # Split leaf (1-7) # (aa,ad,ak,ar,ay,bf,bm) # (aa,ab,ac)(ad..)(ak..)(ar..)(ay,az,ba,bb,bc,bd,be)(bf,bg,bh,bi,bj,bk,bl)(bm) ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('bn'); INSERT INTO t1 (a00) VALUES ('bo'); INSERT INTO t1 (a00) VALUES ('bp'); @@ -142,6 +154,7 @@ INSERT INTO t1 (a00) VALUES ('bq'); INSERT INTO t1 (a00) VALUES ('br'); INSERT INTO t1 (a00) VALUES ('bs'); INSERT INTO t1 (a00) VALUES ('bt'); +COMMIT; # Raise root (1-2-8) # (aa,ar) # (aa,ad,ak) (ar,ay,bf,bm,bt) @@ -149,7 +162,7 @@ INSERT INTO t1 (a00) VALUES ('bt'); ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - +BEGIN; INSERT INTO t1 (a00) VALUES ('bu'); INSERT INTO t1 (a00) VALUES ('bv'); INSERT INTO t1 (a00) VALUES ('bw'); @@ -173,6 +186,7 @@ INSERT INTO t1 (a00) VALUES ('cl'); INSERT INTO t1 (a00) VALUES ('cm'); INSERT INTO t1 (a00) VALUES ('cn'); INSERT INTO t1 (a00) VALUES ('co'); +COMMIT; # Split also at level 1 (1-3-11) # (aa,ar,co) # (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co) @@ -180,7 +194,7 @@ INSERT INTO t1 (a00) VALUES ('co'); ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - +BEGIN; INSERT INTO t1 (a00) VALUES ('cp'); INSERT INTO t1 (a00) VALUES ('cq'); INSERT INTO t1 (a00) VALUES ('cr'); @@ -236,6 +250,7 @@ INSERT INTO t1 (a00) VALUES ('ei'); INSERT INTO t1 (a00) VALUES ('ej'); INSERT INTO t1 (a00) VALUES ('ek'); INSERT INTO t1 (a00) VALUES ('el'); +COMMIT; # Split also at level 1 (1-4-18) # (aa,ar,co,el) # (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el) @@ -243,6 +258,7 @@ INSERT INTO t1 (a00) VALUES ('el'); ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +BEGIN; INSERT INTO t1 (a00) VALUES ('em'); INSERT INTO t1 (a00) VALUES ('en'); INSERT INTO t1 (a00) VALUES ('eo'); @@ -297,7 +313,7 @@ INSERT INTO t1 (a00) VALUES ('ge'); INSERT INTO t1 (a00) VALUES ('gf'); INSERT INTO t1 (a00) VALUES ('gg'); INSERT INTO t1 (a00) VALUES ('gh'); - +COMMIT; # Current tree form (1-4-24) # (aa,ar,co,el) @@ -306,12 +322,9 @@ INSERT INTO t1 (a00) VALUES ('gh'); ANALYZE TABLE t1; SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - # Insert the rest of records normally SET GLOBAL innodb_limit_optimistic_insert_debug = 0; - --echo # Test start # (1) Insert records to leaf page (bf..) and cause modify_page. diff --git a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test index 48137a7e920..7ffb9bb6596 100644 --- a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test +++ b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test @@ -3,8 +3,8 @@ --disable_query_log call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed file read of tablespace test/t1 page "); -call mtr.add_suppression("InnoDB: Background Page read failed to read or decrypt \\[page id: space=\\d+, page number=14\\]"); -call mtr.add_suppression("\\[ERROR\\] InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 14: Page read from tablespace is corrupted\\."); +call mtr.add_suppression("InnoDB: Background Page read failed to read or decrypt \\[page id: space=\\d+, page number=19\\]"); +call mtr.add_suppression("\\[ERROR\\] InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 19: Page read from tablespace is corrupted\\."); call mtr.add_suppression("\\[ERROR\\] InnoDB: Plugin initialization aborted at srv0start\\.cc.* with error Data structure corruption"); call mtr.add_suppression("\\[ERROR\\] Plugin 'InnoDB' (init function|registration)"); call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption"); @@ -20,12 +20,8 @@ INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), (8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), (12, 'test8'); -# Flush all pages of the table, and perform log checkpoint, -# so that no page initialization can be replayed from the redo log -# to undo the page corruption that we are going to inject. -FLUSH TABLE t1 FOR EXPORT; -UNLOCK TABLES; -SET GLOBAL innodb_log_checkpoint_now=ON; +let $restart_noprint=2; +--source include/restart_mysqld.inc let INNODB_PAGE_SIZE=`select @@innodb_page_size`; let MYSQLD_DATADIR=`select @@datadir`; @@ -42,7 +38,7 @@ perl; my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; open(FILE, "+<$file") || die "Unable to open $file"; binmode FILE; -seek (FILE, $ENV{INNODB_PAGE_SIZE} * 14 + 38, SEEK_SET) or die "seek"; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 19 + 38, SEEK_SET) or die "seek"; print FILE "junk"; close FILE or die "close"; EOF @@ -54,7 +50,7 @@ SELECT * FROM t1 WHERE PK = 1; let $restart_parameters=--innodb-force-recovery=1; --source include/restart_mysqld.inc SELECT * FROM t1 WHERE PK = 1; ---error ER_GET_ERRNO +--error ER_NOT_KEYFILE SELECT * FROM t1 WHERE pk = 12; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result index d7dc9a745f7..3b98527250b 100644 --- a/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result +++ b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result @@ -962,7 +962,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 159744 +The size of the tab5.ibd file: 163840 # fetch the compressed page and check the stats =============== Fetch Records @@ -986,7 +986,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 159744 +The size of the tab5.ibd file: 163840 # fetch the compressed same page once again and check the stats # the stat figures should be same as above query =============== @@ -1011,7 +1011,7 @@ AND compress_ops BETWEEN @inl_val AND 1000 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 159744 +The size of the tab5.ibd file: 163840 DROP TABLE tab5; #****************************************************************** # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K diff --git a/storage/innobase/btr/btr0bulk.cc b/storage/innobase/btr/btr0bulk.cc index 4fdae95c173..d199f1230dc 100644 --- a/storage/innobase/btr/btr0bulk.cc +++ b/storage/innobase/btr/btr0bulk.cc @@ -466,28 +466,20 @@ inline void PageBulk::finishPage() } ut_ad(!m_index->is_spatial()); - ut_ad(!page_get_instant(m_page)); - ut_ad(!mach_read_from_2(PAGE_HEADER + PAGE_N_DIRECTION + m_page)); if (fmt != COMPRESSED) { static_assert(PAGE_N_DIR_SLOTS == 0, "compatibility"); - alignas(8) byte page_header[PAGE_N_RECS + 2]; + alignas(8) byte page_header[PAGE_N_HEAP + 2]; mach_write_to_2(page_header + PAGE_N_DIR_SLOTS, 1 + (slot0 - slot) / PAGE_DIR_SLOT_SIZE); mach_write_to_2(page_header + PAGE_HEAP_TOP, m_heap_top - m_page); mach_write_to_2(page_header + PAGE_N_HEAP, (PAGE_HEAP_NO_USER_LOW + m_rec_no) | uint16_t{fmt != REDUNDANT} << 15); - memset_aligned<2>(page_header + PAGE_FREE, 0, 4); - static_assert(PAGE_GARBAGE == PAGE_FREE + 2, "compatibility"); - mach_write_to_2(page_header + PAGE_LAST_INSERT, m_cur_rec - m_page); - mach_write_to_2(page_header + PAGE_DIRECTION_B - 1, PAGE_RIGHT); - mach_write_to_2(page_header + PAGE_N_DIRECTION, m_rec_no); - memcpy_aligned<2>(page_header + PAGE_N_RECS, - page_header + PAGE_N_DIRECTION, 2); m_mtr.memcpy(*m_block, PAGE_HEADER + m_page, page_header, sizeof page_header); + m_mtr.write<2>(*m_block, PAGE_HEADER + PAGE_N_RECS + m_page, m_rec_no); m_mtr.memcpy(*m_block, page_offset(slot), slot0 - slot); } else @@ -501,14 +493,7 @@ inline void PageBulk::finishPage() mach_write_to_2(PAGE_HEADER + PAGE_N_HEAP + m_page, (PAGE_HEAP_NO_USER_LOW + m_rec_no) | 1U << 15); mach_write_to_2(PAGE_HEADER + PAGE_N_RECS + m_page, m_rec_no); - mach_write_to_2(PAGE_HEADER + PAGE_LAST_INSERT + m_page, - static_cast<ulint>(m_cur_rec - m_page)); - mach_write_to_2(PAGE_HEADER + PAGE_DIRECTION_B - 1 + m_page, PAGE_RIGHT); } - - ut_ad(m_total_data + page_dir_calc_reserved_space(m_rec_no) <= - page_get_free_space_of_empty(m_is_comp)); - m_block->skip_flush_check= false; } /** Mark end of insertion to the page. Scan all records to set page dirs, @@ -522,6 +507,15 @@ inline void PageBulk::finish() finishPage<DYNAMIC>(); else finishPage<REDUNDANT>(); + + ut_ad(!page_header_get_field(m_page, PAGE_FREE)); + ut_ad(!page_header_get_field(m_page, PAGE_GARBAGE)); + ut_ad(!page_header_get_field(m_page, PAGE_LAST_INSERT)); + ut_ad(page_header_get_field(m_page, PAGE_INSTANT) == PAGE_NO_DIRECTION); + ut_ad(!page_header_get_field(m_page, PAGE_N_DIRECTION)); + ut_ad(m_total_data + page_dir_calc_reserved_space(m_rec_no) <= + page_get_free_space_of_empty(m_is_comp)); + m_block->skip_flush_check= false; } /** Commit inserts done to the page diff --git a/storage/innobase/page/page0page.cc b/storage/innobase/page/page0page.cc index 2458a40ec1d..f402792671b 100644 --- a/storage/innobase/page/page0page.cc +++ b/storage/innobase/page/page0page.cc @@ -562,8 +562,11 @@ page_copy_rec_list_end( const mtr_log_t log_mode = new_page_zip ? mtr->set_log_mode(MTR_LOG_NONE) : MTR_LOG_NONE; - ut_d(const bool was_empty = page_dir_get_n_heap(new_page) - == PAGE_HEAP_NO_USER_LOW); + const bool was_empty = page_dir_get_n_heap(new_page) + == PAGE_HEAP_NO_USER_LOW; + alignas(2) byte h[PAGE_N_DIRECTION + 2 - PAGE_LAST_INSERT]; + memcpy_aligned<2>(h, PAGE_HEADER + PAGE_LAST_INSERT + new_page, + sizeof h); if (index->is_spatial()) { ulint max_to_move = page_get_n_recs( @@ -584,6 +587,11 @@ page_copy_rec_list_end( } else { page_copy_rec_list_end_no_locks(new_block, block, rec, index, mtr); + if (was_empty) { + mtr->memcpy<mtr_t::MAYBE_NOP>(*new_block, PAGE_HEADER + + PAGE_LAST_INSERT + + new_page, h, sizeof h); + } } /* Update PAGE_MAX_TRX_ID on the uncompressed page. |