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:
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.Unable to execute statement#012Database error was:#012Duplicate entry '0' for key 'PRIMARY'
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)