[mythtv-users] Database backup failing

[Bob Sully]

> On Wed, 11 Jul 2018 23:22:00 -0700, you wrote:
> 
>> Hi all -
>> 
>> I have been having issues with Mysql since the latest update.  First,
>> the update itself failed and I had to ask Dr. Google how to MacGyver a
>> fix for that (yes, it worked, got it installed).  However, since then 
>> I
>> have not been able to get mythconverg_backup.pl to work.  If fails as
>> follows:
>> 
>> rcs at vader: ~]$ /usr/share/mythtv/mythconverg_backup.pl -verbose
>> 
>> Configuring environment:
>>   -    username: rcs
>>   -        HOME: /home/rcs
>>   - MYTHCONFDIR: /home/rcs/.mythtv
>> 
>> Parsing configuration files:
>>   - checking: /home/rcs/.mythtv/config.xml
>>      parsing: /home/rcs/.mythtv/config.xml
>>   - checking: /home/rcs/.mythtv/backuprc
>>      parsing: /home/rcs/.mythtv/backuprc
>> 
>> Applying command-line arguments.
>> 
>> Checking configuration.
>> 
>> No DBSchemaVer specified, querying database.
>> Found DBSchemaVer: 1344.
>> 
>> Database Information:
>>          DBHostName: 192.168.1.9
>>              DBPort: 3306
>>          DBUserName: mythtv
>>          DBPassword: XXX
>>              DBName: mythconverg
>>         DBSchemaVer: 1344
>>   DBBackupDirectory: /misc/dbbackup
>>    DBBackupFilename: mythconverg-1344-20180711231352.sql
>> 
>> Executables:
>>           mysqldump: mysqldump
>>            compress: gzip
>> 
>> Attempting to use supplied password for mysqldump.
>> Any [client] or [mysqldump] password specified in the MySQL options 
>> file
>> will
>> take precedence.
>> 
>> Executing command:
>> '/usr/bin/mysqldump' --defaults-extra-file='/tmp/aVusmdWS2l'
>> --host='192.168.1.9' --port='3306' --user='mythtv' --add-drop-table
>> --add-locks --allow-keywords --complete-insert --extended-insert
>> --lock-tables --no-create-db --quick --add-drop-table 'mythconverg' 
>> 2>&1
>> 1>'/misc/dbbackup/mythconverg-1344-20180711231352.sql'
>> 
>> mysqldump exited with status: 2
>> mysqldump output:
>> mysqldump: Got error: 1812: Tablespace is missing for table
>> `mythconverg`.`music_albumart`. when using LOCK TABLES
>> 
>> 
>> 
>> Running 0.28.x-fixes (via mythbuntu repo) on Ubuntu 16.04 LTS, all
>> currently updated.
>> 
>> I'm not even using mythmusic (not a fan of the interface...I use vlc 
>> or
>> Rhythmbox to play music files on this machine).
>> 
>> Is there an easy way to fix this?  I am by no means a Mysql guru.
>> Thanks in advance!  I definitely am not happy with not like not having 
>> a
>> current DB backup (though I back up the whole system every couple of
>> weeks).
> 
> Before trying any fixes, it is best to make a manual backup of the
> database files, just in case.  To do that, first shut down mythbackend
> and MySQL/MariaDB (sudo or root required):
> 
> systemctl stop mythtv-backend
> systemctl stop mysql
> 
> Then go to where the mythconverg database is stored:
> 
> cd /var/lib/mysql
> 
> Find out how big your database is:
> 
> du -h mythconverg
> 
> Check that you have enough free space on your system drive for a
> second copy of the database:
> 
> df -h .
> 
> If so, then just copy the entire database directory:
> 
> cp -a mythconverg mythconverg-bak
> 
> If there is not enough free space, you will need to do the copy to
> another drive.
> 
> Then restart MySQL/MariaDB and try to repair the mythconverg database:
> 
> systemctl start mysql
> 
> The first thing to try when you have any problems with database tables
> is to run the database check and repair script:
> 
> /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl
> 
> If you originally installed MythTV with the old Mythbuntu Control
> Centre, that script should be set up to be automatically run daily
> here:
> 
> /etc/cron.daily/optimize_mythdb
> 
> If it is not in cron.daily, put it there.  MySQL/MariaDB databases
> seem to be unstable unless a check and repair is done regularly, and
> it should also be done after any bad shutdown, such as a power failure
> or use of the reset button, as tables can be damaged by that.
> 
> If optimize_mythtb.pl does not work, try using the mysqlrepair command
> on the bad table(s).  It has various options you can try.  The backup
> stopped when it found the first bad table, so there may be others
> after that also in need of repairs.
> 
> If that does not work, then the other thing that you can do is to
> uninstall the mythmusic package.  Installing mythmusic creates all the
> music_* tables in the database, but I am not sure if removing it will
> also delete all those tables.  If it does not, once the package is
> removed, you can manually delete those tables with a DROP command for
> each table, if necessary.  This is almost certain to fix any problems
> in the music_* tables, but it is still possible that there are
> problems in the tables that were never processed due to the backup
> script stopping.
> 
> Once all appears working again, check that the backup script works,
> before deleting the backup copy of the database files:
> 
> rm -r /var/lib/mysql/mythconverg-bak
> 
> WARNING: Copy and paste the rm command - mistyping an rm -r command
> can cause a disaster!
> 
> 
>> 
>> Great information Stephen.
> I will be keeping a copy of this email in the hope that I will never 
> need
> it :-)
> George



Stephen -

Thanks very much - finally found some time to do this.  The 
optimize_mythdb.pl and mysqlrepair scripts did not work - even after 
doing a remove/purge of mythmusic.

I wound up going into mysql and dropping mythmusic-generated tables 
(about 10 of them).  After doing that, all is well.

I did add the optimize_mythdb.pl script to /etc/cron.daily, also - hope 
that will keep the database in good shape for here on in.

Thanks again!

Bob