[mythtv-users] Database backup failing

[Stephen Worthington]

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!