Artem Russakovskii added a comment - 2023-09-08 20:47 Thank you, Marko, for all your help. As we reached a conclusion that it wasn't possible to repair (or at least easily repair) the table and prevent crashes, I blew up the slave data on the 11.0.3 machine, ran an extended check and then fixed whatever index issues that were found with an optimize command on another slave that was still on 10.11, and then copied that slave's fixed data onto the 11.0.3 machine. This time, the crashes did not occur anymore, and the server has been running smoothly.

Marko Mäkelä added a comment - 2023-09-13 14:57 I think that we can try to make the upgrade logic similar to how the slow shutdown fix in MDEV-30009 works: disregard any buffered changes for pages for which there are no buffered changes according to the change buffer bitmap. This should avoid corruption of any actual data (clustered index pages), unless the change buffer bitmaps are really corrupted.

Marko Mäkelä added a comment - 2023-09-19 10:58 To prove that the current upgrade in 11.0 does not care about the change buffer bitmap bits, I ran the following test modification on an older 10.6 server (right before MDEV-30400 ) and then started 11.0 on the data directory that was left behind by the failing test: diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index 9362f8daffa..a1895f4c49c 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -33,6 +33,8 @@ SET GLOBAL innodb_change_buffering=all; # 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_1024; +UPDATE t1 SET b='y'; +UPDATE t1 SET b='x'; let MYSQLD_DATADIR=`select @@datadir`; let PAGE_SIZE=`select @@innodb_page_size`; @@ -69,6 +71,7 @@ syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file"; EOF +stop_already_here; --let $restart_parameters= --innodb-force-recovery=6 --innodb-change-buffer-dump --source include/start_mysqld.inc This is similar to the upgrade test MDEV-29694 , except that we stop after some Perl code had corrupted the change buffer bitmap in t1.ibd to claim that no buffered changes exist. It is noteworthy that this test fails to actually use the change buffer when the MDEV-30400 fix is present, so I used an older build. Regrettably I did not record the exact revision in MDEV-29694 : 10.6 e0dbec1ce38688e25cab3720044d6e7e676381f6 innodb.ibuf_not_empty 'innodb,strict_crc32' [ fail ] Test ended at 2023-09-19 13:52:09   CURRENT_TEST: innodb.ibuf_not_empty mysqltest: At line 74: query 'stop_already_here' failed: <Unknown> (2006): Server has gone away Unfortunately, no matter what I try (including stopping the test before the Perl code corrupts the .ibd file), I keep getting the following output on the upgrade: 2023-09-19 13:53:11 0 [Note] InnoDB: Upgrading the change buffer 2023-09-19 13:53:11 0 [Note] InnoDB: Upgraded the change buffer: 0 tablespaces, 0 pages That is, the change buffer was actually empty to begin with. I will next attempt creating a modified version of the data.tar.xz that was attached to MDEV-31443 , with a modified change buffer bitmap that claims that no changes need to be applied. By using such a modified data directory, I expect to get some corrupted secondary indexes reported when this bug has been fixed. The plain 11.0 without the fix should recover that kind of a corruption fine (apply the possibly stale changes to the index pages).

Marko Mäkelä added a comment - 2023-09-19 12:28 I created ibuf_page.bin.xz to corrupt the data.tar.xz of MDEV-31443 as follows: tar xJf data.tar.xz xzcat ibuf_page.bin.xz| dd bs=4096 count=1 seek=1 conv=notrunc of=data/test/table100_innodb_int_autoinc.ibd If I start up the unmodified 11.0 server on this, it will apply changes to one page: 11.0 d515fa72a0294d97e3f8d6894714c10331bd771d 2023-09-19 15:23:49 0 [Note] InnoDB: Upgrading the change buffer 2023-09-19 15:23:49 0 [Note] InnoDB: Upgraded the change buffer: 1 tablespaces, 1 pages After this, CHECK TABLE test.table100_innodb_int_autoinc EXTENDED; does not report any error. I hope that after my not-yet-written code fix, it will report some error. (Yes, this is corrupting the file the "wrong way around", opposite of MDEV-32132 .)

Marko Mäkelä added a comment - 2023-09-19 15:11 The above data set contains exactly 2 buffered changes in the change buffer root page (file offset 0x4000 in data/ibdata1 ), both for the same record in page 5:0x3a: a delete-marking and an insert of the record at file byte offset 0x3adbe (page 0x3a, byte 0xdbe) in file table100_innodb_int_autoinc.ibd . The funny thing is that these two operations will cancel out each other. So, for this particular dataset, there will be no corruption no matter whether the changes to the page are merged or discarded. I debugged with rr record and rr replay that the revised upgrade logic works as expected: the bitmap bit in the original data.tar.xz is set, and clear when I artificially corrupt the change buffer bitmap page. The changes were applied or discarded accordingly. The message 2023-09-19 18:03:59 0 [Note] InnoDB: Upgraded the change buffer: 1 tablespaces, 1 pages will count all pages for which change buffer records exist, including those for which the changes will be skipped due to the IBUF_BITMAP_BUFFERED bit being clean.