* MySQL corruption on BTRFS
@ 2022-02-08 8:07 Tymoteusz Dolega
2022-02-09 21:35 ` Chris Murphy
2022-02-11 8:19 ` Forza
0 siblings, 2 replies; 6+ messages in thread
From: Tymoteusz Dolega @ 2022-02-08 8:07 UTC (permalink / raw)
To: linux-btrfs
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.
^ permalink raw reply [flat|nested] 6+ messages in thread* Re: MySQL corruption on BTRFS 2022-02-08 8:07 MySQL corruption on BTRFS Tymoteusz Dolega @ 2022-02-09 21:35 ` Chris Murphy 2022-02-10 0:54 ` Qu Wenruo 2022-02-11 8:19 ` Forza 1 sibling, 1 reply; 6+ messages in thread From: Chris Murphy @ 2022-02-09 21:35 UTC (permalink / raw) To: Tymoteusz Dolega, Btrfs BTRFS 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: MySQL corruption on BTRFS 2022-02-09 21:35 ` Chris Murphy @ 2022-02-10 0:54 ` Qu Wenruo 0 siblings, 0 replies; 6+ messages in thread From: Qu Wenruo @ 2022-02-10 0:54 UTC (permalink / raw) To: Chris Murphy, Tymoteusz Dolega, Btrfs BTRFS 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. > > ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: MySQL corruption on BTRFS 2022-02-08 8:07 MySQL corruption on BTRFS Tymoteusz Dolega 2022-02-09 21:35 ` Chris Murphy @ 2022-02-11 8:19 ` Forza 2022-02-20 9:41 ` Wang Yugui 1 sibling, 1 reply; 6+ messages in thread From: Forza @ 2022-02-11 8:19 UTC (permalink / raw) To: Tymoteusz Dolega, linux-btrfs 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: MySQL corruption on BTRFS 2022-02-11 8:19 ` Forza @ 2022-02-20 9:41 ` Wang Yugui 2022-02-20 9:55 ` Forza 0 siblings, 1 reply; 6+ messages in thread From: Wang Yugui @ 2022-02-20 9:41 UTC (permalink / raw) To: Forza; +Cc: Tymoteusz Dolega, linux-btrfs 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 ^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: MySQL corruption on BTRFS 2022-02-20 9:41 ` Wang Yugui @ 2022-02-20 9:55 ` Forza 0 siblings, 0 replies; 6+ messages in thread From: Forza @ 2022-02-20 9:55 UTC (permalink / raw) To: Wang Yugui; +Cc: Tymoteusz Dolega, linux-btrfs ---- 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 > ^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2022-02-20 10:04 UTC | newest] Thread overview: 6+ messages (download: mbox.gz follow: Atom feed -- links below jump to the message on this page -- 2022-02-08 8:07 MySQL corruption on BTRFS Tymoteusz Dolega 2022-02-09 21:35 ` Chris Murphy 2022-02-10 0:54 ` Qu Wenruo 2022-02-11 8:19 ` Forza 2022-02-20 9:41 ` Wang Yugui 2022-02-20 9:55 ` Forza
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox