Problem with corrupted MariaDB database file

Question

When the MariaDB database on my Docker container keeps restarting itself, I figured out that the problem is in my database (I think it's corrupted).

root@website:~# docker-compose ps
 Name Command State Ports
----------------------------------------------------------------------------------------------------
root_mariadb_1 docker-entrypoint.sh mariadbd Restarting
root_phpmyadmin_1 /docker-entrypoint.sh apac ... Up 0.0.0.0:9500->80/tcp,:::9500->80/t
 cp
root_redis_1 /opt/bitnami/scripts/redis ... Restarting

MariaDB docker container error logs file:

[0m 2022年07月27日 23:22:00+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.7.3+maria~focal started.
[0m 2022年07月27日 23:22:00+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
[0m 2022年07月27日 23:22:00+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.7.3+maria~focal started.
[0m 2022年07月27日 23:22:00+00:00 [Note] [Entrypoint]: MariaDB upgrade not required
[0m 2022年07月27日 23:22:00 0 [Note] mariadbd (server 10.7.3-MariaDB-1:10.7.3+maria~focal) starting as process 1 ...
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Number of transaction pools: 1
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
[0m 2022年07月27日 23:22:00 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Using Linux native AIO
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Completed initialization of buffer pool
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16587504,22296402
[0m 2022年07月27日 23:22:00 0 [ERROR] InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 60
[0m 2022年07月27日 23:22:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
[0m 2022年07月27日 23:22:00 0 [Note] InnoDB: Starting shutdown...
[0m 2022年07月27日 23:22:01 0 [ERROR] Plugin 'InnoDB' init function returned error.
[0m 2022年07月27日 23:22:01 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[0m 2022年07月27日 23:22:01 0 [Note] Plugin 'FEEDBACK' is disabled.
[0m 2022年07月27日 23:22:01 0 [ERROR] Unknown/unsupported storage engine: InnoDB
[0m 2022年07月27日 23:22:01 0 [ERROR] Aborting

Here is the docker-compose.yml file:

version: '3.4'
services:
 mariadb:
 image: mariadb
 ports:
 - 3306:3306
 environment:
 MYSQL_ROOT_PASSWORD: "pass"
 restart: always
 volumes:
 - '/data/mysql:/var/lib/mysql'

I already have a full physical backup of my MariaDB database and I have already restored it to my local/dev/test environment.

When I start MariaDB on localhost (xampp), the problem still persists (like on my docker container).

Here is MariaDB error logs from localhost/xampp:

2022年07月29日 11:04:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022年07月29日 11:04:20 0 [Note] InnoDB: Number of transaction pools: 1
2022年07月29日 11:04:20 0 [Note] InnoDB: Using generic crc32 instructions
2022年07月29日 11:04:20 0 [Note] InnoDB: Initializing buffer pool, total size = 16777216, chunk size = 16777216
2022年07月29日 11:04:20 0 [Note] InnoDB: Completed initialization of buffer pool
2022年07月29日 11:04:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16587504,22296402
2022年07月29日 11:04:20 0 [ERROR] InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 60
2022年07月29日 11:04:20 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
2022年07月29日 11:04:20 0 [Note] InnoDB: Starting shutdown...
2022年07月29日 11:04:21 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022年07月29日 11:04:21 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022年07月29日 11:04:21 0 [Note] Plugin 'FEEDBACK' is disabled.
2022年07月29日 11:04:21 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022年07月29日 11:04:21 0 [ERROR] Aborting

File and folder on MariaDB:

root@website:/data/mysql# ls -l
total 206316
-rw-rw---- 1 systemd-coredump systemd-coredump 18268160 Jul 29 05:29 aria_log.00000001
-rw-rw---- 1 systemd-coredump systemd-coredump 52 Jul 29 05:29 aria_log_control
-rw-rw---- 1 systemd-coredump systemd-coredump 16384 Jul 25 16:27 ddl_recovery-backup.log
-rw-rw---- 1 systemd-coredump systemd-coredump 872 Apr 10 01:34 ib_buffer_pool
-rw-rw---- 1 systemd-coredump systemd-coredump 79691776 Jul 25 16:27 ibdata1
-rw-rw---- 1 systemd-coredump systemd-coredump 100663296 Jul 25 16:27 ib_logfile0
-rw-rw---- 1 systemd-coredump systemd-coredump 12582912 Jul 25 16:27 ibtmp1
-rw-rw---- 1 systemd-coredump systemd-coredump 0 Apr 10 01:27 multi-master.info
drwx------ 2 systemd-coredump systemd-coredump 4096 Apr 10 01:27 mysql
-rw-r--r-- 1 systemd-coredump systemd-coredump 14 Apr 10 01:27 mysql_upgrade_info
drwx------ 2 systemd-coredump systemd-coredump 4096 May 28 05:44 websitedb
drwx------ 2 systemd-coredump systemd-coredump 4096 Apr 10 01:27 performance_schema
drwx------ 2 systemd-coredump systemd-coredump 12288 Apr 10 01:27 sys

My database file on websitedb:

root@website:/data/mysql/websitedb# ls -l
total 13404
-rw-rw---- 1 systemd-coredump systemd-coredump 2094 Apr 10 02:00 slide.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 98304 Jul 25 16:27 slide.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump 67 Apr 10 01:58 db.opt
-rw-rw---- 1 systemd-coredump systemd-coredump 4630 Apr 10 02:00 role.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 114688 Jul 25 16:27 role.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump 7505 Apr 10 02:00 setting.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 98304 Jul 25 16:27 setting.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump 10307 Apr 10 02:00 user.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 212992 Jul 25 16:27 user.ibd

Answer 1

Looks like MDEV-28870 fixed in 10.7.5.

Physical backups can preserve some forms of corruption. So if your backup is generating this then:

• use --innodb_force_recovery=6 as a container
• take a logical dump of the database in the container
• restore into a 10.7.5+ container.

• chanartdev: I got error when dump sql dataabase using mysqldump. mysqldump: Got error: 1030: "Got error 194 "Tablespace is missing for a table" from storage engine InnoDB" when using LOCK TABLES After using --skip-lock-tables mysqldump: Couldn't execute 'show create table gpservices': Got error 194 "Tablespace is missing for a table" from storage engine InnoDB (1030)
• danblack: I'm sorry it. It looks like its corrupted then. Reading the MDEV there are gaps that might make this recoverable, maybe a backup restoration without a prepare stage, but I'm going into very unfamiliar territory even suggesting this.
• chanartdev: Thank you, the problem solved when I dump mysql per table.