# # Bug #17335427 INNODB CAN NOT USE THE DOUBLEWRITE BUFFER PROPERLY # Bug #18144349 INNODB CANNOT USE THE DOUBLEWRITE BUFFER FOR THE FIRST # PAGE OF SYSTEM TABLESPACE # SET GLOBAL innodb_fast_shutdown = 0; # restart show variables like 'innodb_doublewrite'; Variable_name Value innodb_doublewrite ON show variables like 'innodb_fil_make_page_dirty_debug'; Variable_name Value innodb_fil_make_page_dirty_debug 0 show variables like 'innodb_saved_page_number_debug'; Variable_name Value innodb_saved_page_number_debug 0 connect stop_purge,localhost,root,,; START TRANSACTION WITH CONSISTENT SNAPSHOT; connection default; create table t1 (f1 int primary key, f2 blob) engine=innodb; start transaction; insert into t1 values(1, repeat('#',12)); insert into t1 values(2, repeat('+',12)); insert into t1 values(3, repeat('/',12)); insert into t1 values(4, repeat('-',12)); insert into t1 values(5, repeat('.',12)); commit work; # --------------------------------------------------------------- # Test Begin: Test if recovery works if first page of user # tablespace is full of zeroes. select space from information_schema.innodb_sys_tables where name = 'test/t1' into @space_id; Warnings: Warning 1287 ' INTO FROM...' instead begin; insert into t1 values (6, repeat('%', 12)); # Ensure that dirty pages of table t1 are flushed. set global innodb_buf_flush_list_now = 1; # Make the first page dirty for table t1 set global innodb_saved_page_number_debug = 0; set global innodb_fil_make_page_dirty_debug = @space_id; # Ensure that dirty pages of table t1 are flushed. set global innodb_buf_flush_list_now = 1; # Kill the server disconnect stop_purge; # Make the first page (page_no=0) of the user tablespace # full of zeroes. # # MDEV-11623: Use old FSP_SPACE_FLAGS in the doublewrite buffer. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if first page of user # tablespace is corrupted. select space from information_schema.innodb_sys_tables where name = 'test/t1' into @space_id; Warnings: Warning 1287 ' INTO FROM...' instead # Ensure that dirty pages of table t1 is flushed. flush tables t1 for export; unlock tables; begin; insert into t1 values (6, repeat('%', 12)); # Make the first page dirty for table t1 set global innodb_saved_page_number_debug = 0; set global innodb_fil_make_page_dirty_debug = @space_id; # Ensure that dirty pages of table t1 are flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Corrupt the first page (page_no=0) of the user tablespace. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if 2nd page of user # tablespace is full of zeroes. select space from information_schema.innodb_sys_tables where name = 'test/t1' into @space_id; Warnings: Warning 1287 ' INTO FROM...' instead # Ensure that dirty pages of table t1 is flushed. flush tables t1 for export; unlock tables; begin; insert into t1 values (6, repeat('%', 400)); # Make the 2nd page dirty for table t1 set global innodb_saved_page_number_debug = 1; set global innodb_fil_make_page_dirty_debug = @space_id; # Ensure that dirty pages of table t1 are flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Make the 2nd page (page_no=1) of the tablespace all zeroes. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if 2nd page of user # tablespace is corrupted. select space from information_schema.innodb_sys_tables where name = 'test/t1' into @space_id; Warnings: Warning 1287 ' INTO FROM...' instead # Ensure that dirty pages of table t1 is flushed. flush tables t1 for export; unlock tables; begin; insert into t1 values (6, repeat('%', 400)); # Make the 2nd page dirty for table t1 set global innodb_saved_page_number_debug = 1; set global innodb_fil_make_page_dirty_debug = @space_id; # Ensure that the dirty pages of table t1 are flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Corrupt the 2nd page (page_no=1) of the user tablespace. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if first page of # system tablespace is full of zeroes. begin; insert into t1 values (6, repeat('%', 400)); # Ensure that all dirty pages in the system are flushed. set global innodb_buf_flush_list_now = 1; # Make the first page dirty for system tablespace set global innodb_saved_page_number_debug = 0; set global innodb_fil_make_page_dirty_debug = 0; # Ensure that the dirty page of system tablespace is also flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Make the first page (page_no=0) of the system tablespace # all zeroes. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if first page of # system tablespace is corrupted. begin; insert into t1 values (6, repeat('%', 400)); # Ensure that all dirty pages in the system are flushed. set global innodb_buf_flush_list_now = 1; # Make the first page dirty for system tablespace set global innodb_saved_page_number_debug = 0; set global innodb_fil_make_page_dirty_debug = 0; # Ensure that the dirty page of system tablespace is also flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Corrupt the first page (page_no=0) of the system tablespace. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if 2nd page of # system tablespace is full of zeroes. begin; insert into t1 values (6, repeat('%', 400)); # Ensure that all dirty pages in the system are flushed. set global innodb_buf_flush_list_now = 1; # Make the second page dirty for system tablespace set global innodb_saved_page_number_debug = 1; set global innodb_fil_make_page_dirty_debug = 0; # Ensure that the dirty page of system tablespace is also flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Make the 2nd page (page_no=1) of the system tablespace # all zeroes. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ # Test End # --------------------------------------------------------------- # Test Begin: Test if recovery works if 2nd page of # system tablespace is corrupted. begin; insert into t1 values (6, repeat('%', 400)); # Ensure that all dirty pages in the system are flushed. set global innodb_buf_flush_list_now = 1; # Make the second page dirty for system tablespace set global innodb_saved_page_number_debug = 1; set global innodb_fil_make_page_dirty_debug = 0; # Ensure that the dirty page of system tablespace is also flushed. set global innodb_buf_flush_list_now = 1; # Kill the server # Make the 2nd page (page_no=1) of the system tablespace # all zeroes. # restart check table t1; Table Op Msg_type Msg_text test.t1 check status OK FOUND 1 /InnoDB: .*test.t1\.ibd/ in mysqld.1.err select f1, f2 from t1; f1 f2 1 ############ 2 ++++++++++++ 3 //////////// 4 ------------ 5 ............ drop table t1; # # MDEV-12600 crash during install_db with innodb_page_size=32K # and ibdata1=3M # # 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 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb' AND support IN ('YES', 'DEFAULT', 'ENABLED'); ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS FOUND 1 /\[ERROR\] InnoDB: Cannot create doublewrite buffer/ in mysqld.1.err # restart