By Programmer For Programmer

Background

So at my current job at jamasoftware.com, our Contour application supports the three major databases MySQL, SQL Server, and Oracle (sorry PostgreSQL fans… maybe soon).  Recently I needed to run UPDATE statements with JOINs, and had to learn the different syntax for each of these databases.  I thought I would share all of them side-by-side, so that you can easily see the differences:

Our Simplified Schema:

Table: ORGANIZATION
id int primary key

Table: PROJECT
id int primary key
organizationId int foreign key ORGANIZATION(id)

Table: DOCUMENT
id int primary key
projectId int foreign key ORGANIZATION(id)
organizationId int null

The Task

Update all DOCUMENT records with the organizationId of their corresponding project

The Solution

MySQL:
UPDATE DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
SET d.organizationId = p.organizationId
WHERE d.organizationId IS NULL;

SQL Server:
UPDATE d
SET d.organizationId = p.organizationId
FROM DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
WHERE d.organizationId IS NULL

Oracle:
UPDATE (SELECT d.organizationId AS docOrgId, p.organizationId AS projectOrgId
        FROM DOCUMENT d
            INNER JOIN PROJECT p ON d.projectId = p.id
        WHERE d.organizationId IS NULL) v
set v.docOrgId = v.projectOrgId

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