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

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

Author:  cesman [ Mon Mar 22, 2004 10:35 am ]
Post subject:  Repairing broken MySQL tables

If you experience system crash it is probably a good idea to check the MySQL table to ensure your tables have not become corrupted. To do this:
Code:
cd /var/lib/mysql/mythconverg
/etc/init.d/mythtv-backend stop
/etc/init.d/mysql stop
myisamchk *.MYI

It is probably a good idea to send the output to a text file for investigation..
Code:
myisamchk *.MYI >> mythconverg_check.log

After the check is complete you may see something like this:

Quote:
myisamchk: warning: Table is marked as crashed and last repair failed
myisamchk: warning: Size of indexfile is: 1331200 Should be: 1024
myisamchk: error: Found wrong record at 0
MyISAM-table 'recordedmarkup.MYI' is corrupted
Fix it using switch "-r" or "-o"

You should check the log file to see which tables are broken and need to be fixed.
Code:
more  mythconverg_check.log

You should see comething like this:
Quote:
Checking MyISAM file: capturecard.MYI
Data records: 2 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links

---------

If a table is corrupt, you see something like this at the end of the check for that table:
Quote:
MyISAM-table 'videometadata.MYI' is usable but should be fixed

To fix the table:
Code:
myisamchk -r videometadata.MYI

Once you have repaired all the broken tables, you should run the check again and verify that all table are ok. You'll know all table are ok, because you should get a message stating that table X needs to be fixed... Don't forget to restart MySQL followed by the backend.

Author:  jesse [ Mon Mar 22, 2004 2:53 pm ]
Post subject: 

You can do the same thing from a separate computer by using MySQL Control Center (http://www.mysql.com/downloads/mysqlcc.html). You just right click on the table(s) then go to Tools. You can choose from Analyze, Check, Optimize, Repair.

This can be done from a Windows or Linux box.

Author:  notivo [ Mon Mar 29, 2004 2:17 pm ]
Post subject:  if you don't want to open up ports for mycc

you can try this command:

cd /var/lib/mysql/mythconverg
myisamchk -f -r *.MYI

it force repairs all broken tables

Author:  Human [ Thu Oct 14, 2004 10:44 am ]
Post subject:  Re: if you don't want to open up ports for mycc

notivo wrote:
you can try this command:

cd /var/lib/mysql/mythconverg
myisamchk -f *.MYI

it force repairs all broken tables


I don't know much about MySQL, so forgive me if this is a naive question.

Is there a reason we shouldn't make a MythTV box do this automatically on bootup, before the backend runs? Will it hurt good tables, or will it take a long time, or otherwise introduce problems you wouldn't otherwise get?

Author:  tjc [ Thu Oct 14, 2004 11:12 am ]
Post subject: 

There are some really good reasons that force repair is not the default option... Mostly that it's not always safe. This would be kind of like your car always starting up in gear...

Author:  tvmyth [ Wed Nov 17, 2004 9:41 am ]
Post subject:  database repair

I was looking for this post, and several other posts stated ...search for the post containing database and repair
Unfortunatly my searches failed because the word database was no where in this post, so here it is.

Author:  lynchaj [ Thu Feb 03, 2005 7:39 pm ]
Post subject: 

I had my hda1 partition fill up for some mysterious reason and then the mythtvbackend froze up and the database got corrupted. So what I did was:

/etc/init.d/mythtv-backend stop
mysqlcheck -c -umythtv -pmythtv mythconverg

heres where I detected something was wrong with the database. Lots of error messages about tables

so then I did:

mysqlcheck -r -umythtv -pmythtv mythconverg

which attempted to repair the database. Some errors went away but about half stayed. "Error 28" which you can figure out what that means by using

perror 28

prints out that it means "disk full" or something like that

so I was starting to sweat and locate the KnoppMyth R4V5 reinstall disk and told my wife that all her Christmas shows were probably lost. So, that motivated me to keep looking.

Did some research and found this command to repair the MySQL tables


cd /var/lib/mysql/mythconverg
myisamchk -r *.MYI

which fixed all the table errors, reran

mysqlcheck -c -umythtv -pmythtv mythconverg

all errors gone, so looking good. Rebooted, and crossed my fingers.

Sweat. More sweat,

finally it booted and everything was normal. All the wifes Christmas shows were saved and I slept indoors that night. Happy endings are always nice when its snowing outside.

Andrew Lynch

Author:  mccoyn [ Mon Feb 21, 2005 9:28 pm ]
Post subject: 

After repairing my tables everything worked fine for a few days. Then, suddenly, it stopped recording. It still lists scheduled recordings, but nothing gets recorded.

Some investigation showed that the hda4 (where my recordings are at) was full. I tried manually deleting the oldest recording that was supposed to auto-expire. Everything worked like it was supposed to, except that when it was done, the recording was still listed. Next, I tried to delete the file manually (getting the filename from mythweb.) This resulted in a file not found message. Apparently it is listed in the database, but there is no file to delete. This leads to an error, which seems to block the auto-expire.

Here is how I fixed it:

edit: A patch has been added to CVS which prevents this problem from stopping the auto-expirer. One partial fix, then, is to install from CVS.

1. From a console create an empty file using a text editor.
Code:
vi /myth/tv/empty.nuv


2. From the front end find the oldest auto-expire recording. Try to delete it. If this works, you have a different problem.

3. Use mythweb or something else to find the filename. From a console copy the empty.nuv file to the currupted filename.

4. From the front end delete the previously currupted file. This should work as it will delete the copy.

5. Repeat step 2 through 4 until you are confident you've cleaned out the problem. I'm not sure if this will require deleting all the files or not.

6. Delete empty.nuv

This is rather tedious, and there is probally a better way to do it, but at least it worked for me.

Author:  spud [ Wed May 10, 2006 2:44 am ]
Post subject: 

I was experiencing:
1) painfully slow skipping
2) commercial skip stopped working
3) recording editor would not start

