Wednesday, June 11, 2008

Mysql fix

I don't believe this post will be very useful to anyone else, but I want to record it anyway. I noticed a few weeks ago that my drupal installation was complaining that the watchdog table had crashed. With my limited understanding of mysql, I didn't event know that a table *could* crash. Everything else on the site looked fine to the anonymous user so I just ignored it.



That brings me to today. I found this interesting script online that will dump all of my mysql databases every hour to another file system. I figured I would give a shot. I entered my root db password and the dst directory and let her rip. I got a few errors right away:



[root@www storage]# mysql-backup.sh

mysqldump: Error 1194: Table 'watchdog' is marked as crashed and should be repaired when dumping table `watchdog` at row: 283

mysqldump: Got error: 145: Table './drupal/watchdog' is marked as crashed and should be repaired when using LOCK TABLES

mysqldump: Got error: 145: Table './gallery2/g2_CacheMap' is marked as crashed and should be repaired when using LOCK TABLES



Google quickly found the following post: http://gallery.menalto.com/node/72721, where a user kindly posted the solution to their own problem:



Stage 1: Checking your tablesRun myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as “checked.”You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.
Stage 2: Easy safe repairFirst, try myisamchk -r -q tbl_name (-r -q means “quick recovery mode”). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed.source:
Website



[root@www storage]# myisamchk -r -q /var/lib/mysql/drupal/watchdog.MYI

- check record delete-chain

- recovering (with sort) MyISAM-table '/var/lib/mysql/drupal/watchdog.MYI'Data records: 513

- Fixing index 1

Found block that points outside data file at 122592

MyISAM-table '/var/lib/mysql/drupal/watchdog.MYI' is not fixed because of errors

Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

[root@www storage]# myisamchk -r /var/lib/mysql/drupal/watchdog.MYI

- recovering (with sort)

MyISAM-table '/var/lib/mysql/drupal/watchdog.MYI'

Data records: 513

- Fixing index 1

Found block that points outside data file at 122592

- Fixing index 2



As you can see, I had to get rid of the -q option to get it to work, but it did in fact work. Same command worked to fix g2_CasheMap, but that one took quite a bit longer.


Looks like that did the trick.

3 comments:

Joris said...

thanks, I had the same problem!

Beatrice M said...

me too! option one fixed it right up. Really appreciate your write up.

jacksinn said...

I just came across this and all went well when taking out the -q option. Thanks!