Copyright

Creative Commons License

This work is licenced under a Creative Commons Licence.

User login

Mythtv Database Cleanup Script

I scribbled out this script quickly when I had a problem with my mythtv database crashing. I haven't really tested it much, but I think it may be useful to run as an automatic fix script triggered by a cron-job.

[update: I actually wrote this a fair while ago, and am just copying it here for reference - I would say that if you don't understand what's going on, DO NOT USE ANYTHING HERE, as it will likely break your database and set your computer on fire. Seriously. Even I have never used this in anger, as I lost interest in the script before I got it working how I wanted].

It's designed to be run automatically at boot in case the shutdown has been improper, and caused database corruption. The basic methodology of the script is the same as the manual method:
1. cd to the database dir (/var/lib/mysql/mythconverg)
2. Check for problems: myisamchk --update-state -s *.MYI
3. Fix any problem databses using the command: myisamchk -r NAME-OF-BROKEN-DB-OR-TABLE
This script, when tested, does not work. The reason for this is that it seems not to be possible to pipe the output of the myisamchk command into anything. I will investigate this further though, as I really want to get this automated. The reason that it does not work is that the myisamchk command outputs details to STDERR not STDOUT.
[EDIT: UPDATED SCRIPT AT END]

 
                #!/bin/bash
                
                ### Needs more checking?
                ### Needs to notify admin that it's run?
                ### Needs testing? How can create crashed databases?
                ### Does mysql etc need stopping?
                
                ### User-definable Aliases
                DATABASE_DIR=/var/lib/mysql/mythconverg
                CHECK_COMMAND=$(myisamchk --update-state -s *.MYI)
                FIX_COMMAND=$(myisamchk -r)
                MYSQLD=/etc/init.d/mysql
                MYTHTV_BACKEND=/etc/init.d/mythtv-backed
                
                ### Functions
                function stop_services {
                        $MYTHTV_BACKEND stop
                        $MYSQLD stop    
                }
                
                function start_services {
                        $MYSQLD start
                        $MYTHTV_BACKEND start
                }
                
                function grab_broken {
                        BROKEN_DATABASES=$($CHECK_COMMAND | grep MYI | grep myisamchk | cut -d' ' -f4)
                }
                
                function fix_broken {
                        while read LINE
                        do
                                $FIX_COMMAND "$LINE"
                        done <<< "$BROKEN_DATABASES"
                }
                
                #### Main
                stop_services
                grab_broken
                fix_broken
                start_services

The script below deals with fixing the databases etc I believe, though I couldn't be bothered to write it into a full script, so I'll leave the stuff above as a shortcut to the destination to anyone interested.

 
                #!/bin/bash
                
                fixcommand="myisamchk -r"
                command="myisamchk -s *.MYI"
                output=$($command 2>&1)
                
                for word in $output
                        do
                        [[ $word = *.MYI ]] &&
                        $fixcommand $word
                        done