[MDEV-32615] InnoDB: Missing FILE_CREATE... before FILE_CHECKPOINT returns after a year Created: 2023-10-28  Updated: 2024-01-08  Resolved: 2024-01-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Aria, Storage Engine - InnoDB
Affects Version/s: 11.1.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Tomasz Assignee: Marko Mäkelä
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Development laptop, Windows 11 Pro (10.0.22621 Build 22621)


 Description   

MariaDB 11.1.2-GA on Windows 11 Pro crashes every couple of days, sometimes couple of hours with "InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT". Having no other way (details below and in linked pages) I decided to throw it away and reinstall whole stack again. It works for another couple of days and then started to crash (actually: fails to start) with exactly the same error in the same conditions.

I am a total newbie an may be wrong, but from my perspective this is a regression of either: MDEV-28870, MDEV-31410 or MDEV-28976.

All the details, including all the attempts of self-resolve this issue are given in two connected questions at Server Fault and Database Administrators. There is also my own answer to the first one. It provides more details and brings some resolution attempts tried.

Here only some quick summary:

1. The whole story started with April 2023 edition (latest) of XAMPP 8.2.4 which claims to have MariaDB 10.4.28 on-board. It was a "never-ending story" of MariaDB running for couple of days and then being unable to start with:

mysqld.exe: Aria engine: checkpoint failed
mysqld.exe: Can't sync file 'C:\XAMPP\mysql\data\aria_log.00000001' to disk (Errcode: 9 "Bad file descriptor")
mysqld.exe: Error writing file 'C:\XAMPP\mysql\data\aria_log.00000001' (Errcode: 9 "Bad file descriptor")

2. Not being able to do anything (every clean reinstall of whole XAMPP or just MariaDB was always lasting for no more than five days) I have decided to upgrade to MariaDB 11.1.2-GA.

Here the "never-ending story" started back only this time the problem is with:

InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT

Thing that I have tried to resolve the above:

  • Setting innodb_force_recovery on any value
  • Deleting ib_logfile0
  • Setting innodb_log_file_size = 48M
  • Setting innodb_buffer_pool_size = 10M

The only thing that I have found working and able to resolve the above are hardcore solutions of:

  • Deleting my corrupted files
  • Reinstalling MariaDB
  • Reinstalling whole XAMPP stack

However, I cannot repeat these tasks over and over again.

 Comments   
Comment by Marko Mäkelä [ 2023-10-29 ]

Because you did not mention mariadb-backup, we can rule out the problem scenarios documented in MDEV-31410.

The reason for a crash recovery failure like this is a bug in the InnoDB checkpoint logic. Just recently, two more bugs in that area have been found and fixed: MDEV-31826 (affecting operations that delete or rebuild .ibd files) and MDEV-32552 (affecting any operation that may free InnoDB data pages, such as ROLLBACK or the purge of history after DELETE).

The next time this occurs, can you please preserve a copy of the ib_logfile0 and provide it to us for further analysis?

Comment by Tomasz [ 2023-10-29 ]

Thanks for taking care of this issue. I will definitely provide you with the copy of ib_logfile0 file, whenever next-time crash appears.

However, as mentioned in one of the linked posts, I am currently at the very end stage of my project and simply cannot afford another crash-down and forced-reinstall. So I am running (for the next 3-4 weeks, until project's end) my local server in "never shutdown, always hibernate" mood. So I may not be able to provide you with any file before November/December month break.

Can you clarify one more thing (as I didn't get such answer at the source – i.e. XAMPP community). What causes that MariaDB released with XAMPP in April 2023 has 5.4.28 as version number and the "newest GA" version that I wanted to install over it has 11.1.2 as version number? I understand that there were no six major updates (major version changes) since April. So why such difference in version numbering scheme.

I am mostly running under XAMPP, so MariaDB 5.4.28 / April edition. I have only upgraded it once to 11.1.2 to see, if that resolves the issue. Are you interested in ib_logfile0 file from 5.4.28 version? Or just the newest one, 11.1.2 version?

Comment by Sergei Golubchik [ 2023-10-29 ]

There never was "MariaDB 5.4". The next release series after 5.3 was 5.5. But XAMPP site says that XAMPP versions 8.0.28, 8.1.17 & 8.2.4 have "MariaDB 10.4.28" — such a version exists, I'll assume "5.4.28" is a typo, and you meant 10.4.28.

10.4 series is our oldest still supported LTS release series, it will reach EOL in June 2024.
10.11 series is our newest LTS release series, first released in February this year.

See https://mariadb.org/about/#maintenance-policy — more versions and dates there.

XAMPP could've moved to 10.5 or 10.6 years ago, but as 10.4 worked for them, they didn't feel like they needed to. There is nothing inherently wrong with that approach.

Comment by Tomasz [ 2023-10-29 ]

You are correct that the Downloads page → What's Included box says MariaDB 10.4.28. I was however mislead by their typo in their blog:

These installers include the next main components: (...) MariaDB 5.4.28 (...)

I'll try to fix 5.4.28 → 10.4.28 wherever possible. Thanks for pointing this out.

Comment by Marko Mäkelä [ 2023-10-30 ]

For what it is worth, the reported error message cannot be from MariaDB Server 10.4. The log record format was changed and the internal names for the records renamed in MDEV-12353 (MariaDB Server 10.5). In MDEV-14425 (MariaDB Server 10.8), the log file format was changed further.

Comment by Sergei Golubchik [ 2023-11-27 ]

trejder, did you have any more crashes? You wrote "I will definitely provide you with the copy of ib_logfile0 file, whenever next-time crash appears."

If you didn't — feel free to ignore this, it'll be closed after a month of no activity (and reopened, if you provide more information later). This my comment will keep it open till the end of December.

Comment by Tomasz [ 2023-11-27 ]

Sergei, I haven't faced any new crashes, because for past month I am running MariaDB as Windows 11 system service. Where crashes does not occur.

I can't switch back to non-service run (where crashes occur) because I am and the go-live moment of my project and can't waste any more hours to trying to reinstall MariaDB on my dev environment.

I will try to do some more tests on non-service, crash-prone run a couple of weeks later when my project is over, sorry.

Comment by Sergei Golubchik [ 2024-01-08 ]

trejder, normally we close issues where we don't have enough information to do anything and the issue didn't have any activity for a month. Now this my comment counts as "activity", so the timer here is reset, but if nothing happens we'll close it in early February. Don't worry, though, there's no hurry, you can still comment any time you like and we'll reopen the issue as needed.

Comment by Tomasz [ 2024-01-08 ]

Sergiei, I haven't provided any new feedback so far, because I wasn't able to reproduce this issue again. For the sake of stability of my project, I changed my local server configuration to run database server as a service. When run as a service it never faces the described problem. Yes, go ahead and close this ticket. If I ever manage to reproduce this issue again, I'll file another one and link to this one. Thank you.

Generated at Thu Feb 08 10:32:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.