MySQL corruption on BTRFS

MySQL corruption on BTRFS

[Tymoteusz Dolega]

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=/).

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.

Re: MySQL corruption on BTRFS

[Chris Murphy]

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=/).
>
> 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.

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.


-- 
Chris Murphy

Re: MySQL corruption on BTRFS

[Qu Wenruo]

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.
>
>

Re: MySQL corruption on BTRFS

[Forza]

Hi,

On 2/8/22 09:07, Tymoteusz Dolega 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=/).
> 
> 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.


Do you use 'nodatacow' attribute on the mysql files? You can check this 
with 'lsattr /var/lib/mysql/'. If you did, and had a power loss, the 
file could be corrupted. However, a scrub could not detect this because 
nodatacow also means no csums.

Out of experience I have had issues with "innodb_fast_shutdown" on 
mariadb. With this enabled, mariadb would sometimes take too long to 
shut down and was killed during reboots, which in turn caused 
corruptions in InnoDB.

I have set the following innodb options and have not had any more 
issues. This has been tested on many crashes and forces shut downs.

innodb_fast_shutdown = 0
innodb_flush_method  = O_DSYNC
innodb-doublewrite   = 0 # Not needed on COW filesystems. Should be 
enabled on on 'nodatacow' files.


~ Forza

Re: MySQL corruption on BTRFS

[Wang Yugui]

Hi,

> On 2/8/22 09:07, Tymoteusz Dolega 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=/).
> >
> > 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.
> 
> 
> Do you use 'nodatacow' attribute on the mysql files? You can check this with 'lsattr /var/lib/mysql/'. If you did, and had a power loss, the file could be corrupted. However, a scrub could not detect this because nodatacow also means no csums.
> 
> Out of experience I have had issues with "innodb_fast_shutdown" on mariadb. With this enabled, mariadb would sometimes take too long to shut down and was killed during reboots, which in turn caused corruptions in InnoDB.
> 
> I have set the following innodb options and have not had any more issues. This has been tested on many crashes and forces shut downs.
> 
> innodb_fast_shutdown = 0
> innodb_flush_method  = O_DSYNC
> innodb-doublewrite   = 0 # Not needed on COW filesystems. Should be enabled on on 'nodatacow' files.

Is innodb-doublewrite related to CoW feature or checksum feature?

For XFS with checksum but without CoW, ' innodb-doublewrite   = 0' is
recommanded too?

Best Regards
Wang Yugui (wangyugui@e16-tech.com)
2022/02/20

Re: MySQL corruption on BTRFS

[Forza]

---- From: Wang Yugui <wangyugui@e16-tech.com> -- Sent: 2022-02-20 - 10:41 ----

> Hi,
> 
>> On 2/8/22 09:07, Tymoteusz Dolega 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=/).
>> >
>> > 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.
>> 
>> 
>> Do you use 'nodatacow' attribute on the mysql files? You can check this with 'lsattr /var/lib/mysql/'. If you did, and had a power loss, the file could be corrupted. However, a scrub could not detect this because nodatacow also means no csums.
>> 
>> Out of experience I have had issues with "innodb_fast_shutdown" on mariadb. With this enabled, mariadb would sometimes take too long to shut down and was killed during reboots, which in turn caused corruptions in InnoDB.
>> 
>> I have set the following innodb options and have not had any more issues. This has been tested on many crashes and forces shut downs.
>> 
>> innodb_fast_shutdown = 0
>> innodb_flush_method  = O_DSYNC
>> innodb-doublewrite   = 0 # Not needed on COW filesystems. Should be enabled on on 'nodatacow' files.
> 
> Is innodb-doublewrite related to CoW feature or checksum feature?

It's related to CoW. Each write on btrfs is guaranteed to be either complete or or not happened in case of a power failure. Partial writes is not supposed to be possible. The double write is to protect against partial writes. 

From https://mariadb.com/kb/en/innodb-doublewrite-buffer/

"To turn off the doublewrite buffer, set the innodb_doublewrite system variable to 0. This is safe on filesystems that write pages atomically - that is, a page write fully succeeds or fails."

> 
> For XFS with checksum but without CoW, ' innodb-doublewrite   = 0' is
> recommanded too?

I don't know, but since, XFS isn't a Cow filesystem, I would be careful to disable double writes. 

> 
> Best Regards
> Wang Yugui (wangyugui@e16-tech.com)
> 2022/02/20
>