Repair corrupted MySQL tables

     Sometimes mysql database tables get corrupted for various reasons like server reboot, multiple processes trying to write to same table etc.. If your databses is using MyISAM storage engine ( which is the default one ), you can recover these tables the following way.

     MyISAM provides a built-in tool for repairing corrupted tables.  The command is myisamchk, which can be used for varied functions like scanning database for errors, tuning tables for performance, repair corrupted tables etc.

     There is also an SQL variant for some functionalities of myisamchk, like CHECK TABLE,REPAIR TABLE etc..  While myisamchk is ran from command line, these queries should be ran from sql console.

     For me, the issue started when I tried to backup a database with mysqldump

safeer@db2 ~:$sudo /usr/bin/mysqldump oncall > oncall.sql
mysqldump: Got error: 1194: Table 'log' is marked as crashed and should be repaired when using LOCK TABLES

     Now this is a sign of problem and to start with troubleshooting, we should first check the table for errors.

     Two things before we proceed: as a best practice if possible, we should shut down the mysql server or make sure no one is accessing the tables ( obtaining a lock on the tables ).  Another thing to do is, backup the database before repairing its table.  In some cases, there is a possibility that while repairing the tables some data might be lost.

     For my case the mysql server was not a production server, rather an internal service.  So I was able to shut it down.  But backing up the databaes using mhysqldump was not an option as you saw above, so I copied out the database's data directory.  In my system the database directory is at /var/lib/mysql/oncall.

     Now let us start checking the table with myisamchk.  myisamchk requires the actual table filename with full path under mysql data directory to work with.

safeer@db2 ~:$sudo myisamchk /var/lib/mysql/oncall/log.MYI
Checking MyISAM file: /var/lib/mysql/oncall/log.MYI
Data records:  116889   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found key at page 1439744 that points to record outside datafile
- check record links
myisamchk: error: Checksum for key:  1 doesn't match checksum for records
myisamchk: warning: Found     116889 parts                Should be: 116890 parts
MyISAM-table '/var/lib/mysql/oncall/log.MYI' is corrupted
Fix it using switch "-r" or "-o"

     Now that we know the table is corrupted, let us try fixing it.

safeer@db2 ~:$sudo myisamchk -r /var/lib/mysql/oncall/log.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/oncall/log.MYI'
Data records: 116889
- Fixing index 1

     Check again to confirm the table is fixed

safeer@db2 ~:$sudo myisamchk /var/lib/mysql/oncall/log.MYI
Checking MyISAM file: /var/lib/mysql/oncall/log.MYI
Data records:  116889   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

     The table is fixed now.

     Now let us see how we can do the same task from mysql console using sql commands.  To do this, I am copying the backed up corrupted mysql files to the /var/lib/mysql/oncall location so that the db will be in the corrupted state again (This is the advantage of doing file system level db backup, you can play around it by copying data files back and forth :) ).

Select Database

mysql oncall>use oncall;

Check Table

mysql oncall>check table log;
+------------+-------+----------+-----------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                                  |
+------------+-------+----------+-----------------------------------------------------------+
| oncall.log | check | warning  | Table is marked as crashed                                |
| oncall.log | check | warning  | 3 clients are using or haven't closed the table properly  |
| oncall.log | check | error    | Size of datafile is: 22364116         Should be: 22364308 |
| oncall.log | check | error    | Corrupt                                                   |
+------------+-------+----------+-----------------------------------------------------------+
Repair Table

mysql> repair table log;
+------------+--------+----------+----------+
| Table      | Op     | Msg_type | Msg_text |
+------------+--------+----------+----------+
| oncall.log | repair | status   | OK       |
+------------+--------+----------+----------+
1 row in set (11.29 sec)

Check Again

mysql> check table log;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| oncall.log | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (7.44 sec)

     If possible always use myisamchk, it has more options that can be used if you run into problems while repairing tables.  A few options worth remembering are:

For checking:

  -F, --fast          Check only tables that haven't been closed properly.
  -m, --medium-check  Faster than extend-check, but only finds 99.99% of
                      all errors.  Should be good enough for most cases.
  -e, --extend-check  Check the table VERY throughly.  Only use this in
                      extreme cases as myisamchk should normally be able to
                      find out if the table is ok even without this switch.

For repairing:

  -r, --recover       Can fix almost anything except unique keys that aren't
                      unique.
  -o, --safe-recover  Uses old recovery method; Slower than '-r' but can
                      handle a couple of cases where '-r' reports that it
                      can't fix the data file.
  -q, --quick         Faster repair by not modifying the data file.
                      One can give a second '-q' to force myisamchk to
                      modify the original datafile in case of duplicate keys.
                      NOTE: Tables where the data file is currupted can't be
                      fixed with this option.

     For more options, checkout the man page and following mysql website links

