[mythtv-users] restoring some channels to get back EIT functionality

[Stephen Worthington]

On Sat, 31 Dec 2016 20:08:06 +0000, you wrote:

>On 31 December 2016 at 16:01, Stephen Worthington <stephen_agent at jsw.gen.nz>
>wrote:
>
>> It is also possible to
>> restore the old channel table to a different table name (eg
>> channel_old), then compare the two tables using SQL commands to see
>> what the differences are and where you went wrong, and just copying
>> the specific rows you want back again from the channel_old table to
>> the channel table, then finally deleting the channel_old table.
>>
>
>To make sure I had a clue of what tables had changed and which changes were
>worth preserving, I thought I might just do a diff of the backups I took
>before and after deleting the channels. But I got
>
>diff: memory exhausted
>
>This seems to be due to the absurdly long lines used by the sql dump,
>rather than to the overall size of the file. Am I right? Is there a simple
>workaround?

Diff is probably doing all its work on the files in memory, so two
copies of a 2.7 Gibyte file is likely just too much.  I think all you
need to extract from the backup is the old channel table though, and
emacs may be able to do that if it pages through the file instead of
loading it all into memory.  You may need to set options to make it do
that - I do not use emacs at all so I can not suggest what.  I use my
SlickEdit Pro (payware) for jobs like this.  I also have payware diff
tools that seem to be able to handle huge files.

To find the start of the SQL for the channel table in the unzipped
.sql file, search for `channel` (those quotes are backtick characters,
include them in the search).  Copy out all the lines until the start
of the next table (where it says "Table structure for table" for the
next table).  If you use channel groups (such as favourite channels),
you probably also want the next two tables as well, channelgroup and
channelgroupname.  Save the copied text to another file, say old.sql.

If you want to do SQL diffing between the old and new tables, you then
need to edit the old.sql file and change all the table names - I
suggest adding _old on the end.

Shut down MythTV - all frontends and mythbackend and any other things
that might use the database.  Do a backup.

To read in your old.sql file and create the new *_old tables, or to
overwrite the existing channel tables, you use the source command from
the mysql command line:

  source old.sql

If you do not know how to get access to the mythconverg database via
the mysql tool, you might like to download the script I use for this
from my web server:

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

Once you have a mysql command prompt, just type in the source command
above.  The source command simply executes all the SQL commands in a
source file.  It is a mysql tool command, not an SQL command, so it
does not have a ; at the end and is terminated by using the <Enter>
key.  Use the help command to see the other mysql tool commands.

To do a diff between mysql tables, see this web page:


http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx

So the command for diffing the channel and channel_old table would be:

  SELECT * FROM (
    SELECT * FROM channel_old
    UNION ALL
    SELECT * FROM channel
  ) tbl
  GROUP BY chanid
  HAVING count(*) = 1
  ORDER BY chanid;

This is big query and can take a while on large tables.

To see a table's columns, use:

  desc <table name>;

To copy a row from the channel_old table to the channel table, use
this sort of command:

  insert into channel select * from channel_old where <matching
condition>;

To delete unwanted rows in the channel table, use:

  delete from channel where <matching condition>;

To update data in the channel table, use the update command.  Here is
an example that turns on the EIT data collection for all the channels
of sourceid 1:

  update channel set useonairguide=1 where sourceid=1;