diff options
Diffstat (limited to 'mysql-test')
170 files changed, 2754 insertions, 582 deletions
diff --git a/mysql-test/README b/mysql-test/README index c5dc3e219de..7c6efe7940e 100644 --- a/mysql-test/README +++ b/mysql-test/README @@ -1,6 +1,10 @@ This directory contains a test suite for mysql daemon. To run the currently existing test cases, simply execute ./mysql-test-run in this directory. It will fire up the newly built mysqld and test it. + +If you want to run the test with a running MySQL server use the --external +option to mysql-test-run. + Note that you do not have to have to do make install, and you could actually have a co-existing MySQL installation - the tests will not conflict with it. @@ -13,8 +17,7 @@ http://www.mysql.com/doc/M/y/MySQL_test_suite.html You can create your own test cases. To create a test case: - cd t - vi test_case_name.test + xeamacs t/test_case_name.test in the file, put a set of SQL commands that will create some tables, load test data, run some queries to manipulate it. diff --git a/mysql-test/include/master-slave.inc b/mysql-test/include/master-slave.inc index 23392690239..474b1357e9e 100644 --- a/mysql-test/include/master-slave.inc +++ b/mysql-test/include/master-slave.inc @@ -7,6 +7,7 @@ connection slave; !slave stop; @r/slave-stopped.result show status like 'Slave_running'; connection master; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; connection slave; reset slave; @@ -14,3 +15,6 @@ reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; @r/slave-running.result show status like 'Slave_running'; + +# Set the default connection to 'master' +connection master; diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index b105609693c..8f90301d2d8 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -1,5 +1,5 @@ #!/bin/sh -# Copyright (C) 1997, 1998, 1999 TCX DataKonsult AB & Monty Program KB & Detron HB +# Copyright (C) 1997-2002 MySQL AB # For a more info consult the file COPYRIGHT distributed with this file # This scripts creates the privilege tables db, host, user, tables_priv, @@ -85,13 +85,15 @@ then c_d="$c_d References_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_d="$c_d Index_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_d="$c_d Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_d="$c_d Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_d="$c_d Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_d="$c_d PRIMARY KEY Host (Host,Db,User)," c_d="$c_d KEY User (User)" c_d="$c_d )" c_d="$c_d comment='Database privileges';" - i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'); - INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');" + i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); + INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');" fi if test ! -f $mdata/host.frm @@ -109,6 +111,8 @@ then c_h="$c_h References_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_h="$c_h Index_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_h="$c_h Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_h="$c_h Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_h="$c_h Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_h="$c_h PRIMARY KEY Host (Host,Db)" c_h="$c_h )" c_h="$c_h comment='Host privileges; Merged with database privileges';" @@ -117,9 +121,9 @@ fi if test ! -f $mdata/user.frm then c_u="$c_u CREATE TABLE user (" - c_u="$c_u Host char(60) DEFAULT '' NOT NULL," - c_u="$c_u User char(16) DEFAULT '' NOT NULL," - c_u="$c_u Password char(16) DEFAULT '' NOT NULL," + c_u="$c_u Host char(60) binary DEFAULT '' NOT NULL," + c_u="$c_u User char(16) binary DEFAULT '' NOT NULL," + c_u="$c_u Password char(16) binary DEFAULT '' NOT NULL," c_u="$c_u Select_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_u="$c_u Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_u="$c_u Update_priv enum('N','Y') DEFAULT 'N' NOT NULL," @@ -134,20 +138,29 @@ then c_u="$c_u References_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_u="$c_u Index_priv enum('N','Y') DEFAULT 'N' NOT NULL," c_u="$c_u Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL," - c_u="$c_u ssl_type enum('NONE','ANY', 'X509', 'SPECIFIED') NOT NULL," - c_u="$c_u ssl_cipher char(60) NULL," - c_u="$c_u x509_issuer blob NULL," - c_u="$c_u x509_subject blob NULL," + c_u="$c_u Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Super_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL," + c_u="$c_u ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL," + c_u="$c_u ssl_cipher BLOB NOT NULL," + c_u="$c_u x509_issuer BLOB NOT NULL," + c_u="$c_u x509_subject BLOB NOT NULL," + c_u="$c_u max_questions int(11) unsigned DEFAULT 0 NOT NULL," + c_u="$c_u max_updates int(11) unsigned DEFAULT 0 NOT NULL," + c_u="$c_u max_connections int(11) unsigned DEFAULT 0 NOT NULL," c_u="$c_u PRIMARY KEY Host (Host,User)" c_u="$c_u )" c_u="$c_u comment='Users and global privileges';" - i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','none',NULL,NULL,NULL); - INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','none',NULL,NULL,NULL); - REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','none',NULL,NULL,NULL); - - INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','none',NULL,NULL,NULL); - INSERT INTO user VALUES ('$hostname','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','none',NULL,NULL,NULL);" + i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + INSERT INTO user (host,user) values ('localhost',''); + INSERT INTO user (host,user) values ('$hostname','');" fi if test ! -f $mdata/func.frm diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 7ae89c96169..3107737ca45 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -19,7 +19,7 @@ TZ=GMT-3; export TZ # for UNIX_TIMESTAMP tests to work # Program Definitions #-- -PATH=/bin:/usr/bin:/usr/local/bin:/usr/bsd:/usr/X11R6/bin:/usr/openwin/bin +PATH=/bin:/usr/bin:/usr/local/bin:/usr/bsd:/usr/X11R6/bin:/usr/openwin/bin:/usr/bin/X11 MASTER_40_ARGS="--rpl-recovery-rank=1 --init-rpl-role=master" # Standard functions @@ -37,10 +37,11 @@ which () continue 2 fi done - echo "which: no $file in ($PATH)" + echo "Fatal error: Cannot find program $file in $PATH" 1>&2 exit 1 done IFS="$save_ifs" + exit 0 } @@ -68,7 +69,7 @@ sleep_until_file_created () do if [ -r $file ] then - return + return 0 fi sleep 1 loop=`expr $loop - 1` @@ -81,8 +82,10 @@ sleep_until_file_created () SED=sed -BASENAME=`which basename | $SED q` +BASENAME=`which basename` +if test $? != 0; then exit 1; fi DIFF=`which diff | $SED q` +if test $? != 0; then exit 1; fi CAT=cat CUT=cut HEAD=head @@ -90,12 +93,14 @@ TAIL=tail ECHO=echo # use internal echo if possible EXPR=expr # use internal if possible FIND=find -GCOV=`which gcov | $SED q` +if test $? != 0; then exit 1; fi PRINTF=printf RM=rm -TIME=time +TIME=`which time` +if test $? != 0; then exit 1; fi TR=tr -XARGS=`which xargs | $SED q` +XARGS=`which xargs` +if test $? != 0; then exit 1; fi # Are we using a source or a binary distribution? @@ -171,7 +176,7 @@ USER_TEST= EXTRA_MASTER_OPT="" EXTRA_MYSQL_TEST_OPT="" -USE_RUNNING_SERVER=1 +USE_RUNNING_SERVER="" DO_GCOV="" DO_GDB="" MANUAL_GDB="" @@ -199,6 +204,7 @@ while test $# -gt 0; do --slave-binary=*) SLAVE_MYSQLD=`$ECHO "$1" | $SED -e "s;--slave-binary=;;"` ;; --local) USE_RUNNING_SERVER="" ;; + --extern) USE_RUNNING_SERVER="1" ;; --tmpdir=*) MYSQL_TMP_DIR=`$ECHO "$1" | $SED -e "s;--tmpdir=;;"` ;; --local-master) MASTER_MYPORT=3306; @@ -234,6 +240,10 @@ while test $# -gt 0; do --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=;;"`;; + --warnings | --log-warnings) + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --log-warnings" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --log-warnings" + ;; --wait-timeout=*) START_WAIT_TIMEOUT=`$ECHO "$1" | $SED -e "s;--wait-timeout=;;"` STOP_WAIT_TIMEOUT=$START_WAIT_TIMEOUT;; @@ -266,6 +276,7 @@ while test $# -gt 0; do exit 1 fi DO_GCOV=1 + GCOV=`which gcov` ;; --gprof ) DO_GPROF=1 @@ -339,6 +350,7 @@ SLAVE_MYPID="$MYRUN_DIR/slave.pid" SLAVE_MYLOG="$MYSQL_TEST_DIR/var/log/slave.log" SLAVE_MYERR="$MYSQL_TEST_DIR/var/log/slave.err" +CURRENT_TEST="$MYSQL_TEST_DIR/var/log/current_test" SMALL_SERVER="-O key_buffer_size=1M -O sort_buffer=256K -O max_heap_table_size=1M" export MASTER_MYPORT @@ -387,7 +399,12 @@ if [ x$SOURCE_DIST = x1 ] ; then CHARSETSDIR="$BASEDIR/sql/share/charsets" INSTALL_DB="./install_test_db" else - MYSQLD="$BASEDIR/bin/mysqld" + if test -x "$BASEDIR/libexec/mysqld" + then + MYSQLD="$BASEDIR/libexec/mysqld" + else + MYSQLD="$BASEDIR/bin/mysqld" + fi MYSQL_TEST="$BASEDIR/bin/mysqltest" MYSQLADMIN="$BASEDIR/bin/mysqladmin" MYSQL_MANAGER="$BASEDIR/bin/mysqlmanager" @@ -451,15 +468,13 @@ GPROF_DIR=$MYSQL_TMP_DIR/gprof GPROF_MASTER=$GPROF_DIR/master.gprof GPROF_SLAVE=$GPROF_DIR/slave.gprof TIMEFILE="$MYSQL_TEST_DIR/var/log/mysqltest-time" -XTERM=`which xterm` +if [ -n "$DO_CLIENT_GDB" -o -n "$DO_GDB" ] ; then + XTERM=`which xterm` +fi #++ # Function Definitions #-- -wait_for_server_start () -{ - $MYSQLADMIN --no-defaults -u $DBUSER --silent -O connect_timeout=10 -w2 --host=$hostname --port=$1 ping >/dev/null 2>&1 -} prompt_user () { @@ -530,6 +545,18 @@ total_inc () { TOT_TEST=`$EXPR $TOT_TEST + 1` } + +skip_test() { + USERT=" ...." + SYST=" ...." + REALT=" ...." + timestr="$USERT $SYST $REALT" + pname=`$ECHO "$1 "|$CUT -c 1-24` + RES="$pname $timestr" + skip_inc + $ECHO "$RES$RES_SPACE [ skipped ]" +} + report_stats () { if [ $TOT_FAIL = 0 ]; then $ECHO "All $TOT_TEST tests were successful." @@ -567,7 +594,7 @@ mysql_install_db () { for slave_num in 1 2 ; do - $RM -rf var/slave$slave_num-data/ + $RM -rf var/slave$slave_num-data mkdir -p var/slave$slave_num-data/mysql mkdir -p var/slave$slave_num-data/test cp var/slave-data/mysql/* var/slave$slave_num-data/mysql @@ -675,9 +702,9 @@ manager_launch() ident=$1 shift if [ $USE_MANAGER = 0 ] ; then - $@ >$CUR_MYERR 2>&1 & - sleep 2 #hack - return + $@ >$CUR_MYERR 2>&1 & + sleep 2 #hack + return fi $MYSQL_MANAGER_CLIENT $MANAGER_QUIET_OPT --user=$MYSQL_MANAGER_USER \ --password=$MYSQL_MANAGER_PW --port=$MYSQL_MANAGER_PORT <<EOF @@ -687,7 +714,7 @@ set_exec_stderr $ident $CUR_MYERR set_exec_con $ident root localhost $CUR_MYSOCK start_exec $ident $START_WAIT_TIMEOUT EOF - abort_if_failed "Could not execute manager command" + abort_if_failed "Could not execute manager command" } manager_term() @@ -716,6 +743,8 @@ start_master() $RM -f $MASTER_MYDDIR/log.* # Remove stale binary logs $RM -f $MYSQL_TEST_DIR/var/log/master-bin.* + # Remove old master.info files + $RM -f $MYSQL_TEST_DIR/var/master-data/master.info #run master initialization shell script if one exists @@ -832,8 +861,9 @@ start_slave() slave_pid=$SLAVE_MYPID slave_sock="$SLAVE_MYSOCK" fi - # Remove stale binary logs - $RM -f $MYSQL_TEST_DIR/var/log/$slave_ident-bin.* + # Remove stale binary logs and old master.info files + $RM -f $MYSQL_TEST_DIR/var/log/$slave_ident-*bin.* + $RM -f $MYSQL_TEST_DIR/$slave_datadir/master.info #run slave initialization shell script if one exists if [ -f "$slave_init_script" ] ; @@ -845,7 +875,7 @@ start_slave() master_info="--master-user=root \ --master-connect-retry=1 \ --master-host=127.0.0.1 \ - --master-password= \ + --master-password="" \ --master-port=$MASTER_MYPORT \ --server-id=$slave_server_id --rpl-recovery-rank=$slave_rpl_rank" else @@ -873,7 +903,7 @@ start_slave() --slave-load-tmpdir=$SLAVE_LOAD_TMPDIR \ --report-host=127.0.0.1 --report-user=root \ --report-port=$slave_port \ - --master-retry-count=5 \ + --master-retry-count=10 \ -O slave_net_timeout=10 \ $SMALL_SERVER \ $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT" @@ -887,13 +917,23 @@ start_slave() "gdb -x $GDB_SLAVE_INIT" $SLAVE_MYSQLD elif [ x$DO_GDB = x1 ] then - $ECHO "set args $slave_args" > $GDB_SLAVE_INIT if [ x$MANUAL_GDB = x1 ] then + $ECHO "set args $slave_args" > $GDB_SLAVE_INIT echo "To start gdb for the slave, type in another window:" echo "cd $CWD ; gdb -x $GDB_SLAVE_INIT $SLAVE_MYSQLD" wait_for_slave=1500 else + ( $ECHO set args $slave_args; + if [ $USE_MANAGER = 0 ] ; then + cat <<EOF +b mysql_parse +commands 1 +disa 1 +end +r +EOF + fi ) > $GDB_SLAVE_INIT manager_launch $slave_ident $XTERM -display $DISPLAY -title "Slave" -e \ gdb -x $GDB_SLAVE_INIT $SLAVE_MYSQLD fi @@ -1007,6 +1047,7 @@ run_testcase () master_init_script=$TESTDIR/$tname-master.sh slave_init_script=$TESTDIR/$tname-slave.sh slave_master_info_file=$TESTDIR/$tname-slave-master-info.opt + echo $tname > $CURRENT_TEST SKIP_SLAVE=`$EXPR \( $tname : rpl \) = 0` if [ $USE_MANAGER = 1 ] ; then many_slaves=`$EXPR \( $tname : rpl_failsafe \) != 0` @@ -1016,7 +1057,8 @@ run_testcase () SKIP_THIS_TEST=`$EXPR \( $tname : "$SKIP_TEST" \) != 0` if [ x$SKIP_THIS_TEST = x1 ] ; then - return; + skip_test $tname; + return; fi fi @@ -1024,21 +1066,15 @@ run_testcase () DO_THIS_TEST=`$EXPR \( $tname : "$DO_TEST" \) != 0` if [ x$DO_THIS_TEST = x0 ] ; then - return; + skip_test $tname; + return; fi fi if [ x${NO_SLAVE}x$SKIP_SLAVE = x1x0 ] ; then - USERT=" ...." - SYST=" ...." - REALT=" ...." - timestr="$USERT $SYST $REALT" - pname=`$ECHO "$tname "|$CUT -c 1-24` - RES="$pname $timestr" - skip_inc - $ECHO "$RES$RES_SPACE [ skipped ]" + skip_test $tname; return fi @@ -1057,8 +1093,8 @@ run_testcase () start_master fi fi - do_slave_restart=0 + do_slave_restart=0 if [ -f $slave_opt_file ] ; then EXTRA_SLAVE_OPT=`$CAT $slave_opt_file | $SED -e "s;\\$MYSQL_TEST_DIR;$MYSQL_TEST_DIR;"` diff --git a/mysql-test/r/bdb-alter-table-1.result b/mysql-test/r/bdb-alter-table-1.result new file mode 100644 index 00000000000..095d89355ad --- /dev/null +++ b/mysql-test/r/bdb-alter-table-1.result @@ -0,0 +1,11 @@ +drop table if exists t1; +create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; +insert into t1 values(1, 't1',4,9); +insert into t1 values(2, 'metatable',1,9); +insert into t1 values(3, 'metaindex',1,9 ); +select * from t1; +objid tablename oid test +1 t1 4 9 +2 metatable 1 9 +3 metaindex 1 9 +alter table t1 drop column test; diff --git a/mysql-test/r/bdb-alter-table-2.result b/mysql-test/r/bdb-alter-table-2.result new file mode 100644 index 00000000000..c23b5ba0031 --- /dev/null +++ b/mysql-test/r/bdb-alter-table-2.result @@ -0,0 +1,6 @@ +select * from t1; +objid tablename oid +1 t1 4 +2 metatable 1 +3 metaindex 1 +drop table t1; diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index f2cb6437f8f..f666c5311b8 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -17,9 +17,6 @@ a select * from t1 where a=18446744073709551615; a 18446744073709551615 -select * from t1 where a='18446744073709551615'; -a -18446744073709551615 delete from t1 where a=18446744073709551615; select * from t1; a diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index 9f1c8ffac41..325accf00e7 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (name char(20) not null, primary key (name)); create table t2 (name char(20) binary not null, primary key (name)); insert into t1 values ("å"); @@ -53,3 +54,13 @@ select * from t1 where b="hello"; a b hello hello drop table t1; +create table t1 (b char(8)); +insert into t1 values(NULL); +select b from t1 where binary b like ''; +b +select b from t1 group by binary b like ''; +b +NULL +select b from t1 having binary b like ''; +b +drop table t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 625ef684698..e030698a396 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -150,5 +150,12 @@ t1 CREATE TABLE `t1` ( KEY `b_29` (`b`), KEY `b_30` (`b`), KEY `b_31` (`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; +create table t1 select if(1,'1','0'), month("2002-08-02"); +drop table t1; +create table t1 select if('2002'='2002','Y','N'); +select * from t1; +if('2002'='2002','Y','N') +Y +drop table if exists t1; diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 58ed0dfdbf3..e5ae6f249ee 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -192,7 +192,7 @@ strcmp('ßa','ss') strcmp('ssa','ß') strcmp('sssb','sßa') strcmp('ß','s') select strcmp('u','öa'),strcmp('u','ö'); strcmp('u','öa') strcmp('u','ö') 1 1 -create table t1 (a varchar(10), key(a)); +create table t1 (a varchar(10), key(a), fulltext (a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; a @@ -204,4 +204,7 @@ test select * from t1 where a like "te_t"; a test +select * from t1 where match a against ("te*" in boolean mode)+0; +a +test drop table t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 44aef139ad2..9e375203e49 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a char(10), tmsp timestamp); insert into t1 set a = 1; insert delayed into t1 set a = 2; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 5848e583d7e..227b34f3691 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -350,3 +350,48 @@ select distinct a from t1 where a >= '1' order by a desc; a 1 drop table t1; +CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); +CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); +INSERT INTO t1 (email, infoID, dateentered) VALUES +('test1@testdomain.com', 1, '2002-07-30 22:56:38'), +('test1@testdomain.com', 1, '2002-07-27 22:58:16'), +('test2@testdomain.com', 1, '2002-06-19 15:22:19'), +('test2@testdomain.com', 2, '2002-06-18 14:23:47'), +('test3@testdomain.com', 1, '2002-05-19 22:17:32'); +INSERT INTO t2(infoID, shipcode) VALUES +(1, 'Z001'), +(2, 'R002'); +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; +email shipcode +test1@testdomain.com Z001 +test2@testdomain.com Z001 +test3@testdomain.com Z001 +test2@testdomain.com R002 +SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; +email +test1@testdomain.com +test2@testdomain.com +test3@testdomain.com +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; +email shipcode +test1@testdomain.com Z001 +test2@testdomain.com Z001 +test2@testdomain.com R002 +test3@testdomain.com Z001 +drop table t1,t2; +CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); +CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) TYPE=MyISAM; +INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); +SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); +privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate +128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 33 6 Kevin 0 kevin@stileproject.com 1 0 http://www.stileproject.com 1 1 0 Administrator 0 996120694 1 -1 1030996168 1031027028 1030599436 36 -6 0 1 0 1 0 0 15 0000-00-00 -1 1 64.0.0.0 0 1 -1 0 0 4 19 1 +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a int primary key, b int, c int); +INSERT t1 VALUES (1,2,3); +CREATE TABLE t2 (a int primary key, b int, c int); +INSERT t2 VALUES (3,4,5); +SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; +a b +1 4 +DROP TABLE IF EXISTS t1,t2; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index e6598ca9ca4..2b666b11362 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -70,6 +70,8 @@ Full-text search in MySQL implements vector space model 0 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); a b Full-text indexes are called collections +select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +a b select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); a b MySQL has now support for full-text search @@ -139,7 +141,7 @@ t2 CREATE TABLE `t2` ( `inhalt` text character set latin1, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 select * from t2 where MATCH inhalt AGAINST (NULL); ticket inhalt select * from t2 where MATCH inhalt AGAINST ('foobar'); @@ -192,3 +194,12 @@ a b 2 fullaaa fullzzz 1 I wonder why the fulltext index doesnt work? drop table t1; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); +select 8 from t1; +8 +8 +8 +8 +8 +drop table t1; diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result index 9f2ba6a5947..abc63358dbe 100644 --- a/mysql-test/r/fulltext_left_join.result +++ b/mysql-test/r/fulltext_left_join.result @@ -1,3 +1,4 @@ +drop table if exists t1, t2; CREATE TABLE t1 ( id VARCHAR(255) NOT NULL PRIMARY KEY, sujet VARCHAR(255), diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result index 8d88b8d6ebf..c0e72ff789f 100644 --- a/mysql-test/r/fulltext_order_by.result +++ b/mysql-test/r/fulltext_order_by.result @@ -64,3 +64,18 @@ a rel 4 1 7 1 drop table t1; +CREATE TABLE t1 ( +a INT AUTO_INCREMENT PRIMARY KEY, +message CHAR(20), +FULLTEXT(message) +); +INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; +a rel +1 1 +2 2 +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; +a rel +1 1 +2 2 +drop table t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 2d24b8ffd4e..c1f36283ab0 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (grp int, a bigint unsigned, c char(10) not null); insert into t1 values (1,1,"a"); insert into t1 values (2,2,"b"); diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 16fb0358d51..0ab41258091 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -46,3 +46,9 @@ select sum(if(num is null,0.00,num)) from t1; sum(if(num is null,0.00,num)) 144.54 drop table t1; +create table t1 (x int, y int); +insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); +select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; +min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) +6 56 +drop table t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 8c991dee8d8..ba33ee0831d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 (field char(1)); INSERT INTO t1 VALUES ('A'),(NULL); SELECT * from t1 WHERE field IN (NULL); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index f067d1f651e..f1c0de2f88a 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -16,9 +16,18 @@ round(5.64,1) round(5.64,2) round(5.64,-1) round(5.64,-2) select abs(-10), sign(-5), sign(5), sign(0); abs(-10) sign(-5) sign(5) sign(0) 10 -1 1 0 -select log(exp(10)),exp(log(sqrt(10))*2); -log(exp(10)) exp(log(sqrt(10))*2) -10.000000 10.000000 +select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); +log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2) +10.000000 10.000000 NULL NULL NULL 2.000000 NULL NULL +select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); +ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL) +10.000000 10.000000 NULL NULL NULL +select log2(8),log2(15),log2(-2),log2(0),log2(NULL); +log2(8) log2(15) log2(-2) log2(0) log2(NULL) +3.000000 3.906891 NULL NULL NULL +select log10(100),log10(18),log10(-4),log10(0),log10(NULL); +log10(100) log10(18) log10(-4) log10(0) log10(NULL) +2.000000 1.255273 NULL NULL NULL select pow(10,log10(10)),power(2,4); pow(10,log10(10)) power(2,4) 10.000000 16.000000 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index a58f3c57169..69e37d7b911 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -83,6 +83,36 @@ soundex('') soundex('he') soundex('hello all folks') select md5('hello'); md5('hello') 5d41402abc4b2a76b9719d911017c592 +select sha('abc'); +sha('abc') +a9993e364706816aba3e25717850c26c9cd0d89d +select sha1('abc'); +sha1('abc') +a9993e364706816aba3e25717850c26c9cd0d89d +select aes_decrypt(aes_encrypt('abc','1'),'1'); +aes_decrypt(aes_encrypt('abc','1'),'1') +abc +select aes_decrypt(aes_encrypt('abc','1'),1); +aes_decrypt(aes_encrypt('abc','1'),1) +abc +select aes_encrypt(NULL,"a"); +aes_encrypt(NULL,"a") +NULL +select aes_encrypt("a",NULL); +aes_encrypt("a",NULL) +NULL +select aes_decrypt(NULL,"a"); +aes_decrypt(NULL,"a") +NULL +select aes_decrypt("a",NULL); +aes_decrypt("a",NULL) +NULL +select aes_decrypt("a","a"); +aes_decrypt("a","a") +NULL +select aes_decrypt(aes_encrypt("","a"),"a"); +aes_decrypt(aes_encrypt("","a"),"a") + select repeat('monty',5),concat('*',space(5),'*'); repeat('monty',5) concat('*',space(5),'*') montymontymontymontymonty * * @@ -116,6 +146,15 @@ decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000) select decode(encode("abcdef","monty"),"monty")="abcdef"; decode(encode("abcdef","monty"),"monty")="abcdef" 1 +select quote('\'\"\\test'); +quote('\'\"\\test') +'\'"\\test' +select quote(concat('abc\'', '\\cba')); +quote(concat('abc\'', '\\cba')) +'abc\'\\cba' +select quote(1/0), quote('\0\Z'); +quote(1/0) quote('\0\Z') +NULL '\0\Z' select reverse(""); reverse("") @@ -184,3 +223,8 @@ SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugs CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4 drop table t1; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); +SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); +1 +DROP TABLE t1; diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index deea5b8bb8b..8cfae44b9dd 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -40,6 +40,12 @@ select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2, select -1.49 or -1.49,0.6 or 0.6; -1.49 or -1.49 0.6 or 0.6 1 1 +select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +3 ^ 11 1 ^ 1 1 ^ 0 1 ^ NULL NULL ^ 1 +8 0 1 NULL NULL +select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; +1 XOR 1 1 XOR 0 0 XOR 1 0 XOR 0 NULL XOR 1 1 XOR NULL 0 XOR NULL +0 1 1 0 NULL NULL NULL select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 0 1 diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result new file mode 100644 index 00000000000..a1a0c91a575 --- /dev/null +++ b/mysql-test/r/grant.result @@ -0,0 +1,60 @@ +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +flush privileges; +grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant delete on mysqltest.* to mysqltest_1@localhost; +select * from mysql.user where user="mysqltest_1"; +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke delete on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant select on mysqltest.* to mysqltest_1@localhost require NONE; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +delete from mysql.user where user='mysqltest_1'; +flush privileges; +grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index d236c26d71a..3892765f587 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -104,11 +104,11 @@ a 1 2 select c from t1; -select command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' +SELECT command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' select * from t2; select command denied to user: 'mysqltest_3@localhost' for table 't2' select mysqltest.t1.c from test.t1,mysqltest.t1; -select command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' +SELECT command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 6 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 54b4e3fba6d..449d7bcb818 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -375,21 +375,45 @@ hijklm 2 DROP TABLE t1; drop table if exists t1; create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); -insert into t1 values (1,2,1,4); -insert into t1 values (1,2,2,4); -insert into t1 values (1,2,3,4); -insert into t1 values (1,2,4,4); -insert into t1 values (1,1,1,4); -insert into t1 values (1,1,2,4); -insert into t1 values (1,1,3,4); -insert into t1 values (1,1,4,4); -insert into t1 values (1,3,1,4); -insert into t1 values (1,3,2,4); -insert into t1 values (1,3,3,4); -insert into t1 values (1,3,4,4); +insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4); select One, Two, sum(Four) from t1 group by One,Two; One Two sum(Four) 1 1 16 1 2 16 1 3 16 -drop table if exists t1; +drop table t1; +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); +create table t2 (user_id integer not null, date date); +insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09'); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +gender dist_count percentage +F 3 60.00 +M 1 20.00 +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +gender dist_count percentage +M 1 20.00 +F 3 60.00 +drop table t1,t2; +CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID +)); +insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; +xID xID1 +1 1 +2 2 +2 2 +3 134 +3 134 +3 134 +4 185 +4 185 +4 185 +4 185 +select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; +xID xID1 Level +1 1 * +2 2 ** +3 134 *** +4 185 **** +drop table t1; diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index d495dde75bf..35765c48049 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -134,6 +134,6 @@ handler t2 read next; a b 19 fff handler t2 read last; -You have an error in your SQL syntax near '' at line 1 +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 handler t2 close; drop table if exists t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index de08465a2a0..8f139799577 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -455,7 +455,7 @@ Duplicate entry 'test2' for key 2 select * from t1; id ggid email passwd 1 this will work -4 test2 this will work +3 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work @@ -807,7 +807,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` char(20) character set latin1 default NULL, KEY `a` (`a`) -) TYPE=InnoDB +) TYPE=InnoDB CHARSET=latin1 drop table t1; create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb; insert into t1 values (NULL),(NULL),(NULL); @@ -920,12 +920,6 @@ create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; Field Type Null Key Default Extra t int(11) MUL 1 -handler t1 open t1; -Table handler for 't1' doesn't have this option -handler t1 read t first; -Unknown table 't1' in HANDLER -handler t1 close; -Unknown table 't1' in HANDLER drop table t1; CREATE TABLE t1 ( number bigint(20) NOT NULL default '0', @@ -989,3 +983,41 @@ select * from t2; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 333 tubs 99 2 20020109113453 501 20020109113453 500 3 10 0 drop table t1,t2; +create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +SELECT @@tx_isolation,@@global.tx_isolation; +@@tx_isolation @@global.tx_isolation +SERIALIZABLE READ-COMMITTED +insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); +select id, code, name from t1 order by id; +id code name +1 1 Tim +2 1 Monty +3 2 David +COMMIT; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); +select id, code, name from t1 order by id; +id code name +1 1 Tim +2 1 Monty +3 2 David +4 2 Erik +5 3 Sasha +COMMIT; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); +select id, code, name from t1 order by id; +id code name +1 1 Tim +2 1 Monty +3 2 David +4 2 Erik +5 3 Sasha +6 3 Jeremy +7 4 Matt +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result new file mode 100644 index 00000000000..a55114a0e3d --- /dev/null +++ b/mysql-test/r/innodb_handler.result @@ -0,0 +1,139 @@ +drop table if exists t1; +create table t1 (a int, b char(10), key a(a), key b(a,b)) type=innodb; +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +a b +14 aaa +handler t2 read a next; +a b +15 bbb +handler t2 read a next; +a b +16 ccc +handler t2 read a prev; +a b +15 bbb +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a prev; +a b +20 ggg +handler t2 read a first; +a b +14 aaa +handler t2 read a prev; +a b +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a next; +a b +22 iii +handler t2 read a next; +a b +handler t2 read a=(15); +a b +15 bbb +handler t2 read a=(16); +a b +16 ccc +handler t2 read a=(19,"fff"); +Too many key parts specified. Max 1 parts allowed +handler t2 read b=(19,"fff"); +a b +19 fff +handler t2 read b=(19,"yyy"); +a b +19 yyy +handler t2 read b=(19); +a b +19 fff +handler t1 read a last; +Unknown table 't1' in HANDLER +handler t2 read a=(11); +a b +handler t2 read a>=(11); +a b +14 aaa +handler t2 read a=(18); +a b +18 eee +handler t2 read a>=(18); +a b +18 eee +handler t2 read a>(18); +a b +19 fff +handler t2 read a<=(18); +a b +18 eee +handler t2 read a<(18); +a b +17 ddd +handler t2 read a first limit 5; +a b +14 aaa +15 bbb +16 ccc +16 xxx +17 ddd +handler t2 read a next limit 3; +a b +18 eee +19 fff +19 yyy +handler t2 read a prev limit 10; +a b +19 fff +18 eee +17 ddd +16 xxx +16 ccc +15 bbb +14 aaa +handler t2 read a>=(16) limit 4; +a b +16 ccc +16 xxx +17 ddd +18 eee +handler t2 read a>=(16) limit 2,2; +a b +17 ddd +18 eee +handler t2 read a last limit 3; +a b +22 iii +21 hhh +20 ggg +handler t2 read a=(19); +a b +19 fff +handler t2 read a=(19) where b="yyy"; +a b +19 yyy +handler t2 read first; +a b +17 ddd +handler t2 read next; +a b +18 eee +alter table t1 type=innodb; +handler t2 read next; +a b +19 fff +handler t2 read last; +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 +handler t2 close; +drop table if exists t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index f57efa33020..2ffa9d88618 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -19,3 +19,39 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; +create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); +select a,t>0,c,i from t1; +a t>0 c i +1 1 hello NULL +2 1 hello NULL +4 0 a 5 +5 1 hello NULL +truncate table t1; +insert into t1 set a=default,t=default,c=default; +insert into t1 set a=default,t=default,c=default,i=default; +insert into t1 set a=4,t=0,c="a",i=5; +insert into t1 set a=5,t=0,c="a",i=null; +insert into t1 set a=default,t=default,c=default,i=default; +select a,t>0,c,i from t1; +a t>0 c i +1 1 hello NULL +2 1 hello NULL +4 0 a 5 +5 0 a NULL +6 1 hello NULL +drop table t1; +create table t1 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id)); +insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL); +select * from t1; +sid id +skr 1 +skr 2 +test 1 +drop table t1; +drop database if exists foo; +create database foo; +use foo; +create table t1 (c int); +insert into foo.t1 set foo.t1.c = '1'; +drop database foo; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index d61b6c67030..e24c3179a0c 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -61,7 +61,7 @@ SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2 WHERE numeropost=9 ORDER BY numreponse ASC; show variables like '%bulk%'; Variable_name Value -myisam_bulk_insert_tree_size 8388608 +bulk_insert_buffer_size 8388608 INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2 WHERE numeropost=9 ORDER BY numreponse ASC; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 6dfe0809e7f..1cd9c9dfe79 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -147,3 +147,34 @@ t1 0 e 1 e A 0 NULL NULL BTREE t1 0 b 1 b A NULL NULL NULL YES BTREE t1 1 c 1 c A NULL NULL NULL YES BTREE drop table t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +c i + 1 + 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i + 1 + 2 +a 1 +a 2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +c i +NULL 1 +NULL 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i +NULL 1 +NULL 2 +a 1 +a 2 +drop table t1; diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result index df9d962b3ac..e148548b721 100644 --- a/mysql-test/r/key_primary.result +++ b/mysql-test/r/key_primary.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t1 char(3) primary key); insert into t1 values("ABC"); insert into t1 values("ABA"); diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 6fe8cc71185..20bc9b9572f 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1(n int); insert into t1 values (1); lock tables t1 write; diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result new file mode 100644 index 00000000000..1caaf317c96 --- /dev/null +++ b/mysql-test/r/lowercase_table.result @@ -0,0 +1,15 @@ +drop table if exists t1,t2,t3; +create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +SELECT * FROM t1; +id Word +1 a +2 b +3 c +RENAME TABLE T1 TO T2; +ALTER TABLE T2 ADD new_col int not null; +ALTER TABLE T2 RENAME T3; +show tables like 't_'; +Tables_in_test (t_) +t3 +drop table t3; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 590169338a6..27ce8e1d915 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -174,7 +174,7 @@ t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` char(20) character set latin1 default NULL, KEY `a` (`a`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); select * from t4; Can't open file: 't4.MRG'. (errno: 143) @@ -249,14 +249,14 @@ t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0', PRIMARY KEY (`incr`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0' -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) drop table t3,t2,t1; create table t1 (a int not null) type=merge; select * from t1; @@ -275,42 +275,42 @@ a b 1 2 drop table t3,t1,t2; drop table if exists t6, t5, t4, t3, t2, t1; -create table t1 (a int not null, b int not null, key(a,b)); -create table t2 (a int not null, b int not null, key(a,b)); +create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); +create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; -create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', - KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2) + `b` int(11) NOT NULL auto_increment, + PRIMARY KEY (`a`,`b`) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', - KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2) -insert into t1 values (1,1),(1,2),(1,3),(1,4); -insert into t2 values (2,1),(2,2),(2,3),(2,4); + `b` int(11) NOT NULL auto_increment, + PRIMARY KEY (`a`,`b`) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2) +insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); +insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); select * from t3 order by b,a limit 3; a b select * from t4 order by b,a limit 3; @@ -373,7 +373,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3) select * from t4 order by a,b; a b 1 1 @@ -399,7 +399,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3) insert into t4 values (4,1),(4,2); select * from t1 order by a,b; a b @@ -461,4 +461,83 @@ a b 5 2 6 1 6 2 +select 1; +1 +1 +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +4 1 +4 2 +5 1 +5 2 +5 3 +select * from t2 order by a,b; +a b +2 1 +2 2 +2 3 +2 4 +2 5 +6 1 +6 2 +6 3 +select * from t5 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +2 1 +2 2 +2 3 +2 4 +2 5 +4 1 +4 2 +5 1 +5 2 +5 3 +6 1 +6 2 +6 3 +select * from t6 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +2 1 +2 2 +2 3 +2 4 +2 5 +4 1 +4 2 +5 1 +5 2 +5 3 +6 1 +6 2 +6 3 drop table if exists t6, t5, t4, t3, t2, t1; +CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,1); +CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,2), (2,2); +CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); +select max(b) from t where a = 2; +max(b) +NULL +select max(b) from t1 where a = 2; +max(b) +1 +drop table if exists t,t1,t2; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 85819d9571d..328eb9b394e 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -66,3 +66,72 @@ select count(*) from t3 where id3; count(*) 0 drop table t1,t2,t3; +create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; +create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; +delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; +drop table t1,t2; +DROP TABLE IF EXISTS a,b,c; +CREATE TABLE a ( +id int(11) NOT NULL default '0', +name varchar(10) default NULL, +PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); +CREATE TABLE b ( +id int(11) NOT NULL default '0', +name varchar(10) default NULL, +PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); +CREATE TABLE c ( +id int(11) NOT NULL default '0', +mydate datetime default NULL, +PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 +00:00:00'),(7,'2002-07-22 00:00:00'); +delete a,b,c from a,b,c +where to_days(now())-to_days(c.mydate)>=30 +and c.id=a.id and c.id=b.id; +select * from c; +id mydate +1 2002-02-04 00:00:00 +5 2002-05-12 00:00:00 +6 2002-06-22 00:00:00 +7 2002-07-22 00:00:00 +DROP TABLE IF EXISTS a,b,c; +drop table if exists parent, child; +CREATE TABLE IF NOT EXISTS `parent` ( +`id` int(11) NOT NULL auto_increment, +`tst` text, +`tst1` text, +PRIMARY KEY (`id`) +) TYPE=MyISAM; +CREATE TABLE IF NOT EXISTS `child` ( +`ID` int(11) NOT NULL auto_increment, +`ParId` int(11) default NULL, +`tst` text, +`tst1` text, +PRIMARY KEY (`ID`), +KEY `IX_ParId_child` (`ParId`), +FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) +) TYPE=MyISAM; +INSERT INTO parent(tst,tst1) +VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); +INSERT INTO child(ParId) +VALUES(1), (2), (3); +select * from child; +ID ParId tst tst1 +1 1 NULL NULL +2 2 NULL NULL +3 3 NULL NULL +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; +select * from child; +ID ParId tst tst1 +1 1 MySQL MySQL AB +2 2 MSSQL Microsoft +3 3 ORACLE ORACLE +drop table parent, child; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 4b4a88a54f7..29818f28c45 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -75,3 +75,42 @@ explain select a,b,c from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 drop table t1; +CREATE TABLE `t1` ( +`post_id` mediumint(8) unsigned NOT NULL auto_increment, +`topic_id` mediumint(8) unsigned NOT NULL default '0', +`post_time` datetime NOT NULL default '0000-00-00 00:00:00', +`post_text` text NOT NULL, +`icon_url` varchar(10) NOT NULL default '', +`sign` tinyint(1) unsigned NOT NULL default '0', +`post_edit` varchar(150) NOT NULL default '', +`poster_login` varchar(35) NOT NULL default '', +`ip` varchar(15) NOT NULL default '', +PRIMARY KEY (`post_id`), +KEY `post_time` (`post_time`), +KEY `ip` (`ip`), +KEY `poster_login` (`poster_login`), +KEY `topic_id` (`topic_id`), +FULLTEXT KEY `post_text` (`post_text`) +) TYPE=MyISAM; +INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES (1), (2), (3); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), KEY t1 (a, b, c)); +Specified key was too long. Max key length is 500 +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255)); +ALTER TABLE t1 ADD INDEX t1 (a, b, c); +Specified key was too long. Max key length is 500 +DROP TABLE t1; diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index f377d54e1a0..498147704a3 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -1,6 +1,7 @@ select {fn length("hello")}, { date "1997-10-20" }; {fn length("hello")} 1997-10-20 5 1997-10-20 +drop table if exists t1; create table t1 (a int not null auto_increment,b int not null,primary key (a,b)); insert into t1 SET A=NULL,B=1; insert into t1 SET a=null,b=2; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result new file mode 100644 index 00000000000..52bd83df5ed --- /dev/null +++ b/mysql-test/r/olap.result @@ -0,0 +1,27 @@ +drop table if exists sales; +create table sales ( product varchar(32), country varchar(32), year int, profit int); +insert into sales values ( 'Computer', 'India',2000, 1200), +( 'TV', 'United States', 1999, 150), +( 'Calculator', 'United States', 1999,50), +( 'Computer', 'United States', 1999,1500), +( 'Computer', 'United States', 2000,1500), +( 'TV', 'United States', 2000, 150), +( 'TV', 'India', 2000, 100), +( 'TV', 'India', 2000, 100), +( 'Calculator', 'United States', 2000,75), +( 'Calculator', 'India', 2000,75), +( 'TV', 'India', 1999, 100), +( 'Computer', 'India', 1999,1200), +( 'Computer', 'United States', 2000,1500), +( 'Calculator', 'United States', 2000,75); +select product, country , year, sum(profit) from sales group by product, country, year with cube; +This version of MySQL doesn't yet support 'CUBE' +explain select product, country , year, sum(profit) from sales group by product, country, year with cube; +This version of MySQL doesn't yet support 'CUBE' +select product, country , year, sum(profit) from sales group by product, country, year with rollup; +This version of MySQL doesn't yet support 'ROLLUP' +explain select product, country , year, sum(profit) from sales group by product, country, year with rollup; +This version of MySQL doesn't yet support 'ROLLUP' +select product, country , year, sum(profit) from sales group by product, country, year with cube union all select product, country , year, sum(profit) from sales group by product, country, year with rollup; +This version of MySQL doesn't yet support 'CUBE' +drop table sales; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 563aa178c87..64e2c9f5240 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -471,3 +471,38 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used drop table t1,t2,t3; +CREATE TABLE t1 ( +`titre` char(80) NOT NULL default '', +`numeropost` mediumint(8) unsigned NOT NULL auto_increment, +`date` datetime NOT NULL default '0000-00-00 00:00:00', +`auteur` char(35) NOT NULL default '', +`icone` tinyint(2) unsigned NOT NULL default '0', +`lastauteur` char(35) NOT NULL default '', +`nbrep` smallint(6) unsigned NOT NULL default '0', +`dest` char(35) NOT NULL default '', +`lu` tinyint(1) unsigned NOT NULL default '0', +`vue` mediumint(8) unsigned NOT NULL default '0', +`ludest` tinyint(1) unsigned NOT NULL default '0', +`ouvert` tinyint(1) unsigned NOT NULL default '1', +PRIMARY KEY (`numeropost`), +KEY `date` (`date`), +KEY `dest` (`dest`,`ludest`), +KEY `auteur` (`auteur`,`lu`), +KEY `auteur_2` (`auteur`,`date`), +KEY `dest_2` (`dest`,`date`) +) CHECKSUM=1; +CREATE TABLE t2 ( +`numeropost` mediumint(8) unsigned NOT NULL default '0', +`pseudo` char(35) NOT NULL default '', +PRIMARY KEY (`numeropost`,`pseudo`), +KEY `pseudo` (`pseudo`) +); +INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); +INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); +SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +drop table t1,t2; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index af14575a812..7c53074b6da 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -226,7 +226,7 @@ show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2, t3, t11, t21; -set sql_query_cache_type=demand; +set query_cache_type=demand; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; @@ -242,7 +242,7 @@ a 1 2 3 -set sql_query_cache_type=2; +set query_cache_type=2; select sql_cache * from t1; a 1 @@ -254,7 +254,7 @@ Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 -set sql_query_cache_type=on; +set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; Variable_name Value @@ -490,3 +490,39 @@ drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +set GLOBAL query_cache_size=1000; +show global variables like "query_cache_size"; +Variable_name Value +query_cache_size 0 +set GLOBAL query_cache_size=1100; +set GLOBAL query_cache_size=1200; +set GLOBAL query_cache_size=1300; +set GLOBAL query_cache_size=1400; +set GLOBAL query_cache_size=1500; +set GLOBAL query_cache_size=1600; +set GLOBAL query_cache_size=1700; +set GLOBAL query_cache_size=1800; +set GLOBAL query_cache_size=1900; +show global variables like "query_cache_size"; +Variable_name Value +query_cache_size 1024 +set GLOBAL query_cache_size=1048576; +create table t1 (i int not null); +create table t2 (i int not null); +select * from t1; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +create temporary table t3 (i int not null); +select * from t2; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +select * from t3; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +drop table t1, t2, t3; diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result new file mode 100644 index 00000000000..8b50f9a92e8 --- /dev/null +++ b/mysql-test/r/repair.result @@ -0,0 +1,7 @@ +drop table if exists t1; +create table t1 SELECT 1,"table 1"; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair status OK +drop table if exists t1; diff --git a/mysql-test/r/rollback.result b/mysql-test/r/rollback.result index a5eb6f8729f..d87aa68dce4 100644 --- a/mysql-test/r/rollback.result +++ b/mysql-test/r/rollback.result @@ -4,9 +4,26 @@ begin work; insert into t1 values (4); insert into t1 values (5); rollback; -Warning: Some non-transactional changed tables couldn't be rolled back +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select @@warning_count; +@@warning_count +1 +select @@error_count; +@@error_count +0 +show warnings; +Level Code Message +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show errors; +Level Code Message select * from t1; n 4 5 +select @@warning_count; +@@warning_count +0 +show warnings; +Level Code Message drop table t1; diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result index 60a5db1de3a..2dc21e86152 100644 --- a/mysql-test/r/rpl000001.result +++ b/mysql-test/r/rpl000001.result @@ -1,10 +1,10 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; -drop table if exists t1,t3; +drop table if exists t1,t2,t3; create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; load data local infile 'MYSQL_TEST_DIR/std_data/words.dat' into table t1; @@ -30,11 +30,12 @@ abandoned abandoning abandonment abandons +slave stop; set password for root@"localhost" = password('foo'); +slave start; set password for root@"localhost" = password(''); create table t3(n int); insert into t3 values(1),(2); -use test; select * from t3; n 1 @@ -46,7 +47,6 @@ drop table t1,t3; reset master; slave stop; reset slave; -drop table if exists t1,t2; create table t1(n int); select get_lock("hold_slave",10); get_lock("hold_slave",10) @@ -58,8 +58,8 @@ release_lock("hold_slave") unlock tables; create table t2(id int); insert into t2 values(connection_id()); -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; +create temporary table t3(n int); +insert into t3 select get_lock('crash_lock%20C', 1) from t2; update t1 set n = n + get_lock('crash_lock%20C', 2); select (@id := id) - id from t2; (@id := id) - id @@ -67,7 +67,7 @@ select (@id := id) - id from t2; kill @id; drop table t2; Server shutdown in progress -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; select count(*) from t1; count(*) @@ -75,17 +75,15 @@ count(*) drop table t1; create table t1 (n int); insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) +insert into mysql.user (Host, User, Password) VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); select select_priv,user from mysql.user where user = 'blafasel2'; select_priv user N blafasel2 -update user set Select_priv = "Y" where User="blafasel2"; +update mysql.user set Select_priv = "Y" where User="blafasel2"; select select_priv,user from mysql.user where user = 'blafasel2'; select_priv user Y blafasel2 -use test; select n from t1; n 3456 diff --git a/mysql-test/r/rpl000002.result b/mysql-test/r/rpl000002.result index 88228321897..4c2b3bdfde6 100644 --- a/mysql-test/r/rpl000002.result +++ b/mysql-test/r/rpl000002.result @@ -1,14 +1,13 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists t1; create table t1 (n int auto_increment primary key); set insert_id = 2000; insert into t1 values (NULL),(NULL),(NULL); -use test; select * from t1; n 2000 @@ -16,7 +15,7 @@ n 2002 show slave hosts; Server_id Host Port Rpl_recovery_rank Master_id -2 127.0.0.1 $SLAVE_MYPORT 2 1 +2 127.0.0.1 9999 2 1 drop table t1; slave stop; drop table if exists t2; diff --git a/mysql-test/r/rpl000003.result b/mysql-test/r/rpl000003.result index eb64d1855b1..b123b3d98c5 100644 --- a/mysql-test/r/rpl000003.result +++ b/mysql-test/r/rpl000003.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000004.result b/mysql-test/r/rpl000004.result index e5dee880f73..82b208d0b58 100644 --- a/mysql-test/r/rpl000004.result +++ b/mysql-test/r/rpl000004.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; set SQL_LOG_BIN=0; drop table if exists t1; create table t1 (word char(20) not null, index(word)); @@ -12,7 +12,6 @@ drop table if exists t2; create table t2 (word char(20) not null); load data infile '../../std_data/words.dat' into table t2; create table t3 (word char(20) not null primary key); -use test; drop table if exists t1; load table t1 from master; drop table if exists t2; diff --git a/mysql-test/r/rpl000005.result b/mysql-test/r/rpl000005.result index 0c91f984db1..3e9028bf2cf 100644 --- a/mysql-test/r/rpl000005.result +++ b/mysql-test/r/rpl000005.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000006.result b/mysql-test/r/rpl000006.result index f4e965236af..e7fc5151ac4 100644 --- a/mysql-test/r/rpl000006.result +++ b/mysql-test/r/rpl000006.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000007.result b/mysql-test/r/rpl000007.result index 7b2b9939228..c2823bf1203 100644 --- a/mysql-test/r/rpl000007.result +++ b/mysql-test/r/rpl000007.result @@ -1,13 +1,12 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists foo; create table foo (n int); insert into foo values(4); -use test; drop table if exists foo; create table foo (s char(20)); load data infile '../../std_data/words.dat' into table foo; diff --git a/mysql-test/r/rpl000008.result b/mysql-test/r/rpl000008.result index 5ca156fecf0..a0230d55702 100644 --- a/mysql-test/r/rpl000008.result +++ b/mysql-test/r/rpl000008.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000009.result b/mysql-test/r/rpl000009.result index 5246dadc06b..afd566c366c 100644 --- a/mysql-test/r/rpl000009.result +++ b/mysql-test/r/rpl000009.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000010.result b/mysql-test/r/rpl000010.result index 95792a72a6d..49538ed148e 100644 --- a/mysql-test/r/rpl000010.result +++ b/mysql-test/r/rpl000010.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000011.result b/mysql-test/r/rpl000011.result index 1a46f479677..5d22c29bdba 100644 --- a/mysql-test/r/rpl000011.result +++ b/mysql-test/r/rpl000011.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists t1; create table t1 (n int); insert into t1 values(1); diff --git a/mysql-test/r/rpl000012.result b/mysql-test/r/rpl000012.result index c7f3ab8f578..45de6502bbd 100644 --- a/mysql-test/r/rpl000012.result +++ b/mysql-test/r/rpl000012.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000013.result b/mysql-test/r/rpl000013.result index 5e4b4159e3a..9e802da59c5 100644 --- a/mysql-test/r/rpl000013.result +++ b/mysql-test/r/rpl000013.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000015.result b/mysql-test/r/rpl000015.result index a35004298e0..7010349e5ff 100644 --- a/mysql-test/r/rpl000015.result +++ b/mysql-test/r/rpl000015.result @@ -5,7 +5,6 @@ master-bin.001 79 reset slave; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space - 0 0 0 0 No No 0 0 0 0 change master to master_host='127.0.0.1'; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space diff --git a/mysql-test/r/rpl000017.result b/mysql-test/r/rpl000017.result index e2c3c089378..bac0573165d 100644 --- a/mysql-test/r/rpl000017.result +++ b/mysql-test/r/rpl000017.result @@ -1,6 +1,6 @@ reset master; -grant file on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; -grant file on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; slave start; drop table if exists t1; create table t1(n int); diff --git a/mysql-test/r/rpl_alter.result b/mysql-test/r/rpl_alter.result new file mode 100644 index 00000000000..1dc73c6524a --- /dev/null +++ b/mysql-test/r/rpl_alter.result @@ -0,0 +1,21 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +drop database if exists d1; +create database d1; +create table d1.t1 ( n int); +alter table d1.t1 add m int; +insert into d1.t1 values (1,2); +create table d1.t2 (n int); +insert into d1.t2 values (45); +rename table d1.t2 to d1.t3, d1.t1 to d1.t2; +select * from d1.t2; +n m +1 2 +select * from d1.t3; +n +45 +drop database d1; diff --git a/mysql-test/r/rpl_compat.result b/mysql-test/r/rpl_compat.result deleted file mode 100644 index 5e497791771..00000000000 --- a/mysql-test/r/rpl_compat.result +++ /dev/null @@ -1,77 +0,0 @@ -slave stop; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -slave start; -use test; -drop table if exists t1,t3; -create table t1 (word char(20) not null); -load data infile '../../std_data/words.dat' into table t1; -select * from t1; -word -Aarhus -Aaron -Ababa -aback -abaft -abandon -abandoned -abandoning -abandonment -abandons -set password for root@"localhost" = password('foo'); -set password for root@"localhost" = password(''); -create table t3(n int); -insert into t3 values(1),(2); -use test; -select * from t3; -n -1 -2 -select sum(length(word)) from t1; -sum(length(word)) -71 -drop table t1,t3; -reset master; -slave stop; -reset slave; -create table t1(n int); -insert into t1 values (1),(2),(3); -create table t2(id int); -insert into t2 values(connection_id()); -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; - update t1 set n = n + get_lock('crash_lock%20C', 2); -select (@id := id) - id from t2; -(@id := id) - id -0 -kill @id; -drop table t2; -Server shutdown in progress -slave start; -set sql_slave_skip_counter=1; -slave start; -select count(*) from t1; -count(*) -3 -drop table t1; -create table t1 (n int); -insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) -VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); -select select_priv,user from mysql.user where user = 'blafasel2'; -select_priv user -N blafasel2 -update user set Select_priv = "Y" where User="blafasel2"; -select select_priv,user from mysql.user where user = 'blafasel2'; -select_priv user -Y blafasel2 -use test; -select n from t1; -n -3456 -select select_priv,user from mysql.user where user = 'blafasel2'; -select_priv user -Y blafasel2 -drop table t1; diff --git a/mysql-test/r/rpl_empty_master_crash.result b/mysql-test/r/rpl_empty_master_crash.result index 6ae5d15031b..19e2bf28dcd 100644 --- a/mysql-test/r/rpl_empty_master_crash.result +++ b/mysql-test/r/rpl_empty_master_crash.result @@ -1,12 +1,13 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists t1; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space - 0 0 0 0 No No 0 0 0 0 load table t1 from master; -Error in fetch_master_table +Error connecting to master: Master is not configured +load table t1 from master; +Error from master: 'Table 'test.t1' doesn't exist' diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result new file mode 100644 index 00000000000..da2930f30c3 --- /dev/null +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -0,0 +1,17 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=MASTER_PORT; +slave start; +slave stop; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=SLAVE_PORT; +slave start; +flush logs; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.001 119 slave-bin.001 Yes Yes 0 0 79 119 diff --git a/mysql-test/r/rpl_get_lock.result b/mysql-test/r/rpl_get_lock.result index c86b8f830a5..a8e602be03f 100644 --- a/mysql-test/r/rpl_get_lock.result +++ b/mysql-test/r/rpl_get_lock.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -17,4 +18,13 @@ get_lock("lock",3) select * from t1; n 1 +select is_free_lock("lock"); +is_free_lock("lock") +0 +select is_free_lock("lock2"); +is_free_lock("lock2") +1 +select is_free_lock(NULL); +is_free_lock(NULL) +NULL drop table t1; diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index f5885085dee..b6e1419b466 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -7,7 +8,6 @@ slave stop; reset master; reset slave; 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; @@ -17,46 +17,48 @@ drop table t1; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) -master-bin.001 263 Query 1 263 use test; drop table t1 -master-bin.001 311 Query 1 311 use test; create table t1 (word char(20) not null) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +master-bin.001 263 Query 1 263 use `test`; drop table t1 +master-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 master-bin.001 556 Exec_load 1 556 ;file_id=1 -master-bin.001 579 Query 1 579 use test; drop table t1 +master-bin.001 579 Query 1 579 use `test`; drop table t1 show binlog events from 79 limit 1; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) show binlog events from 79 limit 2; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 show binlog events from 79 limit 2,1; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +flush logs; +slave start; flush logs; +slave stop; create table t1 (n int); insert into t1 values (1); drop table t1; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) -master-bin.001 263 Query 1 263 use test; drop table t1 -master-bin.001 311 Query 1 311 use test; create table t1 (word char(20) not null) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +master-bin.001 263 Query 1 263 use `test`; drop table t1 +master-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 master-bin.001 556 Exec_load 1 556 ;file_id=1 -master-bin.001 579 Query 1 579 use test; drop table t1 +master-bin.001 579 Query 1 579 use `test`; drop table t1 master-bin.001 627 Rotate 1 627 master-bin.002;pos=4 -master-bin.001 668 Stop 1 668 show binlog events in 'master-bin.002'; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.002 4 Query 1 4 use test; create table t1 (n int) -master-bin.002 62 Query 1 62 use test; insert into t1 values (1) -master-bin.002 122 Query 1 122 use test; drop table t1 +master-bin.002 4 Query 1 4 use `test`; create table t1 (n int) +master-bin.002 62 Query 1 62 use `test`; insert into t1 values (1) +master-bin.002 122 Query 1 122 use `test`; drop table t1 show master logs; Log_name master-bin.001 @@ -67,32 +69,24 @@ Log_name slave-bin.001 slave-bin.002 show binlog events in 'slave-bin.001' from 4; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.001 4 Start 2 4 Server ver: VERSION, Binlog ver: 3 +slave-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) +slave-bin.001 172 Intvar 1 200 INSERT_ID=1 +slave-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +slave-bin.001 263 Query 1 263 use `test`; drop table t1 +slave-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) +slave-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 +slave-bin.001 565 Exec_load 1 556 ;file_id=1 +slave-bin.001 588 Query 1 579 use `test`; drop table t1 +slave-bin.001 636 Rotate 2 636 slave-bin.002;pos=4 show binlog events in 'slave-bin.002' from 4; Log_name Pos Event_type Server_id Orig_log_pos Info -slave-bin.002 4 Slave 2 627 host=127.0.0.1,port=MASTER_PORT,log=master-bin.002,pos=4 -slave-bin.002 57 Query 1 4 use test; create table t1 (n int) -slave-bin.002 115 Query 1 62 use test; insert into t1 values (1) -slave-bin.002 175 Query 1 122 use test; drop table t1 +slave-bin.002 4 Query 1 4 use `test`; create table t1 (n int) +slave-bin.002 62 Query 1 62 use `test`; insert into t1 values (1) +slave-bin.002 122 Query 1 122 use `test`; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 916 master-bin.002 Yes Yes 0 0 170 924 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.001 132 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=79 and master_server_id=1; -Log_name Log_pos -slave-bin.001 225 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=311 and master_server_id=1; -Log_name Log_pos -slave-bin.001 439 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.002 57 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=122 and master_server_id=1; -Log_name Log_pos -slave-bin.002 223 +127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 957 master-bin.002 Yes Yes 0 0 170 961 +show binlog events in 'slave-bin.005' from 4; +Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl000014.result b/mysql-test/r/rpl_log_pos.result index c9926c92a4f..6883da76180 100644 --- a/mysql-test/r/rpl000014.result +++ b/mysql-test/r/rpl_log_pos.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -8,7 +9,7 @@ File Position Binlog_do_db Binlog_ignore_db master-bin.001 79 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 128 +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 124 change master to master_log_pos=73; slave stop; change master to master_log_pos=73; @@ -18,22 +19,24 @@ Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Lo slave start; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 4 master-bin.001 Yes Yes 0 0 73 4 +127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 4 master-bin.001 No Yes 0 0 73 4 change master to master_log_pos=173; +slave start; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 173 slave-relay-bin.001 4 master-bin.001 Yes Yes 0 0 173 4 +127.0.0.1 root MASTER_PORT 1 master-bin.001 173 slave-relay-bin.001 4 master-bin.001 No Yes 0 0 173 4 show master status; File Position Binlog_do_db Binlog_ignore_db master-bin.001 79 -create table if not exists foo(n int); -drop table if exists foo; -create table foo (n int); -insert into foo values (1),(2),(3); +create table if not exists t1 (n int); +drop table if exists t1; +create table t1 (n int); +insert into t1 values (1),(2),(3); change master to master_log_pos=79; -select * from foo; +slave start; +select * from t1; n 1 2 3 -drop table foo; +drop table t1; diff --git a/mysql-test/r/rpl_magic.result b/mysql-test/r/rpl_magic.result deleted file mode 100644 index b30d40d04eb..00000000000 --- a/mysql-test/r/rpl_magic.result +++ /dev/null @@ -1,36 +0,0 @@ -slave stop; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -slave start; -drop table if exists t1; -create table t1 ( n int); -insert into t1 values (1),(2),(3),(4); -insert into t1 values(5); -select * from t1; -n -1 -2 -3 -4 -5 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -5 -drop table t1; diff --git a/mysql-test/r/rpl_mystery22.result b/mysql-test/r/rpl_mystery22.result index e798d774380..5dd665fe9d5 100644 --- a/mysql-test/r/rpl_mystery22.result +++ b/mysql-test/r/rpl_mystery22.result @@ -1,5 +1,5 @@ -drop table if exists t1; slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl_redirect.result b/mysql-test/r/rpl_redirect.result new file mode 100644 index 00000000000..6103a075684 --- /dev/null +++ b/mysql-test/r/rpl_redirect.result @@ -0,0 +1,43 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +SHOW SLAVE STATUS; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +SHOW SLAVE HOSTS; +Server_id Host Port Rpl_recovery_rank Master_id +2 127.0.0.1 SLAVE_PORT 2 1 +drop table if exists t1; +create table t1 ( n int); +insert into t1 values (1),(2),(3),(4); +insert into t1 values(5); +select * from t1; +n +1 +2 +3 +4 +5 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +5 +drop table t1; +drop table t1; diff --git a/mysql-test/r/rpl000016.result b/mysql-test/r/rpl_rotate_logs.result index 9d6ba071200..63d5b0b63e1 100644 --- a/mysql-test/r/rpl000016.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -2,20 +2,20 @@ slave start; Could not initialize master info structure, check permisions on master.info slave start; Could not initialize master info structure, check permisions on master.info -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; Could not initialize master info reset slave; -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; reset master; slave start; -drop table if exists t1; +drop table if exists t1, t2, t3, t4; +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables"); create table t1 (s text); insert into t1 values('Could not break slave'),('Tried hard'); show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.001 234 slave-relay-bin.001 275 master-bin.001 Yes Yes 0 0 234 275 +127.0.0.1 root MASTER_PORT 60 master-bin.001 417 slave-relay-bin.001 458 master-bin.001 Yes Yes 0 0 417 458 select * from t1; s Could not break slave @@ -30,10 +30,15 @@ Log_name master-bin.001 master-bin.002 master-bin.003 +create table t3 select * from temp_table; +select * from t3; +a +testing temporary tables +drop table temp_table, t3; insert into t2 values(1234); set insert_id=1234; insert into t2 values(NULL); -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; purge master logs to 'master-bin.003'; show master logs; @@ -42,7 +47,7 @@ master-bin.003 insert into t2 values (65); show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.003 155 slave-relay-bin.001 755 master-bin.003 Yes Yes 0 0 155 755 +127.0.0.1 root MASTER_PORT 60 master-bin.003 290 slave-relay-bin.001 1073 master-bin.003 Yes Yes 0 0 290 1073 select * from t2; m 34 @@ -50,8 +55,11 @@ m 67 123 1234 +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables part 2"); drop table if exists t3; create table t3 (n int); +create table t4 select * from temp_table; show master logs; Log_name master-bin.003 @@ -60,15 +68,16 @@ master-bin.005 master-bin.006 show master status; File Position Binlog_do_db Binlog_ignore_db -master-bin.006 445 -slave stop; -slave start; +master-bin.006 838 +select * from t4; +a +testing temporary tables part 2 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.006 445 slave-relay-bin.004 1229 master-bin.006 Yes Yes 0 0 445 1229 +127.0.0.1 root MASTER_PORT 60 master-bin.006 838 slave-relay-bin.001 8034 master-bin.006 Yes Yes 0 0 838 8034 lock tables t3 read; select count(*) from t3 where n >= 4; count(*) 100 unlock tables; -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4; diff --git a/mysql-test/r/rpl_skip_error.result b/mysql-test/r/rpl_skip_error.result index 9fe92292178..946d64ad7c5 100644 --- a/mysql-test/r/rpl_skip_error.result +++ b/mysql-test/r/rpl_skip_error.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl_sporadic_master.result b/mysql-test/r/rpl_sporadic_master.result index ca74904889b..a6a58515f0a 100644 --- a/mysql-test/r/rpl_sporadic_master.result +++ b/mysql-test/r/rpl_sporadic_master.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -drop table if exists t1,t2; create table t2(n int); create table t1(n int not null auto_increment primary key); insert into t1 values (NULL),(NULL); diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 8c7f3326912..fc918012d1f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1718,7 +1718,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -1731,7 +1731,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -1768,7 +1768,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -1781,7 +1781,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 @@ -1809,6 +1809,18 @@ explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 where used; Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 t2.fld1 1 where used; Using index +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort +1 SIMPLE t3 ref period period 4 t1.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t1 ref period period 4 t3.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t3 ref period period 4 t1.period 4181 select period from t1; period 9410 @@ -3229,6 +3241,28 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; -drop table t4, t3,t2, t1; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); +SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; +gvid the_success the_fail the_size the_time +DROP TABLE t1,t2; +create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0'); +INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); +select wss_type from t1 where wss_type ='102935229216544106'; +wss_type +select wss_type from t1 where wss_type ='102935229216544105'; +wss_type +select wss_type from t1 where wss_type ='102935229216544104'; +wss_type +select wss_type from t1 where wss_type ='102935229216544093'; +wss_type +102935229216544093 +select wss_type from t1 where wss_type =102935229216544093; +wss_type +102935229216544093 +drop table t1; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 3d2f2154b37..1c2cd7da894 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -66,7 +66,7 @@ a 2 select FOUND_ROWS(); FOUND_ROWS() -5 +3 drop table t1; CREATE TABLE t1 ( `id` smallint(5) unsigned NOT NULL auto_increment, @@ -153,3 +153,19 @@ SELECT FOUND_ROWS(); FOUND_ROWS() 199 drop table t1,t2; +CREATE TABLE `t1` ( +`titre` char(80) NOT NULL default '', +`numeropost` mediumint(8) unsigned NOT NULL auto_increment, +`maxnumrep` int(10) unsigned NOT NULL default '0', +PRIMARY KEY (`numeropost`), +KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; +INSERT INTO t1 (titre,maxnumrep) VALUES +('test1','1'),('test2','2'),('test3','3'); +SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; +titre numeropost maxnumrep +test2 2 2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +drop table t1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 1f83bc61cdc..462abb29979 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -43,7 +43,7 @@ wait_timeout 28800 show variables like "this_doesn't_exists%"; Variable_name Value show table status from test like "this_doesn't_exists%"; -Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment +Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Charset Create_options Comment show databases; Database mysql @@ -77,13 +77,13 @@ show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 alter table t1 rename t2; show create table t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', @@ -96,7 +96,7 @@ t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) character set latin1 default 'O''Brien' COMMENT 'O''Brien as default', `c` int(11) NOT NULL default '0' COMMENT 'int column' -) TYPE=MyISAM COMMENT='it''s a table' +) TYPE=MyISAM CHARSET=latin1 COMMENT='it''s a table' show full columns from t1; Field Type Null Key Default Extra Privileges Comment test_set set('val1','val2','val3') select,insert,update,references @@ -109,7 +109,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', UNIQUE KEY `aa` (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a int not null, primary key (a)); show create table t1; @@ -117,7 +117,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; flush tables; show open tables; @@ -135,7 +135,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` char(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' +) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; show create table t1; Table Create Table @@ -143,7 +143,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' +) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default; show create table t1; Table Create Table @@ -151,7 +151,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0)); show columns from t1; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index b5b5b774ee5..1eb595eb2a9 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -39,7 +39,7 @@ t9 CREATE TABLE `t9` ( `b` char(16) character set latin1 NOT NULL default '', `c` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 alter table t9 rename t8, add column d int not null; alter table t8 rename t7; rename table t7 to t9; @@ -61,5 +61,5 @@ t9 CREATE TABLE `t9` ( `c` int(11) NOT NULL default '0', `d` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop database test_mysqltest; diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result index e02797cc8e5..ad390c9fa92 100644 --- a/mysql-test/r/truncate.result +++ b/mysql-test/r/truncate.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a integer, b integer,c1 CHAR(10)); insert into t1 (a) values (1),(2); truncate table t1; @@ -20,3 +21,5 @@ truncate table t1; select * from t1; n drop table t1; +truncate non_existing_table; +Table 'test.non_existing_table' doesn't exist diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index f4dfc3c2891..6550e981f5e 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -156,6 +156,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; a 0.00 @@ -176,6 +178,14 @@ a -99999999.99 999999999.99 999999999.99 +999999999.99 +0.00 +-99999999.99 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal(10,2) unsigned); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); @@ -183,19 +193,21 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; a 0.00 0.00 -0.00 -01.00 ++0.00 01.00 ++01.00 0.00 0.00 -0.10 ++0.10 0.10 00000001.00 -00000001.00 ++0000001.00 0.00 99999999.99 99999999.99 @@ -203,6 +215,14 @@ a 0.00 99999999.99 99999999.99 +99999999.99 +0.00 +0.00 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal(10,2) zerofill); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); @@ -210,6 +230,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; a 00000000.00 @@ -230,6 +252,14 @@ a 00000000.00 99999999.99 99999999.99 +99999999.99 +00000000.00 +00000000.00 +00000123.40 +00012340.00 +00000001.23 +00001230.00 +00000123.00 drop table t1; create table t1 (a decimal(10,2)); insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0); @@ -237,6 +267,8 @@ insert into t1 values (-.1),(+.1),(.1); insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); +insert into t1 values (1e+100),(1e-100),(-1e+100); +insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; a 0.00 @@ -257,6 +289,14 @@ a -99999999.99 999999999.99 999999999.99 +999999999.99 +0.00 +-99999999.99 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); @@ -276,9 +316,9 @@ select * from t1; a 0 0 -1 ++1 01 -0000000001 ++000000001 1234567890 9999999999 drop table t1; diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index ee3bd077798..c0f0be246c9 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1627,12 +1627,12 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('','a','b') NOT NULL default '' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a enum (' ','a','b ') not null default 'b '); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('','a','b') NOT NULL default 'b' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 858bf5f3ba2..b0ea1b69e59 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -1,14 +1,15 @@ +drop table if exists t1; create table t1 (a set (' ','a','b') not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('','a','b') NOT NULL default '' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a set (' ','a','b ') not null default 'b '); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('','a','b') NOT NULL default 'b' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index ff700f456ef..bacc3ec0176 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t time); insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index ab53d27e8f3..bd5e9f04992 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 ( t timestamp); SET TIMESTAMP=1234; insert into t1 values(NULL); diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index c892d575ca0..25ddda88f48 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (y year,y2 year(2)); insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); select * from t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index c64ee2ecd78..8ad0d8c6589 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -124,6 +124,8 @@ select * from t1 union select a from t2; The used SELECT statements have a different number of columns select a from t1 union select * from t2; The used SELECT statements have a different number of columns +select * from t1 union select SQL_BUFFER_RESULT * from t2; +Wrong usage/placement of 'SQL_BUFFER_RESULT' create table t3 select a,b from t1 union all select a,b from t2; insert into t3 select a,b from t1 union all select a,b from t2; replace into t3 select a,b as c from t1 union all select a,b from t2; @@ -189,9 +191,9 @@ a 1 2 set SQL_SELECT_LIMIT=2; -(select * from t1 limit 2) union (select * from t2 limit 3); +(select * from t1 limit 1) union (select * from t2 limit 3); a 1 -2 +11 set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 9b2c0316c18..7421c040766 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -1,5 +1,5 @@ set @a := foo; -You may only use constant expressions with SET +Unknown column 'foo' in 'field list' set @a := connection_id() + 3; select @a - connection_id(); @a - connection_id() diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index 1b6cf5e708e..7ce0f78da76 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -7,6 +7,7 @@ select 0x31+1,concat(0x31)+1,-0xf; select x'31',X'ffff'+0; x'31' X'ffff'+0 1 65535 +drop table if exists t1; create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) ); insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; @@ -15,7 +16,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const UNIQ UNIQ 8 const 1 drop table t1; select x'hello'; -You have an error in your SQL syntax near 'x'hello'' at line 1 +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1 select 0xfg; Unknown column '0xfg' in 'field list' create table t1 select 1 as x, 2 as xx; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 52c2c026bd3..f708ddd2ee7 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -44,17 +44,292 @@ c_id c_name c_country 1 Bozo USA 4 Mr. Floppy GB drop table t1; +set max_join_size=100; +show variables like 'max_join_size'; +Variable_name Value +max_join_size 100 +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 4294967295 +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 2000 +set max_join_size=DEFAULT; +show variables like 'max_join_size'; +Variable_name Value +max_join_size 2000 +set GLOBAL max_join_size=DEFAULT; +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 4294967295 +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +@@session.max_join_size @@global.max_join_size +1000 2000 +select @@identity, length(@@version)>0; +@@identity length(@@version)>0 +0 1 select @@VERSION=version(); @@VERSION=version() 1 select last_insert_id(345); last_insert_id(345) 345 -select @@IDENTITY,last_insert_id(); -@@IDENTITY last_insert_id() -345 345 -select @@identity; -@@IDENTITY -345 -select @@unknown_variable; +select @@IDENTITY,last_insert_id(), @@identity; +@@identity last_insert_id() @@identity +345 345 345 +set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; +set global concurrent_insert=ON; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert ON +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert ON +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert OFF +set global concurrent_insert=OFF; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert OFF +set global concurrent_insert=DEFAULT; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert ON +set table_type=MYISAM, table_type="HEAP", global table_type="INNODB"; +show local variables like 'table_type'; +Variable_name Value +table_type HEAP +show global variables like 'table_type'; +Variable_name Value +table_type INNODB +set GLOBAL query_cache_size=100000; +set GLOBAL safe_show_database=0; +set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; +show variables like 'myisam_max_sort_file_size'; +Variable_name Value +myisam_max_sort_file_size 10000 +show global variables like 'myisam_max_sort_file_size'; +Variable_name Value +myisam_max_sort_file_size 20000 +set myisam_max_sort_file_size=default; +show variables like 'myisam_max_sort_file_size'; +Variable_name Value +myisam_max_sort_file_size 20000 +set global net_retry_count=10, session net_retry_count=10; +set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; +set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; +show global variables like 'net_%'; +Variable_name Value +net_buffer_length 1024 +net_read_timeout 300 +net_retry_count 10 +net_write_timeout 200 +show session variables like 'net_%'; +Variable_name Value +net_buffer_length 2048 +net_read_timeout 600 +net_retry_count 10 +net_write_timeout 500 +set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +Variable_name Value +net_buffer_length 1024 +net_read_timeout 900 +net_retry_count 10 +net_write_timeout 1000 +show session variables like 'net_%'; +Variable_name Value +net_buffer_length 7168 +net_read_timeout 600 +net_retry_count 10 +net_write_timeout 500 +set net_buffer_length=1; +show variables like 'net_buffer_length'; +Variable_name Value +net_buffer_length 1024 +set net_buffer_length=2000000000; +show variables like 'net_buffer_length'; +Variable_name Value +net_buffer_length 1048576 +set GLOBAL character set cp1251_koi8; +show global variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +set character set cp1251_koi8; +show variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +set global character set default, session character set default; +show variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +select @@timestamp>0; +@@timestamp>0 +1 +set big_tables=OFFF; +Variable 'big_tables' can't be set to the value of 'OFFF' +set big_tables="OFFF"; +Variable 'big_tables' can't be set to the value of 'OFFF' +set unknown_variable=1; Unknown system variable 'unknown_variable' +set max_join_size="hello"; +Wrong argument type to variable 'max_join_size' +set table_type=UNKNOWN_TABLE_TYPE; +Variable 'table_type' can't be set to the value of 'UNKNOWN_TABLE_TYPE' +set table_type=INNODB, big_tables=2; +Variable 'big_tables' can't be set to the value of '2' +show local variables like 'table_type'; +Variable_name Value +table_type HEAP +set SESSION query_cache_size=10000; +Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +set GLOBAL table_type=DEFAULT; +Variable 'table_type' doesn't have a default value +set convert_character_set=UNKNOWN_CHARACTER_SET; +Unknown character set: 'UNKNOWN_CHARACTER_SET' +set character set unknown; +Unknown character set: 'unknown' +set character set 0; +Wrong argument type to variable 'convert_character_set' +set global autocommit=1; +Variable 'autocommit' is a LOCAL variable and can't be used with SET GLOBAL +select @@global.timestamp; +Variable 'timestamp' is a LOCAL variable and can't be used with SET GLOBAL +set @@version=''; +Unknown system variable 'version' +set @@concurrent_insert=1; +Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL +set @@global.sql_auto_is_null=1; +Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +select @@global.sql_auto_is_null; +Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +@@autocommit @@big_tables +1 1 +set global binlog_cache_size=100; +set bulk_insert_buffer_size=100; +set convert_character_set=cp1251_koi8; +set convert_character_set=default; +set @@global.concurrent_insert=1; +set global connect_timeout=100; +select @@delay_key_write; +@@delay_key_write +ON +set global delay_key_write="OFF"; +select @@delay_key_write; +@@delay_key_write +OFF +set global delay_key_write=ALL; +select @@delay_key_write; +@@delay_key_write +ALL +set global delay_key_write=1; +select @@delay_key_write; +@@delay_key_write +ON +set global delayed_insert_limit=100; +set global delayed_insert_timeout=100; +set global delayed_queue_size=100; +set global flush=1; +set global flush_time=100; +set insert_id=1; +set interactive_timeout=100; +set join_buffer_size=100; +set last_insert_id=1; +set global local_infile=1; +set long_query_time=100; +set low_priority_updates=1; +set max_allowed_packet=100; +set global max_binlog_cache_size=100; +set global max_binlog_size=100; +set global max_connect_errors=100; +set global max_connections=100; +set global max_delayed_threads=100; +set max_heap_table_size=100; +set max_join_size=100; +set max_sort_length=100; +set max_tmp_tables=100; +set global max_user_connections=100; +select @@max_user_connections; +@@max_user_connections +100 +set global max_write_lock_count=100; +set myisam_max_extra_sort_file_size=100; +select @@myisam_max_extra_sort_file_size; +@@myisam_max_extra_sort_file_size +100 +set myisam_max_sort_file_size=100; +set myisam_sort_buffer_size=100; +set net_buffer_length=100; +set net_read_timeout=100; +set net_write_timeout=100; +set global query_cache_limit=100; +set global query_cache_size=100; +set global query_cache_type=demand; +set read_buffer_size=100; +set read_rnd_buffer_size=100; +set global rpl_recovery_rank=100; +set global safe_show_database=1; +set global server_id=100; +set global slave_net_timeout=100; +set global slow_launch_time=100; +set sort_buffer_size=100; +set sql_auto_is_null=1; +select @@sql_auto_is_null; +@@sql_auto_is_null +1 +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +@@sql_auto_is_null +0 +set sql_big_selects=1; +set sql_big_tables=1; +set sql_buffer_result=1; +set sql_log_bin=1; +set sql_log_off=1; +set sql_log_update=1; +set sql_low_priority_updates=1; +set sql_max_join_size=200; +select @@sql_max_join_size,@@max_join_size; +@@sql_max_join_size @@max_join_size +200 200 +set sql_quote_show_create=1; +set sql_safe_updates=1; +set sql_select_limit=1; +set global sql_slave_skip_counter=100; +set sql_warnings=1; +set global table_cache=100; +set table_type=myisam; +set global thread_cache_size=100; +set timestamp=1, timestamp=default; +set tmp_table_size=100; +set tx_isolation="READ-COMMITTED"; +set wait_timeout=100; +set log_warnings=1; +DROP TABLE IF EXISTS t1,t2; +create table t1 (a int not null auto_increment, primary key(a)); +create table t2 (a int not null auto_increment, primary key(a)); +insert into t1 values(null),(null),(null); +insert into t2 values(null),(null),(null); +set global key_buffer_size=100000; +select @@key_buffer_size; +@@key_buffer_size +98304 +select * from t1 where a=2; +a +2 +select * from t2 where a=3; +a +3 +check table t1,t2; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +drop table t1,t2; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d77e98cc635..e5713718db0 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); diff --git a/mysql-test/t/README b/mysql-test/t/README deleted file mode 100644 index e69de29bb2d..00000000000 --- a/mysql-test/t/README +++ /dev/null diff --git a/mysql-test/t/bdb-alter-table-1.test b/mysql-test/t/bdb-alter-table-1.test new file mode 100644 index 00000000000..5861c2fe0bf --- /dev/null +++ b/mysql-test/t/bdb-alter-table-1.test @@ -0,0 +1,13 @@ +# +# Test of problem when shutting down mysqld at once after ALTER TABLE +# +-- source include/have_bdb.inc +drop table if exists t1; +create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; +insert into t1 values(1, 't1',4,9); +insert into t1 values(2, 'metatable',1,9); +insert into t1 values(3, 'metaindex',1,9 ); +select * from t1; +alter table t1 drop column test; + +# Now we do a reboot and continue with the next test diff --git a/mysql-test/t/bdb-alter-table-2-master.opt b/mysql-test/t/bdb-alter-table-2-master.opt new file mode 100644 index 00000000000..15ad73c500f --- /dev/null +++ b/mysql-test/t/bdb-alter-table-2-master.opt @@ -0,0 +1,2 @@ +--skip-external-locking + diff --git a/mysql-test/t/bdb-alter-table-2.test b/mysql-test/t/bdb-alter-table-2.test new file mode 100644 index 00000000000..a474efe42e1 --- /dev/null +++ b/mysql-test/t/bdb-alter-table-2.test @@ -0,0 +1,8 @@ +# +# Note that this test uses tables from the previous test +# This is to test that the table t1 survives a reboot of MySQL +# The options in the -master.opt file are just there to force the reboot +# +-- source include/have_bdb.inc +select * from t1; +drop table t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index cec3e1ca45c..15b35ac7c87 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -5,12 +5,18 @@ select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296; select 9223372036854775807,-009223372036854775808; select +9999999999999999999,-9999999999999999999; +# +# In 3.23 we have to disable the test of column to bigint as +# this fails on AIX powerpc (the resolution for double is not good enough) +# This will work on 4.0 as we then have internal handling of bigint variables. +# + drop table if exists t1; 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'; +# select * from t1 where a='18446744073709551615'; delete from t1 where a=18446744073709551615; select * from t1; drop table t1; diff --git a/mysql-test/t/binary.test b/mysql-test/t/binary.test index 7853b013ae6..fc2d91e20fe 100644 --- a/mysql-test/t/binary.test +++ b/mysql-test/t/binary.test @@ -2,6 +2,7 @@ # test sort,min and max on binary fields # +drop table if exists t1,t2; create table t1 (name char(20) not null, primary key (name)); create table t2 (name char(20) binary not null, primary key (name)); insert into t1 values ("å"); @@ -31,3 +32,13 @@ select * from t1 where a="hello "; select * from t1 where b="hello "; select * from t1 where b="hello"; drop table t1; + +# +# Test of binary and NULL +# +create table t1 (b char(8)); +insert into t1 values(NULL); +select b from t1 where binary b like ''; +select b from t1 group by binary b like ''; +select b from t1 having binary b like ''; +drop table t1; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index ac2f9cd5879..47ee55620e4 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -105,3 +105,8 @@ drop table if exists t1,t2; create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b)); show create table t1; drop table t1; +create table t1 select if(1,'1','0'), month("2002-08-02"); +drop table t1; +create table t1 select if('2002'='2002','Y','N'); +select * from t1; +drop table if exists t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index d39d7f6e5dd..e829005a229 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -38,9 +38,10 @@ select strcmp('u','öa'),strcmp('u','ö'); # Some other simple tests with the current character set # -create table t1 (a varchar(10), key(a)); +create table t1 (a varchar(10), key(a), fulltext (a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; select * from t1 where a like "test%"; select * from t1 where a like "te_t"; +select * from t1 where match a against ("te*" in boolean mode)+0; drop table t1; diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 6fbd99e3283..67111ea1734 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -3,6 +3,7 @@ # (Can't be tested with purify :( ) # +drop table if exists t1; create table t1 (a char(10), tmsp timestamp); insert into t1 set a = 1; insert delayed into t1 set a = 2; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index b850ec5d562..aaffea3c5a5 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -218,3 +218,49 @@ select * from t1 where a >= '1'; select distinct a from t1 order by a desc; select distinct a from t1 where a >= '1' order by a desc; drop table t1; + +# +# Test when using a not previously used column in ORDER BY +# + +CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); +CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); + +INSERT INTO t1 (email, infoID, dateentered) VALUES + ('test1@testdomain.com', 1, '2002-07-30 22:56:38'), + ('test1@testdomain.com', 1, '2002-07-27 22:58:16'), + ('test2@testdomain.com', 1, '2002-06-19 15:22:19'), + ('test2@testdomain.com', 2, '2002-06-18 14:23:47'), + ('test3@testdomain.com', 1, '2002-05-19 22:17:32'); + +INSERT INTO t2(infoID, shipcode) VALUES + (1, 'Z001'), + (2, 'R002'); + +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; +SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; +drop table t1,t2; + +# +# test with table.* in DISTINCT +# + +CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); +CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) TYPE=MyISAM; +INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); +SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); +DROP TABLE IF EXISTS t1,t2; + +# +# test with const_item in ORDER BY +# + +CREATE TABLE t1 (a int primary key, b int, c int); +INSERT t1 VALUES (1,2,3); +CREATE TABLE t2 (a int primary key, b int, c int); +INSERT t2 VALUES (3,4,5); +SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; +DROP TABLE IF EXISTS t1,t2; + diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test index a4f12886120..4ddcd53d5c8 100644 --- a/mysql-test/t/flush_table.test +++ b/mysql-test/t/flush_table.test @@ -4,6 +4,7 @@ # Test of flush table # +#drop table if exists t1; #create table t1 (a int not null auto_increment primary key); #insert into t1 values(0); #lock table t1 read; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 238705c8e3e..65d5381d725 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -35,6 +35,7 @@ select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); @@ -154,4 +155,8 @@ insert into t1 values (2,"fullaaa fullzzz"); select * from t1 where match b against ('full*' in boolean mode); drop table t1; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); +select 8 from t1; +drop table t1; diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index abb1c8e8473..bcf7cbcc505 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -2,6 +2,7 @@ # Test for bug from Jean-Cédric COSTA <jean-cedric.costa@ensmp.fr> # +drop table if exists t1, t2; CREATE TABLE t1 ( id VARCHAR(255) NOT NULL PRIMARY KEY, sujet VARCHAR(255), diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test index d5cb99ef0ee..3b60ee77db2 100644 --- a/mysql-test/t/fulltext_order_by.test +++ b/mysql-test/t/fulltext_order_by.test @@ -30,3 +30,17 @@ SELECT a, MATCH (message) AGAINST ('steve') as rel FROM t1 ORDER BY rel; SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; drop table t1; + +# +# reused boolean scan bug +# +CREATE TABLE t1 ( + a INT AUTO_INCREMENT PRIMARY KEY, + message CHAR(20), + FULLTEXT(message) +); +INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; +drop table t1; + diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index b97afc06340..57e9ae24e08 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -2,6 +2,7 @@ # simple test of all group functions # +drop table if exists t1,t2; create table t1 (grp int, a bigint unsigned, c char(10) not null); insert into t1 values (1,1,"a"); insert into t1 values (2,2,"b"); diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index c5cc73ecd1f..85553d1a2fd 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -28,3 +28,7 @@ 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; +create table t1 (x int, y int); +insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); +select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; +drop table t1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 6830534e761..e5d42ec25c4 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -2,6 +2,7 @@ # test of IN (NULL) # +drop table if exists t1; CREATE TABLE t1 (field char(1)); INSERT INTO t1 VALUES ('A'),(NULL); SELECT * from t1 WHERE field IN (NULL); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 5299897d0f0..74e8a5ce092 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -8,7 +8,10 @@ select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2) select round(5.5),round(-5.5); select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2); select abs(-10), sign(-5), sign(5), sign(0); -select log(exp(10)),exp(log(sqrt(10))*2); +select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); +select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); +select log2(8),log2(15),log2(-2),log2(0),log2(NULL); +select log10(100),log10(18),log10(-4),log10(0),log10(NULL); select pow(10,log10(10)),power(2,4); select rand(999999),rand(); select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 26272fba03f..717b9c86a07 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -37,6 +37,16 @@ select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb') select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; select soundex(''),soundex('he'),soundex('hello all folks'); select md5('hello'); +select sha('abc'); +select sha1('abc'); +select aes_decrypt(aes_encrypt('abc','1'),'1'); +select aes_decrypt(aes_encrypt('abc','1'),1); +select aes_encrypt(NULL,"a"); +select aes_encrypt("a",NULL); +select aes_decrypt(NULL,"a"); +select aes_decrypt("a",NULL); +select aes_decrypt("a","a"); +select aes_decrypt(aes_encrypt("","a"),"a"); select repeat('monty',5),concat('*',space(5),'*'); select reverse('abc'),reverse('abcd'); select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'); @@ -51,6 +61,10 @@ select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000); select decode(encode("abcdef","monty"),"monty")="abcdef"; +select quote('\'\"\\test'); +select quote(concat('abc\'', '\\cba')); +select quote(1/0), quote('\0\Z'); + # # Wrong usage of functions # @@ -100,3 +114,12 @@ SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,mo SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1; SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) FROM t1; drop table t1; + +# +# Test bug in AES_DECRYPT() when called with wrong argument +# + +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); +SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); +DROP TABLE t1; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index ec44009b1a6..f5ad2e21c73 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -15,6 +15,8 @@ select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between select 'b' between 'a' and 'c', 'B' between 'a' and 'c'; select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); select -1.49 or -1.49,0.6 or 0.6; +select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; # # Wrong usage of functions diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test new file mode 100644 index 00000000000..7ee3b08cc3b --- /dev/null +++ b/mysql-test/t/grant.test @@ -0,0 +1,41 @@ +# +# Test that SSL options works properly +# + +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +flush privileges; +grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; +show grants for mysqltest_1@localhost; +grant delete on mysqltest.* to mysqltest_1@localhost; +select * from mysql.user where user="mysqltest_1"; +show grants for mysqltest_1@localhost; +revoke delete on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost require NONE; +show grants for mysqltest_1@localhost; +grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; +show grants for mysqltest_1@localhost; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; + +# +# Test that the new db privileges are stored/retrieved correctly +# + +grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +flush privileges; +show grants for mysqltest_1@localhost; +revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; +flush privileges; +show grants for mysqltest_1@localhost; +revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 171b5510227..072a1830f57 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -284,18 +284,31 @@ INSERT INTO t1 values ('hij','klm'); SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; DROP TABLE t1; drop table if exists t1; + +# +# Test problem with ORDER BY on a SUM() column +# + create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); -insert into t1 values (1,2,1,4); -insert into t1 values (1,2,2,4); -insert into t1 values (1,2,3,4); -insert into t1 values (1,2,4,4); -insert into t1 values (1,1,1,4); -insert into t1 values (1,1,2,4); -insert into t1 values (1,1,3,4); -insert into t1 values (1,1,4,4); -insert into t1 values (1,3,1,4); -insert into t1 values (1,3,2,4); -insert into t1 values (1,3,3,4); -insert into t1 values (1,3,4,4); +insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4); select One, Two, sum(Four) from t1 group by One,Two; -drop table if exists t1; +drop table t1; + +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); +create table t2 (user_id integer not null, date date); +insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09'); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +drop table t1,t2; + +# +# The GROUP BY returned rows in wrong order in 3.23.51 +# + +CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID +)); +insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; +select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 1b7ee99e15a..e6d57899082 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -578,17 +578,11 @@ explain select a,b,c from t1; drop table t1; # -# Check describe & handler +# Check describe # create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; ---error 1031 -handler t1 open t1; ---error 1109 -handler t1 read t first; ---error 1109 -handler t1 close; drop table t1; # @@ -638,3 +632,31 @@ select * from t1; select * from t2; select * from t2; drop table t1,t2; + +# +# A simple test with some isolation levels +# TODO: Make this into a test using replication to really test how +# this works. +# + +create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +SELECT @@tx_isolation,@@global.tx_isolation; +insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); +select id, code, name from t1 order by id; +COMMIT; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); +select id, code, name from t1 order by id; +COMMIT; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); +select id, code, name from t1 order by id; +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test new file mode 100644 index 00000000000..6b85be9c7ab --- /dev/null +++ b/mysql-test/t/innodb_handler.test @@ -0,0 +1,76 @@ +-- source include/have_innodb.inc + +# +# test of HANDLER ... +# + +drop table if exists t1; +create table t1 (a int, b char(10), key a(a), key b(a,b)) type=innodb; +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +handler t2 read a next; +handler t2 read a next; +handler t2 read a prev; +handler t2 read a last; +handler t2 read a prev; +handler t2 read a prev; + +handler t2 read a first; +handler t2 read a prev; + +handler t2 read a last; +handler t2 read a prev; +handler t2 read a next; +handler t2 read a next; + +handler t2 read a=(15); +handler t2 read a=(16); + +--error 1070 +handler t2 read a=(19,"fff"); + +handler t2 read b=(19,"fff"); +handler t2 read b=(19,"yyy"); +handler t2 read b=(19); + +--error 1109 +handler t1 read a last; + +handler t2 read a=(11); +handler t2 read a>=(11); + +handler t2 read a=(18); +handler t2 read a>=(18); +handler t2 read a>(18); +handler t2 read a<=(18); +handler t2 read a<(18); + +handler t2 read a first limit 5; +handler t2 read a next limit 3; +handler t2 read a prev limit 10; + +handler t2 read a>=(16) limit 4; +handler t2 read a>=(16) limit 2,2; +handler t2 read a last limit 3; + +handler t2 read a=(19); +handler t2 read a=(19) where b="yyy"; + +handler t2 read first; +handler t2 read next; +# +# We alter the table even if it's still in use by to test the Innodb +# delayed-drop code. This will generate a warning in the master.err log. +# +alter table t1 type=innodb; +handler t2 read next; +--error 1064 +handler t2 read last; + +handler t2 close; +drop table if exists t1; + diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 270b1cd4c79..0bca8dc5890 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -22,3 +22,39 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; + +# +# Test insert syntax +# + +create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); +select a,t>0,c,i from t1; +truncate table t1; +insert into t1 set a=default,t=default,c=default; +insert into t1 set a=default,t=default,c=default,i=default; +insert into t1 set a=4,t=0,c="a",i=5; +insert into t1 set a=5,t=0,c="a",i=null; +insert into t1 set a=default,t=default,c=default,i=default; +select a,t>0,c,i from t1; +drop table t1; + +# +# Test problem with bulk insert and auto_increment on second part keys +# + +create table t1 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id)); +insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL); +select * from t1; +drop table t1; + +# +# Test of mysqld crash with fully qualified column names +# + +drop database if exists foo; +create database foo; +use foo; +create table t1 (c int); +insert into foo.t1 set foo.t1.c = '1'; +drop database foo; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 6fc975a1cb3..1996c793880 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -168,3 +168,23 @@ drop table t1; create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); show keys from t1; drop table t1; + +# +# Problem with UNIQUE() with NULL parts and auto increment +# + +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +drop table t1; diff --git a/mysql-test/t/key_primary.test b/mysql-test/t/key_primary.test index 75dded5d667..2cdb117128f 100644 --- a/mysql-test/t/key_primary.test +++ b/mysql-test/t/key_primary.test @@ -2,6 +2,7 @@ # test of primary key conversions # +drop table if exists t1; create table t1 (t1 char(3) primary key); insert into t1 values("ABC"); insert into t1 values("ABA"); diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 53e9fd3393c..792ae956e2a 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -6,6 +6,7 @@ # -- source include/not_embedded.inc +drop table if exists t1; #test to see if select will get the lock ahead of low priority update connect (locker,localhost,root,,); diff --git a/mysql-test/t/lowercase_table-master.opt b/mysql-test/t/lowercase_table-master.opt new file mode 100644 index 00000000000..c718e2feb1b --- /dev/null +++ b/mysql-test/t/lowercase_table-master.opt @@ -0,0 +1 @@ +--lower_case_table_names diff --git a/mysql-test/t/lowercase_table.test b/mysql-test/t/lowercase_table.test new file mode 100644 index 00000000000..0d04e6c7df7 --- /dev/null +++ b/mysql-test/t/lowercase_table.test @@ -0,0 +1,14 @@ +# +# Test of --lower-case-table-names +# + +drop table if exists t1,t2,t3; +create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +SELECT * FROM t1; +RENAME TABLE T1 TO T2; +ALTER TABLE T2 ADD new_col int not null; +ALTER TABLE T2 RENAME T3; +show tables like 't_'; +drop table t3; + diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 5bd78769a05..86e787db0a3 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -129,18 +129,18 @@ drop table t3,t1,t2; drop table if exists t6, t5, t4, t3, t2, t1; # first testing of common stuff with new parameters -create table t1 (a int not null, b int not null, key(a,b)); -create table t2 (a int not null, b int not null, key(a,b)); +create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); +create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; -create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; show create table t4; show create table t5; show create table t6; -insert into t1 values (1,1),(1,2),(1,3),(1,4); -insert into t2 values (2,1),(2,2),(2,3),(2,4); +insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); +insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); select * from t3 order by b,a limit 3; select * from t4 order by b,a limit 3; select * from t5 order by b,a limit 3,3; @@ -167,4 +167,21 @@ select * from t2 order by a,b; select * from t3 order by a,b; select * from t4 order by a,b; select * from t5 order by a,b; +# auto_increment +select 1; +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t5 order by a,b; +select * from t6 order by a,b; drop table if exists t6, t5, t4, t3, t2, t1; + +CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,1); +CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,2), (2,2); +CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); +select max(b) from t where a = 2; +select max(b) from t1 where a = 2; +drop table if exists t,t1,t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index c16189168d2..205dbb10ccf 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -62,3 +62,81 @@ select count(*) from t2 where id2; select count(*) from t3 where id3; drop table t1,t2,t3; +create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; +create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; +disable_query_log; +let $1 = 1000; +while ($1) + { + let $2 = 5; + eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); + while ($2) + { + eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); + dec $2; + } + dec $1; + } +enable_query_log; +delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; +drop table t1,t2; +DROP TABLE IF EXISTS a,b,c; +CREATE TABLE a ( + id int(11) NOT NULL default '0', + name varchar(10) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); +CREATE TABLE b ( + id int(11) NOT NULL default '0', + name varchar(10) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); +CREATE TABLE c ( + id int(11) NOT NULL default '0', + mydate datetime default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 +00:00:00'),(7,'2002-07-22 00:00:00'); +delete a,b,c from a,b,c +where to_days(now())-to_days(c.mydate)>=30 +and c.id=a.id and c.id=b.id; +select * from c; +DROP TABLE IF EXISTS a,b,c; +drop table if exists parent, child; +CREATE TABLE IF NOT EXISTS `parent` ( + `id` int(11) NOT NULL auto_increment, + `tst` text, + `tst1` text, + PRIMARY KEY (`id`) +) TYPE=MyISAM; + +CREATE TABLE IF NOT EXISTS `child` ( + `ID` int(11) NOT NULL auto_increment, + `ParId` int(11) default NULL, + `tst` text, + `tst1` text, + PRIMARY KEY (`ID`), + KEY `IX_ParId_child` (`ParId`), + FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) +) TYPE=MyISAM; + +INSERT INTO parent(tst,tst1) +VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); + +INSERT INTO child(ParId) +VALUES(1), (2), (3); + +select * from child; + +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; + +select * from child; + + +drop table parent, child;
\ No newline at end of file diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 42d1dd6d553..c69b87f325a 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -66,3 +66,51 @@ explain select a,b from t1 order by b; explain select a,b from t1; explain select a,b,c from t1; drop table t1; + +# +# Test of REPAIR that once failed +# +CREATE TABLE `t1` ( + `post_id` mediumint(8) unsigned NOT NULL auto_increment, + `topic_id` mediumint(8) unsigned NOT NULL default '0', + `post_time` datetime NOT NULL default '0000-00-00 00:00:00', + `post_text` text NOT NULL, + `icon_url` varchar(10) NOT NULL default '', + `sign` tinyint(1) unsigned NOT NULL default '0', + `post_edit` varchar(150) NOT NULL default '', + `poster_login` varchar(35) NOT NULL default '', + `ip` varchar(15) NOT NULL default '', + PRIMARY KEY (`post_id`), + KEY `post_time` (`post_time`), + KEY `ip` (`ip`), + KEY `poster_login` (`poster_login`), + KEY `topic_id` (`topic_id`), + FULLTEXT KEY `post_text` (`post_text`) +) TYPE=MyISAM; + +INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); + +REPAIR TABLE t1; +CHECK TABLE t1; +drop table t1; + +# +# Test of OPTIMIZE of locked and modified tables +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES (1), (2), (3); +OPTIMIZE TABLE t1; +DROP TABLE t1; + +# +# Test of creating table with too long key +# + +--error 1071 +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), KEY t1 (a, b, c)); +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255)); +--error 1071 +ALTER TABLE t1 ADD INDEX t1 (a, b, c); +DROP TABLE t1; diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test index 4629a08fd3f..3e0fc214d3e 100644 --- a/mysql-test/t/odbc.test +++ b/mysql-test/t/odbc.test @@ -8,6 +8,7 @@ select {fn length("hello")}, { date "1997-10-20" }; # Test retreiving row with last insert_id value. # +drop table if exists t1; create table t1 (a int not null auto_increment,b int not null,primary key (a,b)); insert into t1 SET A=NULL,B=1; insert into t1 SET a=null,b=2; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test new file mode 100644 index 00000000000..8fe547db615 --- /dev/null +++ b/mysql-test/t/olap.test @@ -0,0 +1,27 @@ +drop table if exists sales; +create table sales ( product varchar(32), country varchar(32), year int, profit int); +insert into sales values ( 'Computer', 'India',2000, 1200), +( 'TV', 'United States', 1999, 150), +( 'Calculator', 'United States', 1999,50), +( 'Computer', 'United States', 1999,1500), +( 'Computer', 'United States', 2000,1500), +( 'TV', 'United States', 2000, 150), +( 'TV', 'India', 2000, 100), +( 'TV', 'India', 2000, 100), +( 'Calculator', 'United States', 2000,75), +( 'Calculator', 'India', 2000,75), +( 'TV', 'India', 1999, 100), +( 'Computer', 'India', 1999,1200), +( 'Computer', 'United States', 2000,1500), +( 'Calculator', 'United States', 2000,75); +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube union all select product, country , year, sum(profit) from sales group by product, country, year with rollup; +drop table sales; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index baa3fe67f0b..0ee6f901aae 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -292,3 +292,41 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; drop table t1,t2,t3; + +# +# Test of bug when doing an ORDER BY with const items +# + +CREATE TABLE t1 ( + `titre` char(80) NOT NULL default '', + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `auteur` char(35) NOT NULL default '', + `icone` tinyint(2) unsigned NOT NULL default '0', + `lastauteur` char(35) NOT NULL default '', + `nbrep` smallint(6) unsigned NOT NULL default '0', + `dest` char(35) NOT NULL default '', + `lu` tinyint(1) unsigned NOT NULL default '0', + `vue` mediumint(8) unsigned NOT NULL default '0', + `ludest` tinyint(1) unsigned NOT NULL default '0', + `ouvert` tinyint(1) unsigned NOT NULL default '1', + PRIMARY KEY (`numeropost`), + KEY `date` (`date`), + KEY `dest` (`dest`,`ludest`), + KEY `auteur` (`auteur`,`lu`), + KEY `auteur_2` (`auteur`,`date`), + KEY `dest_2` (`dest`,`date`) +) CHECKSUM=1; + +CREATE TABLE t2 ( + `numeropost` mediumint(8) unsigned NOT NULL default '0', + `pseudo` char(35) NOT NULL default '', + PRIMARY KEY (`numeropost`,`pseudo`), + KEY `pseudo` (`pseudo`) +); + +INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); +INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); +SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +drop table t1,t2; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index a3227a5db1d..5dc6d1047f4 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -139,17 +139,17 @@ drop table t1, t2, t3, t11, t21; # # SELECT SQL_CACHE ... # -set sql_query_cache_type=demand; +set query_cache_type=demand; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; select sql_cache * from t1; -set sql_query_cache_type=2; +set query_cache_type=2; select sql_cache * from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; -set sql_query_cache_type=on; +set query_cache_type=on; # # RESET QUERY CACHE # @@ -336,3 +336,34 @@ select a from t1; show status like "Qcache_queries_in_cache"; drop table t1; show status like "Qcache_queries_in_cache"; + +# +# Test of query cache resizing +# +set GLOBAL query_cache_size=1000; +show global variables like "query_cache_size"; +set GLOBAL query_cache_size=1100; +set GLOBAL query_cache_size=1200; +set GLOBAL query_cache_size=1300; +set GLOBAL query_cache_size=1400; +set GLOBAL query_cache_size=1500; +set GLOBAL query_cache_size=1600; +set GLOBAL query_cache_size=1700; +set GLOBAL query_cache_size=1800; +set GLOBAL query_cache_size=1900; +show global variables like "query_cache_size"; + +# +# Temporary tables +# +set GLOBAL query_cache_size=1048576; +create table t1 (i int not null); +create table t2 (i int not null); +select * from t1; +show status like "Qcache_queries_in_cache"; +create temporary table t3 (i int not null); +select * from t2; +show status like "Qcache_queries_in_cache"; +select * from t3; +show status like "Qcache_queries_in_cache"; +drop table t1, t2, t3; diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test new file mode 100644 index 00000000000..6d79014b23d --- /dev/null +++ b/mysql-test/t/repair.test @@ -0,0 +1,8 @@ +# +# Test of repair table +# + +drop table if exists t1; +create table t1 SELECT 1,"table 1"; +repair table t1 use_frm; +drop table if exists t1; diff --git a/mysql-test/t/rollback.test b/mysql-test/t/rollback.test index 6ea2cca887f..bd37e69ae23 100644 --- a/mysql-test/t/rollback.test +++ b/mysql-test/t/rollback.test @@ -8,7 +8,12 @@ create table t1 (n int not null primary key) type=myisam; begin work; insert into t1 values (4); insert into t1 values (5); -# Should give an error -!$1196 rollback; +rollback; +select @@warning_count; +select @@error_count; +show warnings; +show errors; select * from t1; +select @@warning_count; +show warnings; drop table t1; diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index a5496d505f0..0f195c65fb0 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -1,21 +1,36 @@ -eval_result; source include/master-slave.inc; -connection master; -use test; -drop table if exists t1,t3; +drop table if exists t1,t2,t3; create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR eval load data local infile '$MYSQL_TEST_DIR/std_data/words.dat' into table t1; select * from t1; + +# +# Test slave with wrong password +# +save_master_pos; +connection slave; +sync_with_master; +slave stop; +connection master; set password for root@"localhost" = password('foo'); +connection slave; +slave start; +connection master; +# +# Give slave time to do at last one failed connect retry +# This one must be short so that the slave will not stop retrying +real_sleep 2; set password for root@"localhost" = password(''); +# Give slave time to connect (will retry every second) +sleep 2; + create table t3(n int); insert into t3 values(1),(2); save_master_pos; connection slave; sync_with_master; -use test; select * from t3; select sum(length(word)) from t1; connection master; @@ -32,7 +47,6 @@ slave stop; reset slave; connection master; -drop table if exists t1,t2; create table t1(n int); #we want the log to exceed 16K to test deal with the log that is bigger than #IO_SIZE @@ -45,12 +59,12 @@ while ($1) } enable_query_log; -#try to cause a large relay log lag on the slave +# Try to cause a large relay log lag on the slave connection slave; select get_lock("hold_slave",10); slave start; #hope this is long enough for I/O thread to fetch over 16K relay log data -sleep 1; +sleep 3; select release_lock("hold_slave"); unlock tables; @@ -59,24 +73,24 @@ create table t2(id int); insert into t2 values(connection_id()); save_master_pos; - connection master1; -#avoid generating result -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; +# Avoid generating result +create temporary table t3(n int); +insert into t3 select get_lock('crash_lock%20C', 1) from t2; connection master; send update t1 set n = n + get_lock('crash_lock%20C', 2); connection master1; -sleep 2; +sleep 3; select (@id := id) - id from t2; kill @id; +# We don't drop t3 as this is a temporary table drop table t2; connection master; --error 1053; reap; connection slave; -sync_with_master ; +sync_with_master; #give the slave a chance to exit wait_for_slave_to_stop; @@ -85,20 +99,18 @@ wait_for_slave_to_stop; # --replace_result 9306 9999 3334 9999 3335 9999 # show slave status; -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; select count(*) from t1; connection master1; drop table t1; create table t1 (n int); insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) +insert into mysql.user (Host, User, Password) VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); select select_priv,user from mysql.user where user = 'blafasel2'; -update user set Select_priv = "Y" where User="blafasel2"; +update mysql.user set Select_priv = "Y" where User="blafasel2"; select select_priv,user from mysql.user where user = 'blafasel2'; -use test; save_master_pos; connection slave; sync_with_master; @@ -109,5 +121,3 @@ drop table t1; save_master_pos; connection slave; sync_with_master; - - diff --git a/mysql-test/t/rpl000002.test b/mysql-test/t/rpl000002.test index 420ae27a337..caf0b4ef6d8 100644 --- a/mysql-test/t/rpl000002.test +++ b/mysql-test/t/rpl000002.test @@ -1,17 +1,14 @@ source include/master-slave.inc; -eval_result; -connection master; -use test; drop table if exists t1; create table t1 (n int auto_increment primary key); set insert_id = 2000; insert into t1 values (NULL),(NULL),(NULL); save_master_pos; connection slave; -use test; sync_with_master; select * from t1; connection master; +--replace_result $SLAVE_MYPORT 9999 show slave hosts; drop table t1; save_master_pos; diff --git a/mysql-test/t/rpl000003.test b/mysql-test/t/rpl000003.test index c4b120d31b0..f994ed94371 100644 --- a/mysql-test/t/rpl000003.test +++ b/mysql-test/t/rpl000003.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; drop table if exists t1; create table t1(n int primary key); !insert into t1 values (1),(2),(2); diff --git a/mysql-test/t/rpl000004.test b/mysql-test/t/rpl000004.test index 5b2c41293a2..705e0d51b7b 100644 --- a/mysql-test/t/rpl000004.test +++ b/mysql-test/t/rpl000004.test @@ -1,6 +1,4 @@ source include/master-slave.inc; -connection master; -use test; set SQL_LOG_BIN=0; drop table if exists t1; create table t1 (word char(20) not null, index(word)); @@ -10,7 +8,6 @@ create table t2 (word char(20) not null); load data infile '../../std_data/words.dat' into table t2; create table t3 (word char(20) not null primary key); connection slave; -use test; drop table if exists t1; load table t1 from master; drop table if exists t2; diff --git a/mysql-test/t/rpl000005.test b/mysql-test/t/rpl000005.test index 92d954a6182..1cfea242b69 100644 --- a/mysql-test/t/rpl000005.test +++ b/mysql-test/t/rpl000005.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; drop table if exists t1; CREATE TABLE t1 (name varchar(64), age smallint(3)); INSERT INTO t1 SET name='Andy', age=31; diff --git a/mysql-test/t/rpl000006.test b/mysql-test/t/rpl000006.test index 495f34bd5e7..ad8622c2a81 100644 --- a/mysql-test/t/rpl000006.test +++ b/mysql-test/t/rpl000006.test @@ -2,7 +2,6 @@ # Test forced timestamp # source include/master-slave.inc; -connection master; # Don't log table creating to the slave as we want to test LOAD TABLE set SQL_LOG_BIN=0,timestamp=200006; diff --git a/mysql-test/t/rpl000007.test b/mysql-test/t/rpl000007.test index 4425a5725e8..8ff1e1782cc 100644 --- a/mysql-test/t/rpl000007.test +++ b/mysql-test/t/rpl000007.test @@ -2,12 +2,10 @@ #the ones on bar source include/master-slave.inc; connection slave; -use test; drop table if exists foo; create table foo (n int); insert into foo values(4); connection master; -use test; drop table if exists foo; create table foo (s char(20)); load data infile '../../std_data/words.dat' into table foo; diff --git a/mysql-test/t/rpl000009.test b/mysql-test/t/rpl000009.test index 208e6f0b037..3d0827718ec 100644 --- a/mysql-test/t/rpl000009.test +++ b/mysql-test/t/rpl000009.test @@ -1,7 +1,7 @@ -#this one assumes we are ignoring updates on tables in database foo, but doing -#the ones in database bar +# This one assumes we are ignoring updates on tables in database foo, but doing +# the ones in database bar + source include/master-slave.inc; -connection master; drop database if exists foo; create database foo; drop database if exists bar; @@ -32,9 +32,10 @@ sync_with_master; drop database if exists bar; drop database if exists foo; -#now let's test load data from master +# Now let's test load data from master + +# First create some databases and tables on the master -#first create some databases and tables on the master connection master; set sql_log_bin = 0; create database foo; @@ -55,11 +56,11 @@ save_master_pos; connection slave; sync_with_master; -#this should show that the slave is empty at this point +# 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 +# Now let's check if we have the right tables and the right data in them show databases; use foo; show tables; @@ -68,7 +69,7 @@ show tables; select * from bar.t1; select * from bar.t2; -#now let's see if replication works +# Now let's see if replication works connection master; insert into bar.t1 values (4, 'four bar'); save_master_pos; @@ -76,7 +77,7 @@ connection slave; sync_with_master; select * from bar.t1; -#now time for cleanup +# Now time for cleanup connection master; drop database bar; drop database foo; diff --git a/mysql-test/t/rpl000011.test b/mysql-test/t/rpl000011.test index 7d99222b3c7..d75937e3f81 100644 --- a/mysql-test/t/rpl000011.test +++ b/mysql-test/t/rpl000011.test @@ -1,6 +1,4 @@ source include/master-slave.inc; -connection master; -use test; drop table if exists t1; create table t1 (n int); insert into t1 values(1); diff --git a/mysql-test/t/rpl000013.test b/mysql-test/t/rpl000013.test index a190d5324ae..14619796e01 100644 --- a/mysql-test/t/rpl000013.test +++ b/mysql-test/t/rpl000013.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test index 011b6507e49..f346ca39ca0 100644 --- a/mysql-test/t/rpl000017.test +++ b/mysql-test/t/rpl000017.test @@ -2,8 +2,8 @@ connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); connection master; reset master; -grant file on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; -grant file on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; connection slave; slave start; connection master; diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test index 30d601bb375..291b482b912 100644 --- a/mysql-test/t/rpl000018.test +++ b/mysql-test/t/rpl000018.test @@ -1,3 +1,8 @@ +# +# Running test with abort-slave-event-count=1 +# This will force slave to reconnect after every event +# + require_manager; connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); diff --git a/mysql-test/t/rpl_alter.test b/mysql-test/t/rpl_alter.test new file mode 100644 index 00000000000..710dd2d09d6 --- /dev/null +++ b/mysql-test/t/rpl_alter.test @@ -0,0 +1,19 @@ +source include/master-slave.inc; +drop database if exists d1; +create database d1; +create table d1.t1 ( n int); +alter table d1.t1 add m int; +insert into d1.t1 values (1,2); +create table d1.t2 (n int); +insert into d1.t2 values (45); +rename table d1.t2 to d1.t3, d1.t1 to d1.t2; +save_master_pos; +connection slave; +sync_with_master; +select * from d1.t2; +select * from d1.t3; +connection master; +drop database d1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_compat.test b/mysql-test/t/rpl_compat.test deleted file mode 100644 index c9455c1e2d2..00000000000 --- a/mysql-test/t/rpl_compat.test +++ /dev/null @@ -1,86 +0,0 @@ -eval_result; -source include/master-slave.inc; -connection master; -require_version 3.23; -use test; -drop table if exists t1,t3; -create table t1 (word char(20) not null); -load data infile '../../std_data/words.dat' into table t1; -select * from t1; -set password for root@"localhost" = password('foo'); -set password for root@"localhost" = password(''); -create table t3(n int); -insert into t3 values(1),(2); -save_master_pos; -connection slave; -sync_with_master; -use test; -select * from t3; -select sum(length(word)) from t1; -connection master; -drop table t1,t3; -save_master_pos; -connection slave; -sync_with_master; - -#test handling of aborted connection in the middle of update -connection master; -reset master; -connection slave; -slave stop; -reset slave; - -connection master; -create table t1(n int); -insert into t1 values (1),(2),(3); -create table t2(id int); -insert into t2 values(connection_id()); -save_master_pos; - -connection master1; -#avoid generating result -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; - -connection master; -send update t1 set n = n + get_lock('crash_lock%20C', 2); -connection master1; -sleep 2; -select (@id := id) - id from t2; -kill @id; -drop table t2; -connection master; ---error 1053; -reap; -connection slave; -slave start; -sync_with_master ; -#now slave will hit an error -wait_for_slave_to_stop; - -set sql_slave_skip_counter=1; -slave start; -select count(*) from t1; -connection master1; -drop table t1; -create table t1 (n int); -insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) - VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); -select select_priv,user from mysql.user where user = 'blafasel2'; -update user set Select_priv = "Y" where User="blafasel2"; -select select_priv,user from mysql.user where user = 'blafasel2'; -use test; -save_master_pos; -connection slave; -sync_with_master; -select n from t1; -select select_priv,user from mysql.user where user = 'blafasel2'; -connection master1; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - - diff --git a/mysql-test/t/rpl_empty_master_crash.test b/mysql-test/t/rpl_empty_master_crash.test index 158b59169ee..afa76ce10bb 100644 --- a/mysql-test/t/rpl_empty_master_crash.test +++ b/mysql-test/t/rpl_empty_master_crash.test @@ -1,7 +1,11 @@ source include/master-slave.inc; -connection master; -use test; drop table if exists t1; show slave status; +# +# Load table should not succeed on the master as this is not a slave +# --error 1218 load table t1 from master; +connection slave; +--error 1188 +load table t1 from master; diff --git a/mysql-test/t/rpl_flush_log_loop-master.opt b/mysql-test/t/rpl_flush_log_loop-master.opt new file mode 100644 index 00000000000..4f6e0f3d00c --- /dev/null +++ b/mysql-test/t/rpl_flush_log_loop-master.opt @@ -0,0 +1 @@ +-O max_binlog_size=1M --relay-log=$MYSQL_TEST_DIR/var/master-data/relay-log diff --git a/mysql-test/t/rpl_flush_log_loop-master.sh b/mysql-test/t/rpl_flush_log_loop-master.sh new file mode 100755 index 00000000000..9e56af99f5c --- /dev/null +++ b/mysql-test/t/rpl_flush_log_loop-master.sh @@ -0,0 +1,5 @@ +rm -f $MYSQL_TEST_DIR/var/slave-data/*-bin.* +rm -f $MYSQL_TEST_DIR/var/slave-data/master.info +rm -f $MYSQL_TEST_DIR/var/slave-data/*.index + + diff --git a/mysql-test/t/rpl_flush_log_loop-slave.opt b/mysql-test/t/rpl_flush_log_loop-slave.opt new file mode 100644 index 00000000000..d1373f139b1 --- /dev/null +++ b/mysql-test/t/rpl_flush_log_loop-slave.opt @@ -0,0 +1 @@ +-O max_binlog_size=1M --relay-log=$MYSQL_TEST_DIR/var/slave-data/relay-log diff --git a/mysql-test/t/rpl_flush_log_loop-slave.sh b/mysql-test/t/rpl_flush_log_loop-slave.sh new file mode 100755 index 00000000000..b8814e059a9 --- /dev/null +++ b/mysql-test/t/rpl_flush_log_loop-slave.sh @@ -0,0 +1,4 @@ +rm -f $MYSQL_TEST_DIR/var/master-data/master.info +rm -f $MYSQL_TEST_DIR/var/master-data/*-bin.* +rm -f $MYSQL_TEST_DIR/var/master-data/*.index + diff --git a/mysql-test/t/rpl_flush_log_loop.test b/mysql-test/t/rpl_flush_log_loop.test new file mode 100644 index 00000000000..c68fbb7111a --- /dev/null +++ b/mysql-test/t/rpl_flush_log_loop.test @@ -0,0 +1,20 @@ +# Testing if "flush logs" command bouncing resulting in logs created in a loop +# in case of bi-directional replication + +source include/master-slave.inc + +connection slave; +--replace_result $MASTER_MYPORT MASTER_PORT +eval change master to master_host='127.0.0.1',master_user='root', + master_password='',master_port=$MASTER_MYPORT; +slave start; +connection master; +slave stop; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval change master to master_host='127.0.0.1',master_user='root', + master_password='',master_port=$SLAVE_MYPORT; +slave start; +flush logs; +sleep 5; +--replace_result $SLAVE_MYPORT SLAVE_PORT +show slave status; diff --git a/mysql-test/t/rpl_get_lock.test b/mysql-test/t/rpl_get_lock.test index 3fe45eecf46..e93268e6074 100644 --- a/mysql-test/t/rpl_get_lock.test +++ b/mysql-test/t/rpl_get_lock.test @@ -1,5 +1,5 @@ source include/master-slave.inc; -connection master; + create table t1(n int); insert into t1 values(get_lock("lock",2)); dirty_close master; @@ -21,6 +21,10 @@ connection slave; sync_with_master; select get_lock("lock",3); select * from t1; +select is_free_lock("lock"); +# Check lock functions +select is_free_lock("lock2"); +select is_free_lock(NULL); connection master1; drop table t1; save_master_pos; diff --git a/mysql-test/t/rpl_log.test b/mysql-test/t/rpl_log.test index 3514d435737..85782e78142 100644 --- a/mysql-test/t/rpl_log.test +++ b/mysql-test/t/rpl_log.test @@ -9,7 +9,6 @@ let $VERSION=`select version()`; 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; @@ -22,6 +21,19 @@ show binlog events from 79 limit 1; show binlog events from 79 limit 2; show binlog events from 79 limit 2,1; flush logs; + +# Sync slave and force it to start on another binary log + +save_master_pos; +connection slave; +slave start; +sync_with_master; +flush logs; +slave stop; +connection master; + +# Create some entries for second log + create table t1 (n int); insert into t1 values (1); drop table t1; @@ -35,23 +47,19 @@ slave start; sync_with_master; show master logs; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT $VERSION VERSION -# We can't compare binlog from slave as the result differ between -# machines based on where the LOAD DATA file is stored. -disable_result_log; - show binlog events in 'slave-bin.001' from 4; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT $VERSION VERSION -enable_result_log; show binlog events in 'slave-bin.002' from 4; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; -show new master for slave with master_log_file='master-bin.001' and - master_log_pos=4 and master_server_id=1; -show new master for slave with master_log_file='master-bin.001' and - master_log_pos=79 and master_server_id=1; -show new master for slave with master_log_file='master-bin.001' and - master_log_pos=311 and master_server_id=1; -show new master for slave with master_log_file='master-bin.002' and - master_log_pos=4 and master_server_id=1; -show new master for slave with master_log_file='master-bin.002' and - master_log_pos=122 and master_server_id=1; + +# Need to recode the following + +#show new master for slave with master_log_file='master-bin.001' and master_log_pos=4 and master_server_id=1; +#show new master for slave with master_log_file='master-bin.001' and master_log_pos=79 and master_server_id=1; +#show new master for slave with master_log_file='master-bin.001' and master_log_pos=311 and master_server_id=1; +#show new master for slave with master_log_file='master-bin.002' and master_log_pos=4 and master_server_id=1; +#show new master for slave with master_log_file='master-bin.002' and master_log_pos=122 and master_server_id=1; + +--error 1220 +show binlog events in 'slave-bin.005' from 4; diff --git a/mysql-test/t/rpl000014.test b/mysql-test/t/rpl_log_pos.test index df15fa6e600..b96d32c7fca 100644 --- a/mysql-test/t/rpl000014.test +++ b/mysql-test/t/rpl_log_pos.test @@ -1,5 +1,7 @@ +# +# Testing of setting slave to wrong log position with master_log_pos +# source include/master-slave.inc; -connection master; show master status; save_master_pos; connection slave; @@ -7,30 +9,36 @@ sync_with_master; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=73; +sleep 5; slave stop; change master to master_log_pos=73; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; slave start; +sleep 5; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=173; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT +slave start; +sleep 2; +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; connection master; show master status; -create table if not exists foo(n int); -drop table if exists foo; -create table foo (n int); -insert into foo values (1),(2),(3); +create table if not exists t1 (n int); +drop table if exists t1; +create table t1 (n int); +insert into t1 values (1),(2),(3); save_master_pos; connection slave; change master to master_log_pos=79; +slave start; sync_with_master; -select * from foo; +select * from t1; connection master; -drop table foo; +drop table t1; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl_mystery22.test b/mysql-test/t/rpl_mystery22.test index e987074697b..5280cb360dd 100644 --- a/mysql-test/t/rpl_mystery22.test +++ b/mysql-test/t/rpl_mystery22.test @@ -1,8 +1,7 @@ # test case to make slave thread get ahead by 22 bytes -drop table if exists t1; source include/master-slave.inc; -connection master; + # first, cause a duplicate key problem on the slave create table t1(n int auto_increment primary key); save_master_pos; @@ -14,7 +13,7 @@ insert into t1 values(NULL); insert into t1 values(NULL); save_master_pos; connection slave; -sleep 1; # there is no way around this sleep - we have to wait until +sleep 3; # there is no way around this sleep - we have to wait until # the slave tries to run the query, fails and aborts slave thread delete from t1 where n = 2; slave start; diff --git a/mysql-test/t/rpl_magic.test b/mysql-test/t/rpl_redirect.test index 376edc301d7..4082542f295 100644 --- a/mysql-test/t/rpl_magic.test +++ b/mysql-test/t/rpl_redirect.test @@ -1,13 +1,20 @@ +# +# Test of automatic redirection of queries to master/slave. +# + 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; +--replace_result $MASTER_MYPORT MASTER_PORT +SHOW SLAVE STATUS; +--replace_result $SLAVE_MYPORT SLAVE_PORT +SHOW SLAVE HOSTS; rpl_probe; #turn on master/slave query direction auto-magic @@ -17,11 +24,12 @@ 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; +enable_rpl_parse; +# The first of the queries will be sent to the slave, the second to the master. select * from t1; select * from t1; disable_rpl_parse; @@ -29,3 +37,5 @@ select * from t1; connection slave; select * from t1; drop table t1; +connection master; +drop table t1; diff --git a/mysql-test/t/rpl000016-master.opt b/mysql-test/t/rpl_rotate_logs-master.opt index f27601e0d7d..f27601e0d7d 100644 --- a/mysql-test/t/rpl000016-master.opt +++ b/mysql-test/t/rpl_rotate_logs-master.opt diff --git a/mysql-test/t/rpl000016-slave-master-info.opt b/mysql-test/t/rpl_rotate_logs-slave-master-info.opt index 80190bf6d29..80190bf6d29 100644 --- a/mysql-test/t/rpl000016-slave-master-info.opt +++ b/mysql-test/t/rpl_rotate_logs-slave-master-info.opt diff --git a/mysql-test/t/rpl000016-slave.sh b/mysql-test/t/rpl_rotate_logs-slave.sh index 9259f593e54..9259f593e54 100755 --- a/mysql-test/t/rpl000016-slave.sh +++ b/mysql-test/t/rpl_rotate_logs-slave.sh diff --git a/mysql-test/t/rpl000016.test b/mysql-test/t/rpl_rotate_logs.test index 7559b2d3515..5d8f150cdea 100644 --- a/mysql-test/t/rpl000016.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -1,24 +1,43 @@ +# +# Test are run with max_binlog_size=2048 to force automatic rotation of the +# binary log +# Tests done: +# - Check that slaves reports correct failures if master.info has strange +# modes/information +# - Automatic binary log rotation +# - Ensure that temporary tables works over flush logs and binary log +# changes +# - Test creating a duplicate key error and recover from it +# connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); system cat /dev/null > var/slave-data/master.info; system chmod 000 var/slave-data/master.info; connection slave; -!slave start; +--error 1201 +slave start; system chmod 600 var/slave-data/master.info; -!slave start; +--error 1201 +slave start; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT -!eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, - master_user='root'; +# Will get error 13 on Unix systems becasue file is not readable +!eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; reset slave; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT -eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, - master_user='root'; +eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; connection master; reset master; connection slave; slave start; connection master; -drop table if exists t1; + +drop table if exists t1, t2, t3, t4; + +# +# Test FLUSH LOGS +# +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables"); create table t1 (s text); insert into t1 values('Could not break slave'),('Tried hard'); save_master_pos; @@ -32,15 +51,24 @@ flush logs; drop table if exists t2; create table t2(m int not null auto_increment primary key); insert into t2 values (34),(67),(123); -save_master_pos; flush logs; show master logs; +create table t3 select * from temp_table; -#now lets make some duplicate key mess and see if we can recover from it - -#first insert a value on the slave +save_master_pos; connection slave; sync_with_master; + +select * from t3; +connection master; +drop table temp_table, t3; + +# +# Now lets make some duplicate key mess and see if we can recover from it +# + +# First insert a value on the slave +connection slave; insert into t2 values(1234); #same value on the master @@ -54,7 +82,7 @@ sync_with_master; wait_for_slave_to_stop; #restart slave skipping one event -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; connection master; @@ -73,7 +101,14 @@ sync_with_master; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; select * from t2; + +# +# Test forcing the replication log to rotate +# + connection master; +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables part 2"); let $1=100; drop table if exists t3; create table t3 (n int); @@ -85,13 +120,16 @@ while ($1) dec $1; } enable_query_log; +create table t4 select * from temp_table; show master logs; show master status; save_master_pos; connection slave; -slave stop; -slave start; +#slave stop; +#slave start; sync_with_master; +select * from t4; + --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; # because of concurrent insert, the table may not be up to date @@ -101,7 +139,7 @@ select count(*) from t3 where n >= 4; unlock tables; #clean up connection master; -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl_skip_error.test b/mysql-test/t/rpl_skip_error.test index 3303b45c1ca..d43c59ef024 100644 --- a/mysql-test/t/rpl_skip_error.test +++ b/mysql-test/t/rpl_skip_error.test @@ -1,6 +1,6 @@ source include/master-slave.inc; -connection master; drop table if exists t1; + create table t1 (n int not null primary key); save_master_pos; connection slave; diff --git a/mysql-test/t/rpl_sporadic_master.test b/mysql-test/t/rpl_sporadic_master.test index 0487f868436..e59b93b4475 100644 --- a/mysql-test/t/rpl_sporadic_master.test +++ b/mysql-test/t/rpl_sporadic_master.test @@ -2,8 +2,7 @@ # COM_BINLOG_DUMP and additionally limits the number of events per dump source include/master-slave.inc; -connection master; -drop table if exists t1,t2; + create table t2(n int); create table t1(n int not null auto_increment primary key); insert into t1 values (NULL),(NULL); diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index ab4251fe536..94806d44e37 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1405,9 +1405,9 @@ drop table tmp; # big table done -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; select distinct fld5 from t2 limit 10; @@ -1416,9 +1416,9 @@ select distinct fld5 from t2 limit 10; # select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET OPTION SQL_BIG_TABLES=1; # Force use of MyISAM +SET SQL_BIG_TABLES=1; # Force use of MyISAM select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; # @@ -1440,6 +1440,14 @@ select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr orde explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; # +# Some test with ORDER BY and limit +# + +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; +explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; + +# # Search with a constant table. # @@ -1712,6 +1720,8 @@ show full columns from t2 from test like 'f%'; show full columns from t2 from test like 's%'; show keys from t2; +drop table t4, t3, t2, t1; + # # Test of DO @@ -1719,7 +1729,25 @@ DO 1; DO benchmark(100,1+1),1,1; # -# Drop the test tables +# Test of bug with SUM(CASE...) +# + +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); +SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; +DROP TABLE t1,t2; + +# +# Test of bigint comparision # -drop table t4, t3,t2, t1; +create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0'); +INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); +select wss_type from t1 where wss_type ='102935229216544106'; +select wss_type from t1 where wss_type ='102935229216544105'; +select wss_type from t1 where wss_type ='102935229216544104'; +select wss_type from t1 where wss_type ='102935229216544093'; +select wss_type from t1 where wss_type =102935229216544093; +drop table t1; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index c95d46b166c..0a483c860cb 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -68,3 +68,20 @@ SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id W SELECT FOUND_ROWS(); drop table t1,t2; + +# +# Test bug when using range optimization +# + +CREATE TABLE `t1` ( + `titre` char(80) NOT NULL default '', + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `maxnumrep` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`), + KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; +INSERT INTO t1 (titre,maxnumrep) VALUES +('test1','1'),('test2','2'),('test3','3'); +SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; +SELECT FOUND_ROWS(); +drop table t1; diff --git a/mysql-test/t/truncate.test b/mysql-test/t/truncate.test index 1729ddc586a..eeb79f497fa 100644 --- a/mysql-test/t/truncate.test +++ b/mysql-test/t/truncate.test @@ -1,6 +1,7 @@ # # Test of truncate # +drop table if exists t1; create table t1 (a integer, b integer,c1 CHAR(10)); insert into t1 (a) values (1),(2); truncate table t1; @@ -18,3 +19,5 @@ insert into t1 values (1),(2),(3); truncate table t1; select * from t1; drop table t1; +--error 1146 +truncate non_existing_table; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 21e06c0dbe9..95420539611 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -160,6 +160,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -169,6 +171,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -178,6 +182,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -189,6 +195,8 @@ insert into t1 values (-.1),(+.1),(.1); insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); +insert into t1 values (1e+100),(1e-100),(-1e+100); +insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; drop table t1; diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index 39bcbcbf75d..7f1a75e5dbd 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -2,6 +2,7 @@ # Test of SET with space # +drop table if exists t1; create table t1 (a set (' ','a','b') not null); show create table t1; drop table t1; diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index fde93576510..2496ec36915 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -1,6 +1,7 @@ # # testing of the TIME column type # +drop table if exists t1; create table t1 (t time); insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index a6169c03d3d..19af6b0c49c 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -2,6 +2,7 @@ # Test timestamp # +drop table if exists t1; CREATE TABLE t1 ( t timestamp); SET TIMESTAMP=1234; insert into t1 values(NULL); diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test index 70a3053f19b..8f4f23badb4 100644 --- a/mysql-test/t/type_year.test +++ b/mysql-test/t/type_year.test @@ -1,7 +1,7 @@ # # Test year # - +drop table if exists t1; create table t1 (y year,y2 year(2)); insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); select * from t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 8f8f666bf55..c547d25ba9b 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -23,7 +23,11 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; #(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; select found_rows(); + +# # Test some error conditions with UNION +# + explain select a,b from t1 union all select a,b from t2; --error 1054 @@ -53,6 +57,9 @@ select * from t1 union select a from t2; --error 1222 select a from t1 union select * from t2; +--error 1234 +select * from t1 union select SQL_BUFFER_RESULT * from t2; + # Test CREATE, INSERT and REPLACE create table t3 select a,b from t1 union all select a,b from t2; insert into t3 select a,b from t1 union all select a,b from t2; @@ -88,6 +95,6 @@ insert into t2 values (11),(12),(13),(14),(15); (select * from t1 limit 2) union (select * from t2 limit 3); (select * from t1 limit 2) union (select * from t2 limit 20,3); set SQL_SELECT_LIMIT=2; -(select * from t1 limit 2) union (select * from t2 limit 3); +(select * from t1 limit 1) union (select * from t2 limit 3); set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 70283446fb3..bb1a9b59611 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -1,4 +1,4 @@ -error 1204; +error 1054; set @a := foo; set @a := connection_id() + 3; select @a - connection_id(); diff --git a/mysql-test/t/varbinary.test b/mysql-test/t/varbinary.test index 6d2660271ea..cc9ee99b7f9 100644 --- a/mysql-test/t/varbinary.test +++ b/mysql-test/t/varbinary.test @@ -10,6 +10,7 @@ select x'31',X'ffff'+0; # Test of hex constants in WHERE: # +drop table if exists t1; create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) ); insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; diff --git a/mysql-test/t/variables-master.opt b/mysql-test/t/variables-master.opt new file mode 100644 index 00000000000..5851d32ec31 --- /dev/null +++ b/mysql-test/t/variables-master.opt @@ -0,0 +1 @@ +max_join_size=10 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index b3127402238..7a1d01c2cb5 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -32,9 +32,207 @@ drop table t1; # Test system variables # +set max_join_size=100; +show variables like 'max_join_size'; +show global variables like 'max_join_size'; +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +set max_join_size=DEFAULT; +show variables like 'max_join_size'; +set GLOBAL max_join_size=DEFAULT; +show global variables like 'max_join_size'; +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +select @@identity, length(@@version)>0; select @@VERSION=version(); select last_insert_id(345); -select @@IDENTITY,last_insert_id(); -select @@identity; +select @@IDENTITY,last_insert_id(), @@identity; + +set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; + +set global concurrent_insert=ON; +show variables like 'concurrent_insert'; +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +set global concurrent_insert=OFF; +show variables like 'concurrent_insert'; +set global concurrent_insert=DEFAULT; +show variables like 'concurrent_insert'; + +set table_type=MYISAM, table_type="HEAP", global table_type="INNODB"; +show local variables like 'table_type'; +show global variables like 'table_type'; +set GLOBAL query_cache_size=100000; +set GLOBAL safe_show_database=0; + +set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; +show variables like 'myisam_max_sort_file_size'; +show global variables like 'myisam_max_sort_file_size'; +set myisam_max_sort_file_size=default; +show variables like 'myisam_max_sort_file_size'; + +set global net_retry_count=10, session net_retry_count=10; +set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; +set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; +show global variables like 'net_%'; +show session variables like 'net_%'; +set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +show session variables like 'net_%'; +set net_buffer_length=1; +show variables like 'net_buffer_length'; +set net_buffer_length=2000000000; +show variables like 'net_buffer_length'; + +set GLOBAL character set cp1251_koi8; +show global variables like "convert_character_set"; +set character set cp1251_koi8; +show variables like "convert_character_set"; +set global character set default, session character set default; +show variables like "convert_character_set"; +select @@timestamp>0; + +# The following should give errors + +--error 1231 +set big_tables=OFFF; +--error 1231 +set big_tables="OFFF"; --error 1193 -select @@unknown_variable; +set unknown_variable=1; +--error 1232 +set max_join_size="hello"; +--error 1231 +set table_type=UNKNOWN_TABLE_TYPE; +--error 1231 +set table_type=INNODB, big_tables=2; +show local variables like 'table_type'; +--error 1229 +set SESSION query_cache_size=10000; +--error 1230 +set GLOBAL table_type=DEFAULT; +--error 1115 +set convert_character_set=UNKNOWN_CHARACTER_SET; +--error 1115 +set character set unknown; +--error 1232 +set character set 0; +--error 1228 +set global autocommit=1; +--error 1228 +select @@global.timestamp; +--error 1193 +set @@version=''; +--error 1229 +set @@concurrent_insert=1; +--error 1228 +set @@global.sql_auto_is_null=1; +--error 1228 +select @@global.sql_auto_is_null; + +# Test setting all variables + +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +set global binlog_cache_size=100; +set bulk_insert_buffer_size=100; +set convert_character_set=cp1251_koi8; +set convert_character_set=default; +set @@global.concurrent_insert=1; +set global connect_timeout=100; +select @@delay_key_write; +set global delay_key_write="OFF"; +select @@delay_key_write; +set global delay_key_write=ALL; +select @@delay_key_write; +set global delay_key_write=1; +select @@delay_key_write; +set global delayed_insert_limit=100; +set global delayed_insert_timeout=100; +set global delayed_queue_size=100; +set global flush=1; +set global flush_time=100; +set insert_id=1; +set interactive_timeout=100; +set join_buffer_size=100; +set last_insert_id=1; +set global local_infile=1; +set long_query_time=100; +set low_priority_updates=1; +set max_allowed_packet=100; +set global max_binlog_cache_size=100; +set global max_binlog_size=100; +set global max_connect_errors=100; +set global max_connections=100; +set global max_delayed_threads=100; +set max_heap_table_size=100; +set max_join_size=100; +set max_sort_length=100; +set max_tmp_tables=100; +set global max_user_connections=100; +select @@max_user_connections; +set global max_write_lock_count=100; +set myisam_max_extra_sort_file_size=100; +select @@myisam_max_extra_sort_file_size; +set myisam_max_sort_file_size=100; +set myisam_sort_buffer_size=100; +set net_buffer_length=100; +set net_read_timeout=100; +set net_write_timeout=100; +set global query_cache_limit=100; +set global query_cache_size=100; +set global query_cache_type=demand; +set read_buffer_size=100; +set read_rnd_buffer_size=100; +set global rpl_recovery_rank=100; +set global safe_show_database=1; +set global server_id=100; +set global slave_net_timeout=100; +set global slow_launch_time=100; +set sort_buffer_size=100; +set sql_auto_is_null=1; +select @@sql_auto_is_null; +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +set sql_big_selects=1; +set sql_big_tables=1; +set sql_buffer_result=1; +set sql_log_bin=1; +set sql_log_off=1; +set sql_log_update=1; +set sql_low_priority_updates=1; +set sql_max_join_size=200; +select @@sql_max_join_size,@@max_join_size; +set sql_quote_show_create=1; +set sql_safe_updates=1; +set sql_select_limit=1; +set global sql_slave_skip_counter=100; +set sql_warnings=1; +set global table_cache=100; +set table_type=myisam; +set global thread_cache_size=100; +set timestamp=1, timestamp=default; +set tmp_table_size=100; +set tx_isolation="READ-COMMITTED"; +set wait_timeout=100; +set log_warnings=1; + +# +# key buffer +# + +DROP TABLE IF EXISTS t1,t2; +create table t1 (a int not null auto_increment, primary key(a)); +create table t2 (a int not null auto_increment, primary key(a)); +insert into t1 values(null),(null),(null); +insert into t2 values(null),(null),(null); +set global key_buffer_size=100000; +select @@key_buffer_size; +select * from t1 where a=2; +select * from t2 where a=3; +check table t1,t2; +drop table t1,t2; + diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index e7cb1f46ec3..5fb536def71 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -1,7 +1,7 @@ # # Test some warnings # - +drop table if exists t1; create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); |