View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 43 posts ] 
Go to page Previous  1, 2, 3  Next

Print view Previous topic   Next topic  
Author Message
Search for:
 Post subject:
PostPosted: Fri May 13, 2011 8:07 pm 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 4:53 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 9:45 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 10:23 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 10:53 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 10:56 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 11:03 am 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
Mostly just means you can skip scanning. ;-)


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 14, 2011 11:07 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 9:20 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 11:12 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 11:40 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 11:51 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 12:21 pm 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 12:28 pm 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sun May 15, 2011 12:31 pm 
Offline
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
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 43 posts ] 
Go to page Previous  1, 2, 3  Next



All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 23 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu