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

[Robin Gilks]

> 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
>

That must rate as one of the best replies I've ever seen on -users.

Excellent - clear, to the point, totally reproducable. 11/10 :-)

-- 
Robin Gilks