[SOLVED] Recreating record table

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

Post Reply
littleman_1111
Newcomer
Posts: 8
Joined: Sun Apr 26, 2015 9:57 pm
United States of America

[SOLVED] Recreating record table

Post by littleman_1111 » Sun Apr 26, 2015 10:10 pm

Hello,

I'm having a problem because the record table in my myconverg database is corrupt. Unfortunately, I don't have a good backup of the database. The record table seems to only hold information about saved recording profiles, so it should be easy to just drop the table and create an empty table and add my recording profiles back.

I tried several times to repair the table, with no success. Eventually I dropped the table and tried to recreate it using the table description on the mythtv wiki. But, I'm now getting a problem because when I try and record more than one thing, the mythfrontend log complains about multiple values for the recordid (primary key for the table).

I tried to recreate the table based on the description here:
https://www.mythtv.org/wiki/Record_table

I recreated the table in mysql using the CREATE TABLE command (see below).

The error I get when trying to set more than on thing to record (i.e., more than one row in the record table) is:
Unable to execute statement#012Database error was:#012Duplicate entry '0' for key 'PRIMARY'
One thing I noticed is that according to the description on the wiki, the recordid column should have a default value of NULL, but when I try to set the default value to NULL, mysql compains that I can't.

Can anyone tell me what I'm missing?

Code: Select all

CREATE TABLE record (
    recordid int(10) unsigned PRIMARY KEY,
    type     int(10) unsigned NOT NULL DEFAULT 0,
    chanid   int(10) unsigned NULL REFERENCES channel(chanid),
    starttime time NOT NULL DEFAULT "00:00:00",
    startdate date NOT NULL DEFAULT "0000-00-00",
    endtime   time NOT NULL DEFAULT "00:00:00",
    enddate   date NOT NULL DEFAULT "0000-00-00",
    title     varchar(128) NOT NULL DEFAULT "",
    subtitle  varchar(128) NOT NULL DEFAULT "",
    description varchar(16000) NOT NULL DEFAULT "",
    season    smallint(5) NOT NULL,
    episode   smallint(5) NOT NULL,
    category  varchar(64) NOT NULL DEFAULT "",
    profile   varchar(128) NOT NULL DEFAULT "Default" REFERENCES recordingprofiles(name),
    recpriority int(10) NOT NULL DEFAULT 0,
    autoexpire  int(11) NOT NULL DEFAULT 0,
    maxepisodes int(11) NOT NULL DEFAULT 0,
    maxnewest   int(11) NOT NULL DEFAULT 0,
    startoffset int(11) NOT NULL DEFAULT 0,
    endoffset   int(11) NOT NULL DEFAULT 0,
    recgroup    varchar(32) NOT NULL DEFAULT "Default" REFERENCES recgrouppassword(recgroup),
    dupmethod   int(11) NOT NULL DEFAULT 6,
    dupin       int(11) NOT NULL DEFAULT 15,
    station     varchar(20) NOT NULL DEFAULT "",
    seriesid    varchar(64) NULL,
    programid   varchar(64) NULL,
    inetref     varchar(40) NOT NULL DEFAULT "",
    search      int(10) unsigned NOT NULL DEFAULT 0,
    autotranscode tinyint(1) NOT NULL DEFAULT 0,
    autocommflag  tinyint(1) NOT NULL DEFAULT 0,
    autouserjob1  tinyint(1) NOT NULL DEFAULT 0,
    autouserjob2  tinyint(1) NOT NULL DEFAULT 0,
    autouserjob3  tinyint(1) NOT NULL DEFAULT 0,
    autouserjob4  tinyint(1) NOT NULL DEFAULT 0,
    autometadata  tinyint(1) NOT NULL DEFAULT 0,
    findday       tinyint(4) NOT NULL DEFAULT 0,
    findtime      time       NOT NULL DEFAULT "00:00:00",
    findid        int(11)    NOT NULL DEFAULT 0,
    inactive      tinyint(1) NOT NULL DEFAULT 0,
    parentid      int(11)    NOT NULL DEFAULT 0,
    transcoder    int(11)    NOT NULL DEFAULT 0,
    playgroup     varchar(32) NOT NULL DEFAULT "Default" REFERENCES playgroup(name),
    prefinput     int(10)    NOT NULL  DEFAULT 0 REFERENCES cardinput(cardinputid),
    next_record   datetime   NOT NULL,
    last_record   datetime   NOT NULL,
    last_delete   datetime   NOT NULL,
    storagegroup  varchar(32) NOT NULL DEFAULT "Default" REFERENCES storagegroup(groupname),
    avg_delay     int(11)    NOT NULL DEFAULT 100,
    filter        int(10) unsigned NOT NULL DEFAULT 0 REFERENCES recordfilter(filterid) );
ALTER TABLE record
    ADD KEY(type),
    ADD KEY(chanid),
    ADD KEY(title),
    ADD KEY(maxepisodes),
    ADD KEY(seriesid),
    ADD KEY(programid),
    ADD KEY(search);

Thanks,
Chris.


Here's some info about the original corruption, but it's probably irrelevant now since I've already dropped the corrupted table.
====

