#
#function for deleting the extra column
#
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
column_name=$2
echo "script to delete the column"
echo "column : $column_name"
# len=$(#column)
# echo "length : $len"
# column_name=$(column:1:$len-1)
# echo "column_name: $column_name"
ALTER TABLE $1 drop COLUMN $column_name;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 dropped from $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
may i know what is wrong with this.. i am getting error as below
value1 : 22
value2 : 20
========printing lines not present in new mysql dump(delete)=========
this line not present : `reserved1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
column name is : reserved1
./fileread: line 71: column:1:-1: command not found
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_name=reserved1
echo "script to delete the column"
echo "column : reserv' at line 1
Script failed please verify the sqls
---------- Post updated at 01:03 PM ---------- Previous update was at 01:02 PM ----------
ahamed your code for column extract worked.. thanks 
---------- Post updated at 01:40 PM ---------- Previous update was at 01:03 PM ----------
#
#function for deleting the extra column
#
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
column_name=$2
echo "script to delete the column"
echo "column : $column_name"
# len=$(#column)
# echo "length : $len"
# column_name=$(column:1:$len-1)
# echo "column_name: $column_name"
ALTER TABLE $1 drop COLUMN $column_name;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 dropped from $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
may i know what is wrong with this.. i am getting error as below
value1 : 22
value2 : 20
========printing lines not present in new mysql dump(delete)=========
this line not present : `reserved1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
column name is : reserved1
./fileread: line 71: column:1:-1: command not found
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_name=reserved1
echo "script to delete the column"
echo "column : reserv' at line 1
Script failed please verify the sqls
also your code for extracting column name worked.. thanks ahamed
---------- Post updated at 06:55 PM ---------- Previous update was at 01:40 PM ----------
Hi everyone my code is finally working if the mysqldump1.sql and mysqldump2.sql has only one table and also it performs the function of adding a column and dropping a column. the code is given below. now i need to proceed as creating a table if it doesnt exists. and also most importantly making the code work if the mysqldump files has many tables (for eg 100 plus). i willl do it step by step.. for now the code is below..
#!/bin/sh
#
# Here we are considering testdump1.sql as old mysql dump of database
# and testdump2.sql as latest mysql dump file to be changed to hence
# forth there will be two functions namely to insert missing
# columns from new mysql dump to database, and also deleting new
# columns from database which are not present in new mysql dump.
#
#
# Test purpose testdump1.sql and testdump2.sql has one table each with
# few columns names missing.
#
#
value1=0
value2=0
while read line1
do
value1=`expr $value1 + 1`
# the abovel ine gives the number of line in the file
done < testdump1.sql
while read line2
do
value2=`expr $value2 + 1`
# the abovel ine gives the number of line in the file
done < testdump2.sql
echo "value1 : $value1"
echo "value2 : $value2"
counter1=0
counter2=0
len1=0
len2=0
#
#function for inserting missed columns
#
insert_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 ADD COLUMN $2;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 added to $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
#function for deleting the extra column
#
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 drop COLUMN $2;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 dropped from $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
# this below piece of code is used to find columns not present in new database
# and hence will involve method for deleting that column.
#
echo "========printing lines not present in new mysql dump(delete)========="
echo ""
while read line1
do
# echo "$line1"
counter2=0
len1=${#line1}
while read line2
do
len2=${#line2}
char1=${line1:$len1-1:$len1}
char2=${line2:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line1:0:$len1-1}
else
line11=$line1
fi
if [ "$char2" = "," ]
then
line22=${line2:0:$len2-1}
else
line22=$line2
fi
if [ "$line11" != "$line22" ]
then
counter2=`expr $counter2 + 1`
fi
if [ $counter2 -eq $value2 ]
then
echo "this line not present : $line11"
# here calling the delete method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
val=$line11
# column_name=$( echo $val | awk -F\` '{print $2}' )
column_name=$( echo $line11 | awk -F\` '{print $2}' )
echo "column name is : $column_name"
delete_column "$table_name" "$column_name"
fi
done < testdump2.sql
done < testdump1.sql
#
# this below piece of code is used to find columns not present in old database
# and hence will involve method for inserting that column.
#
echo ""
echo "========printing lines not present in old mysql dump(insert)========="
echo ""
while read line2
do
# echo "$line2"
counter1=0
len2=${#line2}
while read line1
do
len1=${#line1}
char1=${line1:$len1-1:$len1}
char2=${line2:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line1:0:$len1-1}
else
line11=$line1
fi
if [ "$char2" = "," ]
then
line22=${line2:0:$len2-1}
else
line22=$line2
fi
if [ "$line11" != "$line22" ]
then
counter1=`expr $counter1 + 1`
fi
if [ $counter1 -eq $value1 ]
then
echo "this line not present : $line22"
column_name=$line22
# here calling the insert method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
echo "sending these: $table_name and also $line22"
insert_column "$table_name" "$column_name"
fi
done < testdump1.sql
done < testdump2.sql