diff options
Diffstat (limited to 'mysql-test/suite')
14 files changed, 209 insertions, 165 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 cafdbef4b69..d13c730c7e7 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 289 /verysecretmessage/ in t5.ibd +FOUND 256 /verysecretmessage/ in t5.ibd DROP TABLE t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/suite/innodb/r/index_merge_threshold.result b/mysql-test/suite/innodb/r/index_merge_threshold.result index 35cb82e6f1e..b8a6d81aecc 100644 --- a/mysql-test/suite/innodb/r/index_merge_threshold.result +++ b/mysql-test/suite/innodb/r/index_merge_threshold.result @@ -204,9 +204,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -220,8 +221,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -230,31 +231,32 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 4 +5 2 +6 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 2 +index_page_merge_successful 2 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 3 +index_page_merge_successful 3 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 3 +index_page_merge_successful 3 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -285,9 +287,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -301,8 +304,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -311,31 +314,32 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 4 +5 2 +6 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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +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 2 -index_page_merge_successful 2 +index_page_merge_attempts 3 +index_page_merge_successful 3 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=25) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB @@ -366,9 +370,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -382,8 +387,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -392,31 +397,32 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 4 -5 4 +5 1 +6 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 0 -index_page_merge_successful 0 +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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm partitioned table (MERGE_THRESHOLD=35) CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) @@ -452,9 +458,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 begin; delete from tab1 where a = 12; delete from tab1 where a = 13; @@ -468,8 +475,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -479,31 +486,32 @@ PAGE_NUMBER NUMBER_RECORDS 3 0 3 2 4 4 -5 4 +5 2 +6 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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +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 2 -index_page_merge_successful 2 +index_page_merge_attempts 3 +index_page_merge_successful 3 DROP TABLE tab1; # # behavior for updating to smaller records @@ -536,9 +544,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -549,8 +558,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -559,28 +568,29 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 7 +5 3 +6 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 2 +index_page_merge_successful 2 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 2 +index_page_merge_successful 2 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 1 -index_page_merge_successful 1 +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 @@ -611,9 +621,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -624,8 +635,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -634,28 +645,29 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 7 +5 3 +6 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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 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 1 -index_page_merge_successful 1 +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 @@ -686,9 +698,10 @@ 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 2 +3 3 4 7 -5 7 +5 3 +6 4 update tab1 set b='' where a = 12; update tab1 set b='' where a = 13; update tab1 set b='' where a = 14; @@ -699,8 +712,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -709,28 +722,29 @@ and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; PAGE_NUMBER NUMBER_RECORDS 3 2 4 7 -5 7 +5 3 +6 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 0 -index_page_merge_successful 0 +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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # test to confirm explicit temporary table (MERGE_THRESHOLD=35) # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly) @@ -771,8 +785,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -784,22 +798,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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # # behavior for secondary index with blob @@ -863,9 +877,10 @@ 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 2 +4 3 27 21 -28 21 +28 10 +30 11 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -893,8 +908,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -904,31 +919,32 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 11 +28 9 +30 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 2 +index_page_merge_successful 2 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 3 +index_page_merge_successful 3 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 3 +index_page_merge_successful 3 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; @@ -989,9 +1005,10 @@ 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 2 +4 3 27 21 -28 21 +28 10 +30 11 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1019,8 +1036,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1030,31 +1047,32 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 11 +28 9 +30 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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +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 2 -index_page_merge_successful 2 +index_page_merge_attempts 3 +index_page_merge_successful 3 DROP TABLE tab1; # test to confirm behavior (MERGE_THRESHOLD=40) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic; @@ -1115,9 +1133,10 @@ 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 2 +4 3 27 21 -28 21 +28 10 +30 11 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1145,8 +1164,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1156,31 +1175,32 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 11 +28 8 +30 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 0 -index_page_merge_successful 0 +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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +index_page_merge_attempts 2 +index_page_merge_successful 2 DROP TABLE tab1; # compressed table behaves same (MERGE_THRESHOLD=45) CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB @@ -1242,9 +1262,10 @@ 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 2 +4 3 27 21 -28 21 +28 10 +30 11 begin; delete from tab1 where a = 33; delete from tab1 where a = 34; @@ -1272,8 +1293,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 0 -index_page_merge_successful 0 +index_page_merge_attempts 1 +index_page_merge_successful 1 select PAGE_NUMBER, NUMBER_RECORDS from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 @@ -1283,30 +1304,31 @@ PAGE_NUMBER NUMBER_RECORDS 3 22 4 2 27 11 -28 11 +28 9 +30 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 0 -index_page_merge_successful 0 +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 1 -index_page_merge_successful 1 +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 2 -index_page_merge_successful 2 +index_page_merge_attempts 3 +index_page_merge_successful 3 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 29660962e0c..18890d99750 100644 --- a/mysql-test/suite/innodb/r/index_tree_operation.result +++ b/mysql-test/suite/innodb/r/index_tree_operation.result @@ -16,9 +16,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 2 +3 3 4 3 -5 3 +5 1 +6 2 INSERT INTO t1 VALUES (999, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -28,8 +29,8 @@ AND page_type = "INDEX" ORDER BY page_number; page_number number_records 3 3 4 3 -5 3 -6 1 +5 1 +6 3 INSERT INTO t1 VALUES (998, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -37,10 +38,11 @@ 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 4 4 3 -5 3 -6 2 +5 1 +6 3 +7 1 INSERT INTO t1 VALUES (997, REPEAT('a', 4096)); SELECT page_number, number_records FROM information_schema.innodb_sys_tablespaces s1, @@ -48,8 +50,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 4 4 3 -5 3 +5 1 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 d795b516d5e..f8bf417969e 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -13,6 +13,7 @@ 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; @@ -50,5 +51,5 @@ Table Op Msg_type Msg_text test.t1 check status OK SHOW ENGINE INNODB STATUS; Type Name Status -InnoDB insert 79, delete mark 1 +InnoDB insert 139, 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 4610d251fc0..23aa0a28c90 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 24084 /AaAaAaAa/ in innodb_normal.ibd +FOUND 24000 /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 2f675bcf1b4..ea3b18011df 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 24084 /AaAaAaAa/ in innodb_normal.ibd +FOUND 24000 /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_defragment_fill_factor.result b/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result index 8453050a92a..fcd73caf978 100644 --- a/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result +++ b/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result @@ -115,4 +115,5 @@ 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/instant_alter,32k.rdiff b/mysql-test/suite/innodb/r/instant_alter,32k.rdiff index 37c3c479e68..14a19856be5 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 -+5 ++4 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -71,7 +71,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+5 ++4 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -98,7 +98,7 @@ WHERE name = 'test/big'; clust_index_size -7 -+5 ++4 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 ca93b1d6519..ddd2bbf89ae 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 -+28 ++27 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 b96262866e5..8f4294b56d2 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 -+5 ++4 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 -+13 ++12 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -59,7 +59,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+5 ++4 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 -+5 ++4 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 -+13 ++12 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -122,7 +122,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+5 ++4 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 -+5 ++4 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 -+13 ++12 connection default; ROLLBACK; CHECKSUM TABLE big; @@ -185,7 +185,7 @@ WHERE name = 'test/big'; clust_index_size -3 -+5 ++4 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 37ddb0a9348..6e9cf71925a 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,17 +4,23 @@ 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: Index for table 't1' is corrupt; try to repair it +ERROR HY000: Got error 168 "Unknown (generic) error from engine" from storage engine InnoDB 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 79d9cc814a0..9c5acedb620 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -35,9 +35,16 @@ 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/leaf_page_corrupted_during_recovery.test b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test index bd8784236b6..6e080078832 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=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("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("\\[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,8 +20,12 @@ INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), (8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), (12, 'test8'); -let $restart_noprint=2; ---source include/restart_mysqld.inc +# 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 INNODB_PAGE_SIZE=`select @@innodb_page_size`; let MYSQLD_DATADIR=`select @@datadir`; @@ -38,7 +42,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} * 19 + 38, SEEK_SET) or die "seek"; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 14 + 38, SEEK_SET) or die "seek"; print FILE "junk"; close FILE or die "close"; EOF @@ -50,7 +54,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_NOT_KEYFILE +--error ER_GET_ERRNO 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 3b98527250b..d7dc9a745f7 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: 163840 +The size of the tab5.ibd file: 159744 # 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: 163840 +The size of the tab5.ibd file: 159744 # 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: 163840 +The size of the tab5.ibd file: 159744 DROP TABLE tab5; #****************************************************************** # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K |