I'm trying to understand this...

Prepare backup from the 13th (MariaDB 10.8.3, fsync) -> works

Prepare backup from the 16th (MariaDB 10.8.4, 2 hours after upgrade, had table renames in the meantime, fsync, innodb_log_file_buffering ON) -> works

Prepare backup from the 17th (MariaDB 10.8.4, fsync, innodb_log_file_buffering ON) -> works

Prepare backup from the 18th (MariaDB 10.8.4, O_DIRECT, innodb_log_file_buffering ON) -> works

19th - I don't have backup saved - I was trying manual experiments (MDEV-28772)

Prepare backup from the 20th (MariaDB 10.8.4, O_DIRECT, innodb_log_file_buffering OFF, I think) -> works, but fails later due to MDEV-28974 (out of scope of this investigation)

Prepare backup from the 21st (MariaDB 10.8.4, O_DIRECT, innodb_log_file_buffering ON) -> fails/corrupted

Prepare backup from the 22nd (MariaDB 10.8.4, O_DIRECT, innodb_log_file_buffering ON) -> fails/corrupted

Prepare backup from the 23rd (MariaDB 10.8.4, O_DIRECT, innodb_log_file_buffering ON) -> fails/corrupted

Still not sure what it can be...

Doesn't seem to be the combination of "O_DIRECT, innodb_log_file_buffering ON", because the backup of the 18th works.

Unless the fact that MariaDB was restarted recently was what helped. Last restart was on the 17th.

I turned innodb_log_file_buffering = OFF (online), created a new backup, and it crashed again preparing, for the same corruption issue.

—

I sent the backup taken earlier today, to the SSD server (instead of HDD), and it also crashed for corruption.
It didn't show me the errors of "Page read from tablespace is corrupted.", but showed these:

This target seems to be not prepared yet.

mariabackup: using the following InnoDB configuration for recovery:

innodb_data_home_dir = .

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = .

InnoDB: Using Linux native AIO

Starting InnoDB instance for recovery.

mariabackup: Using 10737418240 bytes for buffer pool (set by --use-memory parameter)

[Note] InnoDB: Compressed tables use zlib 1.2.7

[Note] InnoDB: Number of transaction pools: 1

[Note] InnoDB: Using crc32 + pclmulqdq instructions

[Note] InnoDB: Using Linux native AIO

[Note] InnoDB: Initializing buffer pool, total size = 10.000GiB, chunk size = 10.000GiB

[Note] InnoDB: Completed initialization of buffer pool

[Note] InnoDB: Starting crash recovery from checkpoint LSN=10628649691730

[Note] InnoDB: Starting final batch to recover 164530 pages from redo log.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying DELETE_ROW_FORMAT_DYNAMIC due to corruption on [page id: space=4731, page number=27705]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[Note] InnoDB: To recover: 123725 pages from log

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=8133, page number=31742]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

[ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id: space=9536, page number=356]

