[mythtv-users] regenerate newssites table in mysql

[Stephen Worthington]

On Tue, 21 Apr 2015 14:22:30 +0200, you wrote:

>Hello,
>
>I was playing with newssites mysql table (making a restore from
>backup), and I delete the entire table.
>
>This is the lines that I execute:
>
>DROP TABLE IF EXISTS `newssites`;
>CREATE TABLE `newssites` (
>LOCK TABLES `newssites` WRITE;
>INSERT INTO `newssites` (`name`, `category`, `url`, `ico`, `updated`,
>`podcast`) VALUES ('...
>
>How could I generate again the table?
>
>I want to import and old data from a backup file.
>
>Thanks for your help.
>
>Best regards.

The backup file is actually the SQL source code needed to recreate the
all the tables in the mythconverg database.  You should be able to
just take your backup file, uncompress it, and edit it to cut out just
the section for the table you want, then run that SQL code.  I have
done that sort of thing before.  But that sounds like it was exactly
what you were doing, so do you have any idea what went wrong?  How
were you doing it?  What error messages (or other messages) did you
get?

For now, you should immediately take a copy of the latest full backup
you have of your database that has the table you need and store it
somewhere safe.  The automatic weekly backups (or daily backups if you
have added them like me) are on a rotation system and the oldest ones
get deleted as newer ones are done - so if it takes you a while to fix
your problem with this table, you risk having all your good copies of
it disappearing as new backups containing the bad table are done.

What I do for access to the mythconverg database is to use a small
batch file I wrote, called do_mythconverg.sh.  There is a copy on my
web server:

  http://www.jsw.gen.nz/mythtv/do_mythconverg.sh

Running that gets me to the SQL prompt in the mythconverg database.
Then to run a file of SQL code such as restoring a table would be:

source /path/to/file.sql


BUT!!!!! Always backup your database before playing with it!  Do not
play with your database if MythTV is doing anything at the time or you
do not have enough time to do a database backup, whatever work you are
doing, and a full restore if necessary, before the next recording
time!  Leave extra time for possible problems!!!

On Mythbuntu, the backup script is here:

/usr/share/mythtv/mythconverg_backup.pl

but I prefer to run it the same way cron does using the cron job here:

/etc/cron.weekly/mythtv-database

If you have the cron job installed using the MythTV Control Center,
you can run that script as root to backup the database with an
additional check on database integrity done first.  Then if anything
goes wrong, you can simply restore the complete database from the
backup.  Instructions for doing backups and restores are here:

  https://www.mythtv.org/wiki/Database_Backup_and_Restore