[mythtvnz] Channel id to name tables: Where are they?

[Stephen Worthington]

On Mon, 22 Oct 2012 19:47:11 +1300, you wrote:

Before doing any changes, please make sure you have your MythTV
database backed up, and that you have time before the next recording
to try changes and then to restore the old database if necessary.
Unless you have a monster database like mine, backup and restore
should take less than two minutes each.  Restore takes longer than
backup.

To backup:

  stop mythtv-backend
  /usr/share/mythtv/mythconverg_backup.pl
  start mythtv-backend

Every time you run that backup command, a new full backup will be
created here:

  /var/lib/mythtv/db_backups/

and the oldest backup will be deleted (it defaults to keeping five
backups).  There is normally a default cron job that does a backup
once a week using the mythconverg_backup.pl script.

To restore:

  cd /var/lib/mythtv/db_backups/
  ls -alt
  ...then select and copy the filename that you want to restore,
usually the latest backup...
  stop mythtv-backend
  /usr/share/mythtv/mythconverg_restore.pl --drop_database
--create_database --directory . --filename <paste backup filename to
be restored>
  start mythtv-backend

When doing major changes to the database like this, I find it really
useful to keep a copy of all the commands that change things in a .sql
file in my editor.  I write the command in the editor first, the copy
it to the terminal where I am running SQL.  That way, if I have to
restore the database and start again, I can just use that file to run
all the commands again, up to the one that did something wrong.

>On 22/10/12 18:18, Tony Sauri wrote:
>> On 22/10/12 17:31, Worik Stanton wrote:
 
>> What is the output of the following sql query?
>> 
>> mysql> select mplexid, frequency from dtv_multiplex;
>> 
>mysql> select mplexid, frequency from dtv_multiplex;
>+---------+-----------+
>| mplexid | frequency |
>+---------+-----------+
>|       8 | 578000000 |
>|       7 | 562000000 |
>|       4 | 562000000 |
>|       5 | 578000000 |
>|       6 | 594000000 |
>|       9 | 594000000 |
>+---------+-----------+
>6 rows in set (0.00 sec)

This is the first problem.  There are only three multiplexes, so this
looks like you have scanned the transmitters twice and have two copies
of the data.  Sorted by mplexid it looks obvious:

+---------+-----------+
| mplexid | frequency |
+---------+-----------+
|       4 | 562000000 |
|       5 | 578000000 |
|       6 | 594000000 |
|       7 | 562000000 |
|       8 | 578000000 |
|       9 | 594000000 |
+---------+-----------+
6 rows in set (0.00 sec)


At some point we will need to fix this, but not until other fixes have
been done.  When we get there, this should do it:

  update channel set mplexid=4 where mplexid=7;
  update channel set mplexid=5 where mplexid=8;
  update channel set mplexid=6 where mplexid=9;
  delete from dtv_multiplex where mplexid=7;
  delete from dtv_multiplex where mplexid=8;
  delete from dtv_multiplex where mplexid=9;

Before then, we need to sort out the channel table quite a bit.

First, TVNZ7 is gone, so the xmltvid record for it needs to be fixed:

  update channel set callsign='TV ONE plus 1',name='TV ONE plus
1',xmltivd='tv1-plus1.freeviewnz.tv' where callsign='TVNZ7';

Next, I think the xmltvid values need to be put on the proper channel
records, not on null records as they are now.  I got a channel table
like that at one point - I think it is also the result of scanning
twice.  I am sure there is a nice SQL command that will do it, but I
am not good enough at SQL yet to write it.  What needs to be done is
to copy the xmltvid field from each record that has it to the matching
record(s) that do not have it (match by name field).  I think I wound
up doing it all manually when it happened to me.  I have not
definitively checked all the records, but it looks like the name
fields correspond correctly between the xmltvid records and those
without.  Anyone care to venture a nice SQL command to do the job?

Once that is done, then all the null records need to be deleted:

  delete from channel where mplexid=NULL;

I would also delete the useless maintenance channels - they tend to
change from week to week anyway, and are there normally only to send
software updates out to various TVs and DVB-T recorders:

  delete from channel where channum >= 600;

Probably due to multiple scans, you also seem to have two records for
each real channel, with 1xxx or 3xxx chanid values.  Your recording
rules may be referring to either of both of these channel records by
their chanid key.  If so, then the recording rules (and any other
database tables with chanid values) will need to be fixed before the
channel table is fixed by removing the second set of channel records.

Do this query to see what your recording rules are doing:

  select recordid,chanid from record order by chanid;

If we are really lucky, there will only be rules using the 1xxx chanid
values, or the 3xxx ones, but not both.  If both are in use, we need
to decide whether we are keeping the 1xxx or the 3xxx channel records,
and convert everything to use the ones we keep.  The 3xxx ones are
more complete, as they have the radio channels.  Fortunately, the 1xxx
and 3xxx chanid values match in the xxx part, which should make it
relatively easy to fix this once we decide.