[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption.

mariadb-backup: srv_start() returned 37 (Data structure corruption).

So, basically the backups being produced by MariaDB are simply broken... and taking new backups isn't resolving the problem.

I sent over the latest backup I created, to the SSD server, and tried again.
The same happened, with the latest backup I created.

marko - looks like MariaBackup are creating corrupted backups, even worse issue than MDEV-28974, because I don't seem to be able to create a valid backup, even if I create a new one as a workaround attempt.

Tomorrow I'll test restarting MariaDB and create a new fresh backup, to see if that gives me a valid backup.

I wonder how others aren't complaining about these issues.. not sure if it's because not many are yet using MariaDB 10.8, or just not testing their backups.
I'm really worried about losing my database, if I cannot trust MariaBackup doing its job.
MariaDB 10.5 worked perfectly.
Thank you.

I went ahead, restarted the database, and prepared again (on the SSD server).
(O_DIRECT, innodb_log_file_buffering = ON)

Seems it went OK.

However, I've just noticed that SSD server is using 10.8.3.

mariabackup based on MariaDB server 10.8.3-MariaDB Linux (x86_64)

cd to /var/lib/mysql/

open files limit requested 0, set to 1048576

Renaming ./database/table.new to ./database/table.ibd

Renaming ./database/table2.new to ./database/table2.ibd

This target seems to be not prepared yet.

mariabackup: using the following InnoDB configuration for recovery:

innodb_data_home_dir = .

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = .

InnoDB: Using Linux native AIO

Starting InnoDB instance for recovery.

mariabackup: Using 10737418240 bytes for buffer pool (set by --use-memory parameter)

[Note] InnoDB: Compressed tables use zlib 1.2.7

[Note] InnoDB: Number of transaction pools: 1

[Note] InnoDB: Using crc32 + pclmulqdq instructions

[Note] InnoDB: Using Linux native AIO

[Note] InnoDB: Initializing buffer pool, total size = 10.000GiB, chunk size = 10.000GiB

[Note] InnoDB: Completed initialization of buffer pool

[Note] InnoDB: Starting crash recovery from checkpoint LSN=10631282567778

[Note] InnoDB: Starting final batch to recover 8897 pages from redo log.

Last binlog file , position 0

completed OK!

I then tried sending to the HDD server and it worked too:

mariabackup based on MariaDB server 10.8.4-MariaDB Linux (x86_64)

cd to /var/lib/mysql/

open files limit requested 0, set to 1048576

Renaming ./database/table.new to ./database/table.ibd

Renaming ./database/table2.new to ./database/table2.ibd

This target seems to be not prepared yet.

mariabackup: using the following InnoDB configuration for recovery:

innodb_data_home_dir = .

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = .

InnoDB: Using Linux native AIO

Starting InnoDB instance for recovery.

mariabackup: Using 10737418240 bytes for buffer pool (set by --use-memory parameter)

[Note] InnoDB: Compressed tables use zlib 1.2.7

[Note] InnoDB: Number of transaction pools: 1

[Note] InnoDB: Using crc32 + pclmulqdq instructions

[Note] InnoDB: Using Linux native AIO

[Note] InnoDB: Initializing buffer pool, total size = 10.000GiB, chunk size = 10.000GiB

[Note] InnoDB: Completed initialization of buffer pool

[Note] InnoDB: File system buffers for log disabled (block size=512 bytes)

[Note] InnoDB: Starting crash recovery from checkpoint LSN=10631282567778

[Note] InnoDB: Starting final batch to recover 8897 pages from redo log.

[Note] InnoDB: To recover: 683 pages from log

Last binlog file , position 0

completed OK!

Still very concerned that the rest of the days the backups were corrupt...

nunop, I’m sad to read that backups are not working reliably for you. We do test the backup component, but it is mostly with small data sets. Recently, I implemented the a log record of additional page checksums (MDEV-18976), which will be written in debug builds. That should improve the chances of detecting mismatch between the page contents and the log. You could try enabling the additional validation by the following patch:

diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc

index cc24615fd04..e93b1da5d1f 100644

--- a/storage/innobase/mtr/mtr0mtr.cc

+++ b/storage/innobase/mtr/mtr0mtr.cc

@@ -1348,7 +1348,7 @@ std::pair<lsn_t,mtr_t::page_flush_ahead> mtr_t::do_write()

   size_t len= m_log.size() + 5;

   ut_ad(len > 5);

-#ifndef DBUG_OFF

+#if 1

   if (m_log_mode == MTR_LOG_ALL)

   {

     m_memo.for_each_block(CIterate<WriteOPT_PAGE_CHECKSUM>(*this));

Note: It will slow down both the server and anything that processes the log (especially crash recovery and preparing backups).

The only recovery bug that I am currently aware of as affecting the 10.8.4 release is MDEV-29282. It should only affect the recovery of DDL operations that rename files. I do not think that it can explain this failure.

I am afraid that we will need a reproducible test case before we are able to do anything. I have the feeling that the inter-process communication via the file system is a fragile design, and maybe even more so when the file system cache is disabled. We really should implement backup in the server (MDEV-14992), not only because of reliability but performance (MDEV-29043). In any case, it is always a good idea to prepare the backup as soon as possible, because without doing that, you cannot really know if the backup is valid.

Which format were the affected data files created in? Were they created before innodb_checksum_algorihtm=full_crc32 (MDEV-19534) was enabled by default?

Hi marko

Thank you for your reply.

MDEV-18976 - To enable that debug block, I assume I'd have to rebuild MariaDB?
If that is necessary to do in the production server, unfortunately that will be out of question. But if it helps doing that just on the other servers where I try to prepare the backup, I can do that.
Please let me know if that's the case, and how I can do it. (I've never built MariaDB before)

MDEV-29282 - True. I don't seem to be getting that.

MDEV-14992 - I was trying to "prepare" on the actual production server, but it fails right away with "srv_start()" or something similar (I don't have the log with me now, but it didn't give much detail of the fail, just "srv_start()"), which I assume it's because "mariadb" is already running, and "prepare" likely means to start a new process or so. I can consider running a Docker container that prepares the backup, before sending it over to the separate server, though.

innodb_checksum_algorithm=full_crc32 (MDEV-19534) - I have never had this one listed in "my.cnf", so if the default is "full_crc32" since MariaDB 10.5.0, then that means it's been "full_crc32" for me since 10.5, where backups/restores were working perfectly for me.

marko

Strange... Just ran "mariabackup --prepare" on the main production server and it prepared the backup without any problem, this time... so I'm not sure why it was crashing right away yesterday - but it wasn't giving me any explanation, so I assumed it was because "mariadb" was already running.

Anyway - today's backup was successfully prepared on both servers, so that's good...

Also, surprisingly, even though there were many table swaps too (Renaming ...new to ...ibd), MDEV-28974 didn't happen.

nunop, the code to "apply" (validate) the OPT_CHECKSUM record is always present. The code to write those records is only enabled in debug builds by default. The main purpose of these records is internal testing.

Preparing a backup can consume quite a few resources. It could be a good idea to do that somewhere else than the host that runs the database server.

The question regarding innodb_checksum_algorithm=full_crc32 is: When were your data files created or rebuilt (by something like OPTIMIZE TABLE)? It is possible that you still have a few files in an older format where the checksum is more sloppy, meaning that mariadb-backup is unable to detect some intermittent corruption when a data page is being read by backup and written by server at roughly the same time. You could try enabling the setting innodb_checksum_algorithm=strict_full_crc32 so that even for the old data files, only a crc32 checksum (actually an exclusive OR of two CRC-32C) will be accepted.

marko, thanks!

Ah yeah - in that case I'll keep "prepare" happening only on the other server. That's what it's been doing so far.

Thank you - I understand now your question about innodb_checksum_algorithm.

I tried now to prepare one of those corrupt backups after changing innodb_checksum_algorithm=strict_full_crc32 in the my.cnf on that server, but I got the same issue.

To do this test, is the change only needed on the server that runs "prepare", or do I need to change it on the production server too, before the backup?

When were your data files created or rebuilt (by something like OPTIMIZE TABLE)? It is possible that you still have a few files in an older format where the checksum is more sloppy

Yeah, I've been using MariaDB since 10.0 I think, so very likely there are many tables that haven't been rebuilt since.

Although, looking at the tables appearing in the errors, I'm seeing some tables that have been for sure created while I was using 10.5. So it might not be innodb_checksum_algorithm's issue.

This could potentially be explained by MDEV-29383.

I am now even more convinced that this can be explained by MDEV-29383. I am sorry about this regression.

marko No problem. I'm thankful for MariaDB existing and being maintained, and I'm glad at least I was able to get a valid backup since I raised this Issue.

I'm also glad you were able to find the cause for this! I thought it would be harder to figure out. Hopefully that's it.

Once the fix gets tested, will it be released asap, or will it wait until the scheduled Quarter release?

Thanks!

This started happening again.

In addition to MDEV-29383, a more likely cause of this is MDEV-29374, also caused by MDEV-13542.

Thanks marko
Today I reported another different behavior - MDEV-29422
I wonder if that's related to one of those three issues.

nunop, did you have a chance to upgrade to 10.8.5 yet? Does that work for you?

Hi marko
Thank you very much for checking.

I upgraded everything to 10.8.5 as soon as it came out!

So far, so good. The backups and restores seem to be working fine (with DDL changes, crash recovery from checkpoint, segfault, SELECT *, etc). I have only tested the restore on the SSD server.
Seems to be very stable.

MDEV-29097 is still happening (but I know it wasn't expected to be fixed in 10.8.5).

Have a great day. Thank you for all your support.

nunop, thank you, this is great news. To be on the safe side, I would wait for additional feedback from you in a week or two, before claiming that this was fixed in 10.8.5.

nunop, are your backups still doing fine in 10.8.5?

marko - working perfectly!!
Thank you very much.
Have a great week.

nunop, thank you. This was fixed by MDEV-29383 and MDEV-29374.