Latest docker release for unraid corrupted MariaDB/InnoDB database

[TheOneOgre]

I am assuming this is the basis for your zoneminder.unraid docker, which does not have an issues page.

Everything was running fine until I manually updated the docker container, afterwards, MariaDB failed to start. Using the environment variable to get the docker up and running to diagnose, I am presented with this error from MariaDB

2021-06-01 13:17:07 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

The only thing that has changed was the recent update 2 days ago to the container, zoneminder was already updated to 1.36.3 and working prior to this.

Any help would be greatly appreciated.

[TheOneOgre]

I resolved the database issue on my own. The error seemed to be related to this helpful tip by InnoDB: you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files as when running through all mysql diagnostics, all databases came back clean, but only after clearing all innodb log files and remaking the database, did the server start back up cleanly.

Not sure if this is related to a change in the docker, or just a fluke issue that appeared, but if anyone else experiences this, it's nice to keep a trail.

[fiercedeitylink]

Not sure if it's related, but it updated recently and now I'm getting: * Starting MariaDB database server mysqld ...fail!

and

Jun 6 21:17:32 5b675a8a0bbe zmpkg[22385]: ERR [Error reconnecting to db: errstr:Unknown database 'zm' error val:] Jun 6 21:17:32 5b675a8a0bbe zmdc[830]: ERR [Error reconnecting to db: errstr:Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) error val:]

I tried disabling apparmor (based on some old posts), but it didn't work. I'm not sure what steps to take, since I can't connect to mysql in the container. I might just start over from scratch, but it's kind of a bummer because it was working fine for a while.

[TheOneOgre]

Not sure if it's related, but it updated recently and now I'm getting: * Starting MariaDB database server mysqld ...fail!

and

Jun 6 21:17:32 5b675a8a0bbe zmpkg[22385]: ERR [Error reconnecting to db: errstr:Unknown database 'zm' error val:] Jun 6 21:17:32 5b675a8a0bbe zmdc[830]: ERR [Error reconnecting to db: errstr:Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) error val:]

I tried disabling apparmor (based on some old posts), but it didn't work. I'm not sure what steps to take, since I can't connect to mysql in the container. I might just start over from scratch, but it's kind of a bummer because it was working fine for a while.

Set the environment variable for debugging the docker, then check your mariadb error logs, if it's the same issue as I'm getting, I can walk you through it, just involves setting an Innodb variable to enter recovery mode, backing up the database, deleting innodb log files, then importing the database back into mariadb, and then everything works fine.

Let me know

[fiercedeitylink]

Yes, that would be extremely helpful. I was able to read /var/log/mysql/error.log and it has the same error: 2021-06-06 22:01:33 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

[TheOneOgre]

Yes, that would be extremely helpful. I was able to read /var/log/mysql/error.log and it has the same error: 2021-06-06 22:01:33 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

Following that exact link it gave you, edit my.cnf with the option it gives you below. I had to use 2 for it to properly work as 1 didn't allow MySQL to start, do NOT use anything higher unless absolutely necessary, and also double check you make a proper backup before deleting everything. At the bottom of the link it provides, it shows this section which you should follow to a tee

`Try to set innodb_force_recovery to 1 and start mariadb. If that fails, try a value of "2". If a value of 2 works, then there is a chance the only corruption you have experienced is within the innodb "undo logs". If that gets mariadb started, you should be able to dump your database with mysqldump. You can verify any other issues with any tables by running "mysqlcheck --all-databases".

If you were able to successfully dump your databases, or had previously known good backups, drop your database(s) from the mariadb command line like "DROP DATABASE yourdatabase". Stop mariadb. Go to /var/lib/mysql (or whereever your mysql data directory is located) and "rm -i ib*". Start mariadb, create the database(s) you dropped ("CREATE DATABASE yourdatabase"), and then import your most recent dumps: "mysql < mydatabasedump.sql"`

[fiercedeitylink]

Thanks a lot, that worked perfectly. Had to set innodb_force_recovery to 2 and recreating/restoring the database brought everything back to normal.

[bullmoose20]

I am having the exact same experience and will try what was posted here and let you know the results. I had to setup the NO_START_ZM:1 variable in the dlandon container on Unraid to be able to console to the container and then try to start MariaDB with a service mysql restart command. Then I found the error.log had the same errors and reference to InnoDB. Stay tuned for results....

[bullmoose20]

Well.... it was a little more complicated than I had hoped. essentially, I got the DB to a point where I could perform a mysqldump. Then I had to completely wipe the DB and perform an import of the mysqldump file.... now all seems to be fine.

[nicoandra]

I've been having the same issues reported by @bullmoose20 and @fiercedeitylink . However none of the recovery steps worked, went from 1 to 6.

Having a system crash to cause so much troubles is very inconvenient. This means that if I go on vacation and for some reason my home server loses power, even after the power coming back and the server rebooting I'll end up without ZoneMinder for the rest of my vacation.

I wonder if MySQL will be as sensitive as MariaDB to system crashes, and wondering if it wouldn't be better to move to MySQL in that case.

[dlandon]

Your best defense against this issue u UPS and backups.

[TheOneOgre]

This was an issue that I've faced even without power loss issues, so it seems to just be how zoneminder is writing to the database as all other databases hold up just fine from other services running on the machine, only zoneminder has constant issues due to updates, power downs, and other various things.

The biggest fix for this issue and all the other glaring issues I've had with zoneminder was to switch to Blue Iris. Works perfectly with no frills right out of the box l. No database corruption, saves on RAM, more CPU efficient, and overall just a much better software.

[dlandon]

I've been running Zoneminder for many years now and never had a database issue. If it doesn't work for you, that's fine.