Return to Tips and HOWTOs

Tips for using MySQL with MythTV

Please understand that mucking about with your MythTV database without knowing what you’re doing (or making a backup) can ruin your weekend.

Versions:

MySQL 4.X (should work with versions up to and including 5.0.X)
MythTV 0.18.1 (should work with versions up to the 0.20.X series)

Official MythTV Document sections:

Removing unwanted channels

Saving or restoring the database

SQL Magic:

Preliminaries:

You should back up the MythTV database before deleting channels. On most systems, this does not take very much time, and can be a lifesaver if something goes terribly wrong with the tips below – WHICH CAN OFTEN HAPPEN, since you are (presumably) a human being doing this. Typos are often deadly here…

From a shell prompt, do the following:

mysqldump -p --add-drop-table -B mythconverg | gzip - > mysqlbackup.sql.gz

Enter your MySQL user password (you have a password, right?). The backup will be created. To restore, do the following:

gunzip mysqlbackup.sql.gz
mysql -p < mysqlbackup.sql

Next, to be able to use the SQL statements below, you must be logged in to the database.

mysql -p

Then, enter your MySQL password. At the mysql prompt, enter:

use mythconverg;

Now you can go on to use the tips below.

Deleting a channel, simplified:

Lets say you wish to delete channel 493 from your program guide. You don't have to look up the chanid in multiple steps, as the official docs describe. You can simply do this one step:

DELETE channel,program FROM channel NATURAL LEFT JOIN program WHERE channum=493;

You can see that we've eliminated the need to explicitly look up the channel id (chanid) from the channel table and can just use the channel number (channum), by doing a NATURAL LEFT JOIN between the channel and program tables.

Deleting a range of channels:

If you wish to purge channels from the database that you have just deleted from zap2it (and, therefore, still have program data for in your database), deleting each channel individually can be a pain. You can use the BETWEEN statement to specify a range of channels to delete, like so:

DELETE channel,program FROM channel NATURAL LEFT JOIN program WHERE channum BETWEEN 664 AND 776;

Will delete channels between 664 and 776, without having to resolve the chanid first (as it is documented in the official docs).

Deleting zap2it deleted channels:

If you are a zap2it user, and you have deleted channels over 12-13 days ago, you might want to purge all channels that have no program data from your database. The official MythTV docs list methods to delete a channel, but here's a SQL statement to do it "automagically". Note that you MUST back up your mysql database in case of a typo or other problem with the DELETE statements.

First, you can do some tests, as a sanity check:

SELECT channel.chanid, channel.channum FROM channel NATURAL LEFT JOIN program WHERE program.title IS NULL;

This should return the list of channels with "no data". Note that if any program information still exists for that channel, they will not be returned in this list.

If that select returns the correct channels (which I check by hand against MythWeb to make sure), then you can issue a DELETE statement:

DELETE channel,program FROM channel NATURAL LEFT JOIN program WHERE program.title IS NULL;

Now, go to your Program Guide (or MythWeb) and check out you're newly neatened listings. Very simple, and it Works for Me(tm).

1 ping

  1. […] channels that I’ve delselected in my channel lineup at Schedules Direct, I happened upon this blog post. Seems to work great […]

Leave a Reply

Your email address will not be published.