Re: MySQL corruption on BTRFS

[Qu Wenruo <quwenruo.btrfs@gmx.com>]

On 2022/2/10 05:35, Chris Murphy wrote:
> On Tue, Feb 8, 2022 at 1:07 AM Tymoteusz Dolega
> <tymoteuszdolega@gmail.com> wrote:
>>
>> Hello,
>> I maybe encountered a bug. I'm using NixOS, and after enabling MySQL with:
>>
>> services.mysql = {
>>        enable = true;
>>        package = pkgs.mariadb;
>>   };
>>
>> it cannot even start, and fails with "code=killed, status=6/ABRT". The
>> problem that MySQL reports in journal is about file corruption. I
>> attached all logs at the bottom of this mail.
>> I tried changing database location to different BTRFS SSD, cleanly
>> formatted, and problem persists. After changing database location to
>> EXT4 partition, everything works perfectly. I tried newer MySQL
>> version (from nix-unstable), but still errors show up. Current version
>> is 10.6.5-MariaDB. I tried deleting DB folder to force it to make it
>> again. Scrub is clean, check (--readonly) is clean. I have only 1
>> mount option: "noatime". "mount" reports:
>> (rw,noatime,ssd,space_cache,subvolid=5,subvol=/).

I'm thinking if mariadb is setting NOCOW for its database files.

If that's the case, and a power loss happens, btrfs can not ensure the
atomicness of the file content.
(this relies on data COW and checksum, while NOCOW disables them all).

>>
>> uname -a
>> Linux desktop-nixos 5.16.4 #1-NixOS SMP PREEMPT Sat Jan 29 09:59:25
>> UTC 2022 x86_64 GNU/Linux
>>
>> btrfs --version
>> btrfs-progs v5.14.1
>>
>> sudo btrfs fi show
>> Label: 'nixos'  uuid: 67b6e734-cd1e-41e3-ab7a-63660e540014
>>          Total devices 1 FS bytes used 95.05GiB
>>          devid    1 size 249.00GiB used 98.03GiB path /dev/nvme0n1p5
>>
>> Label: 'cruc'  uuid: cc51fa3c-57db-42b6-a890-ff5cd7b18f47
>>          Total devices 1 FS bytes used 125.16MiB
>>          devid    1 size 931.51GiB used 2.02GiB path /dev/sdb1
>>
>> btrfs fi df /mnt/cruc
>> Data, single: total=1.01GiB, used=124.84MiB
>> System, single: total=4.00MiB, used=16.00KiB
>> Metadata, single: total=1.01GiB, used=304.00KiB
>> GlobalReserve, single: total=3.25MiB, used=0.00B
>>
>> dmesg.log  - https://www.dropbox.com/s/ou52m2hdjzmjy6b/dmesg.log?dl=0
>> but there is not much there besides you can see I cleanly formatted the drive
>> mysql - https://www.dropbox.com/s/jjthkfu0anh8n2o/mysql.log?dl=0
>> log with info about corruption
>> (links hosted on dropbox, you can see them without logging in)
>> I will be happy to answer any needed questions.

Mind to locate the file "ibdata1" and provides the following output of it?

  # lsattr ./ibdata1

If it shows something like this:

  ---------------C------ /mnt/btrfs/file

It means it's a NOCOW file, and btrfs doesn't guarantee the correctness
of its content after power loss.
This is the same behavior of other filesystems, and it's on the database
itself to utilize things like write ahead log to ensure the correctness.

Thanks,
Qu
>
> Does this happen just on starting up mariadb for the first time? I'm
> wondering how to go about reproducing it on Fedora where I have
> mariadb 10.6.5, kernel 5.17-rc3. All I'm doing is systemctl start
> mariadb, and it starts up OK. Other than the kernel I'm wondering
> what's different about them.
>
>