#!/bin/sh
#
# Copyright (c) 2006 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#14400 - Query joins wrong rows from table which is subject of \"concurrent insert\""
echo "#"

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

# BASEDIR="install directory"
# DATADIR="databases directory e.g. $BASEDIR/var"
DATADIR="${DATADIR:-$BASEDIR/var}"
TREEROOT="${TREEROOT:-$HOME}"

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="-A -v -f"
CLNT_1="-A"
DATA_1="--basedir=$BASEDIR --datadir=$DATADIR"
SERV_1="--log-error --core"
#SERV_1="$SERV_1 --skip-concurrent-insert"
DBUG_1=
#DBUG_1="--debug=d,query,send_data:i:O,$DATADIR/mysqld.trace"
#DBUG_1="--debug=d,thrlock:i:O,$DATADIR/mysqld.trace"
#DBUG_1="--debug=t:d,query,send_data:i:O,$DATADIR/mysqld.trace"
#DBUG_1="--debug=t:d:i:O,$DATADIR/mysqld.trace"
DDD=
#DDD=1

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

#######################################################################
#
# Functions.

sleep_until_file_created ()
{
  file=$1
  loop=$2
  org_time=$2
  while (test $loop -gt 0)
  do
    if [ -r $file ]
    then
      return 0
    fi
    sleep 1
    loop=`expr $loop - 1`
  done
  echo "ERROR: $file was not created in $org_time seconds;  Aborting"
  exit 1;
}

sleep_until_file_deleted ()
{
  pid=$1;
  file=$2
  loop=$3
  while (test $loop -gt 0)
  do
    if [ ! -r $file ]
    then
      if test $pid != "0"
      then
        wait_for_pid $pid
      fi
      return
    fi
    sleep 1
    loop=`expr $loop - 1`
  done
}

#######################################################################
#
# Run in a loop until it fails.
#
ROUND=1
while [ "$ROUND" -lt 200 ]
do

  echo
  echo "######################################################################"
  echo "#"
  echo "# Starting database server in non-debug mode."
  "$MYSQLD" $PORT_1 $SOCK_1 $DATA_1 $SERV_1 &
  SERV_1_PID=$!
  echo "Process_id $SERV_1_PID"
  echo
  sleep_until_file_created `uname -n`.pid 400


  echo "######################################################################"
  echo "#"
  echo "# Creating tables."
  "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 <<EOF > bug14400-0.log

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
          c1 varchar(250) NOT NULL
          ) ENGINE=MyISAM;

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 (
          c1 varchar(250) NOT NULL,
          PRIMARY KEY (c1)
          ) ENGINE=MyISAM;

