Add new option "check-testcases" to mysql-test-run.pl

Cleanup the sideeffects from most of the  testcases with sideeffects.


mysql-test/mysql-test-run.pl:
  Add option "check-testcases" to mysql-test-run.pl
  Will execute "include/check-testcase.test" once before each tescase and record the output into "var/tmp/check-testcase.result"
  After the teastcase it will run again and this time compare the output with previously recorded file.
mysql-test/r/analyze.result:
  Drop table t1 at end of test
mysql-test/r/create_select_tmp.result:
  Drop table t1 at end of test
mysql-test/r/ctype_cp932.result:
  Drop table t1 at end of test
mysql-test/r/ctype_recoding.result:
  Drop table t1 at end of test
mysql-test/r/grant2.result:
  Drop user mysqltest_2 and mysqltest_A@'%'
mysql-test/r/join_outer.result:
  Drop view v1 to cleanup
mysql-test/r/ps_1general.result:
  Drop table t1 at end of test
mysql-test/r/query_cache.result:
  Drop function "f1"
mysql-test/r/read_only.result:
  Reset the "read_only" flag
mysql-test/r/rpl000001.result:
  Remove user "blafasel2"
mysql-test/r/rpl000017.result:
  Remove user "replicate"
mysql-test/r/rpl_failed_optimize.result:
  Drop table t1 to cleanup
mysql-test/r/rpl_flush_tables.result:
  Drop tables t3, t4, t5
mysql-test/r/rpl_ignore_revoke.result:
  Delete user "user_foo"
mysql-test/r/rpl_insert_id.result:
  Drop table t1 to cleanup
mysql-test/r/rpl_loaddata.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_loaddata_rule_m.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_loaddata_rule_s.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_misc_functions.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_multi_update3.result:
  Drop tyable t1 and t2 to cleanup
mysql-test/r/rpl_replicate_do.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_skip_error.result:
  Drop tyable t1 to cleanup
mysql-test/r/rpl_slave_status.result:
  Drop tyable t1 to cleanup
mysql-test/r/sp-prelocking.result:
  Drop view v1 and tables t1, t2, t3 and t4 to cleanup
mysql-test/r/sp-security.result:
  Delete users to cleanup
  Delete remaining traces in tables_priv and procs_priv
mysql-test/r/subselect_innodb.result:
  Drop procedure p1 to cleanup
mysql-test/r/trigger-compat.result:
  Drop trigger wl2818_trg1 and wl2818_trg2.
  Drop table t1, t2
  Drop database mysqltest_db1
  And the users "mysqltest_dfn@localhost" and "mysqltest_inv@localhost"
mysql-test/r/type_bit.result:
  Drop tables t1 and t2 to cleanup
mysql-test/r/variables.result:
  Set GLOBAL max_join_size to 10 as it originally was in variables-master.opt
mysql-test/r/view_grant.result:
  Dop user "test@localhost" to cleanup
mysql-test/t/analyze.test:
  Drop table t1 to cleanup
mysql-test/t/create_select_tmp.test:
  Drop table t1 to cleanup
mysql-test/t/ctype_cp932.test:
  Drop table t1 to cleanup
mysql-test/t/ctype_recoding.test:
  Drop table t1 to cleanup
mysql-test/t/fulltext_var.test:
  Restore the original ft_boolean_syntax
mysql-test/t/grant2.test:
  Drop users "mysqltest_2" and "mysqltest_A@'%'" to cleanup
mysql-test/t/innodb_cache.test:
  Reset query_cache_size to original value
mysql-test/t/join_outer.test:
  Drop view v1 to cleanup
mysql-test/t/ps_1general.test:
  Drop table t1 to cleanup
mysql-test/t/query_cache.test:
  Drop function "f1" to cleanup
mysql-test/t/read_only.test:
  Reset the readonly flag
mysql-test/t/rpl000001.test:
  Delete user "blafasel2" to cleanup
