I am maintaining several nextcloud instances at fairkom, as it gives our customers back data souvereignity. In fact you host your data, calendar, addressboock by yourself (or a hosting partner you trust) and nextcloud offers nice tools for collaboration.
The photos I create on my (Google-free) smartphone are automatically synced to a private nextcloud server in our living room. It runs on a RaspBerry Pi, which is also serving as a music and TV streaming server and to which I attached a 1 TB hard disc.
Two weeks ago the washing machine in our house caused a series of AC outages (the heating unit needs to be replaced). As the Raspberry PI runs without UPS it stopped immediately and the database seemed to have an issue. Usually databases recover themselves, but probably not when being interrupted again and again. Unfortunately, the daily and weekly database dumps have been overwritten with the corrupted database content, which in fact was empty. Grrr.
I am copying here my gitlab issue documentation how I revived the database - if anybody has got the same problem.
The database did not start anymore, in fact its InnoDB engine:
Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on [page id:> Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore corruption. Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] Plugin 'InnoDB' init function returned error. Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [Note] Plugin 'FEEDBACK' is disabled. Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] Unknown/unsupported storage engine: InnoDB Aug 14 09:09:16 golm mariadbd[29145]: 2022-08-14 9:09:16 0 [ERROR] Aborting Aug 14 09:09:17 golm systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Aug 14 09:09:17 golm systemd[1]: mariadb.service: Failed with result 'exit-code'. Aug 14 09:09:17 golm systemd[1]: Failed to start MariaDB 10.5.15 database server.
Let's stop the web server first so that nextcloud is not touching the corrupted database
service apache2 stop
Created mysql_snapshot.tgz in /var/lib
Trying to start DB repair following this guide https://www.stellarinfo.com/blog/repair-innodb-table-corruption-in-mysql/ (which I do not trust in all aspects, as it is promoting a repair tool) with
innodb_force_recovery=1
in /etc/mysql/conf.d/mysql.cnf
This did not help. Removing again the line. MariaDB tries to recover anyway when starting up - you can see it in the syslog.
I am putting away the log data for the database in /var/lib/mysql with its latest changes:
mv ib_logfile0 ib_logfile0.bak
Okay, at least mysql started again.
We see at least one corrupted index table:
Database page corruption on disk or a failed read of file './nextcloud/oc_file_locks.ibd Aug 14 10:25:21 localhost mariadbd[2137]: 2022-08-14 10:25:21 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 35605653, calculated checksums for field1: crc32 2172406703, innodb 3279121729, page type 17855 == INDEX.none 3735928559, stored checksum in field2 2776663202, calculated checksums for field2: crc32 2172406703, innodb 2388637187, none 3735928559, page LSN 3 3253475813, low 4 bytes of LSN at page end 3099864687, page number (if stored to page already) 736, space id (if created with >= MySQL-4.1.1 and stored already) 159 Aug 14 10:25:21 localhost mariadbd[2137]: 2022-08-14 10:25:21 0 [Note] InnoDB: Page may be an index page where index id is 437 Aug 14 10:25:21 localhost mariadbd[2137]: 2022-08-14 10:25:21 0 [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. Aug 14 10:25:21 localhost mariadbd[2137]: 2022-08-14 10:25:21 0 [ERROR] InnoDB: Failed to read page 736 from file './nextcloud/oc_file_locks.ibd': Page read from tablespace is corrupted.
Testing nextcloud - starting webserver. Login to nextcloud works, but no files are shown and then the database server crashes again. So it seems the database is trying to recover all tables and the fails again. But which tables are corrupted?
Trying to start mysql with `innodb_force_recovery=2` according to https://mariadb.com/kb/en/innodb-recovery-modes/ but that is giving a
mysql: unknown variable 'innodb_force_recovery=2'
While trying
MariaDB [nextcloud]> CHECK TABLE oc_file_locks;
ERROR 2013 (HY000): Lost connection to MySQL server during query
connection gets lost - the DB crashes and restarts.
So we delete all records in the table see https://central.owncloud.org/t/file-is-locked-how-to-unlock/985
MariaDB [nextcloud]> DELETE FROM oc_file_locks WHERE 1;
ERROR 1034 (HY000): Index for table 'oc_file_locks' is corrupt; try to repair it
At least the database is starting again and we see which table is corrupted:
MariaDB [nextcloud]> CHECK TABLE oc_file_locks; +-------------------------+-------+----------+----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------+-------+----------+----------------------------------------------+ | nextcloud.oc_file_locks | check | Warning | InnoDB: Index PRIMARY is marked as corrupted | | nextcloud.oc_file_locks | check | error | Corrupt | +-------------------------+-------+----------+----------------------------------------------+ MariaDB [nextcloud]> OPTIMIZE TABLE oc_file_locks; +-------------------------+----------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------+----------+----------+---------------------------------------------------------+ | nextcloud.oc_file_locks | optimize | Error | Table 'nextcloud.oc_file_locks' doesn't exist in engine | | nextcloud.oc_file_locks | optimize | status | Operation failed | +-------------------------+----------+----------+---------------------------------------------------------+ 2 rows in set (0.001 sec)
oc_file_locks is not so important at this home use nextcloud - it would have locked simultaneous writing actions. So we might delete all records in there. We see in the syslog
> Aug 14 12:47:51 localhost mariadbd[3937]: 2022-08-14 12:47:51 8 [ERROR] InnoDB: Table `nextcloud`.`oc_file_locks` is corrupted. Please drop the table and recreate.
We login to the database with mysql (get credentials from oyur nextcloud config.php) and do a
DROP TABLE oc_file_locks; CREATE TABLE IF NOT EXISTS `oc_file_locks` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `lock` int(11) NOT NULL DEFAULT '0', `key` varchar(64) COLLATE utf8_bin NOT NULL, `ttl` int(11) NOT NULL DEFAULT '-1', PRIMARY KEY (`id`), UNIQUE KEY `lock_key_index` (`key`), KEY `lock_ttl_index` (`ttl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5275 ;
Check the result:
MariaDB [nextcloud]> CHECK TABLE oc_file_locks; +-------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+-------+----------+----------+ | nextcloud.oc_file_locks | check | status | OK | +-------------------------+-------+----------+----------+
Hurray, databases seems to be fixed now!
Starting again web server
service apache2 start
We still had in the syslog
> Aug 14 13:46:35 localhost mariadbd[3937]: 2022-08-14 13:46:35 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the Inno > DB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
so stopping again the DB and moved away the InnoDB recovery log:
sudo mv ib_logfile0 ib_logfile0.bak3
Okay, nextcloud is up and running again. We very lucky, as just the not very important `oc_file_locks` table got corrupted.
Finally I removed all ib_logfile0.bak* files.
Now adding also a monthly backup procedure and I am planning to add a powerbank as an uninterruptable power supply.