LinHES Forums
http://forum.linhes.org/

Repairing broken MySQL tables
http://forum.linhes.org/viewtopic.php?f=3&t=1043
Page 2 of 2

Author:  tjc [ Wed Dec 12, 2007 7:30 pm ]
Post subject: 

Successfully completing the steps described above will ensure that the DB tables are self consistent, but not that they contain all the necessary data for your recordings. The following thread contains directions for other things you may to do to complete the recovery after doing DB repairs. http://mysettopbox.tv/phpBB2/viewtopic.php?t=14002&highlight=mythcommflag

Author:  datobin1 [ Mon Feb 04, 2008 8:57 am ]
Post subject: 

I had a problem last night with my program table that I was not able to correct with the normal methods but, came up with another solution and wanted to share it incase anyone has a similar problem.

Symptoms of the problem;
mythfrontend works fine but there is no guide data/ show listings/ channel info.
if you try to look at the mythconverg database through webmin it will tell you the program table is corrupt.


Results from an attempt to repair the table;
When trying all the different methods of repair the result with an error 5

If I shutdown the mysql and tried to copy the tables file program.MYI it would return an error because the file was corrupt..... This is when I new I was in trouble.

How I fixed the table

Code:
#su
#mysql
mysql> TRUNCATE TABLE program;
mysql>exit
#mythfilldatabase


After mythfilldatabase completed all was good.

This method only works on a table that can be repopulated and where the data file is corrupt but the table schema is in tact.

The truncate table command deletes all the data in the table but leave the table structure in tact.

Author:  BluesBrian [ Sun Feb 24, 2008 1:32 am ]
Post subject: 

yet another system crash, and more filesystem fixes..

this time, mysql won't start, and I get

Quote:
/var/log/syslog
mysqld_safe[3918]: started
mysqld[3921]: 080223 23:07:40 InnoDB: Started; log sequence number 0 43665
mysqld[3921]: 080223 23:07:40 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './mysql/host.frm'
mysqld[3921]: 080223 23:07:40 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './mysql/host.frm'
mysqld[3921]: 080223 23:07:40 [ERROR] Fatal error: Can't open and lock privilege tables:
Incorrect information in file: './mysql/host.frm'
mysqld_safe[3932]: ended

/etc/init.d/mysql[4067]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping'
resulted in
/etc/init.d/mysql[4067]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
/etc/init.d/mysql[4067]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
/etc/init.d/mysql[4067]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
/etc/init.d/mysql[4067]


This seems to indicate that /var/lib/mysql/mysql/host.frm got corrupted. Does anyone have a suggestion on how to fix this file? (This file is not readable on my working machine!)
Thanks!

Author:  Too Many Secrets [ Wed Oct 21, 2009 5:05 pm ]
Post subject: 

In R6 this needs to be changed a bit. This worked for me.

Code:
cd /data/srv/mysql/mythconverg
sv stop mythbackend
sv stop mysql
myisamchk *.MYI >> mythconverg_check.log

To repair
Code:
myisamchk -r <table name here>

Then to restart
Code:
sv start mysql
sv start mythbackend

Author:  tjc [ Wed Oct 21, 2009 7:59 pm ]
Post subject: 

This version of optimize_db.sh should work. I thought it had been published here before, but apparently not... :-/

Code:
#!/bin/bash

. /usr/LH/bin/backupcommon || {
    echo 1>&2 "Can not load common settings!"
    exit 1
}

must_be_root

# Prevent mythshutdown from shutting down the system in the middle...
lock_myth

# The database needs to be idle when we do this...
# Having it auto restarted would also be "bad". ;-)
stop_mythbackend
stop_mysqld

cd $DATABASE_DIR || fatal "Could not cd to $DATABASE_DIR"

for tbl in *.MYI ; do
    # If the fast way fails fall back on the old slow way.
    $MYISAMCHK --recover $tbl || {
        echo "Fast recovery of $tbl failed, attempting slower safe recovery..."
        $MYISAMCHK --safe-recover $tbl ||
            fatal "Even safe recovery of $tbl failed! Aborting!"
    }
    $MYISAMCHK --analyze $tbl
done

# Resatart the servers in the right order...
start_mysqld
start_mythbackend

# Unlock the system again...
unlock_myth

Author:  Girkers [ Wed Oct 21, 2009 8:59 pm ]
Post subject: 

tjc any chance of getting the updated file put in the Arch (LinHES) repositories to make it easier to get?

Author:  tjc [ Thu Oct 22, 2009 9:19 pm ]
Post subject: 

A week ago I thought it was already there, but apparently it never got added. Maybe this weekend I'll get some breathing room and be able to do it.

Author:  Too Many Secrets [ Fri Jun 22, 2012 12:47 pm ]
Post subject:  Re:

tjc wrote:
This version of optimize_db.sh should work. I thought it had been published here before, but apparently not... :-/

Code:
#!/bin/bash

. /usr/LH/bin/backupcommon || {
    echo 1>&2 "Can not load common settings!"
    exit 1
}

must_be_root

# Prevent mythshutdown from shutting down the system in the middle...
lock_myth

# The database needs to be idle when we do this...
# Having it auto restarted would also be "bad". ;-)
stop_mythbackend
stop_mysqld

cd $DATABASE_DIR || fatal "Could not cd to $DATABASE_DIR"

for tbl in *.MYI ; do
    # If the fast way fails fall back on the old slow way.
    $MYISAMCHK --recover $tbl || {
        echo "Fast recovery of $tbl failed, attempting slower safe recovery..."
        $MYISAMCHK --safe-recover $tbl ||
            fatal "Even safe recovery of $tbl failed! Aborting!"
    }
    $MYISAMCHK --analyze $tbl
done

# Resatart the servers in the right order...
start_mysqld
start_mythbackend

# Unlock the system again...
unlock_myth


Does this script still work with 7.2? I tried the .py script for a table repair, and it didn't work for me, so I had to do the manual procedure outlined in this thread. But I would like to automate it with a cront script above.

Page 2 of 2 All times are UTC - 6 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/