Out of Sync: Maddy's Blog

I am encountering data corruption every time MySQL is restarted.  The restart is done through scripts and I encounter the following error:

 myisamchk: MyISAM file /global/mysql/mysql/db.MYI

myisamchk: warning: 1 client is using or hasn't closed the table
properly

MyISAM-table '/global/mysql/mysql/db.MYI' is usable but should be fixed

 After  I googled, I found that running myisamchk  on the table was the most commonly provided solution and tried it:

bash-2.05#  /opt/mysql/mysql/bin/myisamchk -r /global/mysql/mysql/db.MYI

- recovering (with sort) MyISAM-table '/global/mysql/mysql/db.MYI'

Data records: 6

- Fixing index 1

- Fixing index 2

bash-2.05# echo $?

0

 but the  problem resurfaces every time the DB is restarted!!

 Got ideas?

Today in my DB Admin II workshop, faced a rather strange behaviour which none of us had a clue about.  The environment was linux + 10.2.0.1 with normal fs based storeage (no ASM/RAW volumes).  We manually corrupted one of the dbf files using dd.

1.) Let's say the file# was 7 and block id# corrupted were 51,52,53,54. 

2.) Flushed buffer_cache

3.) Executed select statement on the table to confirm that there was corruption.

4.) used dbv file='<file>' blocksize=8192 and confirmed that the blocks from 51-54 were corrupted.

5.) Checked alert_log file and it showed only 53 and 54 as corrupted!

6.) Used RMAN> blockrecover datafile 7 block 53,54;  and recovered the blocks.

7.) issued the select statement and was surprised to find that it worked!!

8.) select file#,blockid from v$database_corruption_block to find that there were no more corrupted blocks!!

9.) Executed dbms_repair.check_object  ==>  reports block 51 alone as corrupted!

10.) Executed dbms_repair.fix_corrupt_blocks to fix 51 and re-executed dbms_repair.check_object ==> No corruptions!!!

 Now, this stumped my trainer as well who was forced to blush and left me wondering what the heck?

 SMF repository corruption is one irritating issue in Solaris 10 with x64 systems.  When you are hitting the issue,  you will see something like this:

Problem

svc.configd: smf(5) database integrity check of:

    /etc/svc/repository.db

  failed. The database might be damaged or a media error might have
  prevented it from being verified.  Additional information useful to
  your service provider is in:

    /etc/svc/volatile/db_errors

  The system will not be able to boot until you have restored a working
  database.  svc.startd(1M) will provide a sulogin(1M) prompt for recovery
  purposes.  The command:

    /lib/svc/bin/restore_repository

  can be run to restore a backup version of your repository.  See
  http://sun.com/msg/SMF-8000-MY for more information.

Requesting System Maintenance Mode
svc.configd exited with status 102 (database initialization failure)

Root password for system maintenance (control-d to bypass): 
single-user privilege assigned to /dev/console.
Entering System Maintenance Mode

Solution

What the system doesn't tell you is that the / file system is mounted as read only!  Hence, you need to remount the file system in rw mode before executing the recovery script :

 # mount -o remount,rw /
# /lib/svc/bin/restore_repository

See http://sun.com/msg/SMF-8000-MY for more information on the use of
this script to restore backup copies of the smf(5) repository.

If there are any problems which need human intervention, this script will
give instructions and then exit back to your shell.

Note that upon full completion of this script, the system will be rebooted
using reboot(1M), which will interrupt any active services.

repository-*-[0-9]*[0-9]: No such file or directory
There are no available backups of /etc/svc/repository.db.
The only available repository is "-seed-".  Note that restoring the seed
will lose all customizations, including those made by the system during
the installation and/or upgrade process.

Enter -seed- to restore from the seed, or -quit- to exit: -seed-

After confirmation, the following steps will be taken:

svc.startd(1M) and svc.configd(1M) will be quiesced, if running.
/etc/svc/repository.db
    -- renamed --> /etc/svc/repository.db_old_20070807_083027
/lib/svc/seed/global.db
    -- copied --> /etc/svc/repository.db
and the system will be rebooted with reboot(1M).

Proceed [yes/no]?

For what its worth!