diff options
Diffstat (limited to 'mysql-test')
67 files changed, 984 insertions, 75 deletions
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am index d98c10a29a9..91333dfad9b 100644 --- a/mysql-test/Makefile.am +++ b/mysql-test/Makefile.am @@ -59,6 +59,8 @@ SUFFIXES = .sh -e 's!@''libexecdir''@!$(libexecdir)!g' \ -e 's!@''PERL''@!@PERL@!' \ -e 's!@''VERSION''@!@VERSION@!' \ + -e 's!@''MYSQL_BASE_VERSION''@!@MYSQL_BASE_VERSION@!' \ + -e 's!@''MYSQL_NO_DASH_VERSION''@!@MYSQL_NO_DASH_VERSION@!' \ -e 's!@''MYSQL_SERVER_SUFFIX''@!@MYSQL_SERVER_SUFFIX@!' \ $< > $@-t @CHMOD@ +x $@-t diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index 049ac6b1cd7..34df311e683 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -30,7 +30,7 @@ else fi mdata=$data/mysql - +EXTRA_ARG="" if test ! -x $execdir/mysqld then @@ -57,9 +57,7 @@ if [ x$BINARY_DIST = x1 ] ; then basedir=.. else basedir=. -rm -rf share -mkdir share -ln -sf ../../sql/share share/mysql +EXTRA_ARG="--language=../sql/share/english/" fi # Initialize variables @@ -193,7 +191,7 @@ then fi if $execdir/mysqld --no-defaults --bootstrap --skip-grant-tables \ - --basedir=$basedir --datadir=$ldata --skip-innodb --skip-bdb --skip-gemini << END_OF_DATA + --basedir=$basedir --datadir=$ldata --skip-innodb --skip-bdb --skip-gemini $EXTRA_ARG << END_OF_DATA use mysql; $c_d $i_d diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 49012799103..4d508883496 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -99,8 +99,9 @@ export MYSQL_TEST_DIR STD_DATA=$MYSQL_TEST_DIR/std_data hostname=`hostname` # Installed in the mysql privilege table -TESTDIR="$MYSQL_TEST_DIR/t/" +TESTDIR="$MYSQL_TEST_DIR/t" TESTSUFFIX=test +TOT_SKIP=0 TOT_PASS=0 TOT_FAIL=0 TOT_TEST=0 @@ -124,6 +125,7 @@ USE_RUNNING_SERVER=1 DO_GCOV="" DO_GDB="" DO_DDD="" +DO_CLIENT_GDB="" SLEEP_TIME=2 DBUSER="" @@ -142,6 +144,8 @@ while test $# -gt 0; do EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --skip-bdb" EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --skip-bdb" ;; --skip-rpl) NO_SLAVE=1 ;; + --skip-test=*) SKIP_TEST=`$ECHO "$1" | $SED -e "s;--skip-test=;;"`;; + --do-test=*) DO_TEST=`$ECHO "$1" | $SED -e "s;--do-test=;;"`;; --record) RECORD=1; EXTRA_MYSQL_TEST_OPT="$EXTRA_MYSQL_TEST_OPT $1" ;; @@ -149,10 +153,17 @@ while test $# -gt 0; do DO_BENCH=1 NO_SLAVE=1 ;; + --big*) # Actually --big-test + EXTRA_MYSQL_TEST_OPT="$EXTRA_MYSQL_TEST_OPT $1" ;; --sleep=*) EXTRA_MYSQL_TEST_OPT="$EXTRA_MYSQL_TEST_OPT $1" SLEEP_TIME=`$ECHO "$1" | $SED -e "s;--sleep=;;"` ;; + --mysqld=*) + TMP=`$ECHO "$1" | $SED -e "s;--mysqld-=;"` + EXTRA_MYSQL_TEST_OPT="$EXTRA_MYSQL_TEST_OPT $TMP" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT $TMP" + ;; --gcov ) if [ x$BINARY_DIST = x1 ] ; then $ECHO "Cannot do coverage test without the source - please use source dist" @@ -165,15 +176,23 @@ while test $# -gt 0; do ;; --gdb ) if [ x$BINARY_DIST = x1 ] ; then - $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with -gdb option" + $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with --gdb option" fi DO_GDB=1 + USE_RUNNING_SERVER="" + ;; + --client-gdb ) + if [ x$BINARY_DIST = x1 ] ; then + $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with --client-gdb option" + fi + DO_CLIENT_GDB=1 ;; --ddd ) if [ x$BINARY_DIST = x1 ] ; then - $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with -gdb option" + $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with --ddd option" fi DO_DDD=1 + USE_RUNNING_SERVER="" ;; --skip-*) EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT $1" @@ -232,7 +251,7 @@ fi [ -z "$COLUMNS" ] && COLUMNS=80 E=`$EXPR $COLUMNS - 8` -#DASH72=`expr substr '------------------------------------------------------------------------' 1 $E` +#DASH72=`$EXPR substr '------------------------------------------------------------------------' 1 $E` DASH72=`$ECHO '------------------------------------------------------------------------'|$CUT -c 1-$E` # on source dist, we pick up freshly build executables @@ -242,6 +261,7 @@ if [ x$SOURCE_DIST = x1 ] ; then MYSQL_TEST="$BASEDIR/client/mysqltest" MYSQLADMIN="$BASEDIR/client/mysqladmin" MYSQL="$BASEDIR/client/mysql" + LANGUAGE="$BASEDIR/sql/share/english/" INSTALL_DB="./install_test_db" else MYSQLD="$BASEDIR/bin/mysqld" @@ -249,6 +269,12 @@ else MYSQLADMIN="$BASEDIR/bin/mysqladmin" MYSQL="$BASEDIR/bin/mysql" INSTALL_DB="./install_test_db -bin" + if test -d "$BASEDIR/share/mysql/english" + then + LANGUAGE="$BASEDIR/share/mysql/english/" + else + LANGUAGE="$BASEDIR/share/english/" + fi fi # If we should run all tests cases, we will use a local server for that @@ -273,7 +299,10 @@ then fi -MYSQL_TEST="$MYSQL_TEST --no-defaults --socket=$MASTER_MYSOCK --database=$DB --user=$DBUSER --password=$DBPASSWD --silent -v --tmpdir=$MYSQL_TMP_DIR" +MYSQL_TEST_ARGS="--no-defaults --socket=$MASTER_MYSOCK --database=$DB --user=$DBUSER --password=$DBPASSWD --silent -v --tmpdir=$MYSQL_TMP_DIR" +MYSQL_TEST_BIN=$MYSQL_TEST +MYSQL_TEST="$MYSQL_TEST $MYSQL_TEST_ARGS" +GDB_CLIENT_INIT=$MYSQL_TMP_DIR/gdbinit.client GDB_MASTER_INIT=$MYSQL_TMP_DIR/gdbinit.master GDB_SLAVE_INIT=$MYSQL_TMP_DIR/gdbinit.slave GCOV_MSG=$MYSQL_TMP_DIR/mysqld-gcov.out @@ -304,17 +333,34 @@ show_failed_diff () { reject_file=r/$1.reject result_file=r/$1.result + eval_file=r/$1.eval + + if [ -f $eval_file ] + then + result_file=$eval_file + fi + if [ -x "$DIFF" ] && [ -f $reject_file ] then echo "Below are the diffs between actual and expected results:" echo "-------------------------------------------------------" - $DIFF -c $result_file $reject_file + $DIFF -c -a $result_file $reject_file echo "-------------------------------------------------------" - echo "Please e-mail the above, along with the output of mysqlbug" - echo "and any other relevant info to bugs@lists.mysql.com" + echo "Please follow the instructions outlined at" + echo "http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html" + echo "to find the reason to this problem and how to report this." fi } +do_gdb_test () +{ + mysql_test_args="$MYSQL_TEST_ARGS $1" + $ECHO "set args $mysql_test_args < $2" > $GDB_CLIENT_INIT + echo "Set breakpoints ( if needed) and type 'run' in gdb window" + #this xterm should not be backgrounded + xterm -title "Client" -e gdb -x $GDB_CLIENT_INIT $MYSQL_TEST_BIN +} + error () { $ECHO "Error: $1" exit 1 @@ -446,9 +492,8 @@ start_master() --pid-file=$MASTER_MYPID \ --socket=$MASTER_MYSOCK \ --log=$MASTER_MYLOG --default-character-set=latin1 \ - --core \ --tmpdir=$MYSQL_TMP_DIR \ - --language=english \ + --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:50M \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT" @@ -462,7 +507,7 @@ start_master() --default-character-set=latin1 \ --core \ --tmpdir=$MYSQL_TMP_DIR \ - --language=english \ + --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:50M \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT" @@ -518,8 +563,10 @@ start_slave() --log=$SLAVE_MYLOG --default-character-set=latin1 \ --core \ --tmpdir=$MYSQL_TMP_DIR \ - --language=english \ + --language=$LANGUAGE \ --skip-innodb --skip-slave-start \ + --report-host=127.0.0.1 --report-user=root \ + --report-port=$SLAVE_MYPORT \ $SMALL_SERVER \ $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT" if [ x$DO_DDD = x1 ] @@ -628,6 +675,22 @@ run_testcase () slave_init_script=$TESTDIR/$tname-slave.sh slave_master_info_file=$TESTDIR/$tname-slave-master-info.opt SKIP_SLAVE=`$EXPR \( $tname : rpl \) = 0` + if [ -n "$SKIP_TEST" ] ; then + SKIP_THIS_TEST=`$EXPR \( $tname : "$SKIP_TEST" \) != 0` + if [ x$SKIP_THIS_TEST = x1 ] ; + then + return; + fi + fi + + if [ -n "$DO_TEST" ] ; then + DO_THIS_TEST=`$EXPR \( $tname : "$DO_TEST" \) != 0` + if [ x$DO_THIS_TEST = x0 ] ; + then + return; + fi + fi + if [ x${NO_SLAVE}x$SKIP_SLAVE = x1x0 ] ; then @@ -635,9 +698,9 @@ run_testcase () SYST=" ...." REALT=" ...." timestr="$USERT $SYST $REALT" - pname=`$ECHO "$tname "|$CUT -c 1-16` - RES="$pname $timestr" - pass_inc + pname=`$ECHO "$tname "|$CUT -c 1-24` + RES="$pname $timestr" + skip_inc $ECHO "$RES$RES_SPACE [ skipped ]" return fi @@ -691,8 +754,13 @@ run_testcase () if [ -f $tf ] ; then $RM -f r/$tname.*reject - mytime=`$TIME -p $MYSQL_TEST -R r/$tname.result $EXTRA_MYSQL_TEST_OPT \ - < $tf 2> $TIMEFILE` + mysql_test_args="-R r/$tname.result $EXTRA_MYSQL_TEST_OPT" + if [ -z "$DO_CLIENT_GDB" ] ; then + mytime=`$TIME -p $MYSQL_TEST $mysql_test_args < $tf 2> $TIMEFILE` + else + do_gdb_test "$mysql_test_args" "$tf" + fi + res=$? if [ $res = 0 ]; then @@ -711,8 +779,8 @@ run_testcase () fi timestr="$USERT $SYST $REALT" - pname=`$ECHO "$tname "|$CUT -c 1-16` - RES="$pname $timestr" + pname=`$ECHO "$tname "|$CUT -c 1-24` + RES="$pname $timestr" if [ $res = 0 ]; then total_inc @@ -744,7 +812,7 @@ run_testcase () $ECHO "Resuming Tests" $ECHO "" else -# pass_inc + skip_inc $ECHO "$RES$RES_SPACE [ skipped ]" fi fi diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 4f12f71c7ce..dbdbb7f57a9 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -27,3 +27,8 @@ n 12 Table Op Msg_type Msg_text test.t1 optimize status OK +i +1 +2 +3 +4 diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index 5e227313e4a..c88b7375aec 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -509,3 +509,5 @@ id id3 1 1 2 2 100 2 +KINMU_DATE +KINMU_DATE diff --git a/mysql-test/r/big_test.require b/mysql-test/r/big_test.require new file mode 100644 index 00000000000..001b903496b --- /dev/null +++ b/mysql-test/r/big_test.require @@ -0,0 +1,2 @@ +using_big_test +1 diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 08e21b279bc..46ce0fda2c1 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -5,5 +5,11 @@ +9999999999999999999 -9999999999999999999 10000000000000000000 -10000000000000000000 a +18446744073709551614 18446744073709551615 +a +18446744073709551615 +a 18446744073709551615 +a +18446744073709551614 diff --git a/mysql-test/r/check.result b/mysql-test/r/check.result new file mode 100644 index 00000000000..694d7429a14 --- /dev/null +++ b/mysql-test/r/check.result @@ -0,0 +1,2 @@ +Table Op Msg_type Msg_text +test.t1 check status OK diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result index 6fc10f590ec..97d7b57f249 100644 --- a/mysql-test/r/count_distinct.result +++ b/mysql-test/r/count_distinct.result @@ -7,3 +7,5 @@ isbn city libname a isbn city libname a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 +f1 count(distinct t2.f2) count(distinct 1,NULL) +1 0 0 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index f32c9b0bc80..7940d51868a 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -8,3 +8,7 @@ b 1 10000000001 a$1 $b c$ 1 2 3 +table type possible_keys key key_len ref rows Extra +t2 ref B B 21 const 1 where used +a B +3 world diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 67923fe903c..741fc6bba85 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -1,2 +1,11 @@ n 1 +n +4 +Database +foo +mysql +test +Database +mysql +test diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 76862936a16..fca84de710c 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -1,2 +1,6 @@ n 3 +n +23 +n +345 diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 3dc0fc19848..5d2211baf50 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -34,3 +34,5 @@ this is a 2 2.0 1 1 1 and 0 or 2 2 or 1 and 0 1 1 +sum(if(num is null,0.00,num)) +144.54 diff --git a/mysql-test/r/have_symlink.require b/mysql-test/r/have_symlink.require new file mode 100644 index 00000000000..55ad9437034 --- /dev/null +++ b/mysql-test/r/have_symlink.require @@ -0,0 +1,2 @@ +Variable_name Value +have_symlink YES diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 3ad645a6511..3598b15eb0a 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -144,7 +144,7 @@ test.t1 optimize error The handler for the table doesn't support check/repair a 2 Table Op Msg_type Msg_text -test.t1 check error The handler for the table doesn't support check/repair +test.t1 check status OK a b 2 testing Table Op Msg_type Msg_text diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result new file mode 100644 index 00000000000..6358e2a81cd --- /dev/null +++ b/mysql-test/r/multi_update.result @@ -0,0 +1,22 @@ +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +test.t3 check status OK +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 +count(*) +0 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 74c8bd53af2..a47fc950f0e 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -111,3 +111,119 @@ DateOfAction TransactionID member_id nickname voornaam 1 2 +table type possible_keys key key_len ref rows Extra +t1 range a a 20 NULL 2 where used; Using index +a b c +1 NULL b +table type possible_keys key key_len ref rows Extra +t1 range a a 4 NULL 10 where used; Using index +a b c +2 3 c +2 2 b +2 2 a +2 1 b +2 1 a +1 3 b +1 1 b +1 1 b +1 1 NULL +1 NULL b +1 NULL NULL +table type possible_keys key key_len ref rows Extra +t1 ref a a 4 const 5 where used; Using index; Using filesort +a b c +1 3 b +1 1 NULL +1 1 b +1 1 b +1 NULL NULL +1 NULL b +table type possible_keys key key_len ref rows Extra +t1 ref a a 9 const,const 2 where used; Using index; Using filesort +a b c +1 NULL NULL +1 NULL b +table type possible_keys key key_len ref rows Extra +t1 range a a 9 NULL 8 where used; Using index; Using filesort +table type possible_keys key key_len ref rows Extra +t1 range a a 9 NULL 5 where used; Using index +table type possible_keys key key_len ref rows Extra +t1 ref a a 9 const,const 1 where used; Using index; Using filesort +table type possible_keys key key_len ref rows Extra +t1 range a a 9 NULL 6 where used; Using index +table type possible_keys key key_len ref rows Extra +t1 range a a 9 NULL 5 where used; Using index +table type possible_keys key key_len ref rows Extra +t1 range a a 9 NULL 2 where used; Using index; Using filesort +table type possible_keys key key_len ref rows Extra +t1 index NULL a 18 NULL 11 Using index +a b c +1 0 +1 0 b +1 1 +1 1 b +1 1 b +1 3 b +2 1 a +2 1 b +2 2 a +2 2 b +2 3 c +table type possible_keys key key_len ref rows Extra +t1 index NULL a 18 NULL 11 Using index +a b c +2 3 c +2 2 b +2 2 a +2 1 b +2 1 a +1 3 b +1 1 b +1 1 b +1 1 +1 0 b +1 0 +table type possible_keys key key_len ref rows Extra +t1 range a a 18 NULL 3 where used; Using index +a b c +1 1 b +1 1 b +table type possible_keys key key_len ref rows Extra +t1 range a a 4 NULL 6 where used; Using index +a b c +1 1 b +1 1 b +1 1 +1 0 b +1 0 +count(*) +9 +a b c +2 3 c +2 2 b +2 2 a +2 1 b +2 1 a +1 3 b +1 1 b +1 1 b +1 1 +table type possible_keys key key_len ref rows Extra +t1 range a a 8 NULL 10 where used; Using index +a b c +2 1 b +2 1 a +1 1 b +1 1 b +1 1 +1 0 b +1 0 +table type possible_keys key key_len ref rows Extra +t1 range a a 4 NULL 5 where used; Using index +a b c +1 3 b +1 1 b +1 1 b +1 1 +1 0 b +1 0 diff --git a/mysql-test/r/order_fill_sortbuf.result b/mysql-test/r/order_fill_sortbuf.result new file mode 100644 index 00000000000..cb3349cc433 --- /dev/null +++ b/mysql-test/r/order_fill_sortbuf.result @@ -0,0 +1,2 @@ +count(*) +4000 diff --git a/mysql-test/r/rpl000002.result b/mysql-test/r/rpl000002.result index a68ef517708..7f518a7339e 100644 --- a/mysql-test/r/rpl000002.result +++ b/mysql-test/r/rpl000002.result @@ -2,6 +2,8 @@ n 2000 2001 2002 +Server_id Host Port +2 127.0.0.1 9307 id created 1 1970-01-01 06:25:45 id created diff --git a/mysql-test/r/rpl000009.result b/mysql-test/r/rpl000009.result index d5b4cdf3bee..74452473d0c 100644 --- a/mysql-test/r/rpl000009.result +++ b/mysql-test/r/rpl000009.result @@ -1,2 +1,32 @@ n m 4 15 +Database +bar +foo +mysql +test +Database +mysql +test +Database +bar +foo +mysql +test +Tables_in_foo +Tables_in_bar +t1 +t2 +n s +1 one bar +2 two bar +3 three bar +n s +11 eleven bar +12 twelve bar +13 thirteen bar +n s +1 one bar +2 two bar +3 three bar +4 four bar diff --git a/mysql-test/r/rpl000014.result b/mysql-test/r/rpl000014.result index a47c3c91c1d..cdafc7575ab 100644 --- a/mysql-test/r/rpl000014.result +++ b/mysql-test/r/rpl000014.result @@ -1,15 +1,15 @@ File Position Binlog_do_db Binlog_ignore_db -master-bin.001 73 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 1 master-bin.001 73 Yes 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 1 master-bin.001 73 No 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 1 master-bin.001 73 Yes 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 1 master-bin.001 173 Yes 0 0 +master-bin.001 79 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 1 master-bin.001 79 Yes 0 0 1 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 1 master-bin.001 73 No 0 0 1 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 1 master-bin.001 73 Yes 0 0 1 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 1 master-bin.001 173 Yes 0 0 1 File Position Binlog_do_db Binlog_ignore_db -master-bin.001 73 +master-bin.001 79 n 1 2 diff --git a/mysql-test/r/rpl000015.result b/mysql-test/r/rpl000015.result index 58487af27f8..0aef660905b 100644 --- a/mysql-test/r/rpl000015.result +++ b/mysql-test/r/rpl000015.result @@ -1,13 +1,13 @@ File Position Binlog_do_db Binlog_ignore_db -master-bin.001 73 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter - 0 0 0 No 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 test 9998 60 4 No 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 60 4 No 0 0 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 60 master-bin.001 73 Yes 0 0 +master-bin.001 79 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq + 0 0 0 No 0 0 0 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 test 9998 60 4 No 0 0 0 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 60 4 No 0 0 0 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 60 master-bin.001 79 Yes 0 0 1 n 10 45 diff --git a/mysql-test/r/rpl000016.result b/mysql-test/r/rpl000016.result index abe4275a124..65f260bd575 100644 --- a/mysql-test/r/rpl000016.result +++ b/mysql-test/r/rpl000016.result @@ -1,5 +1,5 @@ -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 60 master-bin.001 216 Yes 0 0 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 60 master-bin.001 234 Yes 0 0 3 s Could not break slave Tried hard @@ -9,8 +9,8 @@ master-bin.002 master-bin.003 Log_name master-bin.003 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 60 master-bin.003 184 Yes 0 0 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 60 master-bin.003 202 Yes 0 0 3 m 34 65 @@ -23,8 +23,8 @@ master-bin.004 master-bin.005 master-bin.006 File Position Binlog_do_db Binlog_ignore_db -master-bin.006 131 -Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter -127.0.0.1 root 9999 60 master-bin.006 131 Yes 0 0 +master-bin.006 720 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root 9999 60 master-bin.006 720 Yes 0 0 11 count(*) 100 diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result new file mode 100644 index 00000000000..35a91d03489 --- /dev/null +++ b/mysql-test/r/rpl_log.result @@ -0,0 +1,57 @@ +Log_name Pos Event_type Server_id Log_seq Info +master-bin.001 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2 +master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 172 Intvar 1 3 INSERT_ID=1 +master-bin.001 200 Query 1 4 use test; insert into t1 values (NULL) +master-bin.001 263 Query 1 5 use test; drop table t1 +master-bin.001 311 Query 1 6 use test; create table t1 (word char(20) not null) +master-bin.001 386 Load 1 7 use test; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1 FIELDS TERMINATED BY '\\t' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (word) +master-bin.001 468 Query 1 8 use test; drop table t1 +Log_name Pos Event_type Server_id Log_seq Info +master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key) +Log_name Pos Event_type Server_id Log_seq Info +master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 172 Intvar 1 3 INSERT_ID=1 +Log_name Pos Event_type Server_id Log_seq Info +master-bin.001 200 Query 1 4 use test; insert into t1 values (NULL) +Log_name Pos Event_type Server_id Log_seq Info +master-bin.001 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2 +master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 172 Intvar 1 3 INSERT_ID=1 +master-bin.001 200 Query 1 4 use test; insert into t1 values (NULL) +master-bin.001 263 Query 1 5 use test; drop table t1 +master-bin.001 311 Query 1 6 use test; create table t1 (word char(20) not null) +master-bin.001 386 Load 1 7 use test; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1 FIELDS TERMINATED BY '\\t' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (word) +master-bin.001 468 Query 1 8 use test; drop table t1 +master-bin.001 516 Rotate 1 9 master-bin.002;pos=4 +master-bin.001 557 Stop 1 10 +Log_name Pos Event_type Server_id Log_seq Info +master-bin.002 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2 +master-bin.002 79 Query 1 2 use test; create table t1 (n int) +master-bin.002 137 Query 1 3 use test; insert into t1 values (1) +master-bin.002 197 Query 1 4 use test; drop table t1 +Log_name +master-bin.001 +master-bin.002 +Log_name +slave-bin.001 +slave-bin.002 +Log_name Pos Event_type Server_id Log_seq Info +slave-bin.001 4 Start 2 1 Server ver: $VERSION, Binlog ver: 2 +slave-bin.001 79 Slave 2 2 host=127.0.0.1,port=$MASTER_MYPORT,log=master-bin.001,pos=4 +slave-bin.001 132 Query 1 2 use test; create table t1(n int not null auto_increment primary key) +slave-bin.001 225 Intvar 1 3 INSERT_ID=1 +slave-bin.001 253 Query 1 4 use test; insert into t1 values (NULL) +slave-bin.001 316 Query 1 5 use test; drop table t1 +slave-bin.001 364 Query 1 6 use test; create table t1 (word char(20) not null) +slave-bin.001 439 Query 1 8 use test; drop table t1 +slave-bin.001 487 Rotate 2 3 slave-bin.002;pos=4; forced by master +slave-bin.001 527 Stop 2 4 +Log_name Pos Event_type Server_id Log_seq Info +slave-bin.002 4 Start 2 1 Server ver: $VERSION, Binlog ver: 2 +slave-bin.002 79 Slave 2 2 host=127.0.0.1,port=$MASTER_MYPORT,log=master-bin.002,pos=4 +slave-bin.002 132 Query 1 2 use test; create table t1 (n int) +slave-bin.002 190 Query 1 3 use test; insert into t1 values (1) +slave-bin.002 250 Query 1 4 use test; drop table t1 +Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq +127.0.0.1 root $MASTER_MYPORT 1 master-bin.002 245 Yes 0 0 4 diff --git a/mysql-test/r/rpl_magic.result b/mysql-test/r/rpl_magic.result new file mode 100644 index 00000000000..449a6bca68c --- /dev/null +++ b/mysql-test/r/rpl_magic.result @@ -0,0 +1,22 @@ +n +1 +2 +3 +4 +5 +n +1 +2 +3 +4 +n +1 +2 +3 +4 +n +1 +2 +3 +4 +5 diff --git a/mysql-test/r/rpl_sporadic_master.result b/mysql-test/r/rpl_sporadic_master.result new file mode 100644 index 00000000000..414468f0998 --- /dev/null +++ b/mysql-test/r/rpl_sporadic_master.result @@ -0,0 +1,7 @@ +n +1 +2 +3 +4 +5 +6 diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index fcbe8958748..b3fa281e76d 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -22,7 +22,7 @@ b FOUND_ROWS() 6 b c -2 1 +5 3 FOUND_ROWS() 6 a b a b diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index ce2e5d4f58d..099ea2fa109 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -80,3 +80,13 @@ t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) default 'O''Brien' ) TYPE=MyISAM COMMENT='it''s a table' +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + UNIQUE KEY `aa` (`a`) +) TYPE=MyISAM +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + PRIMARY KEY (`a`) +) TYPE=MyISAM diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result new file mode 100644 index 00000000000..71e8f79b890 --- /dev/null +++ b/mysql-test/r/symlink.result @@ -0,0 +1,23 @@ +Table Op Msg_type Msg_text +test.t9 check status OK +Table Op Msg_type Msg_text +test.t9 optimize status OK +Table Op Msg_type Msg_text +test.t9 repair status OK +Table Create Table +t9 CREATE TABLE `t9` ( + `a` int(11) NOT NULL auto_increment, + `b` char(16) NOT NULL default '', + `c` int(11) NOT NULL default '0', + PRIMARY KEY (`a`) +) TYPE=MyISAM +count(*) +16724 +Table Create Table +t9 CREATE TABLE `t9` ( + `a` int(11) NOT NULL auto_increment, + `b` char(16) NOT NULL default '', + `c` int(11) NOT NULL default '0', + `d` int(11) NOT NULL default '0', + PRIMARY KEY (`a`) +) TYPE=MyISAM diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 93a38e9967f..30a2f884557 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -2,6 +2,8 @@ 10 10.0 10 10 10 6e-05 -6e-05 --6e-05 -6e-05+1.000000 6e-05 -6e-05 6e-05 0.99994 +1e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1 +10 10 10 10 10 10 0.1 0.1 0.1 Field Type Null Key Default Extra Privileges f1 float YES NULL select,insert,update,references f2 double YES NULL select,insert,update,references diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index ee04e437bb7..f852378e6a1 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1,7 +1,7 @@ @test @`select` @TEST @not_used 1 2 3 NULL @test_int @test_double @test_string @test_string2 @select -10 0.00 abcdeghi abcdefghij NULL +10 1e-10 abcdeghi abcdefghij NULL @test_int @test_double @test_string @test_string2 hello hello hello hello @test_int @test_double @test_string @test_string2 @@ -10,3 +10,5 @@ hellohello hellohello hellohello hellohello NULL NULL NULL NULL @t1:=(@t2:=1)+@t3:=4 @t1 @t2 @t3 5 5 1 4 +@t5 +1.23456 diff --git a/mysql-test/std_data/master-bin.001 b/mysql-test/std_data/master-bin.001 Binary files differindex fa30d8e5302..2ec2397acdd 100644 --- a/mysql-test/std_data/master-bin.001 +++ b/mysql-test/std_data/master-bin.001 diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 377e8257457..681e3d36cca 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -71,7 +71,6 @@ ALTER TABLE t1 ADD Column new_col int not null; UNLOCK TABLES; OPTIMIZE TABLE t1; DROP TABLE t1; -drop table if exists t1; # # ALTER TABLE ... ENABLE/DISABLE KEYS @@ -92,3 +91,13 @@ while ($1) } alter table t1 enable keys; drop table t1; + +# +# Drop and add an auto_increment column +# + +create table t1 (i int unsigned not null auto_increment primary key); +insert into t1 values (null),(null),(null),(null); +alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i); +select * from t1; +drop table t1; diff --git a/mysql-test/t/bdb-crash.test b/mysql-test/t/bdb-crash.test index 05ab7260d23..e2d2cd42be2 100644 --- a/mysql-test/t/bdb-crash.test +++ b/mysql-test/t/bdb-crash.test @@ -1,3 +1,4 @@ +-- source include/have_bdb.inc # test for bug reported by Mark Steele drop table if exists tblChange; diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 96296e238fd..d9e08ed33dc 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -686,7 +686,7 @@ drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; ---error 690 +--error 1062 insert into t1 values (99,1,2,'D'),(1,1,2,'D'); select id from t1; select id from t1; @@ -697,7 +697,7 @@ create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(3 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; ---error 690 +--error 1062 insert into t1 values (99,1,2,'D'),(1,1,2,'D'); select id from t1; insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); @@ -705,3 +705,13 @@ commit; select id,id3 from t1; UNLOCK TABLES; DROP TABLE t1; + +# +# Test with empty tables (crashed with lock error) +# + +CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY (SYAIN_NO,KINMU_DATE)) TYPE=BerkeleyDB; +CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) TYPE=BerkeleyDB; +select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; +select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; +DROP TABLE t1,t2; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 9a819463f3f..6470b6f6a30 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -6,7 +6,11 @@ select 9223372036854775807,-009223372036854775808; select +9999999999999999999,-9999999999999999999; drop table if exists t1; -create table t1 (a bigint unsigned); -insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFF); +create table t1 (a bigint unsigned not null, primary key(a)); +insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE); +select * from t1; +select * from t1 where a=18446744073709551615; +select * from t1 where a='18446744073709551615'; +delete from t1 where a=18446744073709551615; select * from t1; drop table t1; diff --git a/mysql-test/t/check.test b/mysql-test/t/check.test new file mode 100644 index 00000000000..6296b31d65d --- /dev/null +++ b/mysql-test/t/check.test @@ -0,0 +1,19 @@ +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection con1; +drop table if exists t1; +#add a lot of keys to slow down check +create table t1(n int not null, key(n), key(n), key(n), key(n)); +let $1=10000; +while ($1) +{ + eval insert into t1 values ($1); + dec $1; +} +send check table t1 type=extended; +connection con2; +insert into t1 values (200000); +connection con1; +reap; +drop table t1; + diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test index b5596784f35..450d9c0961c 100644 --- a/mysql-test/t/compare.test +++ b/mysql-test/t/compare.test @@ -2,6 +2,7 @@ # Bug when using comparions of strings and integers. # +drop table if exists t1; CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id)); insert into t1 values ('000000000001'),('000000000002'); explain select * from t1 where id=000000000001; diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test index 1afb548c2ad..3d795d44821 100644 --- a/mysql-test/t/count_distinct.test +++ b/mysql-test/t/count_distinct.test @@ -32,3 +32,13 @@ insert into t1 values ('NYC Lib','New York'); select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname; select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1; drop table t1, t2, t3; + +# +# Problem with LEFT JOIN +# + +create table t1 (f1 int); +insert into t1 values (1); +create table t2 (f1 int,f2 int); +select t1.f1,count(distinct t2.f2),count(distinct 1,NULL) from t1 left join t2 on t1.f1=t2.f1 group by t1.f1; +drop table t1,t2; diff --git a/mysql-test/t/count_distinct2.test b/mysql-test/t/count_distinct2.test index 2447a7c3611..33d4cf54278 100644 --- a/mysql-test/t/count_distinct2.test +++ b/mysql-test/t/count_distinct2.test @@ -45,7 +45,7 @@ select count(distinct n2), n1 from t1 group by n1; drop table t1; # test the converstion from tree to MyISAM -create table t1 (n int); +create table t1 (n int default NULL); let $1=5000; while ($1) { diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index a5224cd0318..d45d013c9fb 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -2,6 +2,7 @@ # Check some special create statements. # +drop table if exists t1,t2; create table t1 (b char(0)); insert into t1 values (""),(null); select * from t1; @@ -57,3 +58,14 @@ select a$1, $b, c$ from test_$1.$test1; create table test_$1.test2$ (a int); drop table test_$1.test2$; drop database test_$1; + +# +# Test of CREATE ... SELECT with indexes +# + +create table t1 (a int auto_increment not null primary key, B CHAR(20)); +insert into t1 (b) values ("hello"),("my"),("world"); +create table t2 (key (b)) select * from t1; +explain select * from t2 where b="world"; +select * from t2 where b="world"; +drop table t1,t2; diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index 1de387f6e4c..2a45fe8253b 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -10,3 +10,34 @@ insert into t1 values(2); create table t1(n int); drop table t1; select * from t1; + +#now test for a bug in drop database - it is important that the name +#of the table is the same as the name of the database - in the original +#code this triggered a bug +drop database if exists foo; +create database foo; +drop database if exists foo; +create database foo; +create table foo.foo (n int); +insert into foo.foo values (4); +select * from foo.foo; +drop database if exists foo; +create database foo; +drop database foo; + +# test drop/create database and FLUSH TABLES WITH READ LOCK +drop database if exists foo; +flush tables with read lock; +--error 1209 +create database foo; +unlock tables; +create database foo; +show databases; +flush tables with read lock; +--error 1208 +drop database foo; +unlock tables; +drop database foo; +show databases; + + diff --git a/mysql-test/t/err000001.test b/mysql-test/t/err000001.test index 9957cefff9b..d9898054a83 100644 --- a/mysql-test/t/err000001.test +++ b/mysql-test/t/err000001.test @@ -10,7 +10,8 @@ create table t1 (a int); !$1054 select count(test.t1.b) from t1; !$1109 select count(not_existing_database.t1) from t1; !$1109 select count(not_existing_database.t1.a) from t1; -!$1044 select count(not_existing_database.t1.a) from not_existing_database.t1; +--error 1044,1146 +select count(not_existing_database.t1.a) from not_existing_database.t1; !$1054 select 1 from t1 order by 2; !$1054 select 1 from t1 group by 2; !$1054 select 1 from t1 order by t1.b; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 94586519c66..4491de1f82b 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -32,3 +32,28 @@ connection con2; unlock tables; connection con1; reap; + +#test if drop database will wait until we release the global read lock +connection con1; +drop database if exists foo; +create database foo; +create table foo.t1(n int); +insert into foo.t1 values (23); +flush tables with read lock; +connection con2; +send drop database foo; +connection con1; +select * from foo.t1; +unlock tables; +connection con2; +reap; + +# test if dirty close releases global read lock +connection con1; +create table t1 (n int); +flush tables with read lock; +dirty_close con1; +connection con2; +insert into t1 values (345); +select * from t1; +drop table t1; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 9562ae5f77b..0439a96f077 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -24,3 +24,13 @@ select -1.49 or -1.49,0.6 or 0.6; select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; select 1 and 2 between 2 and 10, 2 between 2 and 10 and 1; select 1 and 0 or 2, 2 or 1 and 0; + +# +# Problem with IF() +# + +drop table if exists t1; +create table t1 (num double(12,2)); +insert into t1 values (144.54); +select sum(if(num is null,0.00,num)) from t1; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index cef53ce8165..84b861a9c47 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -437,7 +437,7 @@ drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; ---error 690 +--error 1062 insert into t1 values (99,1,2,'D'),(1,1,2,'D'); select id from t1; select id from t1; @@ -448,7 +448,7 @@ create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(3 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; ---error 690 +--error 1062 insert into t1 values (99,1,2,'D'),(1,1,2,'D'); select id from t1; insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D'); diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index cf6f41d454d..270b1cd4c79 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -10,3 +10,15 @@ insert into t1 values (a+3); insert into t1 values (4),(a+5); select * from t1; drop table t1; + +# +# Test of duplicate key values with packed keys +# + +create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username)); +insert into t1 values (0,"mysql"); +insert into t1 values (0,"mysql ab"); +insert into t1 values (0,"mysql a"); +insert into t1 values (0,"r1manic"); +insert into t1 values (0,"r1man"); +drop table t1; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test new file mode 100644 index 00000000000..7959b67ee9c --- /dev/null +++ b/mysql-test/t/multi_update.test @@ -0,0 +1,51 @@ +# +# Only run the test if we are using --big-test, because this test takes a +# long time +# +-- require r/big_test.require +eval select $BIG_TEST as using_big_test; + +drop table if exists t1,t2,t3; +create table t1(id1 int not null auto_increment primary key, t char(12)); +create table t2(id2 int not null, t char(12)); +create table t3(id3 int not null, t char(12), index(id3)); +let $1 = 10000; +while ($1) + { + let $2 = 5; + eval insert into t1(t) values ('$1'); + while ($2) + { + eval insert into t2(id2,t) values ($1,'$2'); + let $3 = 10; + while ($3) + { + eval insert into t3(id3,t) values ($1,'$2'); + dec $3; + } + dec $2; + } + dec $1; + } + +delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 9500; + +check table t1, t2, t3; + +select count(*) from t1 where id1 > 9500; +select count(*) from t2 where id2 > 9500; +select count(*) from t3 where id3 > 9500; + +delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 500; +select count(*) from t1 where id1 > 500; +select count(*) from t2 where id2 > 500; +select count(*) from t3 where id3 > 500; + +delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0; + +# These queries will force a scan of the table +select count(*) from t1 where id1; +select count(*) from t2 where id2; +select count(*) from t3 where id3; + +drop table t1,t2,t3; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 4e5cee0d0ff..08d26413761 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -168,8 +168,8 @@ drop table t1,t2,t3; #bug reported by Wouter de Jong -drop table if exists members; -CREATE TABLE members ( +drop table if exists t1; +CREATE TABLE t1 ( member_id int(11) NOT NULL auto_increment, inschrijf_datum varchar(20) NOT NULL default '', lastchange_datum varchar(20) NOT NULL default '', @@ -200,8 +200,57 @@ CREATE TABLE members ( PRIMARY KEY (member_id) ) TYPE=MyISAM PACK_KEYS=1; -insert into members (member_id) values (1),(2),(3); -select member_id, nickname, voornaam FROM members +insert into t1 (member_id) values (1),(2),(3); +select member_id, nickname, voornaam FROM t1 ORDER by lastchange_datum DESC LIMIT 2; -drop table members; +drop table t1; + +# +# Test optimization of ORDER BY DESC +# + +create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); +insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); + +explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; +select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; +explain select * from t1 where a >= 1 and a < 3 order by a desc; +select * from t1 where a >= 1 and a < 3 order by a desc; +explain select * from t1 where a = 1 order by a desc, b desc; +select * from t1 where a = 1 order by a desc, b desc; +explain select * from t1 where a = 1 and b is null order by a desc, b desc; +select * from t1 where a = 1 and b is null order by a desc, b desc; +explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; +explain select * from t1 where a = 2 and b >0 order by a desc,b desc; +explain select * from t1 where a = 2 and b is null order by a desc,b desc; +explain select * from t1 where a = 2 and (b is null or b > 0) order by a +desc,b desc; +explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; +explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; + +# +# Test things when we don't have NULL keys +# + +alter table t1 modify b int not null, modify c varchar(10) not null; +explain select * from t1 order by a, b, c; +select * from t1 order by a, b, c; +explain select * from t1 order by a desc, b desc, c desc; +select * from t1 order by a desc, b desc, c desc; +# test multiple ranges, NO_MAX_RANGE and EQ_RANGE +explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; +select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; +# test NEAR_MAX, NO_MIN_RANGE +explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; +select * from t1 where a < 2 and b <= 1 order by a desc, b desc; +select count(*) from t1 where a < 5 and b > 0; +select * from t1 where a < 5 and b > 0 order by a desc,b desc; +# test HA_READ_AFTER_KEY (at the end of the file), NEAR_MIN +explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; +select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; +# test HA_READ_AFTER_KEY (in the middle of the file) +explain select * from t1 where a between 0 and 1 order by a desc, b desc; +select * from t1 where a between 0 and 1 order by a desc, b desc; +drop table t1; + diff --git a/mysql-test/t/order_fill_sortbuf-master.opt b/mysql-test/t/order_fill_sortbuf-master.opt new file mode 100644 index 00000000000..af4e7d33143 --- /dev/null +++ b/mysql-test/t/order_fill_sortbuf-master.opt @@ -0,0 +1 @@ +-O sort_buffer=0 diff --git a/mysql-test/t/order_fill_sortbuf.test b/mysql-test/t/order_fill_sortbuf.test new file mode 100644 index 00000000000..a64ffce08e3 --- /dev/null +++ b/mysql-test/t/order_fill_sortbuf.test @@ -0,0 +1,20 @@ +# +# This test does a create-select with ORDER BY, where there is so many +# rows MySQL needs to use a merge during the sort phase. +# + +drop table if exists t1,t2; +CREATE TABLE `t1` ( + `id` int(11) NOT NULL default '0', + `id2` int(11) NOT NULL default '0', + `id3` int(11) NOT NULL default '0'); +let $1=4000; +while ($1) + { + eval insert into t1 (id,id2,id3) values ($1,$1,$1); + dec $1; + } + +create table t2 select id2 from t1 order by id3; +select count(*) from t2; +drop table t1,t2; diff --git a/mysql-test/t/overflow.test b/mysql-test/t/overflow.test index 7a652257bac..6619a87cabb 100644 --- a/mysql-test/t/overflow.test +++ b/mysql-test/t/overflow.test @@ -1,4 +1,4 @@ connect (con1,localhost,boo,,); connection con1; --- error 1064; +-- error 1064,1102 drop database AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA; diff --git a/mysql-test/t/rpl000002.test b/mysql-test/t/rpl000002.test index 0c490e6316d..865aa5e5bab 100644 --- a/mysql-test/t/rpl000002.test +++ b/mysql-test/t/rpl000002.test @@ -11,6 +11,7 @@ use test; sync_with_master; select * from t1; connection master; +show slave hosts; drop table t1; save_master_pos; connection slave; diff --git a/mysql-test/t/rpl000009.test b/mysql-test/t/rpl000009.test index 768c6c151b4..208e6f0b037 100644 --- a/mysql-test/t/rpl000009.test +++ b/mysql-test/t/rpl000009.test @@ -31,3 +31,56 @@ connection slave; sync_with_master; drop database if exists bar; drop database if exists foo; + +#now let's test load data from master + +#first create some databases and tables on the master +connection master; +set sql_log_bin = 0; +create database foo; +create database bar; +show databases; +create table foo.t1(n int, s char(20)); +create table foo.t2(n int, s text); +insert into foo.t1 values (1, 'one'), (2, 'two'), (3, 'three'); +insert into foo.t2 values (11, 'eleven'), (12, 'twelve'), (13, 'thirteen'); + +create table bar.t1(n int, s char(20)); +create table bar.t2(n int, s text); +insert into bar.t1 values (1, 'one bar'), (2, 'two bar'), (3, 'three bar'); +insert into bar.t2 values (11, 'eleven bar'), (12, 'twelve bar'), + (13, 'thirteen bar'); +set sql_log_bin = 1; +save_master_pos; +connection slave; +sync_with_master; + +#this should show that the slave is empty at this point +show databases; +load data from master; + +#now let's check if we have the right tables and the right data in them +show databases; +use foo; +show tables; +use bar; +show tables; +select * from bar.t1; +select * from bar.t2; + +#now let's see if replication works +connection master; +insert into bar.t1 values (4, 'four bar'); +save_master_pos; +connection slave; +sync_with_master; +select * from bar.t1; + +#now time for cleanup +connection master; +drop database bar; +drop database foo; +save_master_pos; +connection slave; +sync_with_master; + diff --git a/mysql-test/t/rpl000014.test b/mysql-test/t/rpl000014.test index b501d63b10e..604e614b3a8 100644 --- a/mysql-test/t/rpl000014.test +++ b/mysql-test/t/rpl000014.test @@ -25,7 +25,7 @@ create table foo (n int); insert into foo values (1),(2),(3); save_master_pos; connection slave; -change master to master_log_pos=73; +change master to master_log_pos=79; sync_with_master; select * from foo; connection master; diff --git a/mysql-test/t/rpl000017-slave.sh b/mysql-test/t/rpl000017-slave.sh index 4415f093aad..2ead2021416 100755 --- a/mysql-test/t/rpl000017-slave.sh +++ b/mysql-test/t/rpl000017-slave.sh @@ -6,4 +6,5 @@ replicate aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 9306 1 +0 EOF diff --git a/mysql-test/t/rpl_log.test b/mysql-test/t/rpl_log.test new file mode 100644 index 00000000000..9ac6e7ab6b8 --- /dev/null +++ b/mysql-test/t/rpl_log.test @@ -0,0 +1,38 @@ +source include/master-slave.inc; +eval_result; #result depends on some server specific params + +#clean up slave binlogs +connection slave; +slave stop; +reset master; +reset slave; + +connection master; +reset master; +drop table if exists t1; +create table t1(n int not null auto_increment primary key); +insert into t1 values (NULL); +drop table t1; +create table t1 (word char(20) not null); +load data infile '../../std_data/words.dat' into table t1; +drop table t1; +show binlog events; +show binlog events from 79 limit 1; +show binlog events from 79 limit 2; +show binlog events from 79 limit 2,1; +flush logs; +create table t1 (n int); +insert into t1 values (1); +drop table t1; +show binlog events; +show binlog events in 'master-bin.002'; +show master logs; +save_master_pos; +connection slave; +let $VERSION=`select version()`; +slave start; +sync_with_master; +show master logs; +show binlog events in 'slave-bin.001' from 4; +show binlog events in 'slave-bin.002' from 4; +show slave status; diff --git a/mysql-test/t/rpl_magic.test b/mysql-test/t/rpl_magic.test new file mode 100644 index 00000000000..18f1cea34a3 --- /dev/null +++ b/mysql-test/t/rpl_magic.test @@ -0,0 +1,30 @@ +source include/master-slave.inc; + +#first, make sure the slave has had enough time to register +connection master; +save_master_pos; +connection slave; +sync_with_master; + +#discover slaves +connection master; +rpl_probe; + +#turn on master/slave query direction auto-magic +enable_rpl_parse; +drop table if exists t1; +create table t1 ( n int); +insert into t1 values (1),(2),(3),(4); +disable_rpl_parse; +save_master_pos; +enable_rpl_parse; +connection slave; +sync_with_master; +insert into t1 values(5); +connection master; +select * from t1; +select * from t1; +disable_rpl_parse; +select * from t1; +connection slave; +select * from t1; diff --git a/mysql-test/t/rpl_sporadic_master-master.opt b/mysql-test/t/rpl_sporadic_master-master.opt new file mode 100644 index 00000000000..6d5b66bed61 --- /dev/null +++ b/mysql-test/t/rpl_sporadic_master-master.opt @@ -0,0 +1 @@ +--sporadic-binlog-dump-fail --max-binlog-dump-events=2 diff --git a/mysql-test/t/rpl_sporadic_master.test b/mysql-test/t/rpl_sporadic_master.test new file mode 100644 index 00000000000..ce6568f659e --- /dev/null +++ b/mysql-test/t/rpl_sporadic_master.test @@ -0,0 +1,24 @@ +#test to see if replication can continue when master sporadically fails on +# COM_BINLOG_DUMP and additionally limits the number of events per dump +source include/master-slave.inc; +connection master; +drop table if exists t1; +create table t1(n int not null auto_increment primary key); +insert into t1 values (NULL),(NULL); +delete from t1; +insert into t1 values (NULL),(NULL); +insert into t1 values (NULL),(NULL); +flush logs; +delete from t1; +insert into t1 values (NULL),(NULL); +insert into t1 values (NULL),(NULL); +insert into t1 values (NULL),(NULL); +save_master_pos; +connection slave; +sync_with_master; +select * from t1; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 10079ba2549..cdb6ee57e0f 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1609,7 +1609,7 @@ select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 grou # select sum(Period)/count(*) from t1; -select companynr,count(price) as "count",sum(price) as "sum" ,sum(price)/count(price)-avg(price) as "diff",(0+count(price))*companynr as func from t3 group by companynr; +select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr; select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; # diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index d86ef1e0907..52c8039b2f5 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -13,7 +13,7 @@ select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; select found_rows(); select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; select found_rows(); -select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c limit 1; +select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; select found_rows(); select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; select found_rows(); diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index f4f58c8c885..476d8dcdf0d 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -65,3 +65,10 @@ create table t1 ( ) comment = 'it\'s a table' ; show create table t1 ; drop table t1; + +create table t1 (a int not null, unique aa (a)); +show create table t1; +drop table t1; +create table t1 (a int not null, primary key (a)); +show create table t1; +drop table t1; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 70a7a3ab584..bdfeb26073f 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -6,7 +6,7 @@ show status like 'Table_lock%'; connection con1; SET SQL_LOG_BIN=0; drop table if exists t1; -create table t1(n int); +create table t1(n int) type=myisam; insert into t1 values(1); connection con2; lock tables t1 read; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test new file mode 100644 index 00000000000..5e2b8232844 --- /dev/null +++ b/mysql-test/t/symlink.test @@ -0,0 +1,82 @@ +-- require r/have_symlink.require +show variables like "have_symlink"; + +# +# First create little data to play with +# +drop table if exists t1,t2,t7,t8,t9; +create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)); +create table t2 (a int not null auto_increment, b char(16) not null, primary key (a)); +insert into t1 (b) values ("test"),("test1"),("test2"),("test3"); +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +drop table t2; + +# +# Start the test +# We use t9 here to not crash with tables generated by the backup test +# + +eval create table t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp" index directory="$MYSQL_TEST_DIR/var/run"; +insert into t9 select * from t1; +check table t9; +optimize table t9; +repair table t9; +alter table t9 add column c int not null; +show create table t9; + +# Test renames +alter table t9 rename t8, add column d int not null; +alter table t8 rename t7; +rename table t7 to t9; +# Drop old t1 table, keep t9 +drop table t1; + +# +# Test error handling +# Note that we are using the above table t9 here! +# + +--error 1103 +create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="tmp"; + +# Check that we cannot link over a table from another database. + +drop database if exists test_mysqltest; +create database test_mysqltest; + +--error 1 +create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; + +--error 1103 +create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; + +--error 1 +eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; + +--error 1 +eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; + +# Check moving table t9 from default database to test_mysqltest; +# In this case the symlinks should be removed. + +alter table t9 rename test_mysqltest.t9; +select count(*) from test_mysqltest.t9; +show create table test_mysqltest.t9; +drop database test_mysqltest; diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 1496170a256..23941ad2913 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -3,7 +3,8 @@ # Numeric floating point. SELECT 10,10.0,10.,.1e+2,100.0e-1; -select 6e-05, -6e-05, --6e-05, -6e-05+1.000000; +SELECT 6e-05, -6e-05, --6e-05, -6e-05+1.000000; +SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; drop table if exists t1; create table t1 (f1 float(24),f2 float(52)); diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 1067559b759..d5ff64d199b 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1,7 +1,7 @@ # # test variables # -set @`test`=1,@TEST=3,@select=2; +set @`test`=1,@TEST=3,@select=2,@t5=1.23456; select @test,@`select`,@TEST,@not_used; set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL; select @test_int,@test_double,@test_string,@test_string2,@select; @@ -12,3 +12,5 @@ select @test_int,@test_double,@test_string,@test_string2; set @test_int=null,@test_double=null,@test_string=null,@test_string2=null; select @test_int,@test_double,@test_string,@test_string2; select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +select @t5; + |