mysql-test/t/rpl000017.test:
  Delete user "replicate" to cleanup
mysql-test/t/rpl_failed_optimize.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_flush_tables.test:
  Droip table t3, t4 and t5 to cleanup
mysql-test/t/rpl_ignore_revoke.test:
  Delet user "user_foo" to cleanup
mysql-test/t/rpl_insert_id.test:
  drop table t1 to cleanup
mysql-test/t/rpl_loaddata.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_loaddata_rule_m.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_loaddata_rule_s.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_misc_functions.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_multi_update3.test:
  Drop table t1 and t2 to cleanup
mysql-test/t/rpl_replicate_do.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_skip_error.test:
  Drop table t1 to cleanup
mysql-test/t/rpl_slave_status.test:
  Drop table t1 to cleanup
mysql-test/t/sp-prelocking.test:
  Drop table t1, t2 t3 and t4 to cleanup
  Drop view v1
mysql-test/t/sp-security.test:
  Delete  test users from mysql.user, mysql.db, mysql.procs_priv and mysql.tables_priv
  Drop table t1 to cleanup
mysql-test/t/subselect_innodb.test:
  Drop procedure p1 to cleanup
mysql-test/t/trigger-compat.test:
  Drop trigger wl2818_trg1 and wl2818_trg2 to cleanup
  Drop table t1, t2
  Drop users
  drop database mysqltest_db1
mysql-test/t/type_bit.test:
  drop table t1 and t2 to cleanup
mysql-test/t/variables-master.opt:
  Increase max_join_size to 100.
mysql-test/t/variables.test:
  Set max_join_size to 10, which was the original value in variables-master.opt
mysql-test/t/view_grant.test:
  Drop the user "test@localhost"
mysql-test/include/check-testcase.test:
  New BitKeeper file ``mysql-test/include/check-testcase.test''

30 files changed, 49 insertions, 2 deletions

diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result
index 17686d597a4..65c6955a959 100644
--- a/mysql-test/r/analyze.result
+++ b/mysql-test/r/analyze.result
@@ -46,3 +46,4 @@ Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_
 execute stmt1;
 Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
 deallocate prepare stmt1;
+drop table t1;
diff --git a/mysql-test/r/create_select_tmp.result b/mysql-test/r/create_select_tmp.result
index b99bf3e3591..668547bcff9 100644
--- a/mysql-test/r/create_select_tmp.result
+++ b/mysql-test/r/create_select_tmp.result
@@ -17,3 +17,4 @@ CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) ENGINE=MYISAM SELECT a FROM t1;
 ERROR 23000: Duplicate entry '1' for key 1
 select * from t2;
 ERROR 42S02: Table 'test.t2' doesn't exist
+drop table t1;
diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result
index 1afceb86381..61540cb8467 100755
--- a/mysql-test/r/ctype_cp932.result
+++ b/mysql-test/r/ctype_cp932.result
@@ -11366,3 +11366,4 @@ col1
 a
 a
 a
+drop table t1;
diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result
index 1b92da2a7a3..996f6fa8645 100644
--- a/mysql-test/r/ctype_recoding.result
+++ b/mysql-test/r/ctype_recoding.result
@@ -247,3 +247,4 @@ lpad(c1,3,'ö')	lpad('ö',3,c1)
 select rpad(c1,3,'ö'), rpad('ö',3,c1) from t1;
 rpad(c1,3,'ö')	rpad('ö',3,c1)
 ßöö	ößß
+drop table t1;
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result
index 246aaa3a93e..e38953b6446 100644
--- a/mysql-test/r/grant2.result
+++ b/mysql-test/r/grant2.result
@@ -238,6 +238,7 @@ ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%'
 create user 'mysqltest_2' identified by 'Mysqltest-2';
 drop user 'mysqltest_2' identified by 'Mysqltest-2';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'Mysqltest-2'' at line 1
+drop user 'mysqltest_2';
 create user '%@b'@'b';
 show grants for '%@b'@'b';
 Grants for %@b@b