EOF
  echo
  sleep 2


  echo "######################################################################"
  echo "#"
  echo "# Loading data into t1."
  perl -e 'print "INSERT INTO t1 VALUES ('\''test000001'\'')";
           for($idx=     2; $idx <=  25000; $idx++)
           { printf ",('\''test%06d'\'')", $idx; }
           print ";\n";

           print "INSERT INTO t1 VALUES ('\''test025001'\'')";
           for($idx= 25002; $idx <=  50000; $idx++)
           { printf ",('\''test%06d'\'')", $idx; }
           print ";\n";

           print "INSERT INTO t1 VALUES ('\''test050001'\'')";
           for($idx= 50002; $idx <=  75000; $idx++)
           { printf ",('\''test%06d'\'')", $idx; }
           print ";\n";

           print "INSERT INTO t1 VALUES ('\''test075001'\'')";
           for($idx= 75002; $idx <= 100000; $idx++)
           { printf ",('\''test%06d'\'')", $idx; }
           print ";\n";

           print "SELECT COUNT(*) FROM t1;\n";' | \
      "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 >> bug14400-0.log
  echo
  sleep 2


  echo "######################################################################"
  echo "#"
  echo "# Loading data into t2."
  perl -e 'print "INSERT IGNORE INTO t2 VALUES ('\''test000001'\'')";
           for($idx=     2; $idx <=  25000; $idx++)
           { printf ",('\''test%06d'\'')", int(rand(99999))+1; }
           print ";\n";

           print "INSERT IGNORE INTO t2 VALUES ('\''test025001'\'')";
           for($idx= 25002; $idx <=  50000; $idx++)
           { printf ",('\''test%06d'\'')", int(rand(99999))+1; }
           print ";\n";

           print "INSERT IGNORE INTO t2 VALUES ('\''test050001'\'')";
           for($idx= 50002; $idx <=  75000; $idx++)
           { printf ",('\''test%06d'\'')", int(rand(99999))+1; }
           print ";\n";

           print "INSERT IGNORE INTO t2 VALUES ('\''test075001'\'')";
           for($idx= 75002; $idx <= 100000; $idx++)
           { printf ",('\''test%06d'\'')", int(rand(99999))+1; }
           print ";\n";

           print "SELECT COUNT(*) FROM t2;\n";' | \
      "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 >> bug14400-0.log
  echo
  sleep 2


  echo "######################################################################"
  echo "#"
  echo "# Stopping database server."
  "$MYSQLA" $PORT_1 $SOCK_1 -u root shutdown
  sleep_until_file_deleted 0 `uname -n`.pid 60


  echo
  echo "######################################################################"
  echo "#"
  echo "# Starting database server."
  if [ -z "$DDD" ]
  then
      "$MYSQLD" $PORT_1 $SOCK_1 $DATA_1 $SERV_1 $DBUG_1 &
  else
      echo "set args $PORT_1 $SOCK_1 $DATA_1 $SERV_1 $DBUG_1" > bug14400-1.gdb
      ddd --debugger "gdb -x bug14400-1.gdb" "$MYSQLD" &
  fi
  SERV_1_PID=$!
  echo "Process_id $SERV_1_PID"
  echo
  sleep_until_file_created `uname -n`.pid 400


  echo "######################################################################"
  echo "#"
  echo "# Starting client 1 (select from t1,t2)."
  perl -e 'for ($idx= 1; $idx < 100000; $idx++)
           { print "SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 
                    WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;\n" };' | \
      "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 \
          > bug14400-1.log 2>&1 &
  CLNT_1_PID=$!
  echo "Process_id $CLNT_1_PID"
  echo
  sleep 2

  echo "######################################################################"
  echo "#"
  echo "# Starting client 2 (insert into t2)."
  perl -e 'for ($idx= 1; $idx < 1000000; $idx++)
           { printf "INSERT IGNORE INTO t2 VALUES ('\''test%06d'\'');\n",
                   int(rand(99999))+1; }' | \
      "$MYSQLC" $PORT_1 $SOCK_1 $USER_1 $CLNT_1 \
          > bug14400-2.log 2>&1 &
  CLNT_2_PID=$!
  echo "Process_id $CLNT_2_PID"
  echo
  sleep 2

  echo "######################################################################"
  echo "#"
  echo "# Waiting for completion of round $ROUND..."
  exec 6>&2 2>/dev/null
  while kill -0 $CLNT_1_PID && \
        kill -0 $CLNT_2_PID && \
        ! grep -q '^test' bug14400-1.log
  do
      sleep 5
  done
  sleep 2
  kill $CLNT_1_PID
  kill $CLNT_2_PID
  sleep 2
  echo
  exec 2>&6


  echo "######################################################################"
  echo "#"
  echo "# Stopping database server."
  "$MYSQLA" $PORT_1 $SOCK_1 -u root shutdown
  sleep_until_file_deleted 0 `uname -n`.pid 60


  if [ "`echo core*`" != "core*" ] || grep -H "ERROR" `uname -n`.err \
       || egrep -Hi "error|corrupt|crash" bug14400-*.log \
       || egrep -q '^test' bug14400-1.log
  then
      egrep '^test' bug14400-1.log | head -5
      break
  fi

  echo
  echo $ROUND rounds
  echo
  ROUND=`expr $ROUND + 1`
done


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