MYISAMCHK

CHECK TABLES

Bash Recipes Part 3 - Converting between decimal and hexadecimal

     People who work with computer systems would have encountered the problem of converting between decimal and hexadecimal numbers one time or the other.  Here is a bash solution for it

function decToHex()
{
DEC=${1?You should provide an argument}
echo $DEC|grep -q -E "^[0-9]+$" || { echo "The argument is not a decimal"; return 1 ; }
printf "%x\n" ${DEC}
}

function hexToDec()
{
HEX=${1?You should provide an argument}
echo $HEX|grep -q -E "^[0-9a-fA-F]+$" || { echo "The argument is not a hexadecimal"; return 1 ; }
printf "%d\n" 0x${HEX}
}

A few examples

safeer@penguinpower:~$ decToHex 10
a
safeer@penguinpower:~$ hexToDec a
10
safeer@penguinpower:~$ decToHex 1234
4d2
safeer@penguinpower:~$ hexToDec abc
2748

Disable IPv6 on RHEL5

     On one of my RHEL5 boxes, there where a couple of services that were allergic to IPv6.  Hence I had to disable the IPv6 service, here is a rough how-to:

To disable IPv6 we have to do three things:

1) Disable the IPv6 kernel module
2) Remove / Disable IPv6 related network configuration entries.
3) Turn off IPv6-only services.

To disable kernel module, add following lines to /etc/modprobe.conf

alias net-pf-10 off
options ipv6 disable=1

Now remove ipv6 entries from network configuration file /etc/sysconfig/network

Change the setting NETWORKING_IPV6=yes
                to NETWORKING_IPV6=no

Also, remove any other IPv6 related settings you might find here.  Most probable ones are:
      
       IPV6_DEFAULTGW and IPV6_AUTOCONF

In addition you can also turn off ipv6 per Ethernet interface.  But in my experience, this is optional.

Go to /etc/sysconfig/network-scripts

Find if any interface have ipv6 turned on

      grep -i ipv6 ifcfg*

If enabled, you will find following entries in the ifcfg-INTERFACE files.

     IPV6INIT=yes
     IPV6ADDR=YOUR_IPv6_IP_HERE

Remove those entries.

Now disable any dedicated ipv6 based service.  Currently the only only ipv6 exclusive service I am aware of is iptable for ipv6 ( called ip6tables ).  Turn that off.

     service ip6tables stop
     chkconfig ip6table off

   All set, reboot the host and it will come up with ipv6 turned off.

     reboot

A quick primer to Linux sudo

     To simply put it, sudo (superuser do) is the facility  to run commands as another user on a system without knowing that user's password.  sudo allows flexibilities like, allowing a limited set of commands ( as opposed to all command allowed for the real user as whom you are sudo-ing ) for sudo users.  It also allows sudo privilege at group level.  These features in combination makes sudo a powerful utility that can control and manage privileges in a granular way.

     For a non root user to run a command that requires root privilege, prepend the command "sudo" to the command that needs to be ran as sudo.  It will prompt for the non root user's password, and once authenticated the command will be ran with root privilege.  If you want to run the command as another user, you should prefix "sudo -u <username>" to the command.   For this to work you should have proper permissions configured in the sudo configuration file /etc/sudoers.

     Before going into the configuration of /etc/sudoers, let us see a few examples of using sudo

1. User safeer want to run command "useradd" to add a user, this needs root privilege and "safeer" is obviously not "root"

safeer@buttercook:~$ useradd saniba
useradd: cannot lock /etc/passwd; try again later

     Permission is denied since safeer is not having root privilege, now run the same command with sudo

safeer@buttercook:~$ sudo useradd saniba
[sudo] password for safeer:
safeer@buttercook:~$ id -u saniba
1005

     As you can see, the user was successfully added.  Now let us run a script in user "saniba"'s home directory which has executable permission to be ran as saniba only. 

saniba@buttercook:~$ /home/saniba/test.sh
Testing Permission : /home/saniba/test.sh

saniba@buttercook:~$ ls -l /home/saniba/test.sh
-rwxrw-r-- 1 saniba saniba 48 Mar  2 01:44 /home/saniba/test.sh

I am trying to run the command as user "safeer" but fails.

safeer@buttercook:~$ /home/saniba/test.sh
bash: /home/saniba/test.sh: Permission denied

Now using sudo as saniba and running the command.

