[mythtv-users] 0.21 and database corruption/how to rebuild and update from backup
Robin Gilks
g8ecj at gilks.org
Mon Mar 17 22:16:54 UTC 2008
> 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
More information about the mythtv-users
mailing list