[SOLVED] Crash MariaDB:10 (>10.4.28) on Docker in LXC on BTRFS

[TomFreudenberg]

Hi,

we have encountered a strange situation:

Running:

1. PVE 7.4.3
2. Partition /dev/sdb1formatted with BTRFS
3. Partition /dev/sdb1 mounted at /vol/data on host
4. Storage added: `pvesm add btrfs data --path /vol/data ; pvesm set data --format raw`
5. Unpriviledged LXC container created with Template Debian Buster: `pct create ... --rootfs data:0 ...`

while using `data:0` the container is created as subvol on BTRFS filesystem

6. Proceed in LXC Container:
7. Install Docker (https://du.nkel.dev/blog/2021-03-25_proxmox_docker/)
8. Create database path in LXC: `mkdir -p /data/mysql`
9. Running MariaDB:10 via docker: `docker run -e MARIADB_ROOT_PASSWORD=test -it --rm -p 3306:3306 -v /data/mysql:/var/lib/mysql mariadb:10`

importing data from ONE table with huge INSERT INTO statements (100 MB SQL Dump in 143 lines => 130 lines INSERT INTO statements) from other machine

10: `mysql -h 1.2.3.4 -u root -ptest test < prod.sql`

After importing between 100 - 125 lines of the INSERT INTOs, the MariabDB import crashes:

2023-03-31  9:53:27 4 [Note] InnoDB: 130911510f980dda0c270a6f08b106f80540038201c700634199bc1cd39743d8
2023-03-31  9:53:27 4 [Note] InnoDB: End of page dump
2023-03-31  9:53:27 4 [Note] InnoDB:  You can use CHECK TABLE to scan your table for corruption. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2023-03-31  9:53:27 4 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2023-03-31  9:53:27 4 [ERROR] mariadbd: Index for table 'xyz' is corrupt; try to repair it

_________________________

If I use MySQL:latest or MariaDB:10.4.28 everything works fine without errors.

Also if I create an RAW file as additional MP for /data/mysql to the LXC formatted by either ext4 or btrfs, it works with MariaDB:10 also.

If I run MariaDB directly in the LXC without Docker, it works also on the BTRFS subvol.

_________________________


Just latest MariaDB:10 docker images are crashing on import???


Does anybody has an idea or suggestion?

 

[TomFreudenberg]

In case that they changed different tuning values for innodb in the later builds as default, for our use case the default (128M) for `innodb_buffer_pool_size` was too small.

We created a overlay conf with

innodb_buffer_pool_size = 2G

and that solved our issue.

So, nothing wrong with PVE or LXC or UNPRIV or BTRFS or SUBVOL nor MARIADB:10 - just simple configuration!

 

[TomFreudenberg]

One last statement to that error, it depends also on system ressources.

On a LXC with 8G Mem, the buffer needs to be 2G

On a LXC with 2G Mem, a 1G buffer is also fine

 

[TomFreudenberg]

Anothoer option is innodb_flush_method which was changed starting with release 10.5 of MariaDB to O_DIRECT instead fsync.

Using innodb_flush_method = fsync or innodb_flush_method = O_DSYNS will also work.