DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Compare Varchar Limit With Data

10.22.2011
| 2067 views |
  • submit to reddit
        // If you have defined varchar(35) for a column address, the extra characters are chopped off. 
// This script will list all such columns where the maximum allowed limit is reached by any row. 
// It can be run on slave once in a while to know if any table alteration is due.

#!/bin/sh
# script to compare the varchar limit being reached by current data
user='root'
password='root@123'
host='localhost'
port='3306'
mypath='/home/procedure'

mkdir -p $mypath

for dbname in `mysqlshow -u$user -p$password -h$host -P$port | egrep -v "(Databases|information_schema|test|mysql|performance_schema)" | awk '{print $2}' | sed '/^$/d'`
do

for tblname in `mysqlshow -u$user -p$password -h$host -P$port $dbname | awk '{print $2}' | sed '/^$/d'`
do
mkdir -p $mypath/$dbname
mysql -u$user -p$password -h$host -P$port $dbname  -e"select * from $dbname.$tblname procedure analyse ();" > $mypath/$dbname/$tblname.txt
done

done


find $mypath -type f -name '*.txt' -exec cat {} \; | awk -F'\t' '{print $1, $5}' > to_mysql.txt
mysql -u$user -p$password -h$host -P$port -e"drop table if exists test.proc_data; drop table if exists test.compare_diff"
mysql -u$user -p$password -h$host -P$port -e"create table test.proc_data select CONCAT(TABLE_SCHEMA, '.',  TABLE_NAME, '.', COLUMN_NAME) col_name, REPLACE(REPLACE(COLUMN_TYPE,'varchar(', ''),')', '') column_wid  from information_schema.COLUMNS where DATA_TYPE like '%varchar%'  ;"
mysql -u$user -p$password -h$host -P$port test -e"create table compare_diff (col_name varchar(100), col_width varchar(100))"
mysql -u$user -p$password -h$host -P$port test -e"load data local infile 'to_mysql.txt' into table compare_diff fields terminated by ' '"
mysql -u$user -p$password -h$host -P$port test -e'select * from test.proc_data as a inner join test.compare_diff as b on lower(a.col_name) = lower(b.col_name) where column_wid - col_width < 1' | awk '{print $1, $2}' | awk -F'.' '{print $1, $2, $3}'

exit