@@ -279,6 +280,7 @@ Warning	1364	Field 'x509_subject' doesn't have a default value
 create user mysqltest_A@'%';
 rename user mysqltest_B@'%' to mysqltest_C@'%';
 drop user mysqltest_C@'%';
+drop user mysqltest_A@'%';
 drop user mysqltest_3@localhost;
 set @@sql_mode='';
 create database mysqltest_1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 92b352aa608..bd725d9ac84 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -992,6 +992,7 @@ SELECT * FROM v1 WHERE EMPNUM < 10;
 EMPNUM	NAME	GRP
 0	KERI	10
 9	BARRY	NULL
+DROP VIEW v1;
 DROP TABLE t1,t2;
 CREATE TABLE t1 (c11 int);
 CREATE TABLE t2 (c21 int);
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index aba3da02c4c..8e22c5daa8e 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -797,4 +797,4 @@ prepare stmt1 from @string ;
 execute stmt1 ;
 prepare stmt1 from ' select * from t5 ' ;
 execute stmt1 ;
-drop table t5, t9;
+drop table t1, t5, t9;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 405669e95f8..5a7c387a650 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1074,6 +1074,7 @@ a	f1()
 1	2
 2	2
 drop procedure p1//
+drop function f1//
 drop table t1//
 flush query cache;
 reset query cache;
diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result
index 55a14bcaec8..1a1991a6255 100644
--- a/mysql-test/r/read_only.result
+++ b/mysql-test/r/read_only.result
@@ -41,3 +41,4 @@ insert into t1 values(1);
 ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
 drop table t1,t2;
 drop user test@localhost;
+set global read_only=0;
diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result
index de9f6f16b2a..3017a2b76ce 100644
--- a/mysql-test/r/rpl000001.result
+++ b/mysql-test/r/rpl000001.result
@@ -79,3 +79,4 @@ select select_priv,user from mysql.user where user = _binary'blafasel2';
 select_priv	user
 Y	blafasel2
 drop table t1;
+delete from mysql.user where user="blafasel2";
diff --git a/mysql-test/r/rpl000017.result b/mysql-test/r/rpl000017.result
index 64e13042e9c..7a283699370 100644
--- a/mysql-test/r/rpl000017.result
+++ b/mysql-test/r/rpl000017.result
@@ -9,3 +9,4 @@ select * from t1;
 n
 24
 drop table t1;
+delete from mysql.user where user="replicate";
diff --git a/mysql-test/r/rpl_failed_optimize.result b/mysql-test/r/rpl_failed_optimize.result
index fd711f89222..c2c07dc6343 100644
--- a/mysql-test/r/rpl_failed_optimize.result
+++ b/mysql-test/r/rpl_failed_optimize.result
@@ -18,3 +18,4 @@ Table	Op	Msg_type	Msg_text
 test.non_existing	optimize	error	Table 'test.non_existing' doesn't exist
 Warnings:
 Error	1146	Table 'test.non_existing' doesn't exist
+drop table t1;
diff --git a/mysql-test/r/rpl_flush_tables.result b/mysql-test/r/rpl_flush_tables.result
index 667530c4071..7d0bc54ae31 100644
--- a/mysql-test/r/rpl_flush_tables.result
+++ b/mysql-test/r/rpl_flush_tables.result
@@ -44,3 +44,4 @@ flush tables with read lock;
 start slave;
 stop slave;
 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+drop table t3, t4, t5;
diff --git a/mysql-test/r/rpl_ignore_revoke.result b/mysql-test/r/rpl_ignore_revoke.result
index 094b571f4f4..42625119f28 100644
--- a/mysql-test/r/rpl_ignore_revoke.result
+++ b/mysql-test/r/rpl_ignore_revoke.result
@@ -26,3 +26,4 @@ select select_priv from mysql.user where user='user_foo' /* slave:must get Y */;
 select_priv
 Y
 revoke select on *.* FROM 'user_foo';
