Re: Failed to start mysql due to start_lsn and ens_lsn issue

[Jakub Lopuszanski]

After internal discussion with InnoDB, and some code analysis, I'm forced to reach the (a priori improbable) conclusion that your hardware+OS+LVM+lib combination doesn't properly handle fsync().
This is really serious and dangerous circumstance, but not something I can help fixing.

The https://serverfault.com/questions/279571/lvm-dangers-and-caveats contains long list of problems with LVM+fsync() in 2011, and a followup message from 2020 claiming all these problems are gone, but apparently, in your case, they aren't.

I wish I could help you with configuring/fixing LVM, but I don't know how.
I wanted to write a simple test C++ program to verify that indeed fsync() is broken in your environment, but realised, that such test would require you to unplug power cord at specific time, which is impractical - otherwise, as long as OS keeps going, it seems to serve even non-fsync()ed yet data directly from OS Cache even if I use O_DIRECT, which makes such synthetic test useless.

The thing I'd try in your situation is to start the server with innodb_force_recovery=6, mysqldump the whole database as SQL, shut down the server, and start from a clean data-dir and restore the database from the SQL dump. Hopefully, even if there are any inconsistencies in the tablespace, most of them could be ignored by a full table scan over leaf-pages of B-tree, which mysqldump should do. The mysqldump tool has various options which should let you ignore/bypass various errors, hopefully letting you extract as much data from tables as possible.

But if the drive has such issues with fsync(), then I'd use a different one, and/or find some expert advice about configuring/fixing it - this is beyond my skill level, unfortunately.