scripting mysql on unix to clean tables

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.


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

Now here is the sql portion

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;

Share Button

No time to wonder if updates are rebooting your server? Put an email event on a log file

This is for windows. One customer would log in (and me several times) and you are immediately assaulted with windows threatening you that you have 5 minutes and the system will reboot. No recourse, no mercy – for you OR those working on the server. Great – good customer service right? Well – it is windows that is causing the issue of course, not me. First, there are 2 GPO’s (link one, link two) that must be configured to ensure that the use has up to 180 minutes of warning, next to allow the reboot to happen at 3am for pete’s sake. Next, you should set up a log to emall you when events 6005/6006 to see when the even log was started and stopped. This is the same as you computer rebooting. Next, look for event 491 so see when patches are downloaded. Another blog article I wrote has a few other handy GPO’s as well that I suggest.

Share Button

Extracting sharepoint Library Data and attached files

Share Button

So my XMBC/KODI plugin is broken – a diary of links to fix it

Writing and publishing Addons for Kodi

 

Share Button

Scripting in GIMP – some good places to get started

First, I want to scream. It is a language like LiSP which I have avoided for some time. Now I have to use it. Next, there might be some repsite if I decided to make any of my items a plugin as I get to use C. Rewriting it will be a pain.

Here are the links so far:

Share Button

Navisworks COM API how to programmatically get all properties

Finally – it took a while but here is a generic routine in VBA (Excel) that allows you to find all the properties. I have my own class that “just works” called findNavis, but this should help others on how to get and list [to the debug window] all the tabs of the properties window in Navis.

I want the dwg (LcOaNode::SourceFile) and the AutoCAD handle (LcOpDwgEntityAttrib::Value) and the stuff in brackets is the attribute name::property name. Here is the vba code (I did this from Excel) Continue reading

Share Button