+delete from mysql.user where user="user_foo";
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index 8482f631553..d7b6946f7e5 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -73,3 +73,4 @@ CREATE TABLE t1 ( a INT UNIQUE );
 SET FOREIGN_KEY_CHECKS=0;
 INSERT INTO t1 VALUES (1),(1);
 ERROR 23000: Duplicate entry '1' for key 1
+drop table t1;
diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result
index dc11d10fab8..b8190bc0c5b 100644
--- a/mysql-test/r/rpl_loaddata.result
+++ b/mysql-test/r/rpl_loaddata.result
@@ -79,3 +79,4 @@ terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by
 ERROR 23000: Duplicate entry '2003-03-22' for key 1
 drop table t2;
 drop table t2;
+drop table t1;
diff --git a/mysql-test/r/rpl_loaddata_rule_m.result b/mysql-test/r/rpl_loaddata_rule_m.result
index 3571c9973ea..dd68df4fd64 100644
--- a/mysql-test/r/rpl_loaddata_rule_m.result
+++ b/mysql-test/r/rpl_loaddata_rule_m.result
@@ -17,3 +17,5 @@ master-bin.000001	#	Query	1	#	create database mysqltest
 master-bin.000001	#	Begin_load_query	1	#	;file_id=1;block_len=12
 master-bin.000001	#	Execute_load_query	1	#	use `mysqltest`; load data infile '../../std_data/rpl_loaddata.dat' into table test.t1 ;file_id=1
 drop database mysqltest;
+use test;
+drop table t1;
diff --git a/mysql-test/r/rpl_loaddata_rule_s.result b/mysql-test/r/rpl_loaddata_rule_s.result
index 16720c81374..b0a5c07d24b 100644
--- a/mysql-test/r/rpl_loaddata_rule_s.result
+++ b/mysql-test/r/rpl_loaddata_rule_s.result
@@ -12,3 +12,4 @@ count(*)
 2
 show binlog events from 98;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+drop table t1;
diff --git a/mysql-test/r/rpl_misc_functions.result b/mysql-test/r/rpl_misc_functions.result
index a687063706d..864b9660bf7 100644
--- a/mysql-test/r/rpl_misc_functions.result
+++ b/mysql-test/r/rpl_misc_functions.result
@@ -19,3 +19,5 @@ load data local infile './var/master-data/test/rpl_misc_functions.outfile' into
 select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p);
 id	i	r1	r2	p	id	i	r1	r2	p
 stop slave;
+drop table t1;
+drop table t1;
diff --git a/mysql-test/r/rpl_multi_update3.result b/mysql-test/r/rpl_multi_update3.result
index b81af7c6e39..bf454c7bb48 100644
--- a/mysql-test/r/rpl_multi_update3.result
+++ b/mysql-test/r/rpl_multi_update3.result
@@ -194,3 +194,4 @@ idpro	price	nbprice
 1	1.0000	3
 2	1.0000	2
 3	2.0000	1
+drop table t1, t2;
diff --git a/mysql-test/r/rpl_replicate_do.result b/mysql-test/r/rpl_replicate_do.result
index d8666080a71..1991567d23d 100644
--- a/mysql-test/r/rpl_replicate_do.result
+++ b/mysql-test/r/rpl_replicate_do.result
@@ -40,3 +40,4 @@ set one_shot time_zone='met';
 select * from t1;
 ts
 2005-08-12 00:00:00
+drop table t1;
diff --git a/mysql-test/r/rpl_skip_error.result b/mysql-test/r/rpl_skip_error.result
index e52426c381c..adc61f8c2c8 100644
--- a/mysql-test/r/rpl_skip_error.result
+++ b/mysql-test/r/rpl_skip_error.result
@@ -13,3 +13,4 @@ n
 1
 2
 3
