[mythtv-users] Sudden seemingly-random database issues

[Brian S]

On 09/26/2014 02:41 PM, Nicolas Krzywinski wrote:
>
> Regarding your issue:
> MySQL is the database server, which seems to be fine in your case. But 
> to provide any functionality, a database server needs a storage 
> engine, InnoDB in your case (MyISAM is an example of another engine). 
> Therefore, a working storage engine is mandatory for successful mysql 
> startup - which fails in your case.
> It says the InnoDB data files are corrupt and it can not read/build 
> the table structure therefore. Ignore any auxiliary recovery actions 
> therefore (e. g. apparmor) and concentrate on repair of the InnoDB 
> file(s).
> I did the same (or similar) as you some times ago and I can remember 
> that it was possible to really nice and quickly fix this via the 
> mysqladmin command .... as far as I remember. This command did worked 
> without the need to start the mysql server process prior. You have to 
> execute it on the affected machine locally, of course. Now I am not 
> sure about the authentication part .... it may be possible that you 
> are authenticated automatically for the mysqladmin command if it is 
> executed as root, but this is really unsure now.
>
Thanks Nicolas and Tom for the direction.
Here's what I've attempted so far to repair the InnoDB files and/or 
locate which ones are causing the issue. (First I saved a copy of 
/var/lib/mysql).
First based on other mythtv users doing so for their database woes, I 
tried the myisamchk utility by running myisamchk -f -r *.MYI . This 
scrolled through the tables in the folder in question ( ran it on both 
the /var/mysql/mythtv and /var/mysql/mysql folders) and indicated it was 
fixing indexes in several tables (I can attach output if helpful). 
However when I tried to start mysql afterwards, I got the same purge 
error ("/InnoDB: Failing assertion: purge_sys->purge_trx_no <= 
//purge_sys->rseg->last_trx_no/").

Next, I was able to get the mysql daemon to start by adding the 
"innodb_force_recovery = 2" flag to /etc/mysql/my.cnf. First tried =1 
and got the same error as previously noted.

With mysql up and running I checked all databases/tables with:
mysqlcheck -c -u root -p --all-databases
Output indicated all tables in both the mysql database and the 
mythconverg database were "OK." (again can attach output if useful).
This may have been a bad idea, but with mysql running with the force 
recovery option, I was able to start the BE process successfully and 
begin watching a program on the FE. The DB check on mythweb also 
indicated all the mythconverg tables were ok.

So based on what I have been able to find, the next step seems to be to 
dump all the tables and then restore a copy of them, however based on 
what I have so far, a corrupt table doesn't seem to be the problem(?), 
so I'm not sure dumping/restoring is the best way to go. Would this be 
the logical next step, or should I try something else?

Thanks again for all your help.