One could clean the table, but on hostgator’s phpmyadmin in cpanel which is REALLY REALLY REALLY SLOW, it times out and loses connection before it is done cleaning. So – on another server which is lickity split, I have root access. To overcome the slowness, I first, copy the table which cleans it, then rename the original and rename the copy. This allows me to also have a backup table. Below is my unix bash script:

Here is a list of my heros who helped get me to my script below.

• Passing Parameters To A MYSQL Query Inside A Shell Script
• Using mysql in batch mode
• Passing Parameters To A MYSQL Query Inside A Shell Script
• How do I execute an SQL script in MySQL?

[c]

#!/bin/bash

## script name; cleanDirtyTable.sh
## wrapper script to execute mysql script with variables

ARGS=2

if [ $# -ne "$ARGS" ]
then

echo "you passed $# parameters"
echo "Usage: `basename $0` sql_table sql_password"

exit
fi

sqltable=$1
sqlpassword=$2
sql_script=cleanDirtyTable.sql
data_file=cleanDirtyTable_results.txt

#run mysql query with paramenters

echo "set @origTable=${sqltable}"

#/usr/bin/mysql –uUserName -pXXXXXXXXXX –h localhost -D my_dBname -A -e "set @origTable=’${sqltable}’; source ${sql_script};" #>${data_file};

/usr/bin/mysql -uUsername -p${sqlpassword} -h localhost -D my_dBname -A -e "set @origTable=’${sqltable}’; SOURCE ${sql_script};"
exit

[/c]

Now here is the sql portion

[sql]
USE fcs_jo151;

— SET @origTable = ‘jos_fcls_masterquotewjobs’;

SET @ds1 = CONCAT(‘CREATE table `’,@origTable, ‘_copy` LIKE `’,@origTable,’`’);
SET @b =1;
SET @ds2 = CONCAT(‘INSERT `’, @origTable, ‘_copy` SELECT * FROM `’,@origTable,’`;’);
SET @rename1 = CONCAT(‘RENAME TABLE `’,@origTable,’` TO `’, @origTable ,’_bak_’,CURDATE(),’`;’);

SET @rename2 = CONCAT(‘RENAME TABLE `’,@origTable,’_copy` TO `’, @origTable ,’`;’);

PREPARE stmt FROM @ds1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM @ds2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM @rename1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM @rename2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

[/sql]