safeer@buttercook:~$ sudo -u saniba /home/saniba/test.sh
[sudo] password for safeer:
Testing Permission : /home/saniba/test.sh

     Let us examine how sudo can be configured.  All the configurations are inside /etc/sudoers file, this file is often referred to as sudo policy file.  To start editing the sudoers file, you can either run you favorit editor, say vim like this: sudo vi /etc/sudoers OR use the command "sudo visudo" which will open the sudoers file in the default editor.

     The sudoers file contains following types of entries :  Aliases ( Variables ), Defaults ( Override for default options ) and User Specifications ( who runs what )

For a quick configuration, User specifications are more important.  The general format of user specification is like this

"who where = (as_whom) what"

who - The user / group for which sudo permission is being defined.  If "who" is group an "%" should be prefix to it.
where - The host on which the sudo is being run.  This setting was given in the assumption that the same sudo file will be shared over multiple hosts.  This is not the case in most scenarios.  So a keyword  "ALL" will suffice for most of the cases.
as_whom - Under which user's/group's permission the sudo should be run.
what - what commands the "who" is allowed to run.

"as_whom" has the following syntax "(user1,user2,user3...:(group1,group2..)).  The whole list is enclosed in a parentheses and starts with a comma separated list of one or more users followed by a column and a comma separated list of groups enclosed in parenthesis.  A minimum of one user in the form (user1) or group in the form ":(group1)" is required, also the wild card "ALL" can be used.

what - Command has the following syntax : "[tag1,tag2..]: command1,command2..".  Commands can have zero or more tags preceding it, which influences how the command is run. For eg: the tag NOPASSWD causes sudo to not prompt for password when the subsequent command is run. The command itself can be a comma separated list of one or more commands.  The commands can take the shell wildcards like */?/[..] etc for the command name and for its arguments.

We will see a few examples:

root           ALL = (ALL) ALL
Allows root to execute any command on any host as any user ( on any host ).
%admin        ALL = (ALL) ALL
Allow members of group wheel to run any command as any user ( on any host ).
safeer        ALL = ALL
User safeer is allowed to run all commands as himself but not as any other user ( The absense of "as_whom" means, user can run "sudo COMMAND" but not "sudo -u USER COMMAND" )
%vmusers    ALL = (:vboxusers) /usr/bin/VirtualBox
All members of vmusers group can run the VirtualBox application as vboxusers group.
sunil        ALL = NOPASSWD: /bin/mount,/bin/imount
Allow user sunil to mount and unmount devices without verifying his password ( the password prompt that shows up when running sudo command will be gone )

Let us look at aliases now, there are four categories of aliases.
General Syntax is :
ALIAS_TYPE ALIAS_NAME = COMMA_SEPERATED_LIST_OF_ONE_OR_MORE_ALIAS_MEMBERS
Alias type is one of User_Alias, Runas_Alias, Host_Alias and Cmnd_Alias.  Alias name should be a combination of uppercase letters, numbers and underscore starting with an uppercase letter.  

