From bd414c301c1bbadecf385e1c10aa8a27cd76f61a Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 5 Jun 2003 17:02:00 +0200 Subject: Test for bug 578. And a comment in slave.cc. sql/slave.cc: Comment to remember that size of relay logs on disk is not the total size of the relay log. --- mysql-test/t/lock_tables_lost_commit-master.opt | 1 + mysql-test/t/lock_tables_lost_commit.test | 18 ++++++++++++++++++ 2 files changed, 19 insertions(+) create mode 100644 mysql-test/t/lock_tables_lost_commit-master.opt create mode 100644 mysql-test/t/lock_tables_lost_commit.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/lock_tables_lost_commit-master.opt b/mysql-test/t/lock_tables_lost_commit-master.opt new file mode 100644 index 00000000000..d357a51cb27 --- /dev/null +++ b/mysql-test/t/lock_tables_lost_commit-master.opt @@ -0,0 +1 @@ +--binlog-ignore-db=test innodb \ No newline at end of file diff --git a/mysql-test/t/lock_tables_lost_commit.test b/mysql-test/t/lock_tables_lost_commit.test new file mode 100644 index 00000000000..a12ee7369cb --- /dev/null +++ b/mysql-test/t/lock_tables_lost_commit.test @@ -0,0 +1,18 @@ +# This is a test for bug 578 + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +drop table if exists t1; +create table t1(a int) type=innodb; +lock tables t1 write; +insert into t1 values(10); +disconnect con1; + +connection con2; +# The bug was that, because of the LOCK TABLES, the handler "forgot" to commit, +# and the other commit when we write to the binlog was not done because of +# binlog-ignore-db +select * from t1; +drop table t1; -- cgit v1.2.1 From 0880795793f7c0c468bdb23f65050cc8ffd22d24 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 10 Jun 2003 23:29:49 +0200 Subject: More error messages. This is intended to help debugging; presently I have a support issue with an unclear message which can have N reasons for appearing. This should help us know at which point it failed, and get the errno when my_open was involved (as the reason for the unclear message is often a permission problem). RESET SLAVE resets last_error and last_errno in SHOW SLAVE STATUS (without this, rpl_loaddata.test, which is expected to generate an error in last_error, influenced rpl_log_pos.test). A small test update. Added STOP SLAVE to mysql-test-run to get rid of several stupid error messages which are printed while the master restarts and the slave attempts/manages to connect to it and sends it nonsense binlog requests. mysql-test/mysql-test-run.sh: Before running a test, stop slave threads if they exist (if they don't the script goes on fine). This also works fine with the manager. Before this change, when the master was stopped/restarted (which happened before the slave server was stopped/restarted), the slave threads noticed the stop (so printed an error message in slave.err), then managed to reconnect (to the new master, the one that is running for the _next_ test), and this reconnection had time to produce error messages (because, for example, the binlog the slave thread was asking had been deleted) before the slave server was killed. This change reduces by 10% (40 lines) slave.err in replication tests. mysql-test/r/rpl000018.result: Result update. mysql-test/t/rpl000018.test: This test does "show master logs" so should do "reset master" instead of relying on the previous tests. sql/slave.cc: More error messages. sql/sql_repl.cc: More error messages. RESET SLAVE resets last_error and last_errno in SHOW SLAVE STATUS. --- mysql-test/t/rpl000018.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test index 291b482b912..3bd5fd0ef09 100644 --- a/mysql-test/t/rpl000018.test +++ b/mysql-test/t/rpl000018.test @@ -6,6 +6,8 @@ require_manager; connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); +connection master; +reset master; server_stop master; server_start master; connection slave; -- cgit v1.2.1 From 4885c3ff9d9c8daa93f5c344b6b61ccf9bcbba28 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 11 Jun 2003 22:07:23 +0500 Subject: test case for bug #614 mysql-test/r/func_time.result: result's checking added mysql-test/t/func_time.test: testcase added --- mysql-test/t/func_time.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index dd589ff2e66..3057729ab96 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -123,6 +123,8 @@ select extract(MONTH FROM "2001-02-00"); create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; +# test bug 614 (multiple extracts in where) +select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; drop table t1; # -- cgit v1.2.1 From 8fe7e029c211dc3856fd3a8274f5fbbc95f12e3b Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jun 2003 01:04:28 +0300 Subject: Many files: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/ha_innodb.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/handler.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_lex.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/ha_innodb.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/handler.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_parse.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_yacc.yy: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed include/my_base.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed mysql-test/t/innodb.test: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed mysql-test/r/innodb.result: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/btr/btr0cur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/btr/btr0pcur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/buf/buf0buf.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/buf/buf0flu.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/data/data0data.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/data/data0type.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0boot.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0crea.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0dict.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0load.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0mem.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/fil/fil0fil.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/fsp/fsp0fsp.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ha/ha0ha.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ibuf/ibuf0ibuf.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/btr0cur.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/buf0buf.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0data.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0type.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/db0err.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/dict0dict.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/dict0mem.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/fil0fil.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/lock0lock.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/os0file.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/page0page.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/rem0cmp.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0row.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0sel.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0upd.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/srv0srv.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0type.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0roll.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0trx.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0types.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/ut0dbg.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/ut0mem.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0mysql.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0sys.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/lock/lock0lock.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/log/log0log.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/log/log0recv.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/mem/mem0pool.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/os/os0file.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/os/os0thread.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/page/page0cur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/page/page0page.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/pars/pars0opt.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/pars/pars0pars.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/rem/rem0cmp.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0ins.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0mysql.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0row.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0sel.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0umod.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0upd.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0vers.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/srv/srv0srv.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/srv/srv0start.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0rec.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0roll.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0sys.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0trx.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ut/ut0mem.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ut/ut0ut.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed --- mysql-test/t/innodb.test | 9 --------- 1 file changed, 9 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index dc3c76f1a91..cf203d87c8b 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -471,15 +471,6 @@ select id,id3 from t1; UNLOCK TABLES; DROP TABLE t1; -# -# Test prefix key -# ---error 1089 -create table t1 (a char(20), unique (a(5))) type=innodb; -create table t1 (a char(20), index (a(5))) type=innodb; -show create table t1; -drop table t1; - # # Test using temporary table and auto_increment # -- cgit v1.2.1 From 292f91e83d954e2bd23c7f15fc26d0823157594f Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jun 2003 12:01:51 +0200 Subject: Simplified a test. thd->enter_cond() and exit_cond(), so that the I/O thread accepts to stop when it's waiting for relay log space. Reset ignore_log_space_limit to 0 when the SQL thread terminates. mysql-test/r/rpl_relayspace.result: result update mysql-test/t/rpl_relayspace-slave.opt: smaller relay_log_space_limit to speed up things. mysql-test/t/rpl_relayspace.test: Less queries in the test, to make it simpler. Testcase if the IO thread blocked in wait_for_relay_log_space accepts to stop when STOP SLAVE. sql/slave.cc: thd->enter_cond() and exit_cond(), so that the I/O thread accepts to stop when it's waiting on cond (waiting for relay log space). Reset ignore_log_space_limit to 0 when the SQL thread terminates. --- mysql-test/t/rpl_relayspace-slave.opt | 2 +- mysql-test/t/rpl_relayspace.test | 33 ++++++++++++++++----------------- 2 files changed, 17 insertions(+), 18 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_relayspace-slave.opt b/mysql-test/t/rpl_relayspace-slave.opt index 9365a2a0a26..05cb01731d2 100644 --- a/mysql-test/t/rpl_relayspace-slave.opt +++ b/mysql-test/t/rpl_relayspace-slave.opt @@ -1 +1 @@ - -O relay_log_space_limit=1024 \ No newline at end of file + -O relay_log_space_limit=10 \ No newline at end of file diff --git a/mysql-test/t/rpl_relayspace.test b/mysql-test/t/rpl_relayspace.test index 8d4f01339c7..bb82781b511 100644 --- a/mysql-test/t/rpl_relayspace.test +++ b/mysql-test/t/rpl_relayspace.test @@ -1,33 +1,32 @@ -# The slave is started with relay_log_space_limit=1024 bytes, -# to force the deadlock +# The slave is started with relay_log_space_limit=10 bytes, +# to force the deadlock after one event. source include/master-slave.inc; connection slave; stop slave; connection master; +# This will generate a master's binlog > 10 bytes create table t1 (a int); -let $1=200; -disable_query_log; -while ($1) -{ -# eval means expand $ expressions - eval insert into t1 values( $1 ); - dec $1; -} -# This will generate one 10kB master's binlog -enable_query_log; -save_master_pos; +drop table t1; +create table t1 (a int); +drop table t1; connection slave; reset slave; +start slave io_thread; +# Give the I/O thread time to block. +sleep 2; +# A bug caused the I/O thread to refuse stopping. +stop slave io_thread; +reset slave; start slave; # The I/O thread stops filling the relay log when -# it's 1kB. And the SQL thread cannot purge this relay log +# it's >10b. And the SQL thread cannot purge this relay log # as purge is done only when the SQL thread switches to another # relay log, which does not exist here. # So we should have a deadlock. # if it is not resolved automatically we'll detect -# it with master_pos_wait that waits for farther than 1kB; -# it will timeout after 45 seconds; +# it with master_pos_wait that waits for farther than 1Ob; +# it will timeout after 10 seconds; # also the slave will probably not cooperate to shutdown # (as 2 threads are locked) -select master_pos_wait('master-bin.001',5000,45)=-1; +select master_pos_wait('master-bin.001',200,6)=-1; -- cgit v1.2.1 From 366fd92e8d882737268f354e0a744d576868a043 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 16 Jun 2003 15:49:54 +0200 Subject: Fix for nightly build test failure (test update). More messages. Testcase for bug 651. client/mysqltest.c: More explicit error message if MASTER_POS_WAIT() returns NULL. mysql-test/r/rpl_loaddata.result: result update mysql-test/r/rpl_master_pos_wait.result: result update mysql-test/t/rpl000001.test: sync_with_master (=MASTER_POS_WAIT()) was called when we could expect the SQL slave thread had stopped. As I yesterday changed code so that "SQL thread stops => MASTER_POS_WAIT() returns NULL immediately" (bugfix), sync_with_master received NULL (on build.mysql.com, not on my machine; this is a question of milliseconds, if the slave server will process MASTER_POS_WAIT() before or after the slave SQL thread has stopped), and in mysqltest.c, sync_with_master complained that it could not sync. So I just remove this sync_with_master, which does not make sense anymore: we just wait for the slave SQL thread to stop. mysql-test/t/rpl_loaddata.test: Discovered we had wait_for_slave_to_stop, so used it as it automates things. mysql-test/t/rpl_master_pos_wait.test: Discovered we had 'send' to send a query without waiting for the resultn so could had a testcase for bug 651. Shorter timeouts as there is no risk the position is reached. sql/slave.cc: A longer DBUG_PRINT. --- mysql-test/t/rpl000001.test | 4 ++-- mysql-test/t/rpl_loaddata.test | 8 ++------ mysql-test/t/rpl_master_pos_wait.test | 10 ++++++++-- 3 files changed, 12 insertions(+), 10 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index 4ffd7d1d78e..ebce3d0ac94 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -90,8 +90,8 @@ connection master; --error 1053; reap; connection slave; -sync_with_master; -#give the slave a chance to exit +# The SQL slave thread should now have stopped because the query was killed on +# the master (so it has a non-zero error code in the binlog). wait_for_slave_to_stop; # The following test can't be done because the result of Pos will differ diff --git a/mysql-test/t/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test index dc4eadda192..96a4eb3fb76 100644 --- a/mysql-test/t/rpl_loaddata.test +++ b/mysql-test/t/rpl_loaddata.test @@ -42,9 +42,5 @@ load data infile '../../std_data/rpl_loaddata.dat' into table t1; save_master_pos; connection slave; -# don't sync_with_master because the slave SQL thread should be stopped because -# of the error so MASTER_POS_WAIT() will not return; just sleep and hope the -# slave SQL thread will have had time to stop. - -sleep 1; -show status like 'slave_running'; +# The SQL slave thread should be stopped now. +wait_for_slave_to_stop; diff --git a/mysql-test/t/rpl_master_pos_wait.test b/mysql-test/t/rpl_master_pos_wait.test index a6aae222a89..24479636c91 100644 --- a/mysql-test/t/rpl_master_pos_wait.test +++ b/mysql-test/t/rpl_master_pos_wait.test @@ -5,5 +5,11 @@ save_master_pos; connection slave; sync_with_master; # Ask for a master log that has certainly not been reached yet -# timeout= 10 seconds -select master_pos_wait('master-bin.999999',0,10); +# timeout= 2 seconds +select master_pos_wait('master-bin.999999',0,2); +# Testcase for bug 651 (master_pos_wait() hangs if slave idle and STOP SLAVE). +send select master_pos_wait('master-bin.999999',0); +connection slave1; +stop slave sql_thread; +connection slave; +reap; -- cgit v1.2.1 From ad5c763e74821465cc8237aa39e2391239a17206 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 16 Jun 2003 23:05:45 +0200 Subject: fulltext and left join bug fixed mysql-test/t/fulltext.test: explain select fulltext test mysql-test/t/fulltext_left_join.test: explain select fulltext test --- mysql-test/t/fulltext.test | 1 + mysql-test/t/fulltext_left_join.test | 12 ++++++++++++ 2 files changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 128af680854..1b85f5903df 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -13,6 +13,7 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), # nl search +explain select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index bcf7cbcc505..da4df13bc0c 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -28,3 +28,15 @@ select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) drop table t1, t2; +# +# Bug #484, reported by Stephen Brandon +# + +create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) type=myisam; +insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); +create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) type=myisam; +insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; +drop table t1,t2; + -- cgit v1.2.1 From 800bbfa04e0692a78fd790f6ad5b06c48d200d95 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 Jun 2003 16:35:20 +0200 Subject: Better error reporting in mysql-test-run (don't hide the important message with 'head'). Test fix (this sync_with_master was nonsense). Now all tests pass in 4.0 in autobuild. mysql-test/mysql-test-run.sh: Error reporting (how mysql-test-run filters the messages which mysqltest prints on stderr) is not reliable. It does a 'head the first line only' which in my case caused me to lose some time, because in fact the worrying message was on the second line (logically, the one which caused the die() is at the end of the $TIMEFILE file, not at the beginning). Doing a 'tail' is better but not perfect (depending on the build, one may get "Maximum memory usage" as the last line); it's always good to have all messages and spot the important one oneself. So we print all $TIMEFILE (that's a few lines at most). mysql-test/t/rpl_rotate_logs.test: Dont sync_with_master as the SQL slave thread may be stopped at this moment; just wait_for_slave_to_stop. Doing sync_with_master caused a non-deterministic behaviour, because (note that save_master_pos was called to early) sometimes MASTER_POS_WAIT() started before the SQL thread died (because of unique violation) so returned 0, other times it started after the SQL thread died so returned NULL (which is reported by mysqltest as an error, on stderr). --- mysql-test/t/rpl_rotate_logs.test | 3 --- 1 file changed, 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index 6edbf363b1a..9f7f44d7c28 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -73,12 +73,9 @@ insert into t2 values(1234); #same value on the master connection master; -save_master_pos; set insert_id=1234; insert into t2 values(NULL); connection slave; -sync_with_master; - wait_for_slave_to_stop; #restart slave skipping one event -- cgit v1.2.1 From b518744d16a516c842c4ec0382c8ab44a38aebca Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 Jun 2003 23:19:38 +0200 Subject: Changed change_master() to use ER_MASTER_INFO (better display). mysql-test/r/rpl_rotate_logs.result: result update mysql-test/t/rpl_rotate_logs.test: comments and test update with the error code sql/slave.cc: A DBUG_PRINT sql/sql_repl.cc: Use ER_MASTER_INFO instead of custom message and zero error code (which display badly). --- mysql-test/t/rpl_rotate_logs.test | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index 9f7f44d7c28..c4dc5de38da 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -16,14 +16,20 @@ system cat /dev/null > var/slave-data/master.info; system chmod 000 var/slave-data/master.info; connection slave; drop table if exists t1, t2, t3, t4; +# START SLAVE will fail because it can't read the file (mode 000) (system error 13) --error 1201 slave start; system chmod 600 var/slave-data/master.info; +# It will fail again because the file is empty so the slave cannot get valuable +# info about how to connect to the master from it (failure in +# init_strvar_from_file() in init_master_info()). --error 1201 slave start; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT -# 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'; +# CHANGE MASTER will fail because it first parses master.info before changing it +# (so when master.info is bad, people have to use RESET SLAVE first). +--error 1201 +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'; -- cgit v1.2.1 From 85d2282e257ba47555fc05ef62dc70ece5b7a69e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Jun 2003 14:02:19 +0500 Subject: SCRUM Task ID 894: Backport default_week_format variable to 4.0 mysql-test/r/func_time.result: Backport default_week_format variable to 4.0 mysql-test/t/func_time.test: Backport default_week_format variable to 4.0 sql/mysqld.cc: Backport default_week_format variable to 4.0 sql/set_var.cc: Backport default_week_format variable to 4.0 sql/sql_class.h: Backport default_week_format variable to 4.0 sql/sql_yacc.yy: Backport default_week_format variable to 4.0 --- mysql-test/t/func_time.test | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 3057729ab96..a51869e7d76 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -39,6 +39,10 @@ select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', y select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3); select week(20001231,2),week(20001231,3); +set default_week_format = 2; +select week(20001231),week(20001231,2),week(20001231,0); +set default_week_format = 0; + select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); -- cgit v1.2.1 From 35c2b9df7564c95c38f04fc4dbc3bebea81a9a41 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 Jun 2003 20:03:59 +0300 Subject: SHOW GRANTS hided real grants when grants on both column and table (Bug 654) mysql-test/r/grant.result: Test of grant BUG mysql-test/t/grant.test: Test of grant BUG sql/sql_acl.cc: SHOW GRANTS hided real grants when grants on both column and table (Bug 654) Code cleanup (Bigger than intended because of editor problem) sql/sql_acl.h: Fixed grant bug --- mysql-test/t/grant.test | 34 ++++++++++++++++++++++++++++++++-- 1 file changed, 32 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index bd04b2e4c41..b18ae941c26 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -1,3 +1,7 @@ +--disable_warnings +drop table if exists t1; +--enable_warnings + # # Test that SSL options works properly # @@ -39,6 +43,32 @@ 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; -grant usage on test.* to user@localhost with grant option; -show grants for user@localhost; +grant usage on test.* to mysqltest_1@localhost with grant option; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; + +# +# Test what happens when you have same table and colum level grants +# +create table t1 (a int); +GRANT select,update,insert on t1 to mysqltest_1@localhost; +GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +REVOKE select (a), update on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +REVOKE insert,insert (a) on t1 from mysqltest_1@localhost; +GRANT references on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +drop table t1; -- cgit v1.2.1 From 3148d6f2a55211d02f63e4dc3a45f1d19ba536cd Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Jun 2003 10:53:21 +0500 Subject: Another bugfix for #615 Looks a bit nicer than previous one mysql-test/r/insert.result: Test results added mysql-test/t/insert.test: testcase for the bug added sql/field.h: Field::set_default fixed sql/field_conv.cc: Now we can just replace field->reset() with field->set_default() here --- mysql-test/t/insert.test | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 9b06b522028..be585157e63 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -50,6 +50,17 @@ insert into t1 values ('rts',NULL),('rts',NULL),('test',NULL); select * from t1; drop table t1; +# +#Test of behaviour with INSERT VALUES (NULL) +# + +create table t1 (id int NOT NULL DEFAULT 8); +-- error 1048 +insert into t1 values(NULL); +insert into t1 values (1), (NULL), (2); +select * from t1; +drop table t1; + # # Test of mysqld crash with fully qualified column names # @@ -60,3 +71,4 @@ use foo; create table t1 (c int); insert into foo.t1 set foo.t1.c = '1'; drop database foo; + -- cgit v1.2.1 From 8bed69fa9ec200925d9a9ec36419d2c5eecf9739 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Jun 2003 17:07:43 +0500 Subject: Test case for bug #666 mysql-test/r/func_str.result: test for bug #666 result mysql-test/t/func_str.test: test for bug #666 --- mysql-test/t/func_str.test | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 118de6cd01e..476629f98d3 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -125,3 +125,15 @@ CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id ( INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); DROP TABLE t1; + +CREATE TABLE t1 ( + wid int(10) unsigned NOT NULL auto_increment, + data_podp date default NULL, + status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', + PRIMARY KEY(wid), +); + +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +DROP TABLE t1; -- cgit v1.2.1 From 8abad215650386dedf0001812f44859009de74ef Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 17:15:33 +0500 Subject: Testcase for #674 added mysql-test/r/sel000100.result: Result of the testcase added mysql-test/t/sel000100.test: checking for bug #674 added --- mysql-test/t/sel000100.test | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/sel000100.test b/mysql-test/t/sel000100.test index cedb78b17e7..d587fa4ebd0 100644 --- a/mysql-test/t/sel000100.test +++ b/mysql-test/t/sel000100.test @@ -29,3 +29,18 @@ GROUP BY t1.id ORDER BY link; drop table t1,t2; + +# +# test case for #674 +# +CREATE TABLE t1 ( + html varchar(5) default NULL, + rin int(11) default '0', + out int(11) default '0' +) TYPE=MyISAM; + +INSERT INTO t1 VALUES ('1',1,0); + +SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; + +drop table t1; -- cgit v1.2.1 From 50159020c8f0f5b1f37ca29ccd81f086b7d32982 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 21:11:23 -0400 Subject: fixed bug with type of user variables (bug #551) mysql-test/r/user_var.result: added tests for user variables comparing (bug #551) mysql-test/t/user_var.test: added tests for user variables comparing (bug #551) BitKeeper/etc/logging_ok: Logging to logging@openlogging.org accepted --- mysql-test/t/user_var.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 70283446fb3..3103de2dac5 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -17,3 +17,9 @@ explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; explain select @vv1:=i from t1 where i=@vv1; explain select * from t1 where i=@vv1; drop table t1,t2; + +# Check types of variables +select @a:=10, @b:=1, @a > @b, @a < @b; +select @a:="10", @b:="1", @a > @b, @a < @b; +select @a:=10, @b:=2, @a > @b, @a < @b; +select @a:="10", @b:="2", @a > @b, @a < @b; -- cgit v1.2.1 From a3beaaa3af7af26aabadda6836618d32d6c90e51 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 26 Jun 2003 05:38:19 +0300 Subject: LEFT JOIN optimization: Change LEFT JOIN to normal join if possible mysql-test/r/select.result: Added test for LEFT JOIN optimization mysql-test/t/select.test: Added test for LEFT JOIN optimization sql/item.h: LEFT JOIN optimization sql/item_cmpfunc.cc: LEFT JOIN optimization sql/item_cmpfunc.h: LEFT JOIN optimization sql/item_func.cc: LEFT JOIN optimization sql/item_func.h: LEFT JOIN optimization sql/item_strfunc.cc: LEFT JOIN optimization sql/sql_base.cc: Heart of LEFT JOIN optimization --- mysql-test/t/select.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index de90eeec2e8..c2e451ea7f3 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1527,10 +1527,24 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25 # # Test of left join. # +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; +delete from t2 where fld1=999999; + +# +# Test left join optimization + +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +# Following can't be optimized +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; # # Joins with forms. -- cgit v1.2.1 From ec2c923d7d4259709be35dd606cf1b76088e7bcb Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 26 Jun 2003 21:30:53 +0200 Subject: bug with ranges and not_null_col=can_be_null_expr fixed mysql-test/r/range.result: new test added long lines reformatted mysql-test/t/range.test: new test added long lines reformatted --- mysql-test/t/range.test | 84 ++++++++++++++++++++++++++++++------------------- 1 file changed, 52 insertions(+), 32 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 278807eeea4..bb0e696ea11 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1,5 +1,5 @@ # -# Problem med range optimizer +# Problem with range optimizer # drop table if exists t1; @@ -10,7 +10,24 @@ CREATE TABLE t1 ( PRIMARY KEY (event_date,type,event_id) ); -INSERT INTO t1 VALUES ('1999-07-10',100100,24),('1999-07-11',100100,25),('1999-07-13',100600,0),('1999-07-13',100600,4),('1999-07-13',100600,26),('1999-07-14',100600,10),('1999-07-15',100600,16),('1999-07-15',100800,45),('1999-07-15',101000,47),('1999-07-16',100800,46),('1999-07-20',100600,5),('1999-07-20',100600,27),('1999-07-21',100600,11),('1999-07-22',100600,17),('1999-07-23',100100,39),('1999-07-24',100100,39),('1999-07-24',100500,40),('1999-07-25',100100,39),('1999-07-27',100600,1),('1999-07-27',100600,6),('1999-07-27',100600,28),('1999-07-28',100600,12),('1999-07-29',100500,41),('1999-07-29',100600,18),('1999-07-30',100500,41),('1999-07-31',100500,41),('1999-08-01',100700,34),('1999-08-03',100600,7),('1999-08-03',100600,29),('1999-08-04',100600,13),('1999-08-05',100500,42),('1999-08-05',100600,19),('1999-08-06',100500,42),('1999-08-07',100500,42),('1999-08-08',100500,42),('1999-08-10',100600,2),('1999-08-10',100600,9),('1999-08-10',100600,30),('1999-08-11',100600,14),('1999-08-12',100600,20),('1999-08-17',100500,8),('1999-08-17',100600,31),('1999-08-18',100600,15),('1999-08-19',100600,22),('1999-08-24',100600,3),('1999-08-24',100600,32),('1999-08-27',100500,43),('1999-08-31',100600,33),('1999-09-17',100100,37),('1999-09-18',100100,37),('1999-09-19',100100,37),('2000-12-18',100700,38); +INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), +('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), +('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), +('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), +('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), +('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), +('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), +('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), +('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), +('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), +('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), +('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), +('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), +('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), +('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), +('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), +('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), +('1999-09-19',100100,37), ('2000-12-18',100700,38); select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; @@ -25,35 +42,21 @@ CREATE TABLE t1 ( ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); -INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,111,0,'1999-03-23'); -INSERT INTO t1 VALUES (1,1999,222,0,'1999-03-23'); -INSERT INTO t1 VALUES (3,1999,33,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,32,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,31,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,30,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,29,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,28,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,40,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,41,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,42,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,46,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,47,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,48,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,49,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,50,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,51,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,200,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,52,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,53,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,54,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,55,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,56,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,57,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,58,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,59,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,60,0,'1999-07-01'); -INSERT INTO t1 VALUES (3,1999,35,0,'1999-07-12'); +INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), + (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), + (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), + (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), + (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), + (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), + (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), + (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), + (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), + (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), + (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), + (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), + (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), + (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), + (3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; check table t1; repair table t1; @@ -67,7 +70,12 @@ CREATE TABLE t1 ( KEY parent_id (parent_id), KEY level (level) ); -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); +INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), +(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), +(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), +(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), +(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), +(19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; # The following select returned 0 rows in 3.23.8 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; @@ -163,3 +171,15 @@ select count(*) from t1 where art = 'j' or art = 'J'; select count(*) from t1 where art = 'j'; select count(*) from t1 where art = 'J'; drop table t1; + +create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); +insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), + (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), + (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), + (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), + (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), + (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), + (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); +select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; +drop table t1; + -- cgit v1.2.1 From c2cb9b97be721d07e9cb2a27076c8beaf22f304b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Jun 2003 03:04:54 +0300 Subject: Added thread variable max_seeks_for_key Change optimizer to prefer key lookups before table scan Change table scans to be done after tables with constrains on scanned table mysql-test/r/distinct.result: Update results mysql-test/r/group_by.result: Update results mysql-test/r/heap.result: Update results mysql-test/r/join.result: Update results mysql-test/r/key_diff.result: Update results mysql-test/r/myisam.result: Update results mysql-test/r/order_by.result: Update results mysql-test/r/select_safe.result: Update results mysql-test/t/distinct.test: Change test to be repeatable mysql-test/t/join.test: Change test to be repeatable mysql-test/t/select_safe.test: Add tests for MAX_SEEKS_FOR_KEY sql/handler.h: Optimize structure for 64 bit machines (and to avoid problems with gdb) sql/item_cmpfunc.cc: Initialize not_null_tables_cache properly sql/mysqld.cc: Added max_seeks_for_key sql/set_var.cc: Added max_seeks_for_key sql/sql_class.h: Added max_seeks_for_key sql/sql_select.cc: Added max_seeks_for_key Change optimizer to prefer key lookups before table scan. Change table scans to be done after tables with constrains on scanned table --- mysql-test/t/distinct.test | 2 +- mysql-test/t/join.test | 5 +---- mysql-test/t/select_safe.test | 15 +++++++++++++-- 3 files changed, 15 insertions(+), 7 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 7f75b6b1687..ecce2409571 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -60,7 +60,7 @@ drop table t1; # CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); -INSERT INTO t1 VALUES (1,1),(2,1); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); CREATE TABLE t2 (a int(10) unsigned not null, key (A)); INSERT INTO t2 VALUES (1),(2); CREATE TABLE t3 (a int(10) unsigned, key(A), b text); diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 08cc5731723..19e04d2aa7e 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -98,10 +98,7 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES (3,2,11,12,5400,7800); INSERT INTO t2 VALUES (4,2,25,12,6500,11200); INSERT INTO t2 VALUES (5,1,37,6,10000,12000); - -select a.id, b.category as catid, b.state as stateid, b.county as -countyid from t1 a, t2 b where (a.token = -'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); +select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b where (a.token = 'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index a085cfee29d..049a35aa355 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -4,7 +4,7 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int auto_increment primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20), key(b)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; update t1 set b="a" where a=1; @@ -12,7 +12,7 @@ delete from t1 where a=1; insert into t1 values(1,"test"),(2,"test2"); SELECT SQL_BUFFER_RESULT * from t1; update t1 set b="a" where a=1; -select 1 from t1,t1 as t2,t1 as t3,t1 as t4; +select 1 from t1,t1 as t2,t1 as t3; # The following should give errors: --error 1175 @@ -49,6 +49,17 @@ SELECT * from t1; SET MAX_JOIN_SIZE=DEFAULT; SELECT * from t1; +# +# Test MAX_SEEKS_FOR_KEY +# +SELECT @@MAX_SEEKS_FOR_KEY; +analyze table t1; +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +explain select * from t1,t1 as t2 where t1.b=t2.b; +set MAX_SEEKS_FOR_KEY=1; +explain select * from t1,t1 as t2 where t1.b=t2.b; +SET MAX_SEEKS_FOR_KEY=DEFAULT; + drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; -- cgit v1.2.1 From 5768f4a34ed7218c61ed34cf5b76eee66b715224 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Jun 2003 06:40:24 +0300 Subject: Fixed testcase after last changes --- mysql-test/t/select_safe.test | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index 049a35aa355..206f911d028 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -4,7 +4,7 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int auto_increment primary key, b char(20), key(b)); +create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; update t1 set b="a" where a=1; @@ -36,13 +36,14 @@ delete from t1 where a+0=1 limit 2; # Test SQL_BIG_SELECTS +alter table t1 add key b (b); SET MAX_JOIN_SIZE=2; SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); --error 1104 -SELECT * from t1; +SELECT * from t1 order by a; SET SQL_BIG_SELECTS=1; -SELECT * from t1; +SELECT * from t1 order by a; SET MAX_JOIN_SIZE=2; --error 1104 SELECT * from t1; -- cgit v1.2.1 From e224c1238d58c99b10ff6f55f816bee09be146c8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Jun 2003 12:58:43 +0300 Subject: SET CHARACTER SET DEFAULT didn't work (Bug #462) BUILD/compile-pentium-debug-max: Removed -DBIG_TABLES because of problems with gdb 5.3 sql/set_var.cc: SET CHARACTER SET DEFAULT didn't work sql/set_var.h: SET CHARACTER SET DEFAULT didn't work --- mysql-test/t/convert.test | 11 +++++++++++ 1 file changed, 11 insertions(+) create mode 100644 mysql-test/t/convert.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/convert.test b/mysql-test/t/convert.test new file mode 100644 index 00000000000..f26ef3a8c72 --- /dev/null +++ b/mysql-test/t/convert.test @@ -0,0 +1,11 @@ +# Test of character set conversions + +# Test that SET DEFAULT works + +select @@convert_character_set; +select @@global.convert_character_set; +show variables like "%convert_character_set%"; +SET CHARACTER SET cp1251_koi8; +select @@convert_character_set; +SET CHARACTER SET DEFAULT; +select @@convert_character_set; -- cgit v1.2.1 From caddb5ea53cbde18bf0a915bc4867fecd93c55d5 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Jun 2003 21:15:51 +0300 Subject: Fixed 'Unknown error' when doing ORDER BY on reference table which was used with NULL value on NOT NULL column. (Bug #479) mysql-test/r/order_by.result: Update of results mysql-test/t/order_by.test: Test of new code sql/sql_select.cc: Fixed problem with lookup on NULL --- mysql-test/t/order_by.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 16094206745..d6be4c1c45c 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -247,3 +247,26 @@ 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; + +# +# Problem with lookup on NULL (Bug 479) +# + +CREATE TABLE t1 ( + t1_id int(10) unsigned NOT NULL default '0', + ref_id int(10) unsigned default NULL, + PRIMARY KEY (t1_id) +) TYPE=MyISAM; +INSERT INTO t1 VALUES (2401,14590),(2425,NULL); +CREATE TABLE t2 ( + t2_id int(10) unsigned NOT NULL default '0', + ref_id int(10) unsigned NOT NULL default '0', + str varchar(20) default NULL, + PRIMARY KEY (t2_id), + KEY ref_id (ref_id) +) TYPE=MyISAM; +INSERT INTO t2 VALUES (112633,14590,'t2 112633'),(112634,14590,'t2 112634'),(113166,14641,'t2 113166'),(113167,14641,'t2 113167'),(113168,14641,'t2 113168'),(113169,14641,'t2 113169'),(113170,14641,'t2 113170'),(113171,14641,'t2 113171'),(113172,14641,'t2 113172'),(113173,14641,'t2 113173'),(113174,14641,'t2 113174'),(113175,14641,'t2 113175'),(113436,14674,'t2 113436'),(113437,14674,'t2 113437'),(113486,14689,'t2 113486'),(113487,14689,'t2 113487'),(113488,14689,'t2 113488'),(113489,14689,'t2 113489'),(113504,14795,'t2 113504'),(115396,15024,'t2 115396'),(115397,15024,'t2 115397'); +SELECT t2_id, str FROM t1, t2 WHERE t1_id = 2401 AND t1.ref_id = t2.ref_id ORDER BY str, t2_id; +# This one gave an error +SELECT t2_id, str FROM t1, t2 WHERE t1_id = 2425 AND t1.ref_id = t2.ref_id ORDER BY str, t2_id; +DROP TABLE t1,t2; -- cgit v1.2.1 From bbf8ebe87d7c3d3d07f56457168e8faa943f7d64 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 Jul 2003 13:29:55 +0300 Subject: Code/testcase cleanups mysql-test/r/insert_select.result: Fixed results mysql-test/r/join_outer.result: New test case for inner join table dependency mysql-test/t/insert_select.test: Remove not used drop tables. Fixed table names mysql-test/t/join_outer.test: New test case for inner join table dependency sql/slave.cc: Simple optimizations --- mysql-test/t/insert_select.test | 23 ++++++++++------------- mysql-test/t/join_outer.test | 12 ++++++++++++ 2 files changed, 22 insertions(+), 13 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 695f891f678..3b51168269d 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -12,13 +12,13 @@ insert into t2 (payoutID) SELECT payoutID+10 FROM t1; insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; select * from t2; drop table t1,t2; + # # bug in bulk insert optimization # test case by Fournier Jocelyn # -DROP TABLE IF EXISTS crash1,crash2; -CREATE TABLE `crash1` ( +CREATE TABLE `t1` ( `numeropost` bigint(20) unsigned NOT NULL default '0', `icone` tinyint(4) unsigned NOT NULL default '0', `numreponse` bigint(20) unsigned NOT NULL auto_increment, @@ -34,7 +34,7 @@ CREATE TABLE `crash1` ( KEY `numreponse` (`numreponse`) ) TYPE=MyISAM; -CREATE TABLE `crash2` ( +CREATE TABLE `t2` ( `numeropost` bigint(20) unsigned NOT NULL default '0', `icone` tinyint(4) unsigned NOT NULL default '0', `numreponse` bigint(20) unsigned NOT NULL auto_increment, @@ -50,30 +50,27 @@ CREATE TABLE `crash2` ( KEY `numreponse` (`numreponse`) ) TYPE=MyISAM; -INSERT INTO crash2 +INSERT INTO t2 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES (9,1,56,'test','joce','2001-07-25 13:50:53' ,3649052399,0); -INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) -SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2 +INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2 WHERE numeropost=9 ORDER BY numreponse ASC; show variables like '%bulk%'; -INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) -SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2 +INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2 WHERE numeropost=9 ORDER BY numreponse ASC; -DROP TABLE IF EXISTS crash1,crash2; - +DROP TABLE t1,t2; -# Addendum by Guilhem: # Check if a partly-completed INSERT SELECT in a MyISAM table goes # into the binlog -drop table if exists t1; -drop table if exists t2; + create table t1(a int, unique(a)); insert into t1 values(2); create table t2(a int); diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index e172d54194e..ee7d55d2a4e 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -425,3 +425,15 @@ insert into t2 values(2),(3); insert into t3 values(2),(4); select * from t1 natural left join t2 natural left join t3; drop table t1,t2,t3; + +# +# Test of USING +# +create table t1 (f1 integer,f2 integer,f3 integer); +create table t2 (f2 integer,f4 integer); +create table t3 (f3 integer,f5 integer); +--error 1054 +select * from t1 + left outer join t2 using (f2) + left outer join t3 using (f3); +drop table t1,t2,t3; -- cgit v1.2.1 From 12d879c912543fffa77cecfda799bc20ddd35df5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 00:10:47 +0300 Subject: Lot's of clean-ups and fixes for 4.0.14. include/my_global.h: CONFIG_SMP mysql-test/r/grant.result: Test cases for bug fixes mysql-test/r/insert_select.result: Test cases for bug fixes mysql-test/r/union.result: Test cases for bug fixes mysql-test/t/grant.test: Test cases for bug fixes mysql-test/t/insert_select.test: Test cases for bug fixes mysql-test/t/union.test: Test cases for bug fixes sql/sql_acl.cc: Fix for granting global privileges on db level sql/sql_parse.cc: Fix for INSERT... SELECT sql/sql_select.cc: removing a fix sql/sql_union.cc: P --- mysql-test/t/grant.test | 2 ++ mysql-test/t/insert_select.test | 16 ++++++++++++++++ mysql-test/t/union.test | 13 +++++++++++++ 3 files changed, 31 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index b18ae941c26..a93339a3ad6 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -72,3 +72,5 @@ delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; drop table t1; +--error 1054 +GRANT FILE on mysqltest.* to mysqltest_1@localhost; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 695f891f678..2bebd4e86dd 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -87,3 +87,19 @@ let $VERSION=`select version()`; --replace_result $VERSION VERSION show binlog events; drop table t1, t2; +drop table if exists t1, t2; +create table t1 (a int not null); +create table t2 (a int not null); +insert into t1 values (1); +insert into t1 values (a+2); +insert into t1 values (a+3); +insert into t1 values (4),(a+5); +insert into t1 select * from t1; +select * from t1; +insert into t1 select * from t1 as t2; +select * from t1; +insert into t2 select * from t1 as t2; +select * from t1; +insert into t1 select t2.a from t1,t2; +select * from t1; +drop table t1,t2; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 7262b8f78d7..5f7eba83755 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -23,6 +23,8 @@ 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(); +select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; +select found_rows(); # # Test some error conditions with UNION @@ -157,3 +159,14 @@ SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"); drop table t1,t2,t3,t4,t5,t6; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +create table t3 select a,b from t1 union select a,b from t2; +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t4 select a,b from t1 union select a,b from t2; +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +select * from t3; +select * from t4; +drop table t1,t2,t3,t4; -- cgit v1.2.1 From 417c571bb345475bd1794264b18484f874910b85 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 01:24:05 +0300 Subject: Some fixes for CREATE / INSERT FROM UNIO >.. --- mysql-test/t/grant.test | 2 +- mysql-test/t/union.test | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index a93339a3ad6..5b20354336c 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -72,5 +72,5 @@ delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; drop table t1; ---error 1054 +--error 1221 GRANT FILE on mysqltest.* to mysqltest_1@localhost; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 5f7eba83755..dcf2f79e5bf 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -164,9 +164,9 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); create table t3 select a,b from t1 union select a,b from t2; -create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +create table t4 (select a,b from t1) union (select a,b from t2); insert into t4 select a,b from t1 union select a,b from t2; -insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t3 (select a,b from t1) union (select a,b from t2); select * from t3; select * from t4; drop table t1,t2,t3,t4; -- cgit v1.2.1 From 7ad2b84907b4bc5d40551f85c8cdf6d3af1fc29a Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 13:15:45 +0300 Subject: innodb.test, innodb.result: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) mysql-test/r/innodb.result: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) mysql-test/t/innodb.test: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) --- mysql-test/t/innodb.test | 1 - 1 file changed, 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index cf203d87c8b..05b4370d0e0 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -830,7 +830,6 @@ insert into t1 (a) select b from t2; select count(*) from t1; explain select * from t1 where c between 1 and 10000; update t1 set c=a; -explain select * from t1 where c between 1 and 10000; drop table t1,t2; # -- cgit v1.2.1 From 66237f45fb325bbfd3e9b21eaca924920fdc2007 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 14:57:40 +0300 Subject: Fix for a problem with : CREATE / INSERT ... (SELECT ...) UNION (SELECT ...) LIMIT; --- mysql-test/t/union.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index dcf2f79e5bf..5f7eba83755 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -164,9 +164,9 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); create table t3 select a,b from t1 union select a,b from t2; -create table t4 (select a,b from t1) union (select a,b from t2); +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; insert into t4 select a,b from t1 union select a,b from t2; -insert into t3 (select a,b from t1) union (select a,b from t2); +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; select * from t3; select * from t4; drop table t1,t2,t3,t4; -- cgit v1.2.1 From 7bb4166a9aa53755b49de865cc639afa9098de7c Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 17:12:50 +0200 Subject: bug #504: in "datetime op int" comparison int wasn't converted to datetime --- mysql-test/t/type_datetime.test | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index f5103958979..51b65d00243 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -36,8 +36,11 @@ drop table t1; # create table t1 (id int, dt datetime); -insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"); +insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); +create index dt on t1 (dt); +select * from t1 where dt > 20021020; +select * from t1 ignore index (dt) where dt > 20021020; drop table t1; # -- cgit v1.2.1 From 0b37eae10748dc4399c8bf0abbb0eadc37333744 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 02:08:33 +0300 Subject: innodb.result, innodb.test: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates mysql-test/t/innodb.test: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates mysql-test/r/innodb.result: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates --- mysql-test/t/innodb.test | 16 ---------------- 1 file changed, 16 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 05b4370d0e0..7b36a675a31 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -46,7 +46,6 @@ select * from t1 where parent_id=102; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; optimize table t1; -show keys from t1; drop table t1; # @@ -84,7 +83,6 @@ select * from t1; create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; -show keys from t1; drop table t1; @@ -222,7 +220,6 @@ drop table t1; CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); -show index from t1; drop table t1; # @@ -396,7 +393,6 @@ create table t1 (a varchar(100) not null, primary key(a), b int not null) type=i insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; optimize table t1; -show keys from t1; drop table t1; # @@ -441,7 +437,6 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) type = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); -explain select * from t1 where a > 0 and a < 50; drop table t1; # @@ -519,8 +514,6 @@ insert into mysqltest.t3 values(1); commit; drop database mysqltest; # Don't check error message ---error 12,12 -show tables from mysqltest; # # Test truncate table with and without auto_commit @@ -558,14 +551,6 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); -explain select * from t1 order by a; -explain select * from t1 order by b; -explain select * from t1 order by c; -explain select a from t1 order by a; -explain select b from t1 order by b; -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; # @@ -828,7 +813,6 @@ insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; -explain select * from t1 where c between 1 and 10000; update t1 set c=a; drop table t1,t2; -- cgit v1.2.1 From d433fe5580dafbc1fba0ebe9dc76217d1b0e5144 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 10:42:13 +0500 Subject: Proposed bugfix for #717 Current table is placed into read_tables set of the current join_tab->select so get_mm_parts function thinks that current table's record is read and tries to calculate WHERE condition with the fields of the record. Result of these calculations is unpredictable. Looks funny - outcome of the SELECT depends on the queries executed before. Anyway i think we should have testcase on this part of the code. mysql-test/r/sel000001.result: appropriate result added mysql-test/t/sel000001.test: testcase added sql/sql_select.cc: I think we should count current table out of read_tables set --- mysql-test/t/sel000001.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/sel000001.test b/mysql-test/t/sel000001.test index 77355f8d535..ad4af1f92fb 100644 --- a/mysql-test/t/sel000001.test +++ b/mysql-test/t/sel000001.test @@ -16,3 +16,16 @@ CREATE TABLE t1 (s CHAR(20) PRIMARY KEY, id INT); INSERT INTO t1 VALUES ('cat', 1), ('mouse', 3), ('dog', 2), ('snake', 77); SELECT s, id FROM t1 WHERE s = 'mouse'; drop table t1; + +# +#test for bug #717 +# +CREATE TABLE t1 ( + node int(11) NOT NULL default '0', + maxchild int(11) NOT NULL default '0', + PRIMARY KEY (`node`) +); +INSERT INTO t1 (node, maxchild) VALUES (4,4),(5,5),(1,244); +SELECT * FROM t1 g1, t1 g2 + WHERE g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild; +DROP TABLE t1; -- cgit v1.2.1 From 2373397e13d4004816b61df38a234906eb361e0d Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 11:55:36 +0300 Subject: Cleaner implementation if INSERT ... SELECT with same tables Tests cleanup (put drop database first in tests) client/mysql.cc: Cleanup of code in last pull include/config-win.h: Remove HAVE_CHSIZE on windows as it's not 64 bit clean include/my_global.h: Portability fix mysql-test/r/drop.result: Clean up results mysql-test/r/flush.result: Clean up results mysql-test/r/grant_cache.result: Clean up results mysql-test/r/innodb.result: Clean up results mysql-test/r/insert_select.result: Clean up results mysql-test/r/merge.result: Clean up results mysql-test/r/query_cache.result: Clean up results mysql-test/t/drop.test: Clean up tests mysql-test/t/flush.test: Clean up tests mysql-test/t/grant_cache.test: Clean up tests mysql-test/t/innodb.test: Clean up tests mysql-test/t/insert_select.test: Added more tests mysql-test/t/merge.test: Test of bug 515 mysql-test/t/query_cache.test: Clean up tests mysql-test/t/symlink.test: Clean up tests sql/mysql_priv.h: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_lex.h: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_list.h: Indentation cleanup sql/sql_parse.cc: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_yacc.yy: Cleaner implementation if INSERT ... SELECT with same tables --- mysql-test/t/ctype_cp1251-master.opt | 2 ++ mysql-test/t/ctype_cp1251.test | 17 +++++++++++++++++ mysql-test/t/drop.test | 2 -- mysql-test/t/flush.test | 4 ++-- mysql-test/t/grant_cache.test | 5 ++++- mysql-test/t/innodb.test | 2 ++ mysql-test/t/insert_select.test | 22 ++++++++++++++++++++++ mysql-test/t/merge.test | 13 +++++++++++++ mysql-test/t/query_cache.test | 3 ++- mysql-test/t/symlink.test | 25 +++++++++++++------------ 10 files changed, 77 insertions(+), 18 deletions(-) create mode 100644 mysql-test/t/ctype_cp1251-master.opt create mode 100644 mysql-test/t/ctype_cp1251.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/ctype_cp1251-master.opt b/mysql-test/t/ctype_cp1251-master.opt new file mode 100644 index 00000000000..af089d9f176 --- /dev/null +++ b/mysql-test/t/ctype_cp1251-master.opt @@ -0,0 +1,2 @@ +--default-character-set=cp1251 --new + diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test new file mode 100644 index 00000000000..fffade35389 --- /dev/null +++ b/mysql-test/t/ctype_cp1251.test @@ -0,0 +1,17 @@ +# Test of charset cp1251 + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Test problem with LEFT() (Bug #514) +# + +create table t1 (a varchar(10) not null); +insert into t1 values ("a"),("ab"),("abc"); +select * from t1; +select a, left(a,1) as b from t1; +select a, left(a,1) as b from t1 group by a; +SELECT DISTINCT RIGHT(a,1) from t1; +drop table t1; diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index c92f2b1f3b9..2f3fa99bac0 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -1,5 +1,4 @@ drop table if exists t1; -drop table if exists t1; --error 1051; drop table t1; create table t1(n int); @@ -26,7 +25,6 @@ create database mysqltest; drop database mysqltest; # test drop/create database and FLUSH TABLES WITH READ LOCK -drop database if exists mysqltest; flush tables with read lock; --error 1209,1223; create database mysqltest; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 540aa4235cc..7754fc2e197 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -10,6 +10,8 @@ connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; drop table if exists t1; +drop database if exists mysqltest; + create temporary table t1(n int not null primary key); drop table if exists t2; create table t2(n int); @@ -44,7 +46,6 @@ reap; #test if drop database will wait until we release the global read lock connection con1; -drop database if exists mysqltest; create database mysqltest; create table mysqltest.t1(n int); insert into mysqltest.t1 values (23); @@ -66,4 +67,3 @@ connection con2; insert into t1 values (345); select * from t1; drop table t1; - diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test index 7dfbb3b8d83..95989d1ee99 100644 --- a/mysql-test/t/grant_cache.test +++ b/mysql-test/t/grant_cache.test @@ -1,9 +1,12 @@ -- source include/have_query_cache.inc +drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; + # # Test grants with query cache # -drop table if exists test.t1,mysqltest.t1,mysqltest.t2; + reset query cache; flush status; connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index cf203d87c8b..509cae9c1dc 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -5,6 +5,8 @@ # drop table if exists t1,t2,t3; +drop database if exists mysqltest; + 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; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 5c7fc57262b..d115e9c1af6 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -85,6 +85,11 @@ let $VERSION=`select version()`; show binlog events; drop table t1, t2; drop table if exists t1, t2; + +# +# Test of insert ... select from same table +# + create table t1 (a int not null); create table t2 (a int not null); insert into t1 values (1); @@ -99,4 +104,21 @@ insert into t2 select * from t1 as t2; select * from t1; insert into t1 select t2.a from t1,t2; select * from t1; +--error 1066 +insert into t1 select * from t1,t1; +drop table t1,t2; + +# +# test replace ... select +# + +create table t1 (a int not null primary key, b char(10)); +create table t2 (a int not null, b char(10)); +insert into t1 values (1,"t1:1"),(3,"t1:3"); +insert into t2 values (2,"t2:2"), (3,"t2:3"); +--error 1062 +insert into t1 select * from t2; +select * from t1; +replace into t1 select * from t2; +select * from t1; drop table t1,t2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index e2cd8b57b7e..b625e780f1f 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -237,3 +237,16 @@ EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; DROP TABLE IF EXISTS t1, t2; +# +# Test of ORDER BY DESC on key (Bug #515) +# + +create table t1 (x int, y int, index xy(x, y)); +create table t2 (x int, y int, index xy(x, y)); +create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2); +insert into t1 values(1, 2); +insert into t2 values(1, 3); +select * from t3 where x = 1 and y < 5 order by y; +# Bug is that followng query returns empty set while it must be same as above +select * from t3 where x = 1 and y < 5 order by y desc; +drop table t1,t2,t3; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index f0f3063c00d..fe258b2c7e1 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -11,6 +11,7 @@ flush query cache; # This crashed in some versions reset query cache; flush status; drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; +drop database if exists mysqltest; # # First simple test @@ -241,7 +242,7 @@ drop table t1,t2; # # noncachable ODBC work around (and prepare cache for drop database) # -create database if not exists mysqltest; +create database mysqltest; create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)); insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 586761921b8..d8bba12b645 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -3,10 +3,12 @@ disable_query_log; show variables like "have_symlink"; enable_query_log; +drop table if exists t1,t2,t7,t8,t9; +drop database if exists mysqltest; + # # First create little data to play with # -drop table if exists t1,t2,t7,t8,t9; create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)); create table t2 (a int not null auto_increment, b char(16) not null, primary key (a)); insert into t1 (b) values ("test"),("test1"),("test2"),("test3"); @@ -64,26 +66,25 @@ create table t1 (a int not null auto_increment, b char(16) not null, primary key # Check that we cannot link over a table from another database. -drop database if exists test_mysqltest; -create database test_mysqltest; +create database mysqltest; --error 1,1 -create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; --error 1103,1103 -create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; --error 1,1 -eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; --error 1,1 -eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; enable_query_log; -# Check moving table t9 from default database to test_mysqltest; +# Check moving table t9 from default database to mysqltest; # In this case the symlinks should be removed. -alter table t9 rename test_mysqltest.t9; -select count(*) from test_mysqltest.t9; -show create table test_mysqltest.t9; -drop database test_mysqltest; +alter table t9 rename mysqltest.t9; +select count(*) from mysqltest.t9; +show create table mysqltest.t9; +drop database mysqltest; -- cgit v1.2.1 From f7f191904b91136fa1f79bf11bb4ee3400d8a48f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 16:48:47 +0500 Subject: Testcase for #628 mysql-test/r/func_misc.result: Appropriate result mysql-test/t/func_misc.test: Test case added --- mysql-test/t/func_misc.test | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index be64c170fa1..d15c26279ec 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -12,3 +12,8 @@ select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); # Test for core dump with nan # select length(format('nan', 2)) > 0; + +# +# Test for bug #628 +# +select concat("$",format(2500,2)); -- cgit v1.2.1 From 6d1f44e4fdc3eb89ae5a6e5c21660ab5c233819f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 18:21:59 +0500 Subject: Fix and test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. heap/hp_open.c: Fix for the bug #775: SELECT misses rows in indexed HEAP table columns. mysql-test/r/heap.result: Test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. mysql-test/t/heap.test: Test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. --- mysql-test/t/heap.test | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index d1e647ae182..b2de58ca083 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -129,6 +129,15 @@ SELECT * FROM t1 WHERE b<=>NULL; INSERT INTO t1 VALUES (1,3); DROP TABLE t1; +CREATE TABLE t1 ( + a int default NULL, + key a (a) +) TYPE=HEAP; +INSERT INTO t1 VALUES (10), (10), (10); +EXPLAIN SELECT * FROM t1 WHERE a=10; +SELECT * FROM t1 WHERE a=10; +DROP TABLE t1; + # # Test when deleting all rows # -- cgit v1.2.1 From b170d899e8772ea513a04fe8ae9c1f1ec125ea1c Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 19:01:01 +0500 Subject: Test case for #570 added mysql-test/r/alias.result: Appropriate test result mysql-test/t/alias.test: Testcase for #570 --- mysql-test/t/alias.test | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 5c6813d93aa..0dafc62430e 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -62,3 +62,22 @@ INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05 SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; drop table t1; + +# +# test case for #570 +# + +CREATE TABLE t1 ( + AUFNR varchar(12) NOT NULL default '', + PLNFL varchar(6) NOT NULL default '', + VORNR varchar(4) NOT NULL default '', + xstatus_vor smallint(5) unsigned NOT NULL default '0', + +); + +INSERT INTO t1 VALUES ('40004712','000001','0010',9); +INSERT INTO t1 VALUES ('40004712','000001','0020',0); + +UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = +"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr +ASC LIMIT 1; -- cgit v1.2.1 From 8d7eb4a0e5b7d71e09c689fcc0318382c780302e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 20:57:51 +0500 Subject: Proposed fix for #751 Fields in key_expr (mysql_ha_read) wasn't linked to tables. Hmm. How did it work at all? mysql-test/r/alias.result: It's better to delete table after the test mysql-test/r/handler.result: appropriate result mysql-test/t/alias.test: it's better to drop table after test mysql-test/t/handler.test: test case for #751 sql/sql_handler.cc: fix_fields called --- mysql-test/t/alias.test | 2 ++ mysql-test/t/handler.test | 10 ++++++++++ 2 files changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 0dafc62430e..57b46181fa1 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -81,3 +81,5 @@ INSERT INTO t1 VALUES ('40004712','000001','0020',0); UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = "40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr ASC LIMIT 1; + +drop table t1; diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 7020a4ab3d3..b1902c213bf 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -80,3 +80,13 @@ alter table t1 type=MyISAM; handler t2 read first; drop table t1; +# +#test for #751 +# +create table t1(a int, index(a)); +insert into t1 values (1), (2), (3); +handler t1 open; +--error 1054 +handler t1 read a=(W); +drop table t1; + -- cgit v1.2.1 From 33c416f67e6ba11ca1e21888be6cd9c0cc647813 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 23:24:23 +0500 Subject: Fix for #739 check for negative count in RPAD added mysql-test/r/func_str.result: Appropriate result mysql-test/t/func_str.test: testcase added sql/item_strfunc.cc: we return NULL if count < 0 --- mysql-test/t/func_str.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 476629f98d3..5ea3654134b 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -137,3 +137,17 @@ INSERT INTO t1 VALUES (8,NULL,'real'); INSERT INTO t1 VALUES (9,NULL,'nowy'); SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; DROP TABLE t1; + +# +# test for #739 + +CREATE TABLE t1 ( + title text +) TYPE=MyISAM; + +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); + +SELECT CONCAT("",RPAD("",(55 - LENGTH(title)),".")) from t1; + +DROP TABLE t1; -- cgit v1.2.1 From 4b50f4f252bf28b628475339e320d7a927c90459 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 12:55:25 +0200 Subject: handler should be used with constant expressions only (or rand) --- mysql-test/t/handler.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index b1902c213bf..39ceab7ab22 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -88,5 +88,7 @@ insert into t1 values (1), (2), (3); handler t1 open; --error 1054 handler t1 read a=(W); +--error 1210 +handler t1 read a=(a); drop table t1; -- cgit v1.2.1 From 30341167c50dd59ecdd11100b0d5e2fec14ca02b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 17:15:16 +0500 Subject: fix and test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows mysql-test/r/handler.result: test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows mysql-test/t/handler.test: test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows sql/sql_handler.cc: fix for the bug #787: HANDLER without INDEX doesn't work with deleted rows --- mysql-test/t/handler.test | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 39ceab7ab22..737b220c805 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -80,6 +80,21 @@ alter table t1 type=MyISAM; handler t2 read first; drop table t1; +# +# test case for the bug #787 +# + +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +handler t1 read first limit 1,1; +handler t1 read first limit 2,2; +delete from t1 limit 3; +handler t1 read first; +drop table t1; + # #test for #751 # -- cgit v1.2.1 From 421a462990e42eca893d902aeb590acdb64b4a51 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 5 Jul 2003 16:29:11 +0300 Subject: missing test case for DISTINCT .. LEFT .. --- mysql-test/t/distinct.test | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index ecce2409571..1cc692bd886 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -273,4 +273,11 @@ 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; +CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; +INSERT INTO t1 VALUES (1, 'aaaaa'); +INSERT INTO t1 VALUES (3, 'aaaaa'); +INSERT INTO t1 VALUES (2, 'eeeeeee'); +select distinct left(name,1) as name from t1; +drop table t1; + -- cgit v1.2.1 From 27601fc5ee2366ddb7926a82aa50058dc9548e0e Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 6 Jul 2003 17:59:54 +0200 Subject: WL#912 (more user control on relay logs): FLUSH LOGS now rotates relay logs, and a new variable max_relay_log_size. Plus a very small bit of code cleaning. libmysqld/lib_sql.cc: open_log has no default arguments anymore. mysql-test/r/rpl_flush_log_loop.result: result update now that FLUSH LOGS rotates relay logs. mysql-test/r/rpl_log.result: result update now that FLUSH LOGS rotates relay logs. mysql-test/r/rpl_rotate_logs.result: result update now that max_binlog_size is 4096. mysql-test/t/rpl_rotate_logs-master.opt: now max_binlog_size must be a multiple of 4096 (see change in mysqld.cc) sql/log.cc: Got rid of default arguments of various MYSQL_LOG methods (the default arguments made code reading uneasy). Set max_size in ::init(). New function set_max_size() to set max_size of a MYSQL_LOG on-the-fly. More DBUG info. sql/mysql_priv.h: no defaults in open_log(). New variables max_relay_log_size. sql/mysqld.cc: New variable and option max_relay_log_size. max_binlog_size and max_relay_log_size are multiples of IO_SIZE. No more default arguments for log functions. sql/set_var.cc: New variable max_relay_log_size. If it is 0, then max_binlog_size will apply to relay logs. When one of these variables is changed, fix_max_%log_size is called to update max_size of the binary and/or relay logs. sql/slave.cc: New function rotate_relay_log(). sql/slave.h: New function rotate_relay_log(). sql/sql_class.h: New member max_size of MYSQL_LOG (for automatic rotation). New method set_max_size() for setting on-the-fly. sql/sql_parse.cc: Flush the relay log in FLUSH LOGS. --- mysql-test/t/rpl_max_relay_size.test | 81 +++++++++++++++++++++++++++++++++ mysql-test/t/rpl_rotate_logs-master.opt | 2 +- 2 files changed, 82 insertions(+), 1 deletion(-) create mode 100644 mysql-test/t/rpl_max_relay_size.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test new file mode 100644 index 00000000000..4befe4024fc --- /dev/null +++ b/mysql-test/t/rpl_max_relay_size.test @@ -0,0 +1,81 @@ +# Test of options max_binlog_size and max_relay_log_size and +# how they act (if max_relay_log_size == 0, use max_binlog_size +# for relay logs too). +# Test of manual relay log rotation with FLUSH LOGS. + +source include/master-slave.inc; +connection slave; +stop slave; +connection master; +# Generate a big enough master's binlog to cause relay log rotations +create table t1 (a int); +let $1=800; +disable_query_log; +begin; +while ($1) +{ +# eval means expand $ expressions + eval insert into t1 values( $1 ); + dec $1; +} +enable_query_log; +drop table t1; +save_master_pos; +connection slave; +reset slave; +set global max_binlog_size=8192; +set global max_relay_log_size=8192-1; # mapped to 4096 +select @@global.max_relay_log_size; +start slave; +sync_with_master; +show slave status; +stop slave; +reset slave; +set global max_relay_log_size=(5*4096); +select @@global.max_relay_log_size; +start slave; +sync_with_master; +show slave status; +stop slave; +reset slave; +set global max_relay_log_size=0; +select @@global.max_relay_log_size; +start slave; +sync_with_master; +show slave status; + +# Tests below are mainly to ensure that we have not coded with wrong assumptions + +stop slave; +reset slave; +# test of relay log rotation when the slave is stopped +# (to make sure it does not crash). +flush logs; +show slave status; + +reset slave; +start slave; +sync_with_master; +# test of relay log rotation when the slave is started +flush logs; +# We have now easy way to be sure that the SQL thread has now deleted the +# log we just closed. But a trick to achieve this is do an update on the master. +connection master; +create table t1 (a int); +save_master_pos; +connection slave; +sync_with_master; +show slave status; +# one more rotation, to be sure Relay_log_space is correctly updated +flush logs; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; +show slave status; + +connection master; +# test that the absence of relay logs does not make a master crash +flush logs; +show master status; diff --git a/mysql-test/t/rpl_rotate_logs-master.opt b/mysql-test/t/rpl_rotate_logs-master.opt index f27601e0d7d..ad2c6a647b5 100644 --- a/mysql-test/t/rpl_rotate_logs-master.opt +++ b/mysql-test/t/rpl_rotate_logs-master.opt @@ -1 +1 @@ --O max_binlog_size=2048 +-O max_binlog_size=4096 -- cgit v1.2.1 From a4c3f0d7271c6d93b13d2b03b640adb624fd494d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 7 Jul 2003 19:02:58 +0200 Subject: finally proper recursive descent for parsing expressions with MATCH ... AGAINST in add_ft_keys() --- mysql-test/t/fulltext.test | 22 ++++++++++++++++++---- 1 file changed, 18 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 1b85f5903df..942552f5e98 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -12,14 +12,24 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text search in MySQL', 'implements vector space model'); # nl search - -explain select * from t1 where MATCH(a,b) AGAINST ("collections"); + select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); -# UNION of fulltext's -select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); + +# add_ft_keys() tests + +explain select * from t1 where MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; +explain select * from t1 where 00 and a like '%ll%'; # boolean search @@ -48,6 +58,10 @@ select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); +# UNION of fulltext's + +select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); + #update/delete with fulltext index delete from t1 where a like "MySQL%"; -- cgit v1.2.1 From eed62d993d1355c5e84c0b0b17d417e84086c526 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Jul 2003 15:50:57 +0200 Subject: Fix for BUG#797 "If query ignored on slave (replicate-ignore-table) the slave still checks if the returned error (0) is the same as the one on the master, whereas it shouldn't test this. Plus a new test for BUG#797. include/mysqld_error.h: New error code (can be sent only to the slave thread, not to normal clients). sql/log.cc: removed comment from a previous debugging. sql/log_event.cc: ER_SLAVE_IGNORED_TABLE is an ignorable error. sql/share/czech/errmsg.txt: for BUG#797 sql/share/danish/errmsg.txt: for BUG#797 sql/share/dutch/errmsg.txt: for BUG#797 sql/share/english/errmsg.txt: for BUG#797 sql/share/estonian/errmsg.txt: for BUG#797 sql/share/french/errmsg.txt: for BUG#797 sql/share/german/errmsg.txt: for BUG#797 sql/share/greek/errmsg.txt: for BUG#797 sql/share/hungarian/errmsg.txt: for BUG#797 sql/share/italian/errmsg.txt: for BUG#797 sql/share/japanese/errmsg.txt: for BUG#797 sql/share/korean/errmsg.txt: for BUG#797 sql/share/norwegian-ny/errmsg.txt: for BUG#797 sql/share/norwegian/errmsg.txt: for BUG#797 sql/share/polish/errmsg.txt: for BUG#797 sql/share/portuguese/errmsg.txt: for BUG#797 sql/share/romanian/errmsg.txt: for BUG#797 sql/share/russian/errmsg.txt: for BUG#797 sql/share/slovak/errmsg.txt: for BUG#797 sql/share/spanish/errmsg.txt: for BUG#797 sql/share/swedish/errmsg.txt: for BUG#797 sql/share/ukrainian/errmsg.txt: for BUG#797 sql/sql_parse.cc: when in sql_parse in the slave thread we ignore the query because of replicate-do and replicate-ignore options, we return a specific error to the slave thread, so that it knows that the query has been ignored (which is different from a successful query). A small cleanup (test was done twice). --- mysql-test/t/rpl_error_ignored_table-slave.opt | 1 + mysql-test/t/rpl_error_ignored_table.test | 22 ++++++++++++++++++++++ 2 files changed, 23 insertions(+) create mode 100644 mysql-test/t/rpl_error_ignored_table-slave.opt create mode 100644 mysql-test/t/rpl_error_ignored_table.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_error_ignored_table-slave.opt b/mysql-test/t/rpl_error_ignored_table-slave.opt new file mode 100644 index 00000000000..544ecb2959b --- /dev/null +++ b/mysql-test/t/rpl_error_ignored_table-slave.opt @@ -0,0 +1 @@ +--replicate-ignore-table=test.t1 \ No newline at end of file diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test new file mode 100644 index 00000000000..caa289a184d --- /dev/null +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -0,0 +1,22 @@ +# Test for +# Bug #797: If a query is ignored on slave (replicate-ignore-table) the slave +# still checks that it has the same error as on the master. + +source include/master-slave.inc; +connection master; +create table t1 (a int primary key); +# generate an error that goes to the binlog +--error 1062; +insert into t1 values (1),(1); +save_master_pos; +connection slave; +# as the t1 table is ignored on the slave, the slave should be able to sync +sync_with_master; +show slave status; +# check that the table has been ignored, because otherwise the test is nonsense +show tables like 't1'; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; -- cgit v1.2.1 From e13e857cae1ecaa322a0b4dec76e5b5dfb4f53a0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Jul 2003 23:58:04 +0300 Subject: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS Removed double my_thread_end() which caused fatal error on windows if mysqld died on startup myisam/mi_extra.c: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS mysql-test/r/alter_table.result: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS mysql-test/t/alter_table.test: Test DISABLE/ENABLE KEY sql/ha_myisam.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/mysqld.cc: Removed double my_thread_end() which caused fatal error on windows if mysqld died on startup sql/sql_table.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/table.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/table.h: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS --- mysql-test/t/alter_table.test | 62 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 62 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index a3ab62afc69..d438f2d5825 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -142,3 +142,65 @@ ALTER TABLE t1 DISABLE KEYS; INSERT DELAYED INTO t1 VALUES(1),(2),(3); ALTER TABLE t1 ENABLE KEYS; drop table t1; + +# +# Test ALTER TABLE ENABLE/DISABLE keys when things are locked +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User) +) TYPE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; +CHECK TABLES t1; +DROP TABLE t1; + +# +# Test with two keys +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) TYPE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +SHOW INDEX FROM t1; +UNLOCK TABLES; +CHECK TABLES t1; + +# Test RENAME with LOCK TABLES +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +UNLOCK TABLES; +select * from t2; +DROP TABLE t2; + +# +# Test disable keys with locking +# +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) TYPE=MyISAM; + +LOCK TABLES t1 WRITE; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +DROP TABLE t1; -- cgit v1.2.1 From 3c268e59eceb23bb2b62985b5f1166c6ac14fe2f Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 9 Jul 2003 00:55:07 +0300 Subject: Cleanups mysql-test/t/distinct.test: Cleanup sql/log.cc: Cleanup sql/slave.cc: Cleanup sql/sql_delete.cc: Cleanup sql/sql_parse.cc: Simple optimization --- mysql-test/t/distinct.test | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 1cc692bd886..79770a17baa 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -273,11 +273,14 @@ 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; + +# +# Test of LEFT() with distinct +# + CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; INSERT INTO t1 VALUES (1, 'aaaaa'); INSERT INTO t1 VALUES (3, 'aaaaa'); INSERT INTO t1 VALUES (2, 'eeeeeee'); select distinct left(name,1) as name from t1; drop table t1; - - -- cgit v1.2.1 From 830e53c8b18b78639833ae840ac92163a8d364e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 10 Jul 2003 15:27:02 +0500 Subject: Fix for the bug #803. Now INTERVAL(NULL, N1, N2, ...) returns NULL. --- mysql-test/t/func_set.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index cb1aa543d43..81f561989d5 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -15,3 +15,4 @@ select export_set(9,"Y","N","-",5),export_set(9,"Y","N"),export_set(9,"Y","N","" select elt(2,1),field(NULL,"a","b","c"); select find_in_set("","a,b,c"),find_in_set("","a,b,c,"),find_in_set("",",a,b,c"); select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc"); +select interval(null, 1, 10, 100); -- cgit v1.2.1 From f200fa15db3c39639c6467ab114dbff9dc8ff8be Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 10 Jul 2003 16:03:29 +0200 Subject: Fix for BUG#812 "mysqlhotcopy fails to copy tables but does not indicate a failure" ("does not indicate a failure"); this is about "mysqlhotcopy fails to copy tables". mysql-test/t/rpl_error_ignored_table-slave.opt: added a missing newline scripts/mysqlhotcopy.sh: Fix for BUG#812. The problem was that with many tables to copy (10000 in the bug's example), the generated 'cp' command line was 1MB long, whereas (at least on my Linux) it should not exceed 128 kB. Testing the 'cp' in a shell terminal gives "arguments list too long". So we issue several small (100 kB) 'cp' command lines instead of a big one. Of course, this will still fail on systems where the limit is below 100 kB. We now have safe_system() which cuts the command line in pieces, and calls safe_simple_system() (execution) for each piece. --- mysql-test/t/rpl_error_ignored_table-slave.opt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_error_ignored_table-slave.opt b/mysql-test/t/rpl_error_ignored_table-slave.opt index 544ecb2959b..0d3485f9e25 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 \ No newline at end of file +--replicate-ignore-table=test.t1 -- cgit v1.2.1 From 1a45c8397258d992bdc526d3463b92ad448a3754 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jul 2003 14:17:18 +0500 Subject: Fix for the bug #801: create table t1 select x'41'; doesn't work --- mysql-test/t/create.test | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 86c3f6be0f5..d46807f1dca 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -127,3 +127,10 @@ insert into t1 values ("a", 1), ("b", 2); !$1048 insert into t1 values (NULL, 3); !$1048 insert into t1 values (NULL, NULL); drop table t1; + +# +# Bug # 801 +# + +create table t1 select x'4132'; +drop table t1; -- cgit v1.2.1 From 1c5773add420572a012d9f2d8e382dc15e5e1107 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jul 2003 22:18:53 +0200 Subject: - fixed an error in the rpl_error_ignored_table test suite: the master port number can be different from 3306 when doing the release builds with Do-compile, therefore it has to be replaced with the correct value during the test run using the "--replace_result" function. mysql-test/r/rpl_error_ignored_table.result: - replaced hard-coded master port number with MASTER_PORT variable, since the port number is different when running the test suite during the release builds with "Do-compile". mysql-test/t/rpl_error_ignored_table.test: - replaced hard-coded master port number with MASTER_PORT variable, since the port number is different when running the test suite during the release builds with "Do-compile". --- mysql-test/t/rpl_error_ignored_table.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test index caa289a184d..686472433eb 100644 --- a/mysql-test/t/rpl_error_ignored_table.test +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -12,6 +12,9 @@ save_master_pos; connection slave; # as the t1 table is ignored on the slave, the slave should be able to sync sync_with_master; +# The port number is different when doing the release build with +# Do-compile, hence we have to replace the port number here accordingly +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; # check that the table has been ignored, because otherwise the test is nonsense show tables like 't1'; -- cgit v1.2.1 From 128feded87350231bf7c28a870932ef54e00bbdf Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Jul 2003 14:59:26 +0300 Subject: Better fix for bug #791: At binlog rotation, INSERTs may not find their way into the binlog mysql-test/t/rpl_flush_log_loop.test: Add timer to avoid problem when 'flush logs' is executed before we have read all data from master sql/log.cc: Better fix for bug #791: Mark log as LOG_TO_BE_OPENED instead of LOG_CLOSED when it's closed and opened. sql/mysqld.cc: Better startup message sql/slave.cc: Fix argument to close() sql/sql_class.h: Better handling of log.close() --- mysql-test/t/rpl_flush_log_loop.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_flush_log_loop.test b/mysql-test/t/rpl_flush_log_loop.test index c68fbb7111a..8a3da1a2b02 100644 --- a/mysql-test/t/rpl_flush_log_loop.test +++ b/mysql-test/t/rpl_flush_log_loop.test @@ -14,6 +14,7 @@ slave stop; eval change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=$SLAVE_MYPORT; slave start; +sleep 5; flush logs; sleep 5; --replace_result $SLAVE_MYPORT SLAVE_PORT -- cgit v1.2.1 From e97b2766e1cc1d03eca8d1ad21c2eef1a6f923bb Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Jul 2003 23:49:19 +0300 Subject: Fixed wrong test in LOG::close Fixed test for binary build mysql-test/r/rpl_max_relay_size.result: Fixed test for binary build mysql-test/t/rpl_max_relay_size.test: Fixed test for binary build sql/log.cc: Fixed wrong test in close --- mysql-test/t/rpl_max_relay_size.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test index 4befe4024fc..a2167b1ef36 100644 --- a/mysql-test/t/rpl_max_relay_size.test +++ b/mysql-test/t/rpl_max_relay_size.test @@ -28,6 +28,7 @@ set global max_relay_log_size=8192-1; # mapped to 4096 select @@global.max_relay_log_size; start slave; sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; stop slave; reset slave; @@ -35,6 +36,7 @@ set global max_relay_log_size=(5*4096); select @@global.max_relay_log_size; start slave; sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; stop slave; reset slave; @@ -42,6 +44,7 @@ set global max_relay_log_size=0; select @@global.max_relay_log_size; start slave; sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; # Tests below are mainly to ensure that we have not coded with wrong assumptions @@ -51,6 +54,7 @@ reset slave; # test of relay log rotation when the slave is stopped # (to make sure it does not crash). flush logs; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; reset slave; @@ -65,6 +69,7 @@ create table t1 (a int); save_master_pos; connection slave; sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; # one more rotation, to be sure Relay_log_space is correctly updated flush logs; @@ -73,6 +78,7 @@ drop table t1; save_master_pos; connection slave; sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; connection master; -- cgit v1.2.1 From 9942e0c7c65151d36b3ea93622d8a33b04860434 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 17 Jul 2003 20:17:46 +0200 Subject: my_strnncoll_latin1_de rewritten from scratch. Bug with overlapping combos went away. mysql-test/r/ctype_latin1_de.result: testing overlapping combos mysql-test/t/ctype_latin1_de.test: testing overlapping combos --- mysql-test/t/ctype_latin1_de.test | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 6353650f420..3a0f2658969 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -34,6 +34,10 @@ select strcmp('af',' select strcmp('ßa','ss'),strcmp('ssa','ß'),strcmp('sssb','sßa'),strcmp('ß','s'); select strcmp('u','öa'),strcmp('u','ö'); +# +# overlapping combo's +# +select strcmp('sä', 'ßa'), strcmp('aä', 'äx'); # # Some other simple tests with the current character set # -- cgit v1.2.1 From 06517dfed3505981105d892e036e36d32c23c9aa Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 18 Jul 2003 04:04:24 +0300 Subject: Fixed memory overrun when doing REPAIR on table with multi-part auto_increment key where one part was a packed CHAR myisam/mi_check.c: Fixed memory overrun in _mi_put_key_in_record myisam/mi_key.c: Fixed unnecessary memory allocation mysql-test/r/insert_select.result: Added test case for memory corruption mysql-test/t/insert_select.test: Added test case for memory corruption mysys/safemalloc.c: Removed compiler warnings sql/sql_acl.cc: Fixed core dump when running with --debug --- mysql-test/t/insert_select.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index d115e9c1af6..5c63f3b3bb7 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -122,3 +122,13 @@ select * from t1; replace into t1 select * from t2; select * from t1; drop table t1,t2; + +# +# Test that caused uninitialized memory access in auto_increment_key update +# + +CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL); +CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr)); +INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); +INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; +drop table t1,t2; -- cgit v1.2.1 From 2763b52d0a0ad85818f5c8259b4dd1d060e568e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 Jul 2003 14:32:03 +0200 Subject: outer join, impossible on condition, where, and usable key for range bug#926 --- mysql-test/t/select.test | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c2e451ea7f3..9884c88b420 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1832,3 +1832,23 @@ INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; drop table t1,t2; +# +# outer join, impossible on condition, where, and usable key for range +# +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); + +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); + +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; + +drop table t1,t2,t3,t4; + -- cgit v1.2.1 From f3a847eceb41fe4a9ebf765a27a13269f6016f09 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 26 Jul 2003 17:59:05 +0200 Subject: Make rpl_insert_id.test use InnoDB, and added foreign keys to the table, so that it really tests replication of SET FOREIGN_KEY_CHECKS (previously it used MyISAM). mysql-test/r/rpl_insert_id.result: result update mysql-test/t/rpl_insert_id.test: Make test use InnoDB, and added foreign keys to the table, so that it really tests replication of FOREIGN_KEY_CHECKS. --disable_warnings because 4.1 prints a warning when the table type is not available. --- mysql-test/t/rpl_insert_id.test | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 26d5d3a9ed2..d91bd02883e 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -22,8 +22,10 @@ connection master; #are replicated the same way drop table t1; drop table t2; -create table t1(a int auto_increment, key(a)); -create table t2(b int auto_increment, c int, key(b)); +--disable_warnings +create table t1(a int auto_increment, key(a)) type=innodb; +create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) type=innodb; +--enable_warnings SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); insert into t1 values (null),(null),(null); -- cgit v1.2.1 From 9b9a914c5dd75834b43b049df6a32975b2b9d4d8 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 Jul 2003 14:59:56 +0300 Subject: support of more then 255 tables dependence in query cache (fixed BUG#930) mysql-test/r/query_cache.result: test of many merged tales mysql-test/t/query_cache.test: test of many merged tales sql/sql_cache.h: query with merged tables can depend of more then 255 tables --- mysql-test/t/query_cache.test | 530 +++++++++++++++++++++++++++++++++++++++++- 1 file changed, 529 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index fe258b2c7e1..6907a9e8299 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -10,7 +10,7 @@ flush query cache; # This crashed in some versions flush query cache; # This crashed in some versions reset query cache; flush status; -drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00,mysqltest.t1; drop database if exists mysqltest; # @@ -450,3 +450,531 @@ select * from t1; show status like "Qcache_queries_in_cache"; SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; + +# +# more then 255 (257) merged tables test +# +flush status; +create table t0(a int); +create table t1(a int); +create table t2(a int); +create table t3(a int); +create table t4(a int); +create table t5(a int); +create table t6(a int); +create table t7(a int); +create table t8(a int); +create table t9(a int); +create table t10(a int); +create table t11(a int); +create table t12(a int); +create table t13(a int); +create table t14(a int); +create table t15(a int); +create table t16(a int); +create table t17(a int); +create table t18(a int); +create table t19(a int); +create table t20(a int); +create table t21(a int); +create table t22(a int); +create table t23(a int); +create table t24(a int); +create table t25(a int); +create table t26(a int); +create table t27(a int); +create table t28(a int); +create table t29(a int); +create table t30(a int); +create table t31(a int); +create table t32(a int); +create table t33(a int); +create table t34(a int); +create table t35(a int); +create table t36(a int); +create table t37(a int); +create table t38(a int); +create table t39(a int); +create table t40(a int); +create table t41(a int); +create table t42(a int); +create table t43(a int); +create table t44(a int); +create table t45(a int); +create table t46(a int); +create table t47(a int); +create table t48(a int); +create table t49(a int); +create table t50(a int); +create table t51(a int); +create table t52(a int); +create table t53(a int); +create table t54(a int); +create table t55(a int); +create table t56(a int); +create table t57(a int); +create table t58(a int); +create table t59(a int); +create table t60(a int); +create table t61(a int); +create table t62(a int); +create table t63(a int); +create table t64(a int); +create table t65(a int); +create table t66(a int); +create table t67(a int); +create table t68(a int); +create table t69(a int); +create table t70(a int); +create table t71(a int); +create table t72(a int); +create table t73(a int); +create table t74(a int); +create table t75(a int); +create table t76(a int); +create table t77(a int); +create table t78(a int); +create table t79(a int); +create table t80(a int); +create table t81(a int); +create table t82(a int); +create table t83(a int); +create table t84(a int); +create table t85(a int); +create table t86(a int); +create table t87(a int); +create table t88(a int); +create table t89(a int); +create table t90(a int); +create table t91(a int); +create table t92(a int); +create table t93(a int); +create table t94(a int); +create table t95(a int); +create table t96(a int); +create table t97(a int); +create table t98(a int); +create table t99(a int); +create table t100(a int); +create table t101(a int); +create table t102(a int); +create table t103(a int); +create table t104(a int); +create table t105(a int); +create table t106(a int); +create table t107(a int); +create table t108(a int); +create table t109(a int); +create table t110(a int); +create table t111(a int); +create table t112(a int); +create table t113(a int); +create table t114(a int); +create table t115(a int); +create table t116(a int); +create table t117(a int); +create table t118(a int); +create table t119(a int); +create table t120(a int); +create table t121(a int); +create table t122(a int); +create table t123(a int); +create table t124(a int); +create table t125(a int); +create table t126(a int); +create table t127(a int); +create table t128(a int); +create table t129(a int); +create table t130(a int); +create table t131(a int); +create table t132(a int); +create table t133(a int); +create table t134(a int); +create table t135(a int); +create table t136(a int); +create table t137(a int); +create table t138(a int); +create table t139(a int); +create table t140(a int); +create table t141(a int); +create table t142(a int); +create table t143(a int); +create table t144(a int); +create table t145(a int); +create table t146(a int); +create table t147(a int); +create table t148(a int); +create table t149(a int); +create table t150(a int); +create table t151(a int); +create table t152(a int); +create table t153(a int); +create table t154(a int); +create table t155(a int); +create table t156(a int); +create table t157(a int); +create table t158(a int); +create table t159(a int); +create table t160(a int); +create table t161(a int); +create table t162(a int); +create table t163(a int); +create table t164(a int); +create table t165(a int); +create table t166(a int); +create table t167(a int); +create table t168(a int); +create table t169(a int); +create table t170(a int); +create table t171(a int); +create table t172(a int); +create table t173(a int); +create table t174(a int); +create table t175(a int); +create table t176(a int); +create table t177(a int); +create table t178(a int); +create table t179(a int); +create table t180(a int); +create table t181(a int); +create table t182(a int); +create table t183(a int); +create table t184(a int); +create table t185(a int); +create table t186(a int); +create table t187(a int); +create table t188(a int); +create table t189(a int); +create table t190(a int); +create table t191(a int); +create table t192(a int); +create table t193(a int); +create table t194(a int); +create table t195(a int); +create table t196(a int); +create table t197(a int); +create table t198(a int); +create table t199(a int); +create table t200(a int); +create table t201(a int); +create table t202(a int); +create table t203(a int); +create table t204(a int); +create table t205(a int); +create table t206(a int); +create table t207(a int); +create table t208(a int); +create table t209(a int); +create table t210(a int); +create table t211(a int); +create table t212(a int); +create table t213(a int); +create table t214(a int); +create table t215(a int); +create table t216(a int); +create table t217(a int); +create table t218(a int); +create table t219(a int); +create table t220(a int); +create table t221(a int); +create table t222(a int); +create table t223(a int); +create table t224(a int); +create table t225(a int); +create table t226(a int); +create table t227(a int); +create table t228(a int); +create table t229(a int); +create table t230(a int); +create table t231(a int); +create table t232(a int); +create table t233(a int); +create table t234(a int); +create table t235(a int); +create table t236(a int); +create table t237(a int); +create table t238(a int); +create table t239(a int); +create table t240(a int); +create table t241(a int); +create table t242(a int); +create table t243(a int); +create table t244(a int); +create table t245(a int); +create table t246(a int); +create table t247(a int); +create table t248(a int); +create table t249(a int); +create table t250(a int); +create table t251(a int); +create table t252(a int); +create table t253(a int); +create table t254(a int); +create table t255(a int); +create table t256(a int); +create table t00 (a int) type=MERGE UNION=(t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256) INSERT_METHOD=FIRST; +insert into t0 values (1),(2); +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +insert into t4 values (1),(2); +insert into t5 values (1),(2); +insert into t6 values (1),(2); +insert into t7 values (1),(2); +insert into t8 values (1),(2); +insert into t9 values (1),(2); +insert into t10 values (1),(2); +insert into t11 values (1),(2); +insert into t12 values (1),(2); +insert into t13 values (1),(2); +insert into t14 values (1),(2); +insert into t15 values (1),(2); +insert into t16 values (1),(2); +insert into t17 values (1),(2); +insert into t18 values (1),(2); +insert into t19 values (1),(2); +insert into t20 values (1),(2); +insert into t21 values (1),(2); +insert into t22 values (1),(2); +insert into t23 values (1),(2); +insert into t24 values (1),(2); +insert into t25 values (1),(2); +insert into t26 values (1),(2); +insert into t27 values (1),(2); +insert into t28 values (1),(2); +insert into t29 values (1),(2); +insert into t30 values (1),(2); +insert into t31 values (1),(2); +insert into t32 values (1),(2); +insert into t33 values (1),(2); +insert into t34 values (1),(2); +insert into t35 values (1),(2); +insert into t36 values (1),(2); +insert into t37 values (1),(2); +insert into t38 values (1),(2); +insert into t39 values (1),(2); +insert into t40 values (1),(2); +insert into t41 values (1),(2); +insert into t42 values (1),(2); +insert into t43 values (1),(2); +insert into t44 values (1),(2); +insert into t45 values (1),(2); +insert into t46 values (1),(2); +insert into t47 values (1),(2); +insert into t48 values (1),(2); +insert into t49 values (1),(2); +insert into t50 values (1),(2); +insert into t51 values (1),(2); +insert into t52 values (1),(2); +insert into t53 values (1),(2); +insert into t54 values (1),(2); +insert into t55 values (1),(2); +insert into t56 values (1),(2); +insert into t57 values (1),(2); +insert into t58 values (1),(2); +insert into t59 values (1),(2); +insert into t60 values (1),(2); +insert into t61 values (1),(2); +insert into t62 values (1),(2); +insert into t63 values (1),(2); +insert into t64 values (1),(2); +insert into t65 values (1),(2); +insert into t66 values (1),(2); +insert into t67 values (1),(2); +insert into t68 values (1),(2); +insert into t69 values (1),(2); +insert into t70 values (1),(2); +insert into t71 values (1),(2); +insert into t72 values (1),(2); +insert into t73 values (1),(2); +insert into t74 values (1),(2); +insert into t75 values (1),(2); +insert into t76 values (1),(2); +insert into t77 values (1),(2); +insert into t78 values (1),(2); +insert into t79 values (1),(2); +insert into t80 values (1),(2); +insert into t81 values (1),(2); +insert into t82 values (1),(2); +insert into t83 values (1),(2); +insert into t84 values (1),(2); +insert into t85 values (1),(2); +insert into t86 values (1),(2); +insert into t87 values (1),(2); +insert into t88 values (1),(2); +insert into t89 values (1),(2); +insert into t90 values (1),(2); +insert into t91 values (1),(2); +insert into t92 values (1),(2); +insert into t93 values (1),(2); +insert into t94 values (1),(2); +insert into t95 values (1),(2); +insert into t96 values (1),(2); +insert into t97 values (1),(2); +insert into t98 values (1),(2); +insert into t99 values (1),(2); +insert into t100 values (1),(2); +insert into t101 values (1),(2); +insert into t102 values (1),(2); +insert into t103 values (1),(2); +insert into t104 values (1),(2); +insert into t105 values (1),(2); +insert into t106 values (1),(2); +insert into t107 values (1),(2); +insert into t108 values (1),(2); +insert into t109 values (1),(2); +insert into t110 values (1),(2); +insert into t111 values (1),(2); +insert into t112 values (1),(2); +insert into t113 values (1),(2); +insert into t114 values (1),(2); +insert into t115 values (1),(2); +insert into t116 values (1),(2); +insert into t117 values (1),(2); +insert into t118 values (1),(2); +insert into t119 values (1),(2); +insert into t120 values (1),(2); +insert into t121 values (1),(2); +insert into t122 values (1),(2); +insert into t123 values (1),(2); +insert into t124 values (1),(2); +insert into t125 values (1),(2); +insert into t126 values (1),(2); +insert into t127 values (1),(2); +insert into t128 values (1),(2); +insert into t129 values (1),(2); +insert into t130 values (1),(2); +insert into t131 values (1),(2); +insert into t132 values (1),(2); +insert into t133 values (1),(2); +insert into t134 values (1),(2); +insert into t135 values (1),(2); +insert into t136 values (1),(2); +insert into t137 values (1),(2); +insert into t138 values (1),(2); +insert into t139 values (1),(2); +insert into t140 values (1),(2); +insert into t141 values (1),(2); +insert into t142 values (1),(2); +insert into t143 values (1),(2); +insert into t144 values (1),(2); +insert into t145 values (1),(2); +insert into t146 values (1),(2); +insert into t147 values (1),(2); +insert into t148 values (1),(2); +insert into t149 values (1),(2); +insert into t150 values (1),(2); +insert into t151 values (1),(2); +insert into t152 values (1),(2); +insert into t153 values (1),(2); +insert into t154 values (1),(2); +insert into t155 values (1),(2); +insert into t156 values (1),(2); +insert into t157 values (1),(2); +insert into t158 values (1),(2); +insert into t159 values (1),(2); +insert into t160 values (1),(2); +insert into t161 values (1),(2); +insert into t162 values (1),(2); +insert into t163 values (1),(2); +insert into t164 values (1),(2); +insert into t165 values (1),(2); +insert into t166 values (1),(2); +insert into t167 values (1),(2); +insert into t168 values (1),(2); +insert into t169 values (1),(2); +insert into t170 values (1),(2); +insert into t171 values (1),(2); +insert into t172 values (1),(2); +insert into t173 values (1),(2); +insert into t174 values (1),(2); +insert into t175 values (1),(2); +insert into t176 values (1),(2); +insert into t177 values (1),(2); +insert into t178 values (1),(2); +insert into t179 values (1),(2); +insert into t180 values (1),(2); +insert into t181 values (1),(2); +insert into t182 values (1),(2); +insert into t183 values (1),(2); +insert into t184 values (1),(2); +insert into t185 values (1),(2); +insert into t186 values (1),(2); +insert into t187 values (1),(2); +insert into t188 values (1),(2); +insert into t189 values (1),(2); +insert into t190 values (1),(2); +insert into t191 values (1),(2); +insert into t192 values (1),(2); +insert into t193 values (1),(2); +insert into t194 values (1),(2); +insert into t195 values (1),(2); +insert into t196 values (1),(2); +insert into t197 values (1),(2); +insert into t198 values (1),(2); +insert into t199 values (1),(2); +insert into t200 values (1),(2); +insert into t201 values (1),(2); +insert into t202 values (1),(2); +insert into t203 values (1),(2); +insert into t204 values (1),(2); +insert into t205 values (1),(2); +insert into t206 values (1),(2); +insert into t207 values (1),(2); +insert into t208 values (1),(2); +insert into t209 values (1),(2); +insert into t210 values (1),(2); +insert into t211 values (1),(2); +insert into t212 values (1),(2); +insert into t213 values (1),(2); +insert into t214 values (1),(2); +insert into t215 values (1),(2); +insert into t216 values (1),(2); +insert into t217 values (1),(2); +insert into t218 values (1),(2); +insert into t219 values (1),(2); +insert into t220 values (1),(2); +insert into t221 values (1),(2); +insert into t222 values (1),(2); +insert into t223 values (1),(2); +insert into t224 values (1),(2); +insert into t225 values (1),(2); +insert into t226 values (1),(2); +insert into t227 values (1),(2); +insert into t228 values (1),(2); +insert into t229 values (1),(2); +insert into t230 values (1),(2); +insert into t231 values (1),(2); +insert into t232 values (1),(2); +insert into t233 values (1),(2); +insert into t234 values (1),(2); +insert into t235 values (1),(2); +insert into t236 values (1),(2); +insert into t237 values (1),(2); +insert into t238 values (1),(2); +insert into t239 values (1),(2); +insert into t240 values (1),(2); +insert into t241 values (1),(2); +insert into t242 values (1),(2); +insert into t243 values (1),(2); +insert into t244 values (1),(2); +insert into t245 values (1),(2); +insert into t246 values (1),(2); +insert into t247 values (1),(2); +insert into t248 values (1),(2); +insert into t249 values (1),(2); +insert into t250 values (1),(2); +insert into t251 values (1),(2); +insert into t252 values (1),(2); +insert into t253 values (1),(2); +insert into t254 values (1),(2); +insert into t255 values (1),(2); +insert into t256 values (1),(2); +enable_result_log; +select count(*) from t00; +select count(*) from t00; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +delete from t256; +show status like "Qcache_queries_in_cache"; +drop table t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00; -- cgit v1.2.1 From 687ebba461075af77544c3bf1d268148fc3b2c90 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 31 Jul 2003 13:45:35 +0200 Subject: bug #942. docid == ftb->lastpos in join on looping over nested table --- mysql-test/t/fulltext.test | 16 +++++++++++++++- 1 file changed, 15 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 942552f5e98..387a36f1f52 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -189,7 +189,7 @@ select * from t1 where match (a) against ('aaaa'); drop table t1; # -# bug 283 by jocelyn fournier +# bug #283 by jocelyn fournier # FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore # @@ -201,3 +201,17 @@ alter table t1 change ref_mag ref_mag char (255) not null; select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); drop table t1; +# +# bug #942: JOIN +# + +create table t1 (t1_id int(11) primary key, name varchar(32)); +insert into t1 values (1, 'data1'); +insert into t1 values (2, 'data2'); +create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); +insert into t2 values (1, 1, 'xxfoo'); +insert into t2 values (2, 1, 'xxbar'); +insert into t2 values (3, 1, 'xxbuz'); +select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); +drop table t1,t2; + -- cgit v1.2.1 From 3b013646e1550d22dff01dd02c8d12118a563fe2 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 2 Aug 2003 23:46:26 +0200 Subject: Fix so that SET PASSWORD is not replicated by the slave if running with replicate-*-table rules which exclude 'mysql' tables (e.g. replicate-wild-ignore-table=mysql.%). This was already the behaviour for GRANT/REVOKE, I'm extending it to SET PASSWORD because it seems very logical (the contrary seems illogical). 2 new tests: - one to test if GRANT and SET PASSWORD are replicated - one to test if they are not replicated if replicate-wild-ignore-table=mysql.% The 2nd is also a testcase for BUG#980. sql/sql_acl.cc: Fix so that SET PASSWORD is not replicated by the slave if running with replicate-*-table rules which exclude 'mysql' tables (e.g. replicate-wild-ignore-table=mysql.%). This was already the behaviour for GRANT/REVOKE, I'm extending it to SET PASSWORD because it seems very logical (the contrary seems illogical). --- mysql-test/t/rpl_do_grant.test | 46 ++++++++++++++++++++++++++ mysql-test/t/rpl_ignore_grant-slave.opt | 1 + mysql-test/t/rpl_ignore_grant.test | 57 +++++++++++++++++++++++++++++++++ 3 files changed, 104 insertions(+) create mode 100644 mysql-test/t/rpl_do_grant.test create mode 100644 mysql-test/t/rpl_ignore_grant-slave.opt create mode 100644 mysql-test/t/rpl_ignore_grant.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_do_grant.test b/mysql-test/t/rpl_do_grant.test new file mode 100644 index 00000000000..89ff1afb5c9 --- /dev/null +++ b/mysql-test/t/rpl_do_grant.test @@ -0,0 +1,46 @@ +# Test that GRANT and SET PASSWORD are replicated to the slave + +source include/master-slave.inc; + +# do not be influenced by other tests. +connection master; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# if these DELETE did nothing on the master, we need to do them manually on the +# slave. +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; + +# test replication of GRANT +connection master; +grant select on *.* to rpl_do_grant@localhost; +grant drop on test.* to rpl_do_grant@localhost; +save_master_pos; +connection slave; +sync_with_master; +show grants for rpl_do_grant@localhost; + +# test replication of SET PASSWORD +connection master; +set password for rpl_do_grant@localhost=password("does it work?"); +save_master_pos; +connection slave; +sync_with_master; +select password<>'' from mysql.user where user='rpl_do_grant'; + +# clear what we have done, to not influence other tests. +connection master; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# no need to delete manually, as the DELETEs must have done some real job on +# master (updated binlog) +flush privileges; diff --git a/mysql-test/t/rpl_ignore_grant-slave.opt b/mysql-test/t/rpl_ignore_grant-slave.opt new file mode 100644 index 00000000000..e931bfbd37e --- /dev/null +++ b/mysql-test/t/rpl_ignore_grant-slave.opt @@ -0,0 +1 @@ +--replicate-wild-ignore-table=mysql.% diff --git a/mysql-test/t/rpl_ignore_grant.test b/mysql-test/t/rpl_ignore_grant.test new file mode 100644 index 00000000000..2fd7f186b3e --- /dev/null +++ b/mysql-test/t/rpl_ignore_grant.test @@ -0,0 +1,57 @@ +# Test that GRANT is not replicated to the slave +# when --replicate-wild-ignore-table=mysql.% +# In BUG#980, this test would _randomly_ fail. + +source include/master-slave.inc; + +# do not be influenced by other tests. +connection master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# as these DELETE were not replicated, we need to do them manually on the +# slave. +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; + +# test non-replication of GRANT +connection master; +grant select on *.* to rpl_ignore_grant@localhost; +grant drop on test.* to rpl_ignore_grant@localhost; +show grants for rpl_ignore_grant@localhost; +save_master_pos; +connection slave; +sync_with_master; +--error 1141 #("no such grant for user") +show grants for rpl_ignore_grant@localhost; +# check it another way +select count(*) from mysql.user where user='rpl_ignore_grant'; +select count(*) from mysql.db where user='rpl_ignore_grant'; + +# test non-replication of SET PASSWORD +# first force creation of the user on slave (because as the user does not exist +# on slave, the SET PASSWORD may be replicated but silently do nothing; this is +# not what we want; we want it to be not-replicated). +grant select on *.* to rpl_ignore_grant@localhost; +connection master; +set password for rpl_ignore_grant@localhost=password("does it work?"); +save_master_pos; +connection slave; +sync_with_master; +select password<>'' from mysql.user where user='rpl_ignore_grant'; + +# clear what we have done, to not influence other tests. +connection master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; -- cgit v1.2.1 From d8df84aa438a2dfe4a90d7889713c2ccd52cca1d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 4 Aug 2003 10:59:44 +0200 Subject: 2 bugfixes: - Bug #985: "Between RESET SLAVE and START SLAVE, SHOW SLAVE STATUS is wrong." Now RESET SLAVE puts correct info in mi->host etc. A new test rpl_reset_slave for that. - Bug #986: "CHANGE MASTER & START SLAVE do not reset error columns in SHOW SLAVE STATUS". Now these reset the errors. mysql-test/r/rpl_loaddata.result: result update. mysql-test/t/rpl_loaddata.test: Test that RESET SLAVE, START SLAVE and CHANGE MASTER all reset Last_slave_error and Last_slave_errno (columns of SHOW SLAVE STATUS). We do it in this test because that's one of tests which have an intentional query error on the slave. sql/slave.cc: As we need TWICE the code to copy command-line options (--master-host etc) to mi (we already had it in init_master_info, but we also need it in RESET SLAVE to fix bug#985), I make a function of this code. And a function to reset Last_slave_error and Last_slave_errno (we need it in CHANGE MASTER, RESET SLAVE, and at the start of the SQL thread). sql/slave.h: declarations for new functions. sql/sql_repl.cc: copy --master-host etc to mi in RESET SLAVE, so that SHOW SLAVE STATUS shows correct information. --- mysql-test/t/rpl_loaddata.test | 53 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/rpl_reset_slave.test | 26 +++++++++++++++++++ 2 files changed, 79 insertions(+) create mode 100644 mysql-test/t/rpl_reset_slave.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test index 96a4eb3fb76..0a07dd7549b 100644 --- a/mysql-test/t/rpl_loaddata.test +++ b/mysql-test/t/rpl_loaddata.test @@ -6,6 +6,9 @@ # # check if duplicate entries trigger an error (they should unless IGNORE or # REPLACE was used on the master) (bug 571). +# +# check if START SLAVE, RESET SLAVE, CHANGE MASTER reset Last_slave_error and +# Last_slave_errno in SHOW SLAVE STATUS (1st and 3rd commands did not: bug 986). source include/master-slave.inc; @@ -44,3 +47,53 @@ save_master_pos; connection slave; # The SQL slave thread should be stopped now. wait_for_slave_to_stop; + +# Skip the bad event and see if error is cleared in SHOW SLAVE STATUS by START +# SLAVE, even though we are not executing any event (as sql_slave_skip_counter +# takes us directly to the end of the relay log). + +set global sql_slave_skip_counter=1; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +# Trigger error again to test CHANGE MASTER + +connection master; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +save_master_pos; +connection slave; +# The SQL slave thread should be stopped now. +wait_for_slave_to_stop; + +# CHANGE MASTER and see if error is cleared in SHOW SLAVE STATUS. +stop slave; +change master to master_user='test'; +change master to master_user='root'; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +# Trigger error again to test RESET SLAVE + +set global sql_slave_skip_counter=1; +start slave; +sync_with_master; +connection master; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +save_master_pos; +connection slave; +# The SQL slave thread should be stopped now. +wait_for_slave_to_stop; + +# RESET SLAVE and see if error is cleared in SHOW SLAVE STATUS. +stop slave; +reset slave; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; diff --git a/mysql-test/t/rpl_reset_slave.test b/mysql-test/t/rpl_reset_slave.test new file mode 100644 index 00000000000..9c58ac0c787 --- /dev/null +++ b/mysql-test/t/rpl_reset_slave.test @@ -0,0 +1,26 @@ +# See SHOW SLAVE STATUS displays well after RESET SLAVE (it should display the +# --master-* options from mysqld, as this is what is going to be used next time +# slave threads will be started). In bug 985, it displayed old values (of before +# RESET SLAVE). + +source include/master-slave.inc; +connection master; +save_master_pos; +connection slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +stop slave; +change master to master_user='test'; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +reset slave; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; -- cgit v1.2.1 From 0f314c9da3803367d34633a51dd2dbf3f0bd10a0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Aug 2003 16:29:09 +0200 Subject: make it clear for optimizer that XOR's are not optimizable at the moment (BUG#992) --- mysql-test/t/func_test.test | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index f5ad2e21c73..700fab97f5e 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -18,6 +18,11 @@ 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; +create table t1 (a int); +insert t1 values (1); +select * from t1 where 1 xor 1; +drop table t1; + # # Wrong usage of functions # -- cgit v1.2.1 From e50930d36c33c4457d9ba6d68e5f6b2c6e622f11 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Aug 2003 15:37:22 +0200 Subject: Bug #792 combination of date-interval and between with mixed constant and non-constant --- mysql-test/t/type_date.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 68c2d55aac9..bd6d8c49c22 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -34,6 +34,7 @@ INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; +SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; DROP TABLE t1; # -- cgit v1.2.1 From fa1613be0a54ba87321501a52d62d4e99d237945 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 7 Aug 2003 20:16:37 +0300 Subject: Review of changesets since last pull. (Mostly code cleanups) include/my_sys.h: Removed not used define myisam/mi_check.c: Indentation change mysql-test/t/rpl_insert_id.test: Add test if server supports innodb mysys/mf_format.c: Remove QUOTE handling from fn_format() (fn_format() should not have anything to do with quoting things) sql/log_event.cc: Removed some unnecessary casts (by changing functions to use const char *) Cleaned up some error messages to make them shorter and (hopefully) more readable. Fixed wrong format strings Restored 'thd->options' on error. sql/mini_client.cc: Changed order of include files to remove compilation warning sql/mysqld.cc: Add proper quoting of service arguments (don't use fn_format) sql/slave.cc: Simple optimization and cleanup. Changed rewrite_db() and print_slave_db_safe() to use const char* to avoid casting of arguments when calling functions. Cleanup of some error messages. sql/slave.h: Changed protypes to use const char * sql/sql_acl.cc: Simple optimization vio/viosslfactories.c: Changed order of include files to remove compilation warning --- mysql-test/t/rpl_insert_id.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index d91bd02883e..49fefae72b8 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -4,6 +4,7 @@ # We also check how the foreign_key_check variable is replicated source include/master-slave.inc; +source include/have_innodb.inc connection master; drop table if exists t1; create table t1(a int auto_increment, key(a)); -- cgit v1.2.1