tdmyth at yahoo
Jun 3, 2006, 2:21 AM
Post #4 of 12
(1479 views)
Permalink
|
Steve Daniels wrote:
> > -----Original Message-----
> > From: mythtv-users-bounces[at]mythtv.org
</ym/Compose?To=mythtv-users-bounces[at]mythtv.org&YY=29373&y5beta=yes&y5beta=yes&order=down&sort=date&pos=0>
[mailto:mythtv-users-
> > bounces[at]mythtv.org
</ym/Compose?To=bounces[at]mythtv.org&YY=29373&y5beta=yes&y5beta=yes&order=down&sort=date&pos=0>]
On Behalf Of Tom Dale
> > Sent: 23 May 2006 15:24
> > To: mythtv-users[at]mythtv.org
</ym/Compose?To=mythtv-users[at]mythtv.org&YY=29373&y5beta=yes&y5beta=yes&order=down&sort=date&pos=0>
> > Subject: [mythtv-users] Still stuck...MythTV Dead
> >
> > I realize that my previous thread may have been difficult to follow, but
> > with the help I got from several folks, I believe that we've narrowed
> > down the problem. I'm sure that someone here can help me. I completely
> > re-installed from scratch using Mythdora and restored my database per
> > the MythTV FAQ. Live TV does not work, prior recordings do not show up,
> > MythWeb displays errors. This box has run for over a year with various
> > Fedora or Mythdora installations. I don't know what broke it, but
> > restoring the database seems to restore the problem.
> > Thanks to help from this list, here's what I found in my
> > mythbackend.log that looks suspicious:
> >
> > 2006-05-20 03:02:24.244 DB Error (Error in JobQueue::GetJobs(), Unable
> > to query list of Jobs in Queue.):
> > Query was:
> > SELECT j.id, j.chanid, j.starttime, j.inserttime, j.type, j.cmds,
> > j.flags, j.status, j.statustime, j.hostname, j.args, j.comment,
> > r.endtime FROM jobqueue j, recorded r WHERE j.chanid = r.chanid AND
> > j.starttime = r.starttime ORDER BY j.inserttime, j.chanid, j.id;
> > Driver error was [2/1146]:
> > QMYSQL3: Unable to execute query
> > Database error was:
> > Table 'mythconverg.jobqueue' doesn't exist
> >
> > To see the full contents of the log, mysqld.log, error
> > logs and previous
> > thread history, see
> >
> > http://www.gossamer-threads.com/lists/mythtv/users/204612
> >
> > Thank you, in advance, for any help you can provide!
> > -Tom-
> >
>
> Hi Tom,
>
> When you did the fresh install, before you restored the db, did live tv
> work? Did mythweb work?
>
> Can you check that the jobqueue table exists or not in the mythconverg
> database?
>
> Can you verify it exisists in the backup?
>
> What steps did you follow?
>
> These?
>
> http://www.mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5
>
> 23.5 Saving or restoring the database
>
> See the mysqldump manpage for more information.
>
> $ mysqldump -u mythtv -pmythtv mythconverg -c > mythtv_backup.sql
>
> To restore: (assuming that you've dropped the database)
>
> $ mysql -u root
> mysql>create database mythconverg;
> mysql>exit
> $ mysql -u mythtv -pmythtv mythconverg < mythtv_backup.sql
>
> You may need to alter the MySQL permissions if this database is being
> shared
> with multiple systems. See the Modifying access to the MySQL database
> for
> multiple systems section for more information.
>
> If you can scrub the entire database (keeping you back up) then start a
> new
> database, get myth configured and working.
>
> Then once it's all working, live tv, mythweb etc, follow these
> instructions
> from a tiny bit down from where the above are.
>
> http://www.mythtv.org/docs/mythtv-HOWTO.html#toc23.7
>
> This assumes that you will be moving your data to newer / bigger
> hardware
> and don't want to lose your programs.
>
> The first step is to create a database backup as demonstrated in an
> earlier
> section.
>
> Next, you will extract only the data that is relevant to the programs:
>
> $ grep "INSERT INTO record " mythtv_backup.sql > restore.sql
> $ grep "INSERT INTO recorded " mythtv_backup.sql >> restore.sql
> $ grep "INSERT INTO oldrecorded " mythtv_backup.sql >> restore.sql
> $ grep "INSERT INTO recordedmarkup " mythtv_backup.sql >> restore.sql
>
> NOTE: Newer versions of mysqldump place backticks around the table names.
> Backticks are not the same as apostrophes! On a typical North American
> keyboard, backticks are located to the left of the "1" key, on the same
> key as the tilde. Also, because the bash shell may try to interpret the
> backticks, make sure you use a \ before each one.
>
> If your restore.sql file is empty, you'll need to re-run the commands
> like this:
>
> $ grep "INSERT INTO \`record\` " mythtv_backup.sql > restore.sql
> $ grep "INSERT INTO \`recorded\` " mythtv_backup.sql >> restore.sql
> $ grep "INSERT INTO \`oldrecorded\` " mythtv_backup.sql >> restore.sql
> $ grep "INSERT INTO \`recordedmarkup\` " mythtv_backup.sql >> restore.sql
>
> Note the space after the table name and the ">>" to append to the file
> for all but the first grep. "recordedmarkup" is huge and may be hundreds of
> thousands of lines if you had lots of hours of recordings.
>
> After you have moved the data files to the new hardware, configure
> MythTV using the mythtv-setup program as you normally would with a standard
> MythTV installation.
>
> At this point we will restore the information about your programs back
> into the database:
>
> $ mysql -u mythtv -pmythtv mythconverg < restore.sql
>
> After successful insertion of the data you may delete the restore.sql
> file.
>
>
> Hope that helps :-)
>
> Steve Daniels
Steve/Allan,
Thank you! I know this is a bit late, but I have not had much time to
put into this project. YES! This helped a bunch. Allan was right about
breaking things down into separate problems. As you both pointed out,
we needed to find out if LiveTV and MythWeb worked immediately after
re-installing. As it turns out, the SQL database 'mythconverg' was
corrupted, so when we reinstalled MythDora, LiveTV and MythWeb worked as
long as we did not restore the database. With Steve's help, we were
able to recover the previous recordings and import them into the new
database. Steve helped us recover the things that we wanted from a
corrupt database.
Unfortunately, our problems have mutated and we still have a dead
system. After fixing the database, we could not get mythfilldatabase to
work. It seems to log in, find data, download it to temporary location
and then it decides that there is no new program data so it doesn't add
to the database. We restored 'mythconverg' to its original condition
(prior to importing data) to see if that helped but it didn't. We
wondered if we screwed up something else, so ultimately, we tried
another complete re-install of the OS and MythTV. Thanks to MythDora,
it's pretty easy. Strangely enough, mythfilldatabase simply refuses to
get data. I read something about 'tv_grab_na_dd', so we tried that and
it works. We decided to run mythfilldatabase with the verbose setting
and redirected the output to a file. I've deleted the repetitive lines
and pasted it below.
I sincerely hope that someone can help us solve this latest glitch. I
don't know why we've been having so much trouble lately. The system has
worked for more than a year. Thanks, in advance, for any help you can
provide.
Command:
-------------
$ mythfilldatabase --refresh-today --max-days 1 -v all
--do_channel_updates 1> /video/TDbackup/mythfill.log 2>& 1
Logged Output:
-------------
2006-05-29 21:38:47.140 Using runtime prefix = /usr
2006-05-29 21:38:47.168 New DB connection, total: 1
2006-05-29 21:38:47.176 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:38:47.178 Enabling Settings Cache.
2006-05-29 21:38:47.179 Clearing Settings Cache.
2006-05-29 21:38:47.180 MSqlQuery: SELECT data FROM settings WHERE
value = 'LogEnabled' AND hostname = 'mythserver' ;
2006-05-29 21:38:47.181 MSqlQuery: SELECT data FROM settings WHERE value
= 'LogEnabled' AND hostname IS NULL;
2006-05-29 21:38:47.183 MSqlQuery: SELECT data FROM settings WHERE
value = 'LogMaxCount' AND hostname = 'mythserver' ;
2006-05-29 21:38:47.184 MSqlQuery: SELECT data FROM settings WHERE
value = 'LogPrintLevel' AND hostname = 'mythserver' ;
2006-05-29 21:38:47.184 MSqlQuery: SELECT
sourceid,name,xmltvgrabber,userid,password,lineupid FROM videosource
ORDER BY sourceid;
2006-05-29 21:38:47.185 New DB connection, total: 2
2006-05-29 21:38:47.186 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:38:47.187 MSqlQuery: SELECT MAX(endtime) FROM program
WHERE manualid=0;
2006-05-29 21:38:47.187 Updating source #1 (Charter) with grabber datadirect
2006-05-29 21:38:47.188 Checking day @ offset 0, date: Mon May 29 2006
2006-05-29 21:38:47.188 Refreshing data for Mon May 29 2006
2006-05-29 21:38:47.189 New DB DataDirect connection
2006-05-29 21:38:47.190 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:38:47.191 MSqlQuery: UPDATE settings SET data ='2006-05-29
21:38' WHERE value='mythfilldatabaseLastRunStart'
2006-05-29 21:38:47.191 Retrieving datadirect data.
2006-05-29 21:38:47.191 Grabbing data for Mon May 29 2006 offset 0
2006-05-29 21:38:47.191 From Mon May 29 04:00:00 2006 to Tue May 30
04:00:00 2006 (UTC)
--21:38:47--
http://datadirect.webservices.zap2it.com/tvlistings/xtvdService
=> `-'
Resolving datadirect.webservices.zap2it.com... 206.18.98.160
Connecting to datadirect.webservices.zap2it.com|206.18.98.160|:80...
connected.
HTTP request sent, awaiting response... 401 Unauthorized
Connecting to datadirect.webservices.zap2it.com|206.18.98.160|:80...
connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/xml]
0K .......... .......... .......... .......... .......... 20.85 KB/s
50K .......... .......... .......... .......... .......... 76.30 KB/s
100K .......... .......... .......... .......... ........ 79.61 KB/s
21:38:51 (40.48 KB/s) - `-' saved [151564]
2006-05-29 21:38:51.756 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_station ( stationid char(12), callsign char(10), stationname
varchar(40), affiliate varchar(25), fccchannelnumber char(15) );
2006-05-29 21:38:51.757 MSqlQuery: TRUNCATE TABLE dd_station;
2006-05-29 21:38:51.758 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_lineup ( lineupid char(100), name char(42), type char(20), postal
char(6), device char(30) );
2006-05-29 21:38:51.759 MSqlQuery: TRUNCATE TABLE dd_lineup;
2006-05-29 21:38:51.760 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_lineupmap ( lineupid char(100), stationid char(12), channel char(5),
channelMinor char(3) );
2006-05-29 21:38:51.760 MSqlQuery: TRUNCATE TABLE dd_lineupmap;
2006-05-29 21:38:51.761 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_v_station ( stationid char(12), callsign char(10), stationname
varchar(40), affiliate varchar(25), fccchannelnumber char(15), channel
char(5), channelMinor char(3) );
2006-05-29 21:38:51.762 MSqlQuery: TRUNCATE TABLE dd_v_station;
2006-05-29 21:38:51.764 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_schedule ( programid char(12), stationid char(12), scheduletime
datetime, duration time, isrepeat bool, stereo bool, subtitled bool,
hdtv bool, closecaptioned bool, tvrating char(5), partnumber int,
parttotal int, endtime datetime, INDEX progidx (programid) );
2006-05-29 21:38:51.764 MSqlQuery: TRUNCATE TABLE dd_schedule;
2006-05-29 21:38:51.766 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_program ( programid char(12) NOT NULL, seriesid char(12), title
varchar(120), subtitle varchar(150), description text, mpaarating
char(5), starrating char(5), runtime time, year char(4), showtype
char(30), category_type char(64), colorcode char(20), originalairdate
date, syndicatedepisodenumber char(20), stars float unsigned, PRIMARY
KEY (programid));
2006-05-29 21:38:51.766 MSqlQuery: TRUNCATE TABLE dd_program;
2006-05-29 21:38:51.768 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_v_program ( chanid int unsigned NOT NULL, starttime datetime NOT
NULL, endtime datetime, title varchar(128), subtitle varchar(128),
description text, category varchar(64), category_type varchar(64),
airdate year, stars float unsigned, previouslyshown tinyint, isrepeat
bool, stereo bool, subtitled bool, hdtv bool, closecaptioned bool,
partnumber int, parttotal int, seriesid char(12), originalairdate date,
showtype varchar(30), colorcode varchar(20), syndicatedepisodenumber
varchar(20), programid char(12), tvrating char(5), mpaarating char(5),
INDEX progidx (programid));
2006-05-29 21:38:51.769 MSqlQuery: TRUNCATE TABLE dd_v_program;
2006-05-29 21:38:51.770 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_productioncrew ( programid char(12), role char(30), givenname
char(20), surname char(20), fullname char(41), INDEX progidx
(programid), INDEX nameidx (fullname));
2006-05-29 21:38:51.771 MSqlQuery: TRUNCATE TABLE dd_productioncrew;
2006-05-29 21:38:51.772 MSqlQuery: CREATE TEMPORARY TABLE IF NOT EXISTS
dd_genre ( programid char(12) NOT NULL, class char(30), relevance
char(1), INDEX progidx (programid));
2006-05-29 21:38:51.773 MSqlQuery: TRUNCATE TABLE dd_genre;
2006-05-29 21:38:51.774 DataDirect: Your subscription expires on
08/24/2006 09:41:57 AM
2006-05-29 21:38:51.775 MSqlQuery: UPDATE settings SET data ='Your
subscription expires on 08/24/2006 09:41:57 AM' WHERE
value='DataDirectMessage'
2006-05-29 21:38:51.777 MSqlQuery: INSERT INTO dd_station (stationid,
callsign, stationname, affiliate, fccchannelnumber)
VALUES('10035','AETV','A & E Network','Satellite','');
2006-05-29 21:38:51.778 MSqlQuery: INSERT INTO dd_station (stationid,
callsign, stationname, affiliate, fccchannelnumber)
VALUES('10021','AMC','AMC','Satellite','');
2006-05-29 21:38:51.779 MSqlQuery: INSERT INTO dd_station (stationid,
callsign, stationname, affiliate, fccchannelnumber)
VALUES('16331','ANIMAL','Animal Planet','Satellite','');
..
..
..
snip snip (9800 lines)
..
..
..
2006-05-29 21:39:03.466 MSqlQuery: INSERT INTO dd_genre (programid,
class, relevance) VALUES('SH7580670000','Religious','1');
2006-05-29 21:39:03.467 MSqlQuery: INSERT INTO dd_genre (programid,
class, relevance) VALUES('SH7838090000','Animals','0');
2006-05-29 21:39:03.468 MSqlQuery: INSERT INTO dd_genre (programid,
class, relevance) VALUES('SH8387420000','Shopping','0');
2006-05-29 21:39:03.470 Grab complete. Actual data from Mon May 29
04:00:00 2006 to Tue May 30 04:00:00 2006 (UTC)
2006-05-29 21:39:03.470 MSqlQuery: UPDATE settings SET data ='2006-05-29
21:39' WHERE value='mythfilldatabaseLastRunEnd'
2006-05-29 21:39:03.471 Main temp tables populated.
2006-05-29 21:39:03.471 Updating myth channels.
2006-05-29 21:39:03.472 MSqlQuery: TRUNCATE TABLE dd_v_station;
2006-05-29 21:39:03.473 MSqlQuery: INSERT INTO dd_v_station (stationid,
callsign, stationname, affiliate, fccchannelnumber, channel,
channelMinor) SELECT dd_station.stationid, callsign, stationname,
affiliate, fccchannelnumber, channel, channelMinor FROM dd_station,
dd_lineupmap WHERE ( (dd_station.stationid = dd_lineupmap.stationid)
AND ( dd_lineupmap.lineupid = NULL) );
2006-05-29 21:39:03.474 MSqlQuery: SELECT dd_v_station.stationid,
dd_v_station.callsign, dd_v_station.stationname,
dd_v_station.channel, dd_v_station.fccchannelnumber,
dd_v_station.channelMinor FROM dd_v_station LEFT JOIN channel ON
dd_v_station.stationid = channel.xmltvid AND channel.sourceid = 1
WHERE channel.chanid IS NULL;
2006-05-29 21:39:03.474 MSqlQuery: SELECT callsign, stationname,
stationid, channel, fccchannelnumber, channelMinor FROM dd_v_station
2006-05-29 21:39:03.474 Updating icons for sourceid: 1
2006-05-29 21:39:03.476 New DB connection, total: 3
2006-05-29 21:39:03.476 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:39:03.477 MSqlQuery: SELECT ch.chanid, nim.url FROM
channel ch, callsignnetworkmap csm RIGHT JOIN networkiconmap nim ON
csm.network = nim.network WHERE ch.callsign = csm.callsign AND (icon =
'none' OR icon = '') AND ch.sourceid = 1
2006-05-29 21:39:03.478 Channels updated.
2006-05-29 21:39:03.479 MSqlQuery: TRUNCATE TABLE dd_v_program;
2006-05-29 21:39:03.564 MSqlQuery: INSERT INTO dd_v_program (chanid,
starttime, endtime, title, subtitle, description, airdate, stars,
previouslyshown, stereo, subtitled, hdtv, closecaptioned, partnumber,
parttotal, seriesid, originalairdate, showtype, category_type,
colorcode, syndicatedepisodenumber, tvrating, mpaarating, programid)
SELECT chanid, scheduletime, endtime, title, subtitle, description,
year, stars, isrepeat, stereo, subtitled, hdtv, closecaptioned,
partnumber, parttotal, seriesid, originalairdate, showtype,
category_type, colorcode, syndicatedepisodenumber, tvrating, mpaarating,
dd_program.programid FROM channel, dd_schedule, dd_program WHERE (
(dd_schedule.programid = dd_program.programid) AND (channel.xmltvid =
dd_schedule.stationid) AND (channel.sourceid = 1 ));
2006-05-29 21:39:03.564 MSqlQuery: ANALYZE TABLE dd_v_program;
2006-05-29 21:39:03.572 MSqlQuery: ANALYZE TABLE dd_productioncrew;
2006-05-29 21:39:03.572 MSqlQuery: SELECT count(*) from dd_v_program;
2006-05-29 21:39:03.572 Did not find any new program data.
2006-05-29 21:39:03.573 MSqlQuery: SELECT MAX(endtime) FROM program
WHERE manualid=0;
2006-05-29 21:39:03.573 MSqlQuery: DELETE FROM oldprogram WHERE airdate
< DATE_SUB(CURRENT_DATE, INTERVAL 320 DAY);
2006-05-29 21:39:03.574 MSqlQuery: REPLACE INTO oldprogram
(oldtitle,airdate) SELECT title,starttime FROM program WHERE starttime <
NOW() AND manualid = 0 GROUP BY title;
2006-05-29 21:39:03.574 MSqlQuery: DELETE FROM program WHERE starttime
<= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2006-05-29 21:39:03.575 MSqlQuery: DELETE FROM programrating WHERE
starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2006-05-29 21:39:03.575 MSqlQuery: DELETE FROM programgenres WHERE
starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2006-05-29 21:39:03.576 MSqlQuery: DELETE FROM credits WHERE starttime
<= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2006-05-29 21:39:03.576 MSqlQuery: DELETE FROM record WHERE (type = 1 OR
type = 7 OR type = 8) AND enddate < NOW();
2006-05-29 21:39:03.577 New DB connection, total: 4
2006-05-29 21:39:03.578 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:39:03.579 MSqlQuery: SELECT record.recordid FROM record
LEFT JOIN oldfind ON oldfind.recordid = record.recordid WHERE type = 6
AND oldfind.findid IS NOT NULL;
2006-05-29 21:39:03.580 MSqlQuery: DELETE FROM oldfind WHERE findid <
TO_DAYS(NOW()) - 14;
2006-05-29 21:39:03.580 New DB connection, total: 5
2006-05-29 21:39:03.581 Connected to database 'mythconverg' at host:
localhost
2006-05-29 21:39:03.582 MSqlQuery: SELECT data FROM settings WHERE
value = 'CleanOldRecorded' AND hostname = 'mythserver' ;
2006-05-29 21:39:03.583 MSqlQuery: SELECT data FROM settings WHERE value
= 'CleanOldRecorded' AND hostname IS NULL;
2006-05-29 21:39:03.584 MSqlQuery: DELETE FROM oldrecorded WHERE
recstatus <> -3 AND duplicate = 0 AND endtime < DATE_SUB(CURRENT_DATE,
INTERVAL 10 DAY);
2006-05-29 21:39:03.584 Failed to fetch some program info
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
|
