[mythtv-users] Importing recordings from 18.x into 20.x, lessons learned

[Ackster]

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