1# 2# Bug #17335427 INNODB CAN NOT USE THE DOUBLEWRITE BUFFER PROPERLY 3# Bug #18144349 INNODB CANNOT USE THE DOUBLEWRITE BUFFER FOR THE FIRST 4# PAGE OF SYSTEM TABLESPACE 5# 6SET GLOBAL innodb_fast_shutdown = 0; 7# restart 8show variables like 'innodb_doublewrite'; 9Variable_name Value 10innodb_doublewrite ON 11show variables like 'innodb_fil_make_page_dirty_debug'; 12Variable_name Value 13innodb_fil_make_page_dirty_debug 0 14show variables like 'innodb_saved_page_number_debug'; 15Variable_name Value 16innodb_saved_page_number_debug 0 17create table t1 (f1 int primary key, f2 blob) engine=innodb; 18start transaction; 19insert into t1 values(1, repeat('#',12)); 20insert into t1 values(2, repeat('+',12)); 21insert into t1 values(3, repeat('/',12)); 22insert into t1 values(4, repeat('-',12)); 23insert into t1 values(5, repeat('.',12)); 24commit work; 25# --------------------------------------------------------------- 26# Test Begin: Test if recovery works if first page of user 27# tablespace is full of zeroes. 28select space from information_schema.innodb_sys_tables 29where name = 'test/t1' into @space_id; 30Warnings: 31Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 32# Ensure that dirty pages of table t1 is flushed. 33flush tables t1 for export; 34unlock tables; 35begin; 36insert into t1 values (6, repeat('%', 12)); 37# Make the first page dirty for table t1 38set global innodb_saved_page_number_debug = 0; 39set global innodb_fil_make_page_dirty_debug = @space_id; 40# Ensure that dirty pages of table t1 are flushed. 41set global innodb_buf_flush_list_now = 1; 42# Kill the server 43# Make the first page (page_no=0) of the user tablespace 44# full of zeroes. 45# 46# MDEV-11623: Use old FSP_SPACE_FLAGS in the doublewrite buffer. 47# restart 48check table t1; 49Table Op Msg_type Msg_text 50test.t1 check status OK 51select f1, f2 from t1; 52f1 f2 531 ############ 542 ++++++++++++ 553 //////////// 564 ------------ 575 ............ 58# Test End 59# --------------------------------------------------------------- 60# Test Begin: Test if recovery works if first page of user 61# tablespace is corrupted. 62select space from information_schema.innodb_sys_tables 63where name = 'test/t1' into @space_id; 64Warnings: 65Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 66# Ensure that dirty pages of table t1 is flushed. 67flush tables t1 for export; 68unlock tables; 69begin; 70insert into t1 values (6, repeat('%', 12)); 71# Make the first page dirty for table t1 72set global innodb_saved_page_number_debug = 0; 73set global innodb_fil_make_page_dirty_debug = @space_id; 74# Ensure that dirty pages of table t1 are flushed. 75set global innodb_buf_flush_list_now = 1; 76# Kill the server 77# Corrupt the first page (page_no=0) of the user tablespace. 78# restart 79check table t1; 80Table Op Msg_type Msg_text 81test.t1 check status OK 82select f1, f2 from t1; 83f1 f2 841 ############ 852 ++++++++++++ 863 //////////// 874 ------------ 885 ............ 89# Test End 90# --------------------------------------------------------------- 91# Test Begin: Test if recovery works if 2nd page of user 92# tablespace is full of zeroes. 93select space from information_schema.innodb_sys_tables 94where name = 'test/t1' into @space_id; 95Warnings: 96Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 97# Ensure that dirty pages of table t1 is flushed. 98flush tables t1 for export; 99unlock tables; 100begin; 101insert into t1 values (6, repeat('%', 400)); 102# Make the 2nd page dirty for table t1 103set global innodb_saved_page_number_debug = 1; 104set global innodb_fil_make_page_dirty_debug = @space_id; 105# Ensure that dirty pages of table t1 are flushed. 106set global innodb_buf_flush_list_now = 1; 107# Kill the server 108# Make the 2nd page (page_no=1) of the tablespace all zeroes. 109# restart 110check table t1; 111Table Op Msg_type Msg_text 112test.t1 check status OK 113select f1, f2 from t1; 114f1 f2 1151 ############ 1162 ++++++++++++ 1173 //////////// 1184 ------------ 1195 ............ 120# Test End 121# --------------------------------------------------------------- 122# Test Begin: Test if recovery works if 2nd page of user 123# tablespace is corrupted. 124select space from information_schema.innodb_sys_tables 125where name = 'test/t1' into @space_id; 126Warnings: 127Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 128# Ensure that dirty pages of table t1 is flushed. 129flush tables t1 for export; 130unlock tables; 131begin; 132insert into t1 values (6, repeat('%', 400)); 133# Make the 2nd page dirty for table t1 134set global innodb_saved_page_number_debug = 1; 135set global innodb_fil_make_page_dirty_debug = @space_id; 136# Ensure that the dirty pages of table t1 are flushed. 137set global innodb_buf_flush_list_now = 1; 138# Kill the server 139# Corrupt the 2nd page (page_no=1) of the user tablespace. 140# restart 141check table t1; 142Table Op Msg_type Msg_text 143test.t1 check status OK 144select f1, f2 from t1; 145f1 f2 1461 ############ 1472 ++++++++++++ 1483 //////////// 1494 ------------ 1505 ............ 151# Test End 152# --------------------------------------------------------------- 153# Test Begin: Test if recovery works if first page of 154# system tablespace is full of zeroes. 155begin; 156insert into t1 values (6, repeat('%', 400)); 157# Ensure that all dirty pages in the system are flushed. 158set global innodb_buf_flush_list_now = 1; 159# Make the first page dirty for system tablespace 160set global innodb_saved_page_number_debug = 0; 161set global innodb_fil_make_page_dirty_debug = 0; 162# Ensure that the dirty page of system tablespace is also flushed. 163set global innodb_buf_flush_list_now = 1; 164# Kill the server 165# Make the first page (page_no=0) of the system tablespace 166# all zeroes. 167# restart 168check table t1; 169Table Op Msg_type Msg_text 170test.t1 check status OK 171select f1, f2 from t1; 172f1 f2 1731 ############ 1742 ++++++++++++ 1753 //////////// 1764 ------------ 1775 ............ 178# Test End 179# --------------------------------------------------------------- 180# Test Begin: Test if recovery works if first page of 181# system tablespace is corrupted. 182begin; 183insert into t1 values (6, repeat('%', 400)); 184# Ensure that all dirty pages in the system are flushed. 185set global innodb_buf_flush_list_now = 1; 186# Make the first page dirty for system tablespace 187set global innodb_saved_page_number_debug = 0; 188set global innodb_fil_make_page_dirty_debug = 0; 189# Ensure that the dirty page of system tablespace is also flushed. 190set global innodb_buf_flush_list_now = 1; 191# Kill the server 192# Corrupt the first page (page_no=0) of the system tablespace. 193# restart 194check table t1; 195Table Op Msg_type Msg_text 196test.t1 check status OK 197select f1, f2 from t1; 198f1 f2 1991 ############ 2002 ++++++++++++ 2013 //////////// 2024 ------------ 2035 ............ 204# Test End 205# --------------------------------------------------------------- 206# Test Begin: Test if recovery works if 2nd page of 207# system tablespace is full of zeroes. 208begin; 209insert into t1 values (6, repeat('%', 400)); 210# Ensure that all dirty pages in the system are flushed. 211set global innodb_buf_flush_list_now = 1; 212# Make the second page dirty for system tablespace 213set global innodb_saved_page_number_debug = 1; 214set global innodb_fil_make_page_dirty_debug = 0; 215# Ensure that the dirty page of system tablespace is also flushed. 216set global innodb_buf_flush_list_now = 1; 217# Kill the server 218# Make the 2nd page (page_no=1) of the system tablespace 219# all zeroes. 220# restart 221check table t1; 222Table Op Msg_type Msg_text 223test.t1 check status OK 224select f1, f2 from t1; 225f1 f2 2261 ############ 2272 ++++++++++++ 2283 //////////// 2294 ------------ 2305 ............ 231# Test End 232# --------------------------------------------------------------- 233# Test Begin: Test if recovery works if 2nd page of 234# system tablespace is corrupted. 235begin; 236insert into t1 values (6, repeat('%', 400)); 237# Ensure that all dirty pages in the system are flushed. 238set global innodb_buf_flush_list_now = 1; 239# Make the second page dirty for system tablespace 240set global innodb_saved_page_number_debug = 1; 241set global innodb_fil_make_page_dirty_debug = 0; 242# Ensure that the dirty page of system tablespace is also flushed. 243set global innodb_buf_flush_list_now = 1; 244# Kill the server 245# Make the 2nd page (page_no=1) of the system tablespace 246# all zeroes. 247# restart 248check table t1; 249Table Op Msg_type Msg_text 250test.t1 check status OK 251FOUND 1 /InnoDB: .*test.t1\.ibd/ in mysqld.1.err 252select f1, f2 from t1; 253f1 f2 2541 ############ 2552 ++++++++++++ 2563 //////////// 2574 ------------ 2585 ............ 259drop table t1; 260# 261# MDEV-12600 crash during install_db with innodb_page_size=32K 262# and ibdata1=3M 263# 264# restart: --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/doublewrite --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/doublewrite --innodb-data-file-path=ibdata1:1M;ibdata2:1M:autoextend 265SELECT * FROM INFORMATION_SCHEMA.ENGINES 266WHERE engine = 'innodb' 267AND support IN ('YES', 'DEFAULT', 'ENABLED'); 268ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS 269FOUND 1 /\[ERROR\] InnoDB: Cannot create doublewrite buffer/ in mysqld.1.err 270# restart 271