#!/bin/sh
#
# Copyright 2005 MySQL AB
# 
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

echo "######################################################################"
echo "#"
echo "# Test Bug#7823 - FLUSH TABLES WITH READ LOCK + INSERT DELAYED = deadlock"
echo "#"

#######################################################################
#
# Settings. CAUTION: Paths are not space safe.
#

# BASEDIR="install directory"
# DATADIR="databases directory e.g. $BASEDIR/var"

MYSQLD="$BASEDIR/libexec/mysqld"
MYSQLC="$BASEDIR/bin/mysql"
MYSQLA="$BASEDIR/bin/mysqladmin"
MYSQLT="$BASEDIR/bin/mysqltest"

PORT_1="${MYSQL_TCP_PORT:+--port=$MYSQL_TCP_PORT}"
SOCK_1="${MYSQL_UNIX_PORT:+--socket=$MYSQL_UNIX_PORT}"
USER_1="-u root -D test"
CLNT_1="-v"
DATA_1="--basedir=$BASEDIR --datadir=$DATADIR"
SERV_1="--log-error --core"
DBUG_1="--debug=t:d:i:O,$DATADIR/mysqld.trace"
DBUG_1= #"--debug=d,thrlock:i:O,$DATADIR/mysqld.trace"

cd $DATADIR || exit $?
rm -f *.trace *.err core* test/#sql* bug7823*

echo "######################################################################"
echo "#"
echo "# Starting database server."
"$MYSQLD" $PORT_1 $SOCK_1 $DATA_1 $SERV_1 $DBUG_1 &
SERV_1_PID=$!
echo "Process_id $SERV_1_PID"
echo
sleep 3


echo "######################################################################"
echo "#"
echo "# Preparing python script 1 - insert delayed."
cat > bug7823-1.py <<'EOF'
#!/usr/bin/env python
import sys,random

i = 500000
if len(sys.argv) > 1:
        table = sys.argv[1]
else:
        table = "t1"

print "DROP TABLE IF EXISTS %s;" % (table)

print """
CREATE TABLE %s (
I INT default NULL auto_increment,
A INT,
B INT,
C INT,
PRIMARY KEY(I)
);
""" % (table)

while (i > 0):
        A = random.randint(0, 100000)
        B = random.randint(0, 100000)
        C = A + B 
        print "INSERT DELAYED INTO %s (A, B, C) VALUES (%d, %d, %d);" % (table, A, B, C)
        i -= 1
EOF
echo


echo "######################################################################"
echo "#"
echo "# Preparing python script 2 - flush tables with read lock."
cat > bug7823-2.py <<'EOF'
#!/usr/bin/env python
#import sys,time

i = 300000

while (i > 0):
        print "SHOW PROCESSLIST; FLUSH TABLES WITH READ LOCK; UNLOCK TABLES;"
        i -= 1
EOF
echo


echo "######################################################################"
echo "#"
echo "# Starting client 1 - insert delayed."
(
    python bug7823-1.py t1 |\
        "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 > bug7823-1.out 2>&1
)&
CLNT_1_PID=$!
echo "Process_id $CLNT_1_PID"
echo
sleep 1


echo "######################################################################"
echo "#"
echo "# Starting client 2 - insert delayed."
(
    python bug7823-1.py t2 |\
         "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 > bug7823-2.out 2>&1
)&
CLNT_2_PID=$!
echo "Process_id $CLNT_2_PID"
echo
sleep 1


echo "######################################################################"
echo "#"
echo "# Starting client 3 - flush tables with read lock."
(
    python bug7823-2.py |\
         "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 > bug7823-3.out 2>&1
)&
CLNT_3_PID=$!
echo "Process_id $CLNT_3_PID"
echo
sleep 1


echo "######################################################################"
echo "#"
echo "# Waiting for deadlock."
while :
do
    # check that all clients are still running
    kill -0 $CLNT_1_PID || break
    kill -0 $CLNT_2_PID || break
    kill -0 $CLNT_3_PID || break
    # get a process list
    "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF > bug7823.tmp || break
    show processlist;
EOF
    # check that the flush process time is below 100
    grep -i '[^0-9][1-9][0-9][0-9][0-9]*[^0-9]*Flush' bug7823.tmp && break
    sleep 5
done
# show the last process list
cat bug7823.tmp && sleep 3
# show how the system behaves during the next few seconds
"$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF && sleep 3
    show processlist;
EOF
"$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF && sleep 3
    show processlist;
EOF
echo


echo "######################################################################"
echo "#"
echo "# Issue a select, which blocks if the deadlock exists."
"$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF
    select * from t1 limit 5;
    select * from t2 limit 5;
EOF
echo


echo "######################################################################"
echo "#"
echo "# Waiting for completion..."
wait $CLNT_1_PID
wait $CLNT_2_PID
wait $CLNT_3_PID


echo "######################################################################"
echo "#"
echo "# See if we have inserted the correct number of rows."
"$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF
    select count(*) from t1;
    select count(*) from t2;
EOF
echo


echo "######################################################################"
echo "#"
echo "# Stopping database server."
"$MYSQLA" $PORT_1 $SOCK_1 -u root shutdown
sleep 3


echo "# End of Test."
echo "#"
echo "######################################################################"