Pasted

#!/bin/bash
 
# find-corrupt-mysql-table.sh
# Check corrupt table @ MySQL
 
# InnoDB tables sometime does not show up as errors with mysqlcheck,
# yet when you try to SELECT, it will give message : 
# ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
 
# This script will check every available table & find all with such problem
 
# one-liner version
###rm result.txt ; databases=( `mysql -e "show databases"` ) ; for d in "${databases[@]}" ; do tables=( `mysql -e "use $d ; show tables"` ) ; for t in "${tables[@]}"  ; do printf "\n DATABASE: $d ; TABLE: $t \n" >> result.txt ;  if [[ "$d" == "information_"* ]] ; then sleep 0 ; else if mysql -e "select * from $d.$t limit 1"  |& tee -a result.txt | grep -q "ERROR" ; then sleep 2 ; fi ; fi ; done ; done ;
 
##### START #####################
 
omitted=information_
 
# clean up 
rm result.txt 
 
databases=( `mysql -e "show databases"` ) 
 
for d in "${databases[@]}" 
do 
 
    tables=( `mysql -e "use $d ; show tables"` ) 
 
    for t in "${tables[@]}"  
    do 
        printf "\n DATABASE: $d ; TABLE: $t \n" >> result.txt 
 
        if [[ "$d" == "$omitted"* ]] ; then 
    
            # skip this database
            sleep 0 
        else 
 
            # get the output into result.txt, and also
            # to stdout, so we can grep / check it for error message
            if mysql -e "select * from $d.$t limit 1"  |& tee -a result.txt | grep -q "ERROR" ; then 
 
                # if we encountered a problematic table
                # we need to stop for a while
                # to give time to MySQL to recover
                # if we continue anyway, we won't be able to connect to MySQL
 
                sleep 2 
            fi 
 
        fi 
    done 
 
done