MySql is a registered trademark of Sun Microsystems, Inc.
Disclaimer: This documentation is not official or authorized in any way by any third parties. Use the information at your own risk.
I've been lucky enough to be able to live without an undelete feature on my computer for years. For the most part, I haven't had many problems - usually I have a backup copy of the file I can work with. In this case, I had a backup, but the problem was it wasn't just a file that needed to be restored. I was trying to delete a recording in my mythtv system and I managed to hit the arrow key at the same time I hit the menu key. Without realizing it, I had deleted a recording from the system that I hadn't watched yet. If that wasn't bad enough, I also don't have the system set up in such a way that I can undelete any recordings. Since I have the actual video file in a backup location and since I have running backups of the mysql database, I decided to take a quick look at how I might be able to recover the recording.
Putting the recording back into the storage group is by far the easiest part of the process. The much larger problem I had to deal with is the database and I actually expected the task at hand to be a lot harder than it turned out to be. What I wasn't prepared for, however, is just how much data mythtv stores about a single program. To give an overview, here are some of the more important tables (as of 0.21-fixes):
- recorded - This table contains the program start time, end time, title, subtitle, description and a few other pieces of information.
- recordedmarkup - Contains the bookmarks used by the automatic commercial skip feature
- recordedseek - A huge table containing seek information for each 1/2 second of recorded video. A one hour program has about 13,000 entries on my particular system.
- oldrecorded - This table contains much the same information as the recorded table, but it's only used by the scheduler.
- recordedprogram - Contains in-depth program details for each recording in the system.
- recordedrating - Contains rating information for each recorded program
- recordmatch - A mystery table. I assume this is used by the scheduler based on what I see of the table's columns, but the documentation in the wiki doesn't describe it. (http://www.mythtv.org/wiki/Category:DB_Table)
...and that's all of the places where data is changed when a program is deleted as far as I could figure out. The information about the first three tables I got from the backend server source code in mainserver.cpp:DoDeleteInDB(). For my particular situation, I created a new database on another Linux system and restored the database to the state it was in prior to me deleting the program. I then spent some time comparing the actual database to my restored database to figure out where the changes were. For each place where I found data had been altered or deleted, I used mysqldump to create a script I could use to bring the data back into my live system.
mysqldump is the reason I'm writing this post. Sometimes it's easy to overlook the command line possibilities, but they sometimes contain some very powerful features. In this case, I needed to dump out large amounts of information in a format that could be imported to the other database without having to do a lot of editing of the dump file to get the correct results. As a rule of thumb, the more you can eliminate human involvement in a process, the less mistakes you'll make along the way - so editing dump files was something to be avoided.
The primary information needed for this is the chanid and the starttime of the program. Once you have that information, you can use it to select the information you want when you make the dump from the database. Here is an example:
$ mysqldump --skip-opt --complete-insert --no-create-info --order-by-primary mythconverg --tables recordedseek --where="chanid = 1311 and starttime = '2010-01-14 21:00:00'" -u root -p >>program.sql
In this example, most of the options cause mysqldump to only output insert statements and the reason behind that is I didn't want or need to drop or create any tables which may have caused a much larger loss of data. The --tables parameter tells mysqldump which tables you want in the output file. The --where parameter allows you to select specific information from the table so that you don't get any information you don't need or want. This is the key to using mysqldump effectively when you need to do a partial restore of information between two databases. I repeated the above command for each table in the list above and created a script that could be run on the live system to import the information back into the live database. Restoring the database information is easy:
$ mysql -u root -p mythconverg <program.sql
After entering a password and waiting a few seconds, I simply restarted mythfrontend and all of the information about the recording showed up in the watch recordings section of mythtv. Playback is just as I would expect if I hadn't accidentally deleted the recording, so I'm going to consider this a success.
Subscribe