[mythtv-users] 0.21 and database corruption/how to rebuild and update from backup

[Bruce Markey]

Bob Sully wrote:
> Hi all -
>
> I just did the upgrade from 0.20.2 to 0.21 via rpm yesterday.
...
> Therefore, I have three separate "video sources" all from the same
> Schedules Direct channel list - "Clear", "Analog" (for the analog tuners,
> obviously), and "All channels" (for the S-video off the STB).

Good stuff. See http://www.mythtv.org/docs/mythtv-HOWTO-12.html#ss12.6
for hints on getting the most out of this kind of configuration...

> Well, after the upgrade, all three of the sources had ALL of the
> channels...the only thing I can figure is that mythfilldatabase ran
> without the "delete new channels" option.
>
> Rather than manually deleting channels, I tried clearing the entire
> channel database and rescanning.  However, re-assigning all of the DVB
> channels and XMLTV IDs is becoming very tiring.  Is there any (relatively
> easy) way I can copy my backup (0.20.2) mythconverg database back into the
> new installation and then upgrade to the new schema (after adding "delete
> new channels" to the mythfilldatabase directive in Setup)?  Before
> upgrading, I simply backed up the files with rsync.

Here's what I'd do. First, don't panic. I'll play the home game
while I type =).

The backup I'm using is a gzipped file bigboy.sql.gz . I use
zcat to pipe the ascii through grep:

: bjm at moktoo ; zcat bigboy.sql.gz | grep 'INSERT INTO `channel`' > foo

If your file isn't compressed you can just:

: bjm at moktoo ; grep 'INSERT INTO `channel`' bigboy.sql.gz > foo

You now have a file "foo" that has all the insert lines for your
channel table as it had been when the backup was made. Use "less"
(more, cat)to sanity check that this is the case:

: bjm at moktoo ; head -2 foo
INSERT INTO `channel` VALUES (1003,'3',1,'KVBC','KVBC (NBC)','/home/bjm/.mythtv/channels/kvbc3_lasvegas.jpg',NULL,'quickdnr','10790',31050,31980,34030,0,'3',32768,'Default',0,1,'',0,NULL,NULL,NULL,0,0,0,'2008-03-10 23:33:54',0,'',-1);
INSERT INTO `channel` VALUES (1005,'5',1,'KVVU','KVVU (FOX)','/home/bjm/.mythtv/channels/kvvu5_lasvegas.jpg',NULL,'','10811',32550,30380,34030,0,'5',32768,'Default',0,1,'',0,NULL,NULL,NULL,0,0,0,'2008-01-24 00:51:48',0,'',-1);
: bjm at moktoo ; wc foo
   253  1995 58105 foo

You can now use this file to repopulate your channel table. Knowing
that you have a good backup (Yes? Are you sure?), you can clear out
the crud in your current channel table:

: bjm at moktoo ; mysql -u mythtv -pmythtv mythconverg
mysql> truncate channel;
Query OK, 0 rows affected (0.00 sec)

mysql> source /home/bjm/mythtv/foo
Query OK, 1 row affected (0.00 sec)
...
Query OK, 1 row affected (0.00 sec)

mysql>  select count(*) from channel;
+----------+
| count(*) |
+----------+
|      253 |
+----------+
1 row in set (0.00 sec)

mysql> select * from channel where chanid in (1003, 1005);
+--------+---------+----------+----------+------------+-----------------------------------------------+----------+--------------+---------+----------+------------+--------+-------------+--------+-------+----------+----------+---------+---------------+---------------+---------+-----------+-----------+----------+-----------------+-----------------+---------------------+-----------+-------------------+------------+
| chanid | channum | sourceid | callsign | name       | icon                                          | finetune | videofilters | xmltvid | contrast | brightness | colour | recpriority | freqid | hue   | tvformat | commfree | visible | outputfilters | useonairguide | mplexid | serviceid | atscsrcid | tmoffset | atsc_major_chan | atsc_minor_chan | last_record         | prefinput | default_authority | commmethod |
+--------+---------+----------+----------+------------+-----------------------------------------------+----------+--------------+---------+----------+------------+--------+-------------+--------+-------+----------+----------+---------+---------------+---------------+---------+-----------+-----------+----------+-----------------+-----------------+---------------------+-----------+-------------------+------------+
|   1003 | 3       |        1 | KVBC     | KVBC (NBC) | /home/bjm/.mythtv/channels/kvbc3_lasvegas.jpg |     NULL | quickdnr     | 10790   |    31050 |      31980 |  34030 |           0 | 3      | 32768 | Default  |        0 |       1 |               |             0 |    NULL |      NULL |      NULL |        0 |               0 |               0 | 2008-03-10 23:33:54 |         0 |                   |         -1 |
|   1005 | 5       |        1 | KVVU     | KVVU (FOX) | /home/bjm/.mythtv/channels/kvvu5_lasvegas.jpg |     NULL |              | 10811   |    32550 |      30380 |  34030 |           0 | 5      | 32768 | Default  |        0 |       1 |               |             0 |    NULL |      NULL |      NULL |        0 |               0 |               0 | 2008-01-24 00:51:48 |         0 |                   |         -1 |
+--------+---------+----------+----------+------------+-----------------------------------------------+----------+--------------+---------+----------+------------+--------+-------------+--------+-------+----------+----------+---------+---------------+---------------+---------+-----------+-----------+----------+-----------------+-----------------+---------------------+-----------+-------------------+------------+
2 rows in set (0.02 sec)

mysql>

Right back to status quo! 8-)

good luck,

--  bjm