Code: Select all

/usr/share/mythtv/mythconverg_backup.pl --verbose

mysqldump exited with status: 2
mysqldump output:
mysqldump: Got error: 144: Table './mythconverg/record' is marked as crashed and last (automatic?) repair failed when using LOCK TABLES
====

Code: Select all

/usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl

Repaired/Optimized: `mythconverg`.`record`
Analyzed: `mythconverg`.`record`
====

Code: Select all

/usr/share/mythtv$ sudo /etc/cron.weekly/mythtv-database 

mythconverg.record
warning  : Table is marked as crashed and last repair failed
warning  : 1 client is using or hasn't closed the table properly
error    : Found key at page 4096 that points to record outside datafile
error    : Corrupt
====

Code: Select all

mysqlcheck --auto-repair --check -u root -p mythconverg

mythconverg.recgrouppassword                       OK
mythconverg.record
warning  : Table is marked as crashed and last repair failed
warning  : 1 client is using or hasn't closed the table properly
error    : Found key at page 4096 that points to record outside datafile
error    : Corrupt
mythconverg.recorded                               OK

====

Code: Select all

mysql> REPAIR TABLE record;
+--------------------+--------+----------+-------------------------------------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                                                |
+--------------------+--------+----------+-------------------------------------------------------------------------+
| mythconverg.record | repair | error    | 5 when fixing table                                                     |
| mythconverg.record | repair | error    | 5 when trying to write bufferts                                         |
| mythconverg.record | repair | Error    | Error writing file '/var/lib/mysql/mythconverg/record.MYI' (Errcode: 5) |
| mythconverg.record | repair | status   | Operation failed                                                        |
+--------------------+--------+----------+-------------------------------------------------------------------------+
4 rows in set (23.69 sec)
=====
Last edited by littleman_1111 on Sun Apr 26, 2015 11:13 pm, edited 1 time in total.

User avatar
bill6502
Developer
Posts: 1374
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Recreating record table

Post by bill6502 » Sun Apr 26, 2015 10:33 pm

Hi,

At least: `recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,

but I'd expect you'll spend more time recreating the table in SQL than
you'd spend using the GUI. Don't know what version of MythTV you're
running, but 0.26+ has *most* timestamps in UTC. You can learn how
to develop the findtime. To get the filter values, you look in recordfilter.
Your computer, your choice ;). Even the developer that knows the DB
the best frequently comments on the -users list that he wouldn't try to
do what the software knows how to do best.

littleman_1111
Newcomer
Posts: 8
Joined: Sun Apr 26, 2015 9:57 pm
United States of America

Re: Recreating record table

Post by littleman_1111 » Sun Apr 26, 2015 10:44 pm

Yeah, I'd prefer not to do it in SQL if possible, I just didn't know of any other way, and I didn't think creating an empty table in SQL would be that bad.

How would I go about recreating the table from the GUI? (Which GUI?) I didn't see that anywhere I looked.

I'm running the latest Mythbuntu distribution with mythtv 0.27.

User avatar
bill6502
Developer
Posts: 1374
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Recreating record table

Post by bill6502 » Sun Apr 26, 2015 10:49 pm

I see, you could use the source code here (for 0.27.) See the drop down
box in the upper right for other versions. (I thought you were going to
create the whole thing by hand - <assuming> again.)

EDIT: deleted reference to source code because there are (at least)
7 updates to the CREATE TABLE I cited (thanks sphery, I know
better.) The ALTER TABLE changes are in the same file, but I'll
try not to do this again.
Last edited by bill6502 on Thu Oct 01, 2015 7:45 pm, edited 1 time in total.

littleman_1111
Newcomer
Posts: 8
Joined: Sun Apr 26, 2015 9:57 pm
United States of America

Re: Recreating record table

Post by littleman_1111 » Sun Apr 26, 2015 11:11 pm

Excellent! That's exactly what I needed. I ran the CREATE TABLE from the link you provided and now I can set multiple recording rules again. Thanks.

Now I've got to set up the database backup stuff so I don't run into this again. ;)

Thanks, again.

sphery
Newcomer
Posts: 1
Joined: Fri Mar 07, 2014 2:47 pm
United States of America

Re: [SOLVED] Recreating record table

Post by sphery » Wed Sep 30, 2015 11:10 am

Be careful using the CREATE TABLE statements from InitializeMythSchema() in dbcheck.cpp to re-create tables for MythTV, since that only creates a table with the structure that was valid at the time the last DB schema rollup was performed (i.e. as of right now, that means you would have a table that was valid for MythTV 0.25) and does not include any of the changes made to that table in database updates. Because of the way database upgrades are serialized, required updates will not be performed against your newly-created table and could either break MythTV completely or cause issues (immediately upon restart or at any time in the future), depending on the specific updates that were not performed.

And in the case of the record table, there were changes to the table structure since the last DB schema rollup, so if you created the table based on the CREATE TABLE in dbcheck.cpp and were using version 0.27 of MythTV at the time, you have a corrupt schema that may or may not work properly and that may or may not cause problems when future upgrades are performed.

Post Reply