I performed the steps described by cesman, which correctly identified recordedmarkup.MYI' as corrupt and repaired it.

One more step was required after repairing specifically the "recordedmarkup.MYI" database to fix the skipping problems.

mythcommflag --rebuild --all

This command rebuilds the seek table.

Author:  mal1ch1 [ Sun Jul 09, 2006 6:53 pm ]
Post subject:  Getting Error

On running the above command, I'm receiving a message "Parser not found for Codec Id: 94210 ! and also "You probably should modify the Master Server settings in the setup program and set the proper IP address".
And when I do the myisamchk -f -r *.MYI, it seems to be fixing things, but if I repeat it, it goes through the same sequence.

Author:  dspille [ Mon Nov 06, 2006 8:05 pm ]
Post subject: 

spud wrote:
I was experiencing:
1) painfully slow skipping
2) commercial skip stopped working
3) recording editor would not start

I performed the steps described by cesman, which correctly identified recordedmarkup.MYI' as corrupt and repaired it.

One more step was required after repairing specifically the "recordedmarkup.MYI" database to fix the skipping problems.

mythcommflag --rebuild --all

This command rebuilds the seek table.


I had exactly the same problem, and these steps fixed it.

Author:  BluesBrian [ Fri Nov 10, 2006 9:55 am ]
Post subject: 

I was having the same problem.. and SQL Error in MythWeb. (I'm using R5C7) using myisamchk -r *.MYI pretty much did the trick.

Code:
cd /var/lib/mysql/mythconverg
/etc/init.d/mythtv-backend stop
/etc/init.d/mysql stop
myisamchk *.MYI -r


A couple of hints to the "newbies" would be good...

1. Should I log in as root .. or mythtv (I used root)?
2. command lines to restart mysql and mythtv-backend

Minor things.. I used root.. and I used
Code:
shutdown -r now
to restart the box.. the old Windows trick! :roll: (OK, I'm hearing the newbie joybuzzer!) :twisted:

Author:  bigbro [ Sun Nov 26, 2006 10:38 am ]
Post subject: 

Quote:
A couple of hints to the "newbies" would be good...

1. Should I log in as root .. or mythtv (I used root)?
2. command lines to restart mysql and mythtv-backend


I struggled with these as well.

1) I log in as my user the SU before doing these commands. since I am modifying "System" those usually require an Super User.

2) I just reversed those commands putting start in place of "Stop"

After fixing the Tables I do this.

Code:
/etc/init.d/mysql start
/etc/init.d/mythtv-backend start
 

Author:  silentmic [ Thu Nov 30, 2006 6:38 am ]
Post subject: 

I had this problem. The /usr/sbin/mysqld and the /usr/share/mysql were completely gone. I started copying files from an old installation I had but gave up. I fixed it by reinstalling mysql using apt-get mysql-server. The check indicated no corruption of the database.

Author:  bruce_s01 [ Wed Aug 22, 2007 4:20 am ]
Post subject: 

Just to say thanks for the fix.

Bruce S.

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