diff options
Diffstat (limited to 'mysql-test')
32 files changed, 720 insertions, 23 deletions
diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc new file mode 100644 index 00000000000..5a7aa26c65a --- /dev/null +++ b/mysql-test/include/system_db_struct.inc @@ -0,0 +1,12 @@ +# +# This test must examine structure of current system database +# + +-- replace_result Tables_in_mysql Tables_in_db Tables_in_test Tables_in_db +show tables; +show create table db; +show create table host; +show create table user; +show create table func; +show create table tables_priv; +show create table columns_priv; diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index 11ed1bb312c..ac5435f9832 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -5,20 +5,27 @@ # This scripts creates the privilege tables db, host, user, tables_priv, # columns_priv in the mysql database, as well as the func table. -if [ x$1 = x"-bin" ]; then - shift 1 - execdir=../bin - bindir=../bin - BINARY_DIST=1 - fix_bin=mysql-test - scriptdir=../bin - libexecdir=../libexec +if [ x$1 = x"--bin" ]; then + shift 1 + + # Check if it's a binary distribution or a 'make install' + if test -x ../libexec/mysqld + then + execdir=../libexec + else + execdir=../bin + fi + bindir=../bin + BINARY_DIST=1 + fix_bin=mysql-test + scriptdir=../bin + libexecdir=../libexec else - execdir=../sql - bindir=../client - fix_bin=. - scriptdir=../scripts - libexecdir=../libexec + execdir=../sql + bindir=../client + fix_bin=. + scriptdir=../scripts + libexecdir=../libexec fi vardir=var diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index a1e5322f35d..3ff427f6515 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -18,7 +18,14 @@ TZ=$MY_TZ; export TZ # for UNIX_TIMESTAMP tests to work LOCAL_SOCKET=@MYSQL_UNIX_ADDR@ # For query_cache test -ulimit -n 1024 +case "$SYSTEM" in + SCO_SV | UnixWare | OpenUNIX ) + # do nothing (Causes strange behavior) + ;; + * ) + ulimit -n 1024 + ;; +esac #++ # Program Definitions @@ -312,6 +319,8 @@ while test $# -gt 0; do $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with --gdb option" fi DO_GDB=1 + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --gdb" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --gdb" # This needs to be checked properly # USE_MANAGER=1 USE_RUNNING_SERVER="" @@ -413,8 +422,7 @@ SLAVE_MYERR="$MYSQL_TEST_DIR/var/log/slave.err" CURRENT_TEST="$MYSQL_TEST_DIR/var/log/current_test" SMALL_SERVER="--key_buffer_size=1M --sort_buffer=256K --max_heap_table_size=1M" -export MASTER_MYPORT -export SLAVE_MYPORT +export MASTER_MYPORT SLAVE_MYPORT if [ x$SOURCE_DIST = x1 ] ; then MY_BASEDIR=$MYSQL_TEST_DIR @@ -470,6 +478,7 @@ if [ x$SOURCE_DIST = x1 ] ; then LANGUAGE="$BASEDIR/sql/share/english/" CHARSETSDIR="$BASEDIR/sql/share/charsets" INSTALL_DB="./install_test_db" + MYSQL_FIX_SYSTEM_TABLES="$BASEDIR/scripts/mysql_fix_privilege_tables" else if test -x "$BASEDIR/libexec/mysqld" then @@ -486,7 +495,8 @@ else MYSQL_MANAGER_CLIENT="$BASEDIR/bin/mysqlmanagerc" MYSQL_MANAGER_PWGEN="$BASEDIR/bin/mysqlmanager-pwgen" MYSQL="$BASEDIR/bin/mysql" - INSTALL_DB="./install_test_db -bin" + INSTALL_DB="./install_test_db --bin" + MYSQL_FIX_SYSTEM_TABLES="$BASEDIR/bin/mysql_fix_privilege_tables" if test -d "$BASEDIR/share/mysql/english" then LANGUAGE="$BASEDIR/share/mysql/english/" @@ -499,8 +509,9 @@ fi MYSQL_DUMP="$MYSQL_DUMP --no-defaults -uroot --socket=$MASTER_MYSOCK $EXTRA_MYSQLDUMP_OPT" MYSQL_BINLOG="$MYSQL_BINLOG --no-defaults --local-load=$MYSQL_TMP_DIR $EXTRA_MYSQLBINLOG_OPT" -export MYSQL_DUMP -export MYSQL_BINLOG +MYSQL_FIX_SYSTEM_TABLES="$MYSQL_FIX_SYSTEM_TABLES --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK --user=root --password=" +MYSQL="$MYSQL --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK --user=root --password=" +export MYSQL MYSQL_DUMP MYSQL_BINLOG MYSQL_FIX_SYSTEM_TABLES if [ -z "$MASTER_MYSQLD" ] then @@ -1177,6 +1188,7 @@ run_testcase () master_init_script=$TESTDIR/$tname-master.sh slave_init_script=$TESTDIR/$tname-slave.sh slave_master_info_file=$TESTDIR/$tname.slave-mi + result_file=$tname echo $tname > $CURRENT_TEST SKIP_SLAVE=`$EXPR \( $tname : rpl \) = 0` if [ "$USE_MANAGER" = 1 ] ; then @@ -1226,6 +1238,11 @@ run_testcase () # Note that this must be set to space, not "" for test-reset to work EXTRA_MASTER_OPT=" " ;; + --result-file=*) + result_file=`$ECHO "$EXTRA_MASTER_OPT" | $SED -e "s;--result-file=;;"` + # Note that this must be set to space, not "" for test-reset to work + EXTRA_MASTER_OPT=" " + ;; esac stop_master echo "CURRENT_TEST: $tname" >> $MASTER_MYERR @@ -1283,7 +1300,7 @@ run_testcase () if [ -f $tf ] ; then $RM -f r/$tname.*reject - mysql_test_args="-R r/$tname.result $EXTRA_MYSQL_TEST_OPT" + mysql_test_args="-R r/$result_file.result $EXTRA_MYSQL_TEST_OPT" if [ -z "$DO_CLIENT_GDB" ] ; then `$MYSQL_TEST $mysql_test_args < $tf 2> $TIMEFILE`; else @@ -1318,7 +1335,7 @@ run_testcase () $ECHO "$RES$RES_SPACE [ fail ]" $ECHO error_is - show_failed_diff $tname + show_failed_diff $result_file $ECHO if [ x$FORCE != x1 ] ; then $ECHO "Aborting: $tname failed. To continue, re-run with '--force'." diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index c3043854063..73596b71623 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -262,6 +262,12 @@ a b 0 2 3 4 drop table t1; +create table `t1 `(a int); +Incorrect table name 't1 ' +create database `db1 `; +Incorrect database name 'db1 ' +create table t1(`a ` int); +Incorrect column name 'a ' create table t1 (a int, key(a)); create table t2 (b int, foreign key(b) references t1(a), key(b)); drop table if exists t1,t2; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 0c9fcb9a55e..02850e4d902 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -142,6 +142,10 @@ a b MySQL has now support for full-text search select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); a b +select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); +a b +select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); +a b select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); a b select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 9085849e582..93d4319ea86 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -119,6 +119,15 @@ ASIN(0.8+0.2) SELECT ASIN(1.2-0.2); ASIN(1.2-0.2) 1.570796 +select floor(log(4)/log(2)); +floor(log(4)/log(2)) +2 +select floor(log(8)/log(2)); +floor(log(8)/log(2)) +3 +select floor(log(16)/log(2)); +floor(log(16)/log(2)) +4 explain extended select degrees(pi()),radians(360); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used diff --git a/mysql-test/r/lowercase0.require b/mysql-test/r/lowercase0.require new file mode 100644 index 00000000000..a63906557f8 --- /dev/null +++ b/mysql-test/r/lowercase0.require @@ -0,0 +1,3 @@ +Variable_name Value +lower_case_file_system ON +lower_case_table_names 0 diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index e582978c126..737d49fc340 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -106,3 +106,18 @@ SELECT * from T1; a 1 DROP TABLE T1; +create table T1 (EVENT_ID int auto_increment primary key, LOCATION char(20)); +insert into T1 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6"); +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +LOCATION +Mic-5 +Mic-6 +drop table T1; diff --git a/mysql-test/r/lowercase_table3.result b/mysql-test/r/lowercase_table3.result new file mode 100644 index 00000000000..362d17e0461 --- /dev/null +++ b/mysql-test/r/lowercase_table3.result @@ -0,0 +1,10 @@ +DROP TABLE IF EXISTS t1,T1; +CREATE TABLE t1 (a int); +SELECT * from T1; +a +drop table t1; +flush tables; +CREATE TABLE t1 (a int) type=INNODB; +SELECT * from T1; +Can't open file: 'T1.InnoDB'. (errno: 1) +drop table t1; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index a61ce31b8ae..d5f5d369fb3 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -396,3 +396,43 @@ select 't1 rows after big delete', count(*) from t1; t1 rows after big delete count(*) t1 rows after big delete 1900001 drop table t1,t2; +CREATE TABLE t1 ( a int ); +CREATE TABLE t2 ( a int ); +DELETE t1 FROM t1, t2 AS t3; +DELETE t4 FROM t1, t1 AS t4; +Not unique table/alias: 't4' +DELETE t3 FROM t1 AS t3, t1 AS t4; +Not unique table/alias: 't3' +DELETE t1 FROM t1 AS t3, t2 AS t4; +INSERT INTO t1 values (1),(2); +INSERT INTO t2 values (1),(2); +DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1; +SELECT * from t1; +a +2 +SELECT * from t2; +a +1 +2 +DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2; +SELECT * from t1; +a +2 +SELECT * from t2; +a +1 +DROP TABLE t1,t2; +create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) ); +create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) ); +insert into t1 values (0,'A01-Comp',1); +insert into t1 values (0,'B01-Comp',1); +insert into t2 values (0,1,'A Note',1); +update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; +select * from t1; +p_id p_code p_active +1 A01-Comp 1 +2 B01-Comp 1 +select * from t2; +c2_id c2_p_id c2_note c2_active +1 1 A Note 1 +drop table t1, t2; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index f70eaeae214..72eb4c37d34 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -14,6 +14,7 @@ insert into t1 values ("Alas"); flush logs; --- Local -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; create table t1 (word varchar(20)); @@ -31,19 +32,23 @@ LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-4-0' INTO TABLE `t1` FI LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-5-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); --- Broken LOAD DATA -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); --- --database -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; SET INSERT_ID=1; --- --position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); --- Remote -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; create table t1 (word varchar(20)); @@ -64,14 +69,17 @@ SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); --- Broken LOAD DATA -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); --- --database -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; SET INSERT_ID=1; --- --position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 5bbf0bad445..453b5f66bdc 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -830,3 +830,21 @@ a USE test; DROP TABLE t1; SET GLOBAL query_cache_size=0; +set query_cache_wlock_invalidate=1; +create table t1 (a int not null); +create table t2 (a int not null); +select * from t1; +a +select * from t2; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +lock table t1 write, t2 read; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +unlock table; +drop table t1,t2; +set query_cache_wlock_invalidate=default; +set GLOBAL query_cache_size=0; diff --git a/mysql-test/r/rpl_error_ignored_table.result b/mysql-test/r/rpl_error_ignored_table.result index 8d5bbf91561..46280e60ea1 100644 --- a/mysql-test/r/rpl_error_ignored_table.result +++ b/mysql-test/r/rpl_error_ignored_table.result @@ -13,3 +13,28 @@ Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File show tables like 't1'; Tables_in_test (t1) drop table t1; +select get_lock('crash_lock%20C', 10); +get_lock('crash_lock%20C', 10) +1 +create table t2 (a int primary key); +insert into t2 values(1); +create table t3 (id int); +insert into t3 values(connection_id()); + update t2 set a = a + 1 + get_lock('crash_lock%20C', 10); +select (@id := id) - id from t3; +(@id := id) - id +0 +kill @id; +drop table t2,t3; +Server shutdown in progress +show binlog events from 79; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.001 79 Query 1 79 use `test`; create table t1 (a int primary key) +master-bin.001 149 Query 1 149 use `test`; insert into t1 values (1),(1) +master-bin.001 213 Query 1 213 use `test`; drop table t1 +master-bin.001 261 Query 1 261 use `test`; create table t2 (a int primary key) +master-bin.001 331 Query 1 331 use `test`; insert into t2 values(1) +master-bin.001 390 Query 1 390 use `test`; create table t3 (id int) +master-bin.001 449 Query 1 449 use `test`; insert into t3 values(connection_id()) +master-bin.001 522 Query 1 522 use `test`; update t2 set a = a + 1 + get_lock('crash_lock%20C', 10) +master-bin.001 613 Query 1 613 use `test`; drop table t2,t3 diff --git a/mysql-test/r/rpl_optimize.result b/mysql-test/r/rpl_optimize.result new file mode 100644 index 00000000000..5ed541516dc --- /dev/null +++ b/mysql-test/r/rpl_optimize.result @@ -0,0 +1,27 @@ +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; +create table t1 (a int not null auto_increment primary key, b int, key(b)); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +update t1 set b=(a/2*rand()); +delete from t1 order by b limit 10000; +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +drop table t1; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result new file mode 100644 index 00000000000..257b3b13f08 --- /dev/null +++ b/mysql-test/r/system_mysql_db.result @@ -0,0 +1,119 @@ +show tables; +Tables_in_db +columns_priv +db +func +host +tables_priv +user +show create table db; +Table Create Table +db CREATE TABLE `db` ( + `Host` char(60) binary NOT NULL default '', + `Db` char(64) binary NOT NULL default '', + `User` char(16) binary NOT NULL default '', + `Select_priv` enum('N','Y') NOT NULL default 'N', + `Insert_priv` enum('N','Y') NOT NULL default 'N', + `Update_priv` enum('N','Y') NOT NULL default 'N', + `Delete_priv` enum('N','Y') NOT NULL default 'N', + `Create_priv` enum('N','Y') NOT NULL default 'N', + `Drop_priv` enum('N','Y') NOT NULL default 'N', + `Grant_priv` enum('N','Y') NOT NULL default 'N', + `References_priv` enum('N','Y') NOT NULL default 'N', + `Index_priv` enum('N','Y') NOT NULL default 'N', + `Alter_priv` enum('N','Y') NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', + PRIMARY KEY (`Host`,`Db`,`User`), + KEY `User` (`User`) +) TYPE=MyISAM COMMENT='Database privileges' +show create table host; +Table Create Table +host CREATE TABLE `host` ( + `Host` char(60) binary NOT NULL default '', + `Db` char(64) binary NOT NULL default '', + `Select_priv` enum('N','Y') NOT NULL default 'N', + `Insert_priv` enum('N','Y') NOT NULL default 'N', + `Update_priv` enum('N','Y') NOT NULL default 'N', + `Delete_priv` enum('N','Y') NOT NULL default 'N', + `Create_priv` enum('N','Y') NOT NULL default 'N', + `Drop_priv` enum('N','Y') NOT NULL default 'N', + `Grant_priv` enum('N','Y') NOT NULL default 'N', + `References_priv` enum('N','Y') NOT NULL default 'N', + `Index_priv` enum('N','Y') NOT NULL default 'N', + `Alter_priv` enum('N','Y') NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', + PRIMARY KEY (`Host`,`Db`) +) TYPE=MyISAM COMMENT='Host privileges; Merged with database privileges' +show create table user; +Table Create Table +user CREATE TABLE `user` ( + `Host` varchar(60) binary NOT NULL default '', + `User` varchar(16) binary NOT NULL default '', + `Password` varchar(16) binary NOT NULL default '', + `Select_priv` enum('N','Y') NOT NULL default 'N', + `Insert_priv` enum('N','Y') NOT NULL default 'N', + `Update_priv` enum('N','Y') NOT NULL default 'N', + `Delete_priv` enum('N','Y') NOT NULL default 'N', + `Create_priv` enum('N','Y') NOT NULL default 'N', + `Drop_priv` enum('N','Y') NOT NULL default 'N', + `Reload_priv` enum('N','Y') NOT NULL default 'N', + `Shutdown_priv` enum('N','Y') NOT NULL default 'N', + `Process_priv` enum('N','Y') NOT NULL default 'N', + `File_priv` enum('N','Y') NOT NULL default 'N', + `Grant_priv` enum('N','Y') NOT NULL default 'N', + `References_priv` enum('N','Y') NOT NULL default 'N', + `Index_priv` enum('N','Y') NOT NULL default 'N', + `Alter_priv` enum('N','Y') NOT NULL default 'N', + `Show_db_priv` enum('N','Y') NOT NULL default 'N', + `Super_priv` enum('N','Y') NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', + `Execute_priv` enum('N','Y') NOT NULL default 'N', + `Repl_slave_priv` enum('N','Y') NOT NULL default 'N', + `Repl_client_priv` enum('N','Y') NOT NULL default 'N', + `ssl_type` enum('','ANY','X509','SPECIFIED') NOT NULL default '', + `ssl_cipher` blob NOT NULL, + `x509_issuer` blob NOT NULL, + `x509_subject` blob NOT NULL, + `max_questions` int(11) unsigned NOT NULL default '0', + `max_updates` int(11) unsigned NOT NULL default '0', + `max_connections` int(11) unsigned NOT NULL default '0', + PRIMARY KEY (`Host`,`User`) +) TYPE=MyISAM COMMENT='Users and global privileges' +show create table func; +Table Create Table +func CREATE TABLE `func` ( + `name` char(64) NOT NULL default '', + `ret` tinyint(1) NOT NULL default '0', + `dl` char(128) NOT NULL default '', + `type` enum('function','aggregate') NOT NULL default 'function', + PRIMARY KEY (`name`) +) TYPE=MyISAM COMMENT='User defined functions' +show create table tables_priv; +Table Create Table +tables_priv CREATE TABLE `tables_priv` ( + `Host` char(60) binary NOT NULL default '', + `Db` char(64) binary NOT NULL default '', + `User` char(16) binary NOT NULL default '', + `Table_name` char(64) binary NOT NULL default '', + `Grantor` char(77) NOT NULL default '', + `Timestamp` timestamp(14) NOT NULL, + `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') NOT NULL default '', + `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', + PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`), + KEY `Grantor` (`Grantor`) +) TYPE=MyISAM COMMENT='Table privileges' +show create table columns_priv; +Table Create Table +columns_priv CREATE TABLE `columns_priv` ( + `Host` char(60) binary NOT NULL default '', + `Db` char(64) binary NOT NULL default '', + `User` char(16) binary NOT NULL default '', + `Table_name` char(64) binary NOT NULL default '', + `Column_name` char(64) binary NOT NULL default '', + `Timestamp` timestamp(14) NOT NULL, + `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', + PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) +) TYPE=MyISAM COMMENT='Column privileges' diff --git a/mysql-test/r/system_mysql_db_refs.result b/mysql-test/r/system_mysql_db_refs.result new file mode 100644 index 00000000000..a08b46ffbbb --- /dev/null +++ b/mysql-test/r/system_mysql_db_refs.result @@ -0,0 +1,67 @@ +set @name="This is a very long string, that mustn't find room in a system field like Table_name. Thus it should be cut by the actual size of the field. So we can use this string to find out the actual length of the field and to use it in any compare queries"; +create table test_db select * from mysql.db; +delete from test_db; +insert into test_db (Host,Db,User) values (@name,@name,@name); +create table test_host select * from mysql.host; +delete from test_host; +insert into test_host (Host,Db) values (@name,@name); +create table test_user select * from mysql.user; +delete from test_user; +insert into test_user (Host,User) values (@name,@name); +create table test_func select * from mysql.func; +delete from test_func; +insert into test_func (name) values (@name); +create table test_tables_priv select * from mysql.tables_priv; +delete from test_tables_priv; +insert into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name); +create table test_columns_priv select * from mysql.columns_priv; +delete from test_columns_priv; +insert into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name); +select
+if(isnull(test_db.Host),'WRONG!!!','ok') as test_db_Host,
+if(isnull(test_host.Host),'WRONG!!!','ok') as test_host_Host,
+if(isnull(test_user.Host),'WRONG!!!','ok') as test_user_Host,
+if(isnull(test_tables_priv.Host),'WRONG!!!','ok') as test_tables_priv_Host,
+if(isnull(test_columns_priv.Host),'WRONG!!!','ok') as test_columns_priv_Host
+from test_db
+left join test_host on test_db.Host=test_host.Host
+left join test_user on test_db.Host=test_user.Host
+left join test_tables_priv on test_db.Host=test_tables_priv.Host
+left join test_columns_priv on test_db.Host=test_columns_priv.Host; +test_db_Host test_host_Host test_user_Host test_tables_priv_Host test_columns_priv_Host +ok ok ok ok ok +select
+if(isnull(test_db.Db),'WRONG!!!','ok') as test_db_Db,
+if(isnull(test_host.Db),'WRONG!!!','ok') as test_host_Db,
+if(isnull(test_tables_priv.Db),'WRONG!!!','ok') as test_tables_priv_Db,
+if(isnull(test_columns_priv.Db),'WRONG!!!','ok') as est_columns_priv_Db
+from test_db
+left join test_host on test_db.Db=test_host.Db
+left join test_tables_priv on test_db.Db=test_tables_priv.Db
+left join test_columns_priv on test_db.Db=test_columns_priv.Db; +test_db_Db test_host_Db test_tables_priv_Db est_columns_priv_Db +ok ok ok ok +select
+if(isnull(test_db.User),'WRONG!!!','ok') as test_db_User,
+if(isnull(test_user.User),'WRONG!!!','ok') as test_user_User,
+if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+from test_db
+left join test_user on test_db.User=test_user.User
+left join test_tables_priv on test_db.User=test_tables_priv.User
+left join test_columns_priv on test_db.User=test_columns_priv.User; +test_db_User test_user_User test_tables_priv_User test_columns_priv_User +ok ok ok ok +select
+if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+from test_tables_priv
+left join test_columns_priv on test_tables_priv.Table_name=test_columns_priv.Table_name; +test_tables_priv_User test_columns_priv_User +ok ok +drop table test_columns_priv; +drop table test_tables_priv; +drop table test_func; +drop table test_host; +drop table test_user; +drop table test_db; diff --git a/mysql-test/r/true.require b/mysql-test/r/true.require new file mode 100644 index 00000000000..09aae1ed1d0 --- /dev/null +++ b/mysql-test/r/true.require @@ -0,0 +1,2 @@ +TRUE +1 diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index bd629552924..253aa1bd8b9 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -220,6 +220,18 @@ select * from t1; drop table t1; # +# Test for Bug #2985 +# "Table truncated when creating another table name with Spaces" +# + +--error 1103 +create table `t1 `(a int); +--error 1102 +create database `db1 `; +--error 1166; +create table t1(`a ` int); + +# # Test create with foreign keys # diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 1b56889aec7..50a3d522e3d 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -63,6 +63,9 @@ select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOL 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); select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); + +select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); # bug#2708 crash diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 0f473abb96d..242295f2018 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -40,4 +40,12 @@ SELECT ACOS(0.2*5.0); SELECT ACOS(0.5*2.0); SELECT ASIN(0.8+0.2); SELECT ASIN(1.2-0.2); + +# +# Bug #3051 FLOOR returns invalid +# + +select floor(log(4)/log(2)); +select floor(log(8)/log(2)); +select floor(log(16)/log(2)); explain extended select degrees(pi()),radians(360); diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index 86bb26f0cf9..8f542a7af78 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -78,3 +78,14 @@ RENAME TABLE T2 TO T1; SHOW TABLES LIKE "T1"; SELECT * from T1; DROP TABLE T1; + +# +# Test problem with temporary tables (Bug #2858) +# + +create table T1 (EVENT_ID int auto_increment primary key, LOCATION char(20)); +insert into T1 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6"); +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +drop table T1; diff --git a/mysql-test/t/lowercase_table2-master.opt b/mysql-test/t/lowercase_table3-master.opt index 9b27aef9bf8..9b27aef9bf8 100644 --- a/mysql-test/t/lowercase_table2-master.opt +++ b/mysql-test/t/lowercase_table3-master.opt diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test new file mode 100644 index 00000000000..735a0b390f9 --- /dev/null +++ b/mysql-test/t/lowercase_table3.test @@ -0,0 +1,37 @@ +# +# Test of force of lower-case-table-names=0 +# (User has case insensitive file system and want's to preserve case of +# table names) +# + +--source include/have_innodb.inc +--require r/lowercase0.require +disable_query_log; +show variables like "lower_case_%"; +--require r/true.require +select @@version_compile_os NOT IN ("NT","WIN2000","Win95/Win98","XP") as "TRUE"; +enable_query_log; + +--disable_warnings +DROP TABLE IF EXISTS t1,T1; +--enable_warnings + +# +# This is actually an error, but ok as the user has forced this +# by using --lower-case-table-names=0 + +CREATE TABLE t1 (a int); +SELECT * from T1; +drop table t1; +flush tables; + +# +# InnoDB should in this case be case sensitive +# Note that this is not true on windows as no this OS, InnoDB is always +# storing things in lower case. +# + +CREATE TABLE t1 (a int) type=INNODB; +--error 1016 +SELECT * from T1; +drop table t1; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index eb77734300f..6cb4a107bdb 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -344,3 +344,40 @@ select 't2 rows after big delete', count(*) from t2; select 't1 rows after big delete', count(*) from t1; drop table t1,t2; + +# +# Test alias (this is not correct in 4.0) +# + +CREATE TABLE t1 ( a int ); +CREATE TABLE t2 ( a int ); +DELETE t1 FROM t1, t2 AS t3; +--error 1066 +DELETE t4 FROM t1, t1 AS t4; +--error 1066 +DELETE t3 FROM t1 AS t3, t1 AS t4; +#--error 1066 +DELETE t1 FROM t1 AS t3, t2 AS t4; +INSERT INTO t1 values (1),(2); +INSERT INTO t2 values (1),(2); +DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1; +SELECT * from t1; +SELECT * from t2; +DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2; +SELECT * from t1; +SELECT * from t2; +DROP TABLE t1,t2; + +# +# Test update with const tables +# + +create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) ); +create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) ); +insert into t1 values (0,'A01-Comp',1); +insert into t1 values (0,'B01-Comp',1); +insert into t2 values (0,1,'A Note',1); +update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; +select * from t1; +select * from t2; +drop table t1, t2; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 5ae2039138d..31aa69e32ec 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -544,10 +544,25 @@ select * from t3; enable_result_log; delete from t4 where a=1; flush query cache; - drop table t1,t2,t3,t4; # +# WRITE LOCK & QC +# +set query_cache_wlock_invalidate=1; +create table t1 (a int not null); +create table t2 (a int not null); +select * from t1; +select * from t2; +show status like "Qcache_queries_in_cache"; +lock table t1 write, t2 read; +show status like "Qcache_queries_in_cache"; +unlock table; +drop table t1,t2; +set query_cache_wlock_invalidate=default; + + +# # Test character set related variables: # character_set_result # character_set_client diff --git a/mysql-test/t/reserv/system_mysql_db.test b/mysql-test/t/reserv/system_mysql_db.test new file mode 100644 index 00000000000..85a2f7f9bae --- /dev/null +++ b/mysql-test/t/reserv/system_mysql_db.test @@ -0,0 +1,8 @@ +# +# This test must examine integrity of system database "mysql" +# + +-- disable_query_log +use mysql; +-- enable_query_log +-- source include/system_db_struct.inc diff --git a/mysql-test/t/reserv/system_mysql_db_fix-master.opt b/mysql-test/t/reserv/system_mysql_db_fix-master.opt new file mode 100644 index 00000000000..69eb9d2cbf0 --- /dev/null +++ b/mysql-test/t/reserv/system_mysql_db_fix-master.opt @@ -0,0 +1 @@ +--result-file=system_mysql_db
\ No newline at end of file diff --git a/mysql-test/t/reserv/system_mysql_db_fix.test b/mysql-test/t/reserv/system_mysql_db_fix.test new file mode 100644 index 00000000000..bb2d37ee215 --- /dev/null +++ b/mysql-test/t/reserv/system_mysql_db_fix.test @@ -0,0 +1,14 @@ +# +# This is the test for mysql_fix_privilege_tables +# + +-- disable_query_log +-- source include/create_old_system_tables.inc +-- exec $MYSQL_FIX_SYSTEM_TABLES --database=test > nil 2>nil +-- enable_query_log + +-- source include/system_db_struct.inc + +-- disable_query_log +-- source include/drop_system_tables.inc +-- enable_query_log diff --git a/mysql-test/t/reserv/system_mysql_db_refs.test b/mysql-test/t/reserv/system_mysql_db_refs.test new file mode 100644 index 00000000000..d15141fdaa5 --- /dev/null +++ b/mysql-test/t/reserv/system_mysql_db_refs.test @@ -0,0 +1,89 @@ +# +# This test must examine integrity of current system database +# + +set @name="This is a very long string, that mustn't find room in a system field like Table_name. Thus it should be cut by the actual size of the field. So we can use this string to find out the actual length of the field and to use it in any compare queries"; + +# +# If this part is wrong, most likely you've done wrong modification of system database "mysql" +# + +create table test_db select * from mysql.db; +delete from test_db; +insert into test_db (Host,Db,User) values (@name,@name,@name); + +create table test_host select * from mysql.host; +delete from test_host; +insert into test_host (Host,Db) values (@name,@name); + +create table test_user select * from mysql.user; +delete from test_user; +insert into test_user (Host,User) values (@name,@name); + +create table test_func select * from mysql.func; +delete from test_func; +insert into test_func (name) values (@name); + +create table test_tables_priv select * from mysql.tables_priv; +delete from test_tables_priv; +insert into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name); + +create table test_columns_priv select * from mysql.columns_priv; +delete from test_columns_priv; +insert into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name); + +# 'Host' field must be the same for all the tables: + +select + if(isnull(test_db.Host),'WRONG!!!','ok') as test_db_Host, + if(isnull(test_host.Host),'WRONG!!!','ok') as test_host_Host, + if(isnull(test_user.Host),'WRONG!!!','ok') as test_user_Host, + if(isnull(test_tables_priv.Host),'WRONG!!!','ok') as test_tables_priv_Host, + if(isnull(test_columns_priv.Host),'WRONG!!!','ok') as test_columns_priv_Host + +from test_db +left join test_host on test_db.Host=test_host.Host +left join test_user on test_db.Host=test_user.Host +left join test_tables_priv on test_db.Host=test_tables_priv.Host +left join test_columns_priv on test_db.Host=test_columns_priv.Host; + +# 'Db' field must be the same for all the tables: + +select + if(isnull(test_db.Db),'WRONG!!!','ok') as test_db_Db, + if(isnull(test_host.Db),'WRONG!!!','ok') as test_host_Db, + if(isnull(test_tables_priv.Db),'WRONG!!!','ok') as test_tables_priv_Db, + if(isnull(test_columns_priv.Db),'WRONG!!!','ok') as est_columns_priv_Db + +from test_db +left join test_host on test_db.Db=test_host.Db +left join test_tables_priv on test_db.Db=test_tables_priv.Db +left join test_columns_priv on test_db.Db=test_columns_priv.Db; + +# 'User' field must be the same for all the tables: + +select + if(isnull(test_db.User),'WRONG!!!','ok') as test_db_User, + if(isnull(test_user.User),'WRONG!!!','ok') as test_user_User, + if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User, + if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User + +from test_db +left join test_user on test_db.User=test_user.User +left join test_tables_priv on test_db.User=test_tables_priv.User +left join test_columns_priv on test_db.User=test_columns_priv.User; + +# 'Table_name' field must be the same for all the tables: + +select + if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User, + if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User +from test_tables_priv +left join test_columns_priv on test_tables_priv.Table_name=test_columns_priv.Table_name; + +drop table test_columns_priv; +drop table test_tables_priv; +drop table test_func; +drop table test_host; +drop table test_user; +drop table test_db; diff --git a/mysql-test/t/rpl_error_ignored_table-slave.opt b/mysql-test/t/rpl_error_ignored_table-slave.opt index 0d3485f9e25..cb49119bfcb 100644 --- a/mysql-test/t/rpl_error_ignored_table-slave.opt +++ b/mysql-test/t/rpl_error_ignored_table-slave.opt @@ -1 +1 @@ ---replicate-ignore-table=test.t1 +--replicate-ignore-table=test.t1 --replicate-ignore-table=test.t2 --replicate-ignore-table=test.t3 diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test index aacdb506107..f6d9ffa8125 100644 --- a/mysql-test/t/rpl_error_ignored_table.test +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -24,3 +24,33 @@ drop table t1; save_master_pos; connection slave; sync_with_master; + +# Now test that even critical errors (connection killed) +# are ignored if rules allow it. +# The "kill" idea was copied from rpl000001.test. + +connection master1; +select get_lock('crash_lock%20C', 10); + +connection master; +create table t2 (a int primary key); +insert into t2 values(1); +create table t3 (id int); +insert into t3 values(connection_id()); +send update t2 set a = a + 1 + get_lock('crash_lock%20C', 10); + +connection master1; +sleep 2; +select (@id := id) - id from t3; +kill @id; +drop table t2,t3; +connection master; +--error 1053; +reap; +connection master1; +show binlog events from 79; +save_master_pos; +connection slave; +# SQL slave thread should not have stopped (because table of the killed +# query is in the ignore list). +sync_with_master; diff --git a/mysql-test/t/rpl_optimize.test b/mysql-test/t/rpl_optimize.test new file mode 100644 index 00000000000..9f02b715885 --- /dev/null +++ b/mysql-test/t/rpl_optimize.test @@ -0,0 +1,43 @@ +# Test for BUG#1858 "OPTIMIZE TABLE done by a client +# thread stops the slave SQL thread". +# You can replace OPTIMIZE by REPAIR. + +source include/master-slave.inc; + +create table t1 (a int not null auto_increment primary key, b int, key(b)); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +INSERT INTO t1 (a) SELECT null FROM t1; +save_master_pos; +# a few updates to force OPTIMIZE to do something +update t1 set b=(a/2*rand()); +delete from t1 order by b limit 10000; + +connection slave; +sync_with_master; +optimize table t1; +connection master; +save_master_pos; +connection slave; +# Bug was that when the INSERT terminated on slave, +# the slave SQL thread got killed by OPTIMIZE. +sync_with_master; # won't work if slave SQL thread stopped + +connection master; # cleanup +drop table t1; +connection slave; +sync_with_master; + +# If the machine is so fast that slave syncs before OPTIMIZE +# starts, this test wil demonstrate nothing but will pass. |