[mythtv-users] Importing recordings from 18.x into 20.x, lessons learned
Ackster
ackster at gmail.com
Tue Oct 17 22:52:51 UTC 2006
I took a dump of my old db, and yanked out just the "recorded" table.
# grep "INSERT INTO \`recorded\`" dbbackup.sql > recorded.sql
*NOTE* Depending on the mysql version I think, this table may actually
be multiple INSERT lines, and labeled just as 'recorded' without the
single quotes. Check your DB backup file first.
example:
INSERT INTO recorded (chanid, starttime, endtime, title, subtitle,
description, category, hostname, bookmark, editing, cutlist, autoexpire,
commflagged, recgroup, recordid, seriesid, programid, lastmodified,
filesize, stars, previouslyshown, originalairdate, preserve, findid,
deletepending, transcoder) VALUES (1012,'2005-03-09
00:30:00','2005-03-09 01:00:00',.........);
INSERT INTO recorded (chanid, starttime, endtime, title, subtitle,
description, category, hostname, bookmark, editing, cutlist, autoexpire,
commflagged, recgroup, recordid, seriesid, programid, lastmodified,
filesize, stars, previouslyshown, originalairdate, preserve, findid,
deletepending, transcoder) VALUES (1012,'2005-02-09
00:30:00','2005-02-09 01:00:00',........);
I only needed the recorded table entries, not the table creation or
anything else so far. Your mileage may vary.
That I redirected the new sql statements into the mysql DB, and now I
had access to my recordings again.
$ mysql -u mythtv -pmythtv mythconverg < recorded.sql
But then I noticed a problem in "Recorded Programs" in MythWeb. All
dates showed: 1969
I tried poking around in the php scripts, but gave up fairly quickly
because there were more pressing issues to deal with.
Later I tried testing out the MythArchive plugin, and that also failed
to work on my old recordings, but it gave some insight into just what
was happening: BOTH MythWeb and MythArchive are reading a column that
didn't existing in my old db, namely:
progstart
Since they didn't exist, the default gets set to "0000-00-00 00:00:00".
Sadly, the Wiki page (
http://www.mythtv.org/wiki/index.php/Recorded_table ) doesn't even list
this column. Only one page hit contains this.
Example :
SELECT progstart, title, starttime, chanid FROM recorded;
| 0000-00-00 00:00:00 | News |
2006-06-17 19:00:00 | 1017 | <--- old recording
| 2006-10-13 16:00:00 | News |
2006-10-13 16:00:00 | 1281 | <--- new recording
The solution :
# mysql -t mythconverg
mysql> UPDATE recorded SET progstart = starttime WHERE progstart =
'0000-00-00 00:00:00';
Additionally, there's progend which I suppose should be changed as well:
mysql> UPDATE recorded SET progend = endtime WHERE progend = '0000-00-00
00:00:00';
Both MythArchive and MythWeb seem to work fine now.
I hope this helps some people.
-Ackster
More information about the mythtv-users
mailing list