Author |
Message |
tjc
|
Posted: Fri May 13, 2011 8:07 pm |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
I can't for the life of me find the surrogate column name although I'd swear I've used that trick in the past. Not a big deal as we can always resort to a slightly more round about technique.
|
|
Top |
|
|
TheBigRed
|
Posted: Sat May 14, 2011 4:53 am |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
Here they all are. As you can see the recordedfile isn't there, but this is the rest of them.
Code: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 135 Server version: 5.0.32-Debian_7etch1 Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mythconverg; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> describe channel; +-----------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------------+------+-----+---------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | channum | varchar(10) | NO | MUL | | | | freqid | varchar(10) | YES | | NULL | | | sourceid | int(10) unsigned | YES | | NULL | | | callsign | varchar(20) | NO | | | | | name | varchar(64) | NO | | | | | icon | varchar(255) | NO | | none | | | finetune | int(11) | YES | | NULL | | | videofilters | varchar(255) | NO | | | | | xmltvid | varchar(64) | NO | | | | | recpriority | int(10) | NO | | 0 | | | contrast | int(11) | YES | | 32768 | | | brightness | int(11) | YES | | 32768 | | | colour | int(11) | YES | | 32768 | | | hue | int(11) | YES | | 32768 | | | tvformat | varchar(10) | NO | | Default | | | commfree | tinyint(4) | NO | | 0 | | | visible | tinyint(1) | NO | | 1 | | | outputfilters | varchar(255) | NO | | | | | useonairguide | tinyint(1) | YES | | 0 | | | mplexid | smallint(6) | YES | | NULL | | | serviceid | mediumint(8) unsigned | YES | | NULL | | | atscsrcid | int(11) | YES | | NULL | | | tmoffset | int(11) | NO | | 0 | | | atsc_major_chan | int(10) unsigned | NO | | 0 | | | atsc_minor_chan | int(10) unsigned | NO | | 0 | | +-----------------+-----------------------+------+-----+---------+-------+ 26 rows in set (0.00 sec)
mysql> describe oldprogram; +----------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------------------+-------+ | oldtitle | varchar(128) | NO | PRI | | | | airdate | datetime | NO | | 0000-00-00 00:00:00 | | +----------+--------------+------+-----+---------------------+-------+ 2 rows in set (0.00 sec)
mysql> describe oldrecorded; +-------------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | endtime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | title | varchar(128) | NO | PRI | | | | subtitle | varchar(128) | NO | | | | | description | text | NO | | | | | category | varchar(64) | NO | | | | | seriesid | varchar(12) | NO | MUL | | | | programid | varchar(20) | NO | MUL | | | | findid | int(11) | NO | | 0 | | | recordid | int(11) | NO | MUL | 0 | | | station | varchar(20) | NO | PRI | | | | rectype | int(10) unsigned | NO | | 0 | | | duplicate | tinyint(1) | NO | | 0 | | | recstatus | int(11) | NO | MUL | 0 | | | reactivate | smallint(6) | NO | | 0 | | | generic | tinyint(1) | YES | | 0 | | +-------------+------------------+------+-----+---------------------+-------+ 17 rows in set (0.00 sec)
mysql> describe recorded; +-----------------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | endtime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | title | varchar(128) | NO | MUL | | | | subtitle | varchar(128) | NO | | | | | description | text | NO | | | | | category | varchar(64) | NO | | | | | hostname | varchar(255) | NO | | | | | bookmark | tinyint(1) | NO | | 0 | | | editing | int(10) unsigned | NO | | 0 | | | cutlist | tinyint(1) | NO | | 0 | | | autoexpire | int(11) | NO | | 0 | | | commflagged | int(10) unsigned | NO | | 0 | | | recgroup | varchar(32) | NO | | Default | | | recordid | int(11) | YES | MUL | NULL | | | seriesid | varchar(12) | NO | MUL | | | | programid | varchar(20) | NO | MUL | | | | lastmodified | timestamp | NO | | CURRENT_TIMESTAMP | | | filesize | bigint(20) | NO | | 0 | | | stars | float | NO | | 0 | | | previouslyshown | tinyint(1) | YES | | 0 | | | originalairdate | date | YES | | NULL | | | preserve | tinyint(1) | NO | | 0 | | | findid | int(11) | NO | | 0 | | | deletepending | tinyint(1) | NO | | 0 | | | transcoder | int(11) | NO | | 0 | | | timestretch | float | NO | | 1 | | | recpriority | int(11) | NO | | 0 | | | basename | varchar(128) | NO | | | | | progstart | datetime | NO | | 0000-00-00 00:00:00 | | | progend | datetime | NO | | 0000-00-00 00:00:00 | | | playgroup | varchar(32) | NO | | Default | | | profile | varchar(32) | NO | | | | | duplicate | tinyint(1) | NO | | 0 | | | transcoded | tinyint(1) | NO | | 0 | | | watched | tinyint(4) | NO | | 0 | | +-----------------+------------------+------+-----+---------------------+-------+ 36 rows in set (0.00 sec)
mysql> describe recordedcredits; +-----------+----------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------+ | person | mediumint(8) unsigned | NO | PRI | 0 | | | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | role | set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest') | NO | PRI | | | +-----------+----------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------+ 4 rows in set (0.01 sec)
mysql> describe recordedfile; ERROR 1146 (42S02): Table 'mythconverg.recordedfile' doesn't exist mysql> describe recordedfile; ERROR 1146 (42S02): Table 'mythconverg.recordedfile' doesn't exist mysql> describe recordedmarkup; +-----------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | mark | bigint(20) | NO | PRI | 0 | | | offset | varchar(32) | YES | | NULL | | | type | int(11) | NO | PRI | 0 | | +-----------+------------------+------+-----+---------------------+-------+ 5 rows in set (0.00 sec)
mysql> describe recordedprogram; +-------------------------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | endtime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | title | varchar(128) | NO | MUL | | | | subtitle | varchar(128) | NO | | | | | description | text | NO | | | | | category | varchar(64) | NO | | | | | category_type | varchar(64) | NO | | | | | airdate | year(4) | NO | | 0000 | | | stars | float unsigned | NO | | 0 | | | previouslyshown | tinyint(4) | NO | | 0 | | | title_pronounce | varchar(128) | NO | MUL | | | | stereo | tinyint(1) | NO | | 0 | | | subtitled | tinyint(1) | NO | | 0 | | | hdtv | tinyint(1) | NO | | 0 | | | closecaptioned | tinyint(1) | NO | | 0 | | | partnumber | int(11) | NO | | 0 | | | parttotal | int(11) | NO | | 0 | | | seriesid | varchar(12) | NO | MUL | | | | originalairdate | date | YES | | NULL | | | showtype | varchar(30) | NO | | | | | colorcode | varchar(20) | NO | | | | | syndicatedepisodenumber | varchar(20) | NO | | | | | programid | varchar(20) | NO | MUL | | | | manualid | int(10) unsigned | NO | PRI | 0 | | | generic | tinyint(1) | YES | | 0 | | | listingsource | int(11) | NO | | 0 | | | first | tinyint(1) | NO | | 0 | | | last | tinyint(1) | NO | | 0 | | +-------------------------+------------------+------+-----+---------------------+-------+ 29 rows in set (0.00 sec)
mysql> describe recordedrating; +-----------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | system | char(8) | NO | PRI | | | | rating | char(8) | NO | PRI | | | +-----------+------------------+------+-----+---------------------+-------+ 4 rows in set (0.00 sec)
mysql> describe recordedseek; +-----------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------------------+-------+ | chanid | int(10) unsigned | NO | PRI | 0 | | | starttime | datetime | NO | PRI | 0000-00-00 00:00:00 | | | mark | bigint(20) | NO | PRI | 0 | | | offset | varchar(32) | YES | | NULL | | | type | int(11) | NO | PRI | 0 | | +-----------+------------------+------+-----+---------------------+-------+ 5 rows in set (0.00 sec)
mysql> quit;
_________________ ---------
Amos B.
|
|
Top |
|
|
tjc
|
Posted: Sat May 14, 2011 9:45 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
OK, that looks good. We're also going to want "people" which should be a simple two column table with "person" and "name". The missing table is empty on my system anyway, so it doesn't look like a problem.
If my eyes aren't fooling me the only table we need to fix up is channel, which dropped two columns, "commfree", and "atscsrcid".
I'll go back and do another pass to make sure we haven't missed anything...
|
|
Top |
|
|
TheBigRed
|
Posted: Sat May 14, 2011 10:23 am |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
Here it is:
Code: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 150 Server version: 5.0.32-Debian_7etch1 Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mythconverg; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> describe people; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | person | mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | name | char(128) | NO | UNI | | | +--------+-----------------------+------+-----+---------+----------------+ 2 rows in set (0.03 sec)
mysql> quit
_________________ ---------
Amos B.
|
|
Top |
|
|
tjc
|
Posted: Sat May 14, 2011 10:53 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
So here is the plan in outline, I'll fill in details on the new bits below.
- Do your backups etc. as previously covered.
- Do the install onto the new disk and do the basic setup but don't scan for channels (yet).
- Copy all the /myth stuff across
- Extract the channel data from the backup, load it into the new DB.
- Scan for channels using the minimal updates strategy. That should fill in all your multiplexs and the like.
- Extract the recording data and load it into the new DB. This should be easy since none of the tables appear to have been modified in ways that will cause trouble. If you get an error stop and let me know unless it's trivial.
- Verify that everything is working. Rescan for music, pictures and videos.
Detailed bits:
- Extracting and munging the channel data... Be careful here, the quotes are key to this working, and trailing white space can cause problems too...
Code: gunzip -dc /myth/backup/mythconverg.sql.gz | sed -n -e 's/`channel`/`oldchannel`/' -e '/CREATE TABLE `oldchannel`/,/latin1/p' -e '/INSERT INTO `oldchannel`/p' >oldchannel.sql cat >>oldchannel.sql <<'EOF' INSERT INTO channel (chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, last_record, default_authority, commmethod) SELECT chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, last_record, default_authority, commmethod FROM oldchannel; DROP TABLE oldchannel; EOF
That will create a SQL script which should port your channel data via an intermediate table called "oldchannel" and then cleanup. - Extracting the recording data... This is very much like extracting the channel data but simpler. Again watch out for trailing whitespace on the continuation lines here. Code: gunzip -dc /myth/backup/mythconverg.sql.gz | sed -n >recorded_program_data.sql \ -e '/INSERT INTO `oldprogram`/p' \ -e '/INSERT INTO `oldrecorded`/p' \ -e '/INSERT INTO `people`/p' \ -e '/INSERT INTO `recorded`/p' \ -e '/INSERT INTO `recordedcredits`/p' \ -e '/INSERT INTO `recordedmarkup`/p' \ -e '/INSERT INTO `recordedprogram`/p' \ -e '/INSERT INTO `recordedrating`/p' \ -e '/INSERT INTO `recordedseek`/p'
Hopefully I haven't forgotten anything...
|
|
Top |
|
|
TheBigRed
|
Posted: Sat May 14, 2011 10:56 am |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
Oh, should I mention my chanels etc are just basic (and I mean BASIC cable) so I never scanned etc. I haven't made the move to HD (OTA or otherwise)
Does that help any?
_________________ ---------
Amos B.
|
|
Top |
|
|
tjc
|
Posted: Sat May 14, 2011 11:03 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
Mostly just means you can skip scanning.
|
|
Top |
|
|
tjc
|
Posted: Sat May 14, 2011 11:07 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
BTW - If you've got a current backup on the old system you can try generating the two SQL scripts on it now. That will minimize surprises later.
I'd probably put the shell stuff in a file to make sure it was clean, and to make it easier to correct. Then run it and check the output.
|
|
Top |
|
|
TheBigRed
|
Posted: Sun May 15, 2011 9:20 am |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
Ok, I got the two script files and ran them and now have the .sql files.
I was a bit scared at first when the recorded file only had about 54 lines, till I realized it is one line per channel and all the information follows after that.
I ran the .sql files against my test 6.04 system and each one generated an error after, they are as follows:
Code: ERROR 1054 (42S22) at line 32: Unknown column 'last_record' in 'field list' And Code: ERROR 1054 (42S22) at line 7: Unknown column 'offset' in 'field list'
However, I now "see" all my recordings in the UI and web interface (all be it without png previews etc (to be expected since the media files aren't there)
Though my upcoming recordings and recording schedules are gone. I guess I can rebuild them manually. Also in Myth, the channel for recordings is showing the old 1012 etc( I know that refers to the tuner and chanel, is this fixable in the future after moving or should it be fixed now?)
Thanks TJC for all your help!!
_________________ ---------
Amos B.
|
|
Top |
|
|
tjc
|
Posted: Sun May 15, 2011 11:12 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
OK, I must have missed something, but it's easy to fix. That's the beauty of generating the scripts.
Edit the oldchannel.sql script and delete both occurrences of last_record in the SQL insert that copies from old to new. Also, before that insert add a line that says " delete from channel;". The final section should look like this:
Code: DELETE FROM channel; INSERT INTO channel (chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, default_authority, commmethod) SELECT chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, default_authority, commmethod FROM oldchannel; DROP TABLE oldchannel;
I'll get back to you shortly on that offset thing.
|
|
Top |
|
|
tjc
|
Posted: Sun May 15, 2011 11:40 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
Looks like that one is from the recordedmarkup table. So we're going to have to play the same game as with the channel table. I'm attaching a script to regenerate both SQL files. I found an old back up that is _almost_ old enough to let me test things, although it's also probably where that extra last_record column came from... :-/
BTW - When you rerun the SQL scripts you may get a bunch of duplicate record warnings. To fix that you can add a bunch of delete statements at the beginning of the recorded_program_data.sql
|
|
Top |
|
|
tjc
|
Posted: Sun May 15, 2011 11:51 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
OK, drop this into a file so you can run it as a bash script. It takes the full path to the backup file as it's input. This should also fix the other issue you mentioned, which was caused by the error from the channels table preventing that batch insert from working. With no matching records in the channel table the source for the files falls back to the raw ID.
Code: #!/bin/bash # Generate a pair of SQL scripts which can be used to import channel and # recording history data from a KnoppMyth R5F27 DB into an LinHES 6.04 DB. # It requires a current backup of the R5F27 DB to work.
# This SQL script deals with the changes to the channel table
dbbackup="$1" [ "$#" -eq 1 -a -n "$dbbackup" -a -f "$dbbackup" ] || { echo 1>&2 "Usage: $0 <backup_file_name>" exit 1 }
gunzip -dc "$dbbackup" | sed -n >oldchannel.sql \ -e 's/`channel`/`oldchannel`/' \ -e '/CREATE TABLE `oldchannel`/,/latin1/p' \ -e '/INSERT INTO `oldchannel`/p'
cat >>oldchannel.sql <<'EOF' DELETE FROM channel; INSERT INTO channel (chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, default_authority, commmethod) SELECT chanid, channum, freqid, sourceid, callsign, name, icon, finetune, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, tvformat, visible, outputfilters, useonairguide, mplexid, serviceid, tmoffset, atsc_major_chan, atsc_minor_chan, default_authority, commmethod FROM oldchannel; DROP TABLE oldchannel; EOF
# This SQL script handles all the meta data for recordings recording
cat >recorded_program_data.sql <<'EOF' DELETE FROM oldprogram; DELETE FROM oldrecorded; DELETE FROM people; DELETE FROM recorded; DELETE FROM recordedcredits; DELETE FROM recordedmarkup; DELETE FROM recordedprogram; DELETE FROM recordedrating; DELETE FROM recordedseek; EOF
gunzip -dc "$dbbackup" | sed -n >>recorded_program_data.sql \ -e '/INSERT INTO `oldprogram`/p' \ -e '/INSERT INTO `oldrecorded`/p' \ -e '/INSERT INTO `people`/p' \ -e '/INSERT INTO `recorded`/p' \ -e '/INSERT INTO `recordedcredits`/p' \ -e 's/`recordedmarkup`/`oldrecordedmarkup`/' \ -e '/CREATE TABLE `oldrecordedmarkup`/,/latin1/p' \ -e '/INSERT INTO `oldrecordedmarkup`/p' \ -e '/INSERT INTO `recordedprogram`/p' \ -e '/INSERT INTO `recordedrating`/p' \ -e '/INSERT INTO `recordedseek`/p'
cat >>recorded_program_data.sql <<'EOF' DELETE FROM recordedmarkup; INSERT INTO recordedmarkup (chanid, starttime, mark, type) SELECT chanid, starttime, mark, type FROM oldrecordedmarkup; DROP TABLE oldrecordedmarkup; EOF
Edit: corrected one of the deletes...
|
|
Top |
|
|
TheBigRed
|
Posted: Sun May 15, 2011 12:21 pm |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
I didn't see that last post till I was redoing the first reply about editing and re-running the .sql file.
Will this new script overwrite what is already in the database? When I tried re-running the .sql file I got an error:
Code: ERROR 1050 (42S01) at line 1: Table 'oldchannel' already exists
Generating the script file you just uploaded now.
Also assuming I need to be carefull of the trailing spaces on lines in this script..
_________________ ---------
Amos B.
|
|
Top |
|
|
tjc
|
Posted: Sun May 15, 2011 12:28 pm |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
You'll probably have to manually drop the oldchannel table. Other than that the new scripts should do the necessary pre/post cleanup.
Oh, and on the recording schedules, re-entering those is probably the easiest thing to do. IIRC that area had a bunch of DB changes so porting the old ones could get complicated.
Last edited by tjc on Sun May 15, 2011 12:32 pm, edited 1 time in total.
|
|
Top |
|
|
TheBigRed
|
Posted: Sun May 15, 2011 12:31 pm |
|
Joined: Tue Apr 18, 2006 3:42 pm
Posts: 46
Location:
Central PA
|
Ok, still not showing channels properly, however.
On this test system I don't have an actual tuner card installed, just the dummy mpg file, so I don't have any sources that contain my actual channels, is this causing an issue?
This script didn't output any errors, but I didn't look in the log files either.
Should I set up a test box with an actual (all be it crappy software based) tuner so I can see if that fixes it?
Also won't the channel ID's be off anyway as I believe I read somewhere that the fist number in the id (1014 say) is the number of the tuner that the channel was on, and the last bit is the channel?
_________________ ---------
Amos B.
|
|
Top |
|
|