#!/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