Corrupted database 'program' table MythTV 0.27

For discussion of topics specific to MythTV on linux
Post Reply
nmw01223
Junior
Posts: 25
Joined: Sat Jan 16, 2016 11:52 am
Great Britain

Corrupted database 'program' table MythTV 0.27

Post by nmw01223 »

I have a very old copy of MythTV 0.27 that has been running fine on a Linux 14.04 machine for some years (MythBuntu installation). I do not have a good backup of it.

However, it has just come up with a database corruption - the 'program' table. I tried to get the CREATE TABLE statement from the MySQL command line client, but it just said the table was corrupted.

What I need to do - I guess - is drop the table and create it again, but I need the CREATE TABLE statement.

Where can I find the database creation statements for such an old version?
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Corrupted database 'program' table MythTV 0.27

Post by bill6502 »

I assume you've already tried mysqlcheck and mysqlrepair.

Not sure if the above means you don't have any backups or just one
or more bad ones. If you have some, pick the latest, unzip it and look
for the CREATE TABLE `program` (... line. That has the information
used to create the table for you version of MythTV.

The source SQL is available, BUT, then you must go through every applicable
DBSchemaVer change and look for updates to the table.
nmw01223
Junior
Posts: 25
Joined: Sat Jan 16, 2016 11:52 am
Great Britain

Re: Corrupted database 'program' table MythTV 0.27

Post by nmw01223 »

Thanks for your reply.

I tried CHECK TABLE and REPAIR TABLE from the mysql command line tool. Didn't work. Nor did 'SHOW CREATE TABLE program'.

I have a full partition image of the system partition, but it is 3 1/2 years old. However this is just a working TV PVR, no updates have been applied since, so it is representative of the current system, though the recordings etc will not match the database (actual recordings were not archived - too big). It's an installed image, no source code. Where in there (if at all) might I find the 'CREATE TABLE program' statement? I assume it must be in the database somewhere since you can get it as above for a good table?

Is there any way of persuading the backend setup to recreate the database or specific tables within it? I assume it must do that when first installed.
nmw01223
Junior
Posts: 25
Joined: Sat Jan 16, 2016 11:52 am
Great Britain

Re: Corrupted database 'program' table MythTV 0.27

Post by nmw01223 »

UPDATE:

Think I might have fixed it.

Tried mysqlrepair to see if it is any different to CHECK/REPAIR TABLE using the mysql tool, and it isn't. The error though on repair is that it cannot create program.TMD. Looked that up and found as it's a tmp file, others just deleted it.

Therefore deleted /var/lib/mysql/mythconverg/program.TMD (as superuser), then ran the repair again.

This time it worked. MythTV appears OK again and listings are available. Allowed me to search for and set up a recording, anyway, which it wouldn't previously.

Did a complete dump of MySQL (mysqldump --user=... --password=... --all-databases --events > ...) so at least I have something containing the create table definitions in the future, and also a new partition backup of the system partition. Better late than never.
Post Reply