+drop table t1;
diff --git a/mysql-test/r/rpl_slave_status.result b/mysql-test/r/rpl_slave_status.result
index 2146132aeb0..9a4ec4131b0 100644
--- a/mysql-test/r/rpl_slave_status.result
+++ b/mysql-test/r/rpl_slave_status.result
@@ -52,3 +52,4 @@ Master_SSL_Cert
 Master_SSL_Cipher	
 Master_SSL_Key	
 Seconds_Behind_Master	NULL
+drop table t1;
diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
index 32d6d4e6264..c2614892016 100644
--- a/mysql-test/r/sp-prelocking.result
+++ b/mysql-test/r/sp-prelocking.result
@@ -208,7 +208,8 @@ select f3() //
 f3()
 1
 call sp1() //
-drop table t1,t2,t3;
+drop view v1;
+drop table t1,t2,t3,t4;
 drop function f1;
 drop function f2;
 drop function f3;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 614e670f25d..82fef25e906 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -134,7 +134,11 @@ drop database db2;
 select type,db,name from mysql.proc;
 type	db	name
 delete from mysql.user where user='user1' or user='user2';
+delete from mysql.user where user='' and host='%';
 delete from mysql.procs_priv where user='user1' or user='user2';
+delete from mysql.procs_priv where user='' and host='%';
+delete from mysql.db where user='user2';
+flush privileges;
 grant usage on *.* to usera@localhost;
 grant usage on *.* to userb@localhost;
 grant usage on *.* to userc@localhost;
@@ -195,6 +199,9 @@ use test;
 drop database sptest;
 delete from mysql.user where user='usera' or user='userb' or user='userc';
 delete from mysql.procs_priv where user='usera' or user='userb' or user='userc';
+delete from mysql.tables_priv where user='usera';
+flush privileges;
+drop table t1;
 drop function if exists bug_9503;
 create database mysqltest//
 use mysqltest//
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index 4dec7882d58..6c6d563e284 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -243,4 +243,5 @@ x
 NULL
 x
 NULL
+drop procedure p1;
 drop tables t1,t2,t3;
diff --git a/mysql-test/r/trigger-compat.result b/mysql-test/r/trigger-compat.result
index 5c104a2d2d5..5a203561662 100644
--- a/mysql-test/r/trigger-compat.result
+++ b/mysql-test/r/trigger-compat.result
@@ -38,3 +38,13 @@ NULL	mysqltest_db1	wl2818_trg1	INSERT	NULL	mysqltest_db1	t1	0	NULL
 INSERT INTO t2 VALUES(CURRENT_USER())	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		
 NULL	mysqltest_db1	wl2818_trg2	INSERT	NULL	mysqltest_db1	t1	0	NULL	
 INSERT INTO t2 VALUES(CURRENT_USER())	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		mysqltest_dfn@localhost
+DROP TRIGGER wl2818_trg1;
+Warnings:
+Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
+DROP TRIGGER wl2818_trg2;
+use mysqltest_db1;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP USER mysqltest_dfn@localhost;
+DROP USER mysqltest_inv@localhost;
+DROP DATABASE mysqltest_db1;
diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result
index 0213dbaffde..c6f204e8452 100644
--- a/mysql-test/r/type_bit.result
+++ b/mysql-test/r/type_bit.result
@@ -563,3 +563,4 @@ b1+0	sum(b1)	sum(b2)
 0	0	0
 1	4	4
 2	2	2
+drop table t1, t2;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index df180218a09..0d4c1386148 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -71,6 +71,7 @@ c_id	c_name	c_country
 1	Bozo	USA
 4	Mr. Floppy	GB
 drop table t1;
+set GLOBAL max_join_size=10;
 set max_join_size=100;
 show variables like 'max_join_size';
 Variable_name	Value
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index e2ee02351d7..259106c4e9f 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -7,6 +7,7 @@ revoke create view on test.* from test@localhost;
 show grants for test@localhost;
 Grants for test@localhost
 GRANT USAGE ON *.* TO 'test'@'localhost'
+drop user test@localhost;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
 create table mysqltest.t2 (a int, b int);