How to fix corrupted mariadb database?

Installation Home Assistant OS
1

Hello,

Unfortunately my home-assistant_v2.db disappeared completely random ( overnight? ) from my /config/ folder. I really don’t know what happend, but it’s pretty important for me to restore all the ‘long-term’ history data… But I guess I will end up with all my data being lost, unless someone has a good solution for me?

The mariadb output logs:

[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] done.
[services.d] starting services
[services.d] done.
[09:51:15] INFO: Using existing mariadb initial system
[09:51:16] INFO: Starting MariaDB
220329 09:51:18 mysqld_safe Logging to '/data/databases/mariadb.err'.
220329 09:51:18 mysqld_safe Starting mysqld daemon with databases from /data/databases
220329 09:51:18 mysqld_safe Starting mysqld daemon with databases from /data/databases
2022-03-29  9:51:20 0 [Note] /usr/bin/mysqld (mysqld 10.4.19-MariaDB) starting as process 397 ...
2022-03-29  9:51:20 0 [Note] InnoDB: Using Linux native AIO
2022-03-29  9:51:20 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-03-29  9:51:20 0 [Note] InnoDB: Uses event mutexes
2022-03-29  9:51:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-03-29  9:51:20 0 [Note] InnoDB: Number of pools: 1
2022-03-29  9:51:20 0 [Note] InnoDB: Using generic crc32 instructions
2022-03-29  9:51:20 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2022-03-29  9:51:21 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-03-29  9:51:21 0 [Note] InnoDB: Completed initialization of buffer pool
2022-03-29  9:51:21 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-03-29  9:51:21 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=269677638126
2022-03-29  9:51:21 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 38 row operations to undo
2022-03-29  9:51:21 0 [Note] InnoDB: Trx id counter is 18210404
2022-03-29  9:51:21 0 [Note] InnoDB: Starting final batch to recover 30 pages from redo log.
2022-03-29  9:51:21 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of tablespace homeassistant/events page [page id: space=5, page number=49730]. You may have to recover from a backup.
2022-03-29  9:51:21 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 

-- removed dump due too many characters for this post

InnoDB: End of page dump
2022-03-29  9:51:22 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 3432143944, calculated checksums for field1: crc32 2618634665, innodb 1844157532,  page type 17855 == INDEX.none 3735928559, stored checksum in field2 2998307939, calculated checksums for field2: crc32 2618634665, innodb 3687756281, none 3735928559,  page LSN 62 3389662677, low 4 bytes of LSN at page end 3389664850, page number (if stored to page already) 49730, space id (if created with >= MySQL-4.1.1 and stored already) 5
2022-03-29  9:51:22 0 [Note] InnoDB: Page may be an index page where index id is 27
2022-03-29  9:51:22 0 [Note] InnoDB: Index 27 is `ix_events_time_fired` in table `homeassistant`.`events`
2022-03-29  9:51:22 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. 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.
2022-03-29  9:51:22 0 [ERROR] InnoDB: Failed to read file './homeassistant/events.ibd' at offset 49730: Page read from tablespace is corrupted.
2022-03-29  9:51:22 0 [Note] InnoDB: Set innodb_force_recovery=1 to ignore corrupted pages.
2022-03-29  9:51:22 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
2022-03-29  9:51:23 0 [Note] InnoDB: Starting shutdown...
2022-03-29  9:51:23 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-03-29  9:51:23 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-03-29  9:51:23 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-03-29  9:51:24 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-03-29  9:51:24 0 [ERROR] Aborting
220329 09:51:24 mysqld_safe mysqld from pid file /data/databases/core-mariadb.pid ended

Hopefully someone has a solution?

Thanks in advance,

2

Mariadb does not store its data in home_assistant_v2.db

3

@nickrout, thanks! I see now… (stupid me). But then still, how to solve the corrupt mariadb database?

Thanks!

4

Googling may find something generic for mariadb/mysql.

5

If you are using MariaDB, the data is not stored in home-assistant_v2.db but in a DB specific to MariaDB… On my instance as I am using MariaDB, I even deleted the home-assistant_v2.db.
This should not be the problem…

It seems your DB has been corrupted (corruption on disk ?)…

What environment are you running on ?
Do you have a daily backup ? So you can restore and restart your DB from this dayly backup.

6

@browetd thanks for your reply!

Indeed I see that home-assistant_v2.db is not needed, since the database is stored inside the mariadb docker container.

I’m running homeassistant OS - 2022.3.7

Yes I had a daily backup ( google drive app ), but unfortunately i’ve not checked it good enough last period. There are all automatically created ‘partial’ snapshots which caused that there is no ‘fully’/useable snapshot that I can use to restore everything.

So I somehow need to recover the data from inside the mariadb addon, do you have a suggestion?

Thanks,

7

There are tips on fixing it in the logs you posted. Read them.

8

@nickrout, I know. But how can I set the docker “innodb_force_recovery=1” parameter? Within homeassistant OS? Where is the docker file located?

I can’t find any relative information about this, also not on recovering mariaDB database when it’s not starting up.

9

You can try to recover by dumping to output files all tables of your HA database with select statements…

See here for more details:

https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html

Here is another interresting post:

But before all of this, you have to force the recovery to 1… as far as I understand, this can be done by editing: /etc/my.cnf (see second post)… If you are running Hass.OS, this can be done via the SSH terminal (at least on my instance, that file (/etc/my.cnf) exists)… The second post should help you to recover… The only step that will create some major problems is the one mentioning that the database is in /var/lib/mysql… This is not the case for my HA instance… good luck

10

You’ll need to enter the mariadb docker container. In ssh & webterminal

docker exec -it addon_core_mariadb bash
11

Thanks for the help.

I managed to fix it by ssh/bash into the docker container using :

docker exec -it addon_core_mariadb bash

After that I searched for the ‘ib logfiles’ and deleted them. Restarted the server, and everything seemed to be working again.

1 Like
12

i found that the above responses didnt have enough detail to walk the average user through so here is what i did in detail.

using the addon “advanced ssh & web terminal”
docker exec -it addon_core_mariadb bash
find / -type f -inmae “logfile”
this found the logfiles in /data/databases/ with a file of ib_logfile0
i deleted it with
rm ib_logfile0
then re-started the mariadb addon

hope this helps

1 Like
13

thanks for summing up. That saved my day!