User_Alias - For grouping set of users/groups.  Each item in the list should be either a username, userid ( preppended by #),groupname (preppended by %),groupid(preppended by %#) etc..

Eg: User_Alias SUPERUSERS = root,safeer,#1027,%admins

Host_Alias : For grouping hosts - list items can be hostnames,ips,network id/mask,netgroups

Eg: Host_Alias  MANAGEMENT_HOSTS = admin1,192.168.7.4,10.15.14.0/255.255.255.0

Runas_Alias : For grouping the "as_whom" groups/users.  The list item syntax is same as User_Alias

Eg: Runas_Alias RUNAS01 = dba,%operators

Cmnd_Alias : Grouping commands : The list item can be a single command, command with arguments, commands with wild card or entire directories.

Eg: Cmnd_Alias MOUNT = /bin/mount,/bin/umount

For a single alias type, you can have multiple definitions in one line separated by a column.

Eg: User_Alias DBA = safeer,saniba : OPERATOR = hareesh,bijo

Aliases can be used in User Specification in the place of their respective type, ie: User_alias => who, Host_Alias => Where , Runas_Alias => as_whom, Cmnd_Alias => what.

Eg: ADMINS DBSERVER = ( DBA ) NOPASSWD: DBMCMDS

Defaults: Defaults override sudo's defaul setting in runtime.  The overrides can be applied systemwide, or for specific set of hosts,users,commands and runas (command being run as specific user). Syntax of Defaults is:

Default_Type    Comma_Seprated_Parameter_List

Default_Type can be one of the following depending on how the overrrides are applied

Defaults - Systemwide
Default@HOSTLIST - Apply to given host list
Defaults:USERLIST - Apply to given list of users
Defaults!COMMANDLIST - Apply to given list of commands
Defaults>RUNASLIST - Apply to given list of runas users/groups

Parameter - There is a long list of parameters of different types (boolean,integer,string,list) which are explained in sudoers man page.  Booleans can be turned on by just giving the boolean name or turned off by preppending a "!" to the boolean name. For other types, the parameter takes the form of a key value pair in of of the following three ways: name = value ( set value - for strings ), name += value ( append value - for list ), name -=value ( remove value - from list ).  We will see couple of examples.

Defaults insults  

-- Apply system wide, by default if u type wrong password when promted by sudo the error message is "Sorry, try again".  When u turn on the insults bolean, every time the user enters wrong passord, sudo will give a mildly insulting error message.

Defaults:DBA     always_set_home

IF users defined in DBA User_Alias are sudoing as other users, set the HOME env variable to the target users home directory

Defaults!MOUNTCMDS logfile=/var/log/sudo-mount.log

If commands defined in MOUNTCMDS Cmnd_Alias are run, log them to /var/log/sudo-mount.log

That will be enough for a good sudo policy configuration.  Couple of more points to note before we close this tutorial. 

For the first time a user runs sudo on a system, just before prompting the password, a message will be shown to the user.  This message is called lecture and can be turned on/off using the "lecture" Defaults settings.
When a user runs a sudo command, the user is asked for the password, and if given correct password the command is run.  After this for any sudo command that is ran within a certain time period, password is not promted.  This setting is controlled by the Default setting "timestamp_timeout" and its default value is 15 minutes
You can include extra configuration files by adding one or more lines in the form "include </file/full/path>|</directory/full/path/".  These configuration files should follow the same syntax as sudoers.

"man sudoers" is a beautiful documentation, consult it for more details.

Also checkout the official sudo website

Managing your IMAP account using Perl

This perl script will give you a basic idea of how your IMAP account can be managed  via perl.  I have been using perl to manager certain tasks with my GMail account ( GMail provides both POP and IMAP services).

We make use of the Mail::IMAPClient perl module for manipulating imap accounts.  This script also prompts for a password from command line so that you don't have to hard code your password in the script.

### Module loading

### Mail::IMAPClient for manipulating IMAP accounts
use Mail::IMAPClient;

### Term::ReadKey is for accepting passwords from terminal
use Term::ReadKey;

### Ask for password
print "Enter Password:";
               ReadMode('noecho');
               chomp ( my $imapPassword = ReadLine(0)) ;
               ReadMode('restore');
print "\n";

### Provide your imap username here
my $imapUser = 'your.imap.user' ;

### Provide your imap server name
my $imapServer = 'your.imap.server.name';

### Provide your imap server port number, default is 993.
my $imapPort = 993;

my $imap = Mail::IMAPClient->new(
    Server   => $imapServer,
    User     => $imapUser,
    Port     => $imapPort,
    Password => $imapPassword,
    Ssl      => 1,
  )

or die "Can't connect to $imapServer as $imapUser: $@";

### You are now logged into the imap account.  Play around a bit

### Print all folders
my $folders = $imap->folders or die "List folders error: ", $imap->LastError, "\n";
foreach ( @$folders )  { print $. , " ) " , $_ ,"\n"; }

### Count messages in the Sent folder
my $msgcount = $imap->message_count('Sent');
print "Count $msgcount\n";

### Process all emails in the folder "Work"

## Start by selecting the Work folder
$imap->select('Work') or
    die("Couldn't select Work folder", $!);

## Load all message ids in Work folder to @msgs
my @msgs = $imap->messages or die "Couldn't get messages: $@\n";

## Backup message to $file

my $TS = time;
my $FILE = "imap_bakup_".$TS;
open MAILBACKUP, ">>", $FILE or die "Couldn't open $FILE"
print MAILBACKUP "Starting backup at time : $TS\n";}

foreach my $msgid (@msgs)
{
 $imap->message_to_file(MAILBACKUP,$msgid) or die "Couldn't backup $msgid: $@\n";
}

print MAILBACKUP "Finished backup";
close BACKUP;

## Delete all messages in "Work" folder
$imap->delete_message(\@msgs) or die "Couldn't delete message in folder Work: $@\n";

### Create new folder "Life"
$imap->create("Life") or die "Couldn't create folder Life: $@\n";

### Delete the folder "Life"
$imap->delete("Life") or die "Couldn't delete folder Life: $@\n";

### Expunge messages marked as deleted in the selected folder
$imap->expunge() or die "Couldn't expunge: $@\n";

### Close the selected folder
$imap->close or die "Couldn't close: $@\n";

### Logout IMAP
$imap->logout