InnoDB Errors - Can't Start MariaDB After Unexpected Shutdown
A few hours ago, a Windows VM that I was using to run MariaDB experienced an unexpected shutdown. After powering the VM back on, I can no longer start the MariaDB service. I checked the Windows Event logs, and saw the following entries:
- InnoDB: The size of tablespace file './nextcloud/oc_mail_local_messages.ibd' is only 49152, should be at least 65536!
- InnoDB: Missing FILE_CHECKPOINT at 14003348429 between the checkpoint 14003346939 and the end 14003413504.
- InnoDB: Plugin initialization aborted with error Generic error
- Plugin 'InnoDB' init function returned error.
- Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
- Unknown/unsupported storage engine: InnoDB
- Aborting
These same 7 lines were repeated about 5 times. The .ibd file it's referencing is for a Nextcloud instance, running on a different VM (Linux). But Nextcloud comes with multiple components/apps, and I'm not sure which one it is used by. The Linux VM was not powered on at the time, so Nextcloud itself wasn't accessing MariaDB when the unexpected shutdown occurred. But I still wonder if some queued transactions didn't go through, though. From what I can tell, MariaDB may have been in the middle of one or more transactions when the event took place.
After a preliminary (low-effort) search, it looks as though "oc_mail_local_messages.ibd" is an individual table used by Nextcloud. I'm not sure if I'm right, since I inferred it (indirectly) from this snippet:
"When InnoDB needs to store general information relating to the system as a whole, rather than a specific table, the specific file it writes to is the system tablespace."
The phrase, "rather than a specific table", led me to that assumption. But I could be completely wrong.
When I searched for "Missing FILE_CHECKPOINT at" with Google, I got this as the first hit:
- https://serverfault.com/questions/1094543/mysqld-fails-to-start-due-to-innodb
"InnoDB could indeed not recover from the last logfile named : ib_logfile0 As I didn't need to save anything, I did simply remove /var/lib/mysql/ib_logfile0"
So InnoDB was attempting to recover from a logfile?
When I searched for "Plugin 'InnoDB' registration as a STORAGE ENGINE failed." with Google, I saw these pages:
- https://stackoverflow.com/a/61187808
Both of them recommended renaming or removing "ib_logfile[n]". You can replace [n] with an integer like 0, 1, etc.
Searching up "Unknown/unsupported storage engine: InnoDB" caused me to find these sources:
- https://serverfault.com/a/597391
- https://stackoverflow.com/a/41577894
More people suggesting the removal or renaming of "ib_logfile[n]".
At this point, I decided to check my backups. To my horror, the one thing I haven't backed up yet is the partition/volume holding my MariaDB data. I am obviously a fool. With no backup to rely on currently, I went on and moved "ib_logfile[n]" to a different folder. After that, I attempted to start MariaDB. During my googling, I did encounter this page:
- https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
I'm not sure if I need it just yet, but I am the same person that run a database with no backups. Is removing the log file actually a fix for this, or is there more work to be done?
P.S. And yes, I will start backing up my MariaDB data from here on.
Answer Answered by Marko Mäkelä in this comment.
Questionable advice like the one that you encountered motivated me to implement MDEV-27199. That is, starting with MariaDB 10.8, InnoDB will no longer attempt to recreate a missing log file.
The impact of setting innodb_force_recovery=6 or removing the write-ahead log depends on how many outstanding changes were lost by that, and which versions of modified pages had been last written from the buffer pool to the data files. The database contents could appear to be fine while some data is actually inconsistent. The database could also crash on an attempt to follow a corrupted pointer (say, when after a B-tree page split, only some of the involved pages had been written back). MDEV-13542 in MariaDB 10.6.9, 10.7.5, 10.8.4, 10.9.2 removed many of the crashes but ironically introduced two recovery bugs that were fixed in the unscheduled updates MariaDB 10.6.10, 10.7.6, 10.8.5, 10.9.3: MDEV-29374 and MDEV-29383.
My talk https://archive.fosdem.org/2022/schedule/event/mariadb_innodb/ explains how the InnoDB crash recovery works and some performance improvements that were implemented in MariaDB 10.8.
It is always a good idea to not only take regular backups of important data but also to test that the data can be recovered. When using MariaDB Backup, I recommend running mariadb-backup --prepare on the freshly backed-up data.
