By Programmer For Programmer

We had an issue at work recently where our MySQL transactions weren’t being properly rolled back or committed, and we ended up with transactions that would last several hours, locking tables, until we killed them manually. After fixing the true issue, I wrote this script to check MySQL InnoDB status every 15 minutes (with cron) and email us if there are any transactions lasting longer than 15 seconds.

For testing, you can also pass in as the first argument a file name that contains some example “show innodb status” output to make sure it works.

#!/usr/bin/sh
DB_USER=root
DB_PASS=password
STATUS_FILE=/tmp/innodb_status.out
TMP_FILE=/tmp/long_tx_check.out
MAIL_TO="email1@asdf.com;email2@asdf.com"
TX_THRESH=15

if [ -z $1 ]
then
	mysql -u $DB_USER -p$DB_PASS -A -e "show innodb status\G" > $STATUS_FILE
else
	STATUS_FILE=$1
fi

cat $STATUS_FILE | awk "/TRANSACTIONS/,/FILE I/ { t=1 } /ACTIVE/ { if (\$5 > $TX_THRESH && t == 1) { on=1 } else { on=0 } } /thread id/ { if (on==1) { print \$0 } }" > $TMP_FILE

FIRSTLINE=""
read -r FIRSTLINE < "$TMP_FILE"
if [ "$FIRSTLINE" != "" ]
then
	echo "LONG RUNNING TRANSACTION FOUND... SENDING EMAIL"
	mail -s "LONG RUNNING TRANSACTION!!" "$MAIL_TO" < $TMP_FILE
else
	echo "No long running transactions found"
fi

This code was based off code taken from here. Always give credit where credit is due

Hey everyone,

I created a bash script to update an index created by SupoSE, keeping track of what revision was last successfully updated. I liked this better than their built-in scheduler because it has to continuously be running, and if for some reason our servers had to be restarted, it wouldn’t automatically start up again. Thus, I made a script to set up in cron to run hourly.

To use this script:

• Create an index normally with SupoSE
• Create a file to hold the last successfully indexed revision, and put this number in the file as the only text
• Set the variables in the script below for your environment
• Use bash 3.0 or higher, since I’m using regular expressions. If this isn’t an option, you may just need to rewrite the parsing of “svn info” to get the HEAD revision.

Enjoy!

#!/bin/sh

# Variables
SUPOSE_EXE="supose"
SUPOSE_LOC="/usr/local/supose/bin"
SVN_INDEX="/var/lib/supose/index/myindex"
SVN_URL="http://subversionurl/repo"
SVN_USER="username"
SVN_PASS="password"
LASTREV_FILE="/var/lib/supose/index/last.rev"

# Get the last successfully indexed revision
LASTREV=""
read -r LASTREV < "$LASTREV_FILE"
if [ "$LASTREV" = "" ]; then
        echo "Please specify the last successfully indexed revision in $LASTREV_FILE"
        exit 1;
fi

# Get the "to" revision
TOREV=""
SVNINFO_CMD="svn info $SVN_URL"
SVNINFO=`$SVNINFO_CMD`
if [[ "$SVNINFO" =~ 'Revision: ([0-9]+)' ]]; then
        TOREV=${BASH_REMATCH[1]}
fi
if [ "$TOREV" = "" ]; then
        echo "**** ERROR ****"
        echo "Could not read the HEAD revision number from command: $SVNINFO_CMD"
        echo "Has subversion been updated with a different format?"
        echo "Output from command:"
        echo $SVNINFO
        exit 1;
fi

# Print some output
echo "Updating subversion: $SVN_URL"
echo "Last successful revision: $LASTREV"
echo "HEAD revision: $TOREV"
echo "Index location: $SVN_INDEX"
echo "..."

if [ "$LASTREV" -ge "$TOREV" ]; then
        echo "No revisions to update... skipping process"
        exit 0;
fi

# Add one to to get the FROM revision
let FROMREV=$LASTREV+1
if [ "$FROMREV" = "" ]; then
        echo "Error adding one to last successfully indexed revision: $LASTREV"
        exit 1;
fi

# Run the SupoSE command
SUPOSE_CMD="./$SUPOSE_EXE scan \
                --url $SVN_URL \
                --username $SVN_USER \
                --password $SVN_PASS \
                --fromrev $FROMREV \
                --torev $TOREV \
                --index $SVN_INDEX"
pushd $SUPOSE_LOC
echo "Running $SUPOSE_CMD"
$SUPOSE_CMD
popd

# Was there an error running SupoSE?
SUPOSE_STATUS="$?"
if [ "$SUPOSE_STATUS" -gt "0" ]; then
        echo "**** Error ****"
        echo "$SUPOSE_EXE returned status code: $SUPOSE_STATUS (not successful)"
        echo "File $LASTREV_FILE will not be updated with latest revision"
        echo "Command that was run:"
        echo $SUPOSE_CMD
        exit $SUPOSE_STATUS;
fi

# If no error, update last.rev file
echo "**** Operation completed successfully ****"
echo "Updating $LASTREV_FILE with revision: $TOREV"
echo "$TOREV" > "$LASTREV_FILE"
exit 0