Merge sanja.is.com.ua:/home/bell/mysql/bk/mysql-4.1

into sanja.is.com.ua:/home/bell/mysql/bk/work-4.1

25 files changed, 397 insertions, 219 deletions

diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 122dcaa6c49..5fed85d7f50 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -123,7 +123,7 @@ connection user1;
 alter table t1 rename t2;
 connection root;
 revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
+delete from mysql.user where user=_binary'mysqltest_1';
 drop database mysqltest;
 
 #
diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test
index 7585ff0f608..32c1479ae04 100644
--- a/mysql-test/t/connect.test
+++ b/mysql-test/t/connect.test
@@ -42,7 +42,7 @@ show tables;
 
 
 # check if old password version also works
-update mysql.user set password=old_password("gambling2") where user="test";
+update mysql.user set password=old_password("gambling2") where user=_binary"test";
 flush privileges;
 
 #connect (con1,localhost,test,gambling2,"");
@@ -68,5 +68,5 @@ show tables;
 # remove user 'test' so that other tests which may use 'test'
 # do not depend on this test.
 
-delete from mysql.user where user="test";
+delete from mysql.user where user=_binary"test";
 flush privileges;
diff --git a/mysql-test/t/ctype_create.test b/mysql-test/t/ctype_create.test
index bd8c22bb4f6..6d7ed6fc205 100644
--- a/mysql-test/t/ctype_create.test
+++ b/mysql-test/t/ctype_create.test
@@ -13,25 +13,48 @@
 
 
 SET @@character_set_server=latin5;
-CREATE DATABASE db1 DEFAULT CHARACTER SET cp1251;
-USE db1;
-CREATE DATABASE db2;
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET cp1251;
+USE mysqltest1;
+CREATE DATABASE mysqltest2;
 
 #
 # This should be cp1251
 #
-SHOW CREATE DATABASE db1;
+SHOW CREATE DATABASE mysqltest1;
 
 #
-# This should take the default latin5 value from server level.
+# Database "mysqltest2" should take the default latin5 value from
+# the server level.
+# Afterwards, table "d2.t1" should inherit the default latin5 value from
+# the database "mysqltest2", using database option hash.
 #
-SHOW CREATE DATABASE db2;
-DROP DATABASE db2;
+SHOW CREATE DATABASE mysqltest2;
+CREATE TABLE mysqltest2.t1 (a char(10));
+SHOW CREATE TABLE mysqltest2.t1;
+DROP TABLE mysqltest2.t1;
+
+#
+# Now we check if the database charset is updated in
+# the database options hash when we ALTER DATABASE.
+#
+ALTER DATABASE mysqltest2 DEFAULT CHARACTER SET latin7;
+CREATE TABLE mysqltest2.t1 (a char(10));
+SHOW CREATE TABLE mysqltest2.t1;
+DROP DATABASE mysqltest2;
+
+#
+# Now we check if the database charset is removed from
+# the database option hash when we DROP DATABASE.
+#
+CREATE DATABASE mysqltest2 CHARACTER SET latin2;
+CREATE TABLE mysqltest2.t1 (a char(10));
+SHOW CREATE TABLE mysqltest2.t1;
+DROP DATABASE mysqltest2;
 
 #
 # Check that table value uses database level by default
 #
-USE db1;
+USE mysqltest1;
 CREATE TABLE t1 (a char(10));
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
@@ -50,4 +73,4 @@ DROP TABLE t1;
 #
 #
 #
-DROP DATABASE db1;
+DROP DATABASE mysqltest1;
diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test
index 8ac22e89c2d..de6332f272c 100644
--- a/mysql-test/t/ctype_recoding.test
+++ b/mysql-test/t/ctype_recoding.test
@@ -78,3 +78,32 @@ SET NAMES koi8r;
 SELECT hex('����');
 SET character_set_connection=cp1251;
 SELECT hex('����');
+
+USE test;
+
+# Bug#4417
+# Check that identifiers and strings are not converted 
+# when the client character set is binary.
+
+SET NAMES binary;
+CREATE TABLE `тест` (`тест` int);
+SHOW CREATE TABLE `тест`;
+SET NAMES utf8;
+SHOW CREATE TABLE `тест`;
+DROP TABLE `тест`;
+SET NAMES binary;
+SET character_set_connection=utf8;
+SELECT 'тест' as s;
+SET NAMES utf8;
+SET character_set_connection=binary;
+SELECT 'тест' as s;
+
+#
+# Test that we allow only well-formed UTF8 identitiers
+#
+SET NAMES binary;
+--error 1300
+CREATE TABLE `good�����` (a int);
+SET NAMES utf8;
+--error 1300
+CREATE TABLE `good�����` (a int);
diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test
index 6ff1407247f..0ab46a5a637 100644
--- a/mysql-test/t/ctype_uca.test
+++ b/mysql-test/t/ctype_uca.test
@@ -178,4 +178,5 @@ select group_concat(c1 order by c1) from t1 group by c1 collate utf8_danish_ci;
 select group_concat(c1 order by c1) from t1 group by c1 collate utf8_lithuanian_ci;
 --select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci;
 select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci;
+select group_concat(c1 order by c1) from t1 group by c1 collate utf8_roman_ci;
 
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index cc8d8a88437..61d0326f7dd 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -78,7 +78,7 @@ select quote(concat('abc\'', '\\cba'));
 select quote(1/0), quote('\0\Z');
 select length(quote(concat(char(0),"test")));
 select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
-select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678");
+select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
 select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
 select length(unhex(md5("abrakadabra")));
 
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index 6bd2fa0c703..c112a0e0c1d 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -5,6 +5,8 @@
 drop table if exists t1;
 --enable_warnings
 
+SET NAMES binary;
+
 #
 # Test that SSL options works properly
 #
diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test
index 1fc1ed78385..3a9afa7453b 100644
--- a/mysql-test/t/grant2.test
+++ b/mysql-test/t/grant2.test
@@ -1,3 +1,5 @@
+SET NAMES binary;
+
 #
 # GRANT tests that require several connections
 # (usually it's GRANT, reconnect as another user, try something)
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test
index 7806253124e..a82cd732802 100644
--- a/mysql-test/t/grant_cache.test
+++ b/mysql-test/t/grant_cache.test
@@ -126,6 +126,15 @@ show status like "Qcache_not_cached";
 # Cleanup
 
 connection root;
+#
+# A temporary 4.1 workaround to make this test pass if
+# mysql was compiled with other than latin1 --with-charset=XXX.
+# Without "set names binary" the below queries fail with
+# "Illegal mix of collations" error.
+# In 5.0 we will change grant tables to use NCHAR(N) instead
+# of "CHAR(N) BINARY", and use cast-to-nchar:  N'mysqltest_1'.
+#
+set names binary;
 delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
 delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
 delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 492856f9280..c2814606aa2 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -405,7 +405,7 @@ update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
 connection root;
 revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
+delete from mysql.user where user=_binary'mysqltest_1';
 drop database mysqltest;
 
 #
diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test
index 79686fce0e1..feb4476f5e7 100644
--- a/mysql-test/t/ndb_index_ordered.test
+++ b/mysql-test/t/ndb_index_ordered.test
@@ -106,7 +106,7 @@ select * from t1 where b<=5 order by a;
 select * from t1 where b<=5 and c=0;
 insert into t1 values (19,4, 0);
 select * from t1 where b<=5 and c=0;
-select * from t1 where b=4 and c<=5;
+select * from t1 where b=4 and c<=5 order by a;
 select * from t1 where b<=4 and c<=5 order by a;
 select * from t1 where b<=5 and c=0 or b<=5 and c=2;
 drop table t1;
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index b7686bbd26a..2c86c30f820 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -4,15 +4,9 @@
 #                                                            #
 ##############################################################
 
-
-# Please do not 
-#     - modify (INSERT/UPDATE/DELETE) the content of the tables
-#       t1 and t_many_col_types. Such tests should be done in 
-#           include/ps_modify.inc
-#     - insert test cases where the results depend on the 
-#       table type. Such tests should be done in the files
-#           include/ps_query.inc, include/ps_modify ...
-#
+#    
+# NOTE: PLEASE SEE THE DETAILED DESCRIPTION AT THE BOTTOM OF THIS FILE
+#       BEFORE ADDING NEW TEST CASES HERE !!!
 
 use test;
 --disable_query_log
@@ -375,7 +369,6 @@ revoke all privileges on test.t1 from drop_user@localhost ;
 --error 1295
 prepare stmt3 from ' drop user drop_user@localhost ';
 drop user drop_user@localhost;
---error 1141
 
 #### table related commands
 ## describe
@@ -737,3 +730,101 @@ execute stmt1 using
 @arg60, @arg61 ;
 
 drop table t1 ;
+
+##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES #####
+#
+# 0. You  don't have the time to 
+#    - read and pay attention to these rules of thumb
+#    - accept that QA may move your test case to a different place
+#      (I will not change your code!!) .
+#    Please append your test case to
+#        t/ps.test
+#
+# 1. You have more time and want to get as much value from you test case as
+#    possible. Please try to make the following decisions:
+#
+#    Will the execution or result of the sub test case depend on the
+#    properties of a storage engine ?
+#
+#      NO   --> alter t/ps_1general.test (Example: Command with syntax error)
+#               If you need a table, please try to use
+#               t1               - very simple table
+#               t_many_col_types - table with nearly all available column types
+#               whenever possible.
+#
+#               The structure and the content of these tables can be found in
+#               include/ps_create.inc  CREATE TABLE ...
+#               include/ps_renew.inc   DELETE all rows and INSERT some rows
+#
+#               Both tables are managed by the same storage engine.
+#               The type of the storage engine is stored in the variable 
+#               '$type' .  In ps_1general.test $type is set to 'MYISAM'.
+#    
+#               Please feel free to source ps_create.inc or ps_renew.inc
+#               whenever you think it helps. But please restore the original
+#               state of these tables after your tests, because the following
+#               statements may depend on it.
+#
+#      YES
+#       |
+#       |
+#    Is it possible to apply the sub test case to all table types ?
+#      YES  --> alter include/ps_query.inc   (for SELECTs)
+#                     include/ps_modify.inc  (for INSERT/UPDATE/DELETE)
+#                     include/ps_modify1.inc (also for INSERT/UPDATE/DELETE,
+#                                but t/ps_5merge.test will not source that file)
+#               Please try to find an appropriate place within the file.
+#               It would be nice if we have some systematics in the
+#               order of the sub test cases (if possible).
+#
+#               Please be aware, that
+#                  include: ps_query.inc, ps_modify.inc, ps_modify1.inc
+#               will be sourced by several test case files stored within the 
+#               subdirectory 't'. So every change here will affect several test
+#               cases.
+#
+#      NO
+#       |
+#       |
+#    Append the sub test case to the appropriate 
+#                  ps_<number><table type>.test  .
+#
+# 2. The current structure of the PS tests
+#
+#    t/ps_1general.test     Check of basic PS features, SHOW commands and DDL
+#                           The tests should not depend on the table type.
+#
+#    t/ps_2myisam           Check of PS on tables of type MYISAM .
+#    t/ps_3innodb           Check of PS on tables of type InnoDB .
+#    ...
+#    t/ps_6bdb              Check of PS on tables of type BDB .
+#         All storage engine related tests use the variable $type to hold the
+#         name of the storage engine.        
+#
+#    include/ps_query.inc   test cases with SELECT/... 
+#                           These test cases should not modify the content or
+#                           the structure (DROP/ALTER..) of the tables
+#                           't1' and 't_many_col_types'.
+#    include/ps_modify.inc  test cases with INSERT/UPDATE/... 
+#                           These test cases should not modify the structure 
+#                           (DROP/ALTER..) of the tables
+#                           't1' and 't_many_col_types'.
+#         These two test sequences will be applied to all table types .
+#
+#    include/ps_modify1.inc test cases with INSERT/UPDATE/...
+#         This test sequences will be applied to all table types 
+#         except MERGE tables.
+#
+#    include/ps_create.inc  DROP and CREATE of the tables 
+#                             't1' and 't_many_col_types' .
+#    include/ps_renew.inc   DELETE all rows and INSERT some rows, that means
+#                           recreate the original content of these tables.
+#         Please do not alter the commands concerning these two tables.
+#
+#  Please feel free and encouraged to exploit the current code sharing
+#  mechanism of the 'ps_<number><table type>' test cases. It is an convenient
+#  way to check all storage engines.
+#
+#  Thank you for reading these rules of thumb.
+#
+#     Matthias
diff --git a/mysql-test/t/ps_2myisam.test b/mysql-test/t/ps_2myisam.test
index 3bb8b01cf87..c7e4746762b 100644
--- a/mysql-test/t/ps_2myisam.test
+++ b/mysql-test/t/ps_2myisam.test
@@ -4,6 +4,10 @@
 #                                             #
 ###############################################
 
+#    
+# NOTE: PLEASE SEE ps_1general.test (bottom) 
+#       BEFORE ADDING NEW TEST CASES HERE !!!
+
 use test;
 
 let $type= 'MYISAM' ;
diff --git a/mysql-test/t/ps_3innodb.test b/mysql-test/t/ps_3innodb.test
index 71acf3b76f1..055e1e127e5 100644
--- a/mysql-test/t/ps_3innodb.test
+++ b/mysql-test/t/ps_3innodb.test
@@ -4,8 +4,14 @@
 #                                             #
 ###############################################
 
+#    
+# NOTE: PLEASE SEE ps_1general.test (bottom) 
+#       BEFORE ADDING NEW TEST CASES HERE !!!
+
 use test;
 
+-- source include/have_innodb.inc
+
 let $type= 'InnoDB' ;
 -- source include/ps_create.inc
 -- source include/ps_renew.inc
diff --git a/mysql-test/t/ps_4heap.test b/mysql-test/t/ps_4heap.test
index 8aa715b095e..d1c81d95cd7 100644
--- a/mysql-test/t/ps_4heap.test
+++ b/mysql-test/t/ps_4heap.test
@@ -4,6 +4,10 @@
 #                                             #
 ###############################################
 
+#    
+# NOTE: PLEASE SEE ps_1general.test (bottom) 
+#       BEFORE ADDING NEW TEST CASES HERE !!!
+
 use test;
 
 let $type= 'HEAP' ;
@@ -19,7 +23,8 @@ eval create table t1
 --disable_warnings
 drop table if exists t_many_col_types;
 --enable_warnings
-# The used table type doesn't support BLOB/TEXT columns (error 1163)
+# The used table type doesn't support BLOB/TEXT columns.
+# (The server would send error 1163  .)
 # So we use char(100) instead.
 eval create table t_many_col_types 
 (
diff --git a/mysql-test/t/ps_5merge.test b/mysql-test/t/ps_5merge.test
index ee4beea78c4..ff48a50f331 100644
--- a/mysql-test/t/ps_5merge.test
+++ b/mysql-test/t/ps_5merge.test
@@ -4,6 +4,10 @@
 #                                             #
 ###############################################
 
+#    
+# NOTE: PLEASE SEE ps_1general.test (bottom) 
+#       BEFORE ADDING NEW TEST CASES HERE !!!
+
 use test;
 
 --disable_warnings
diff --git a/mysql-test/t/ps_6bdb.test b/mysql-test/t/ps_6bdb.test
index dde6a05268e..7dbd08f5baa 100644
--- a/mysql-test/t/ps_6bdb.test
+++ b/mysql-test/t/ps_6bdb.test
@@ -4,6 +4,10 @@
 #                                             #
 ###############################################
 
+#    
+# NOTE: PLEASE SEE ps_1general.test (bottom) 
+#       BEFORE ADDING NEW TEST CASES HERE !!!
+
 use test;
 
 -- source include/have_bdb.inc
diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test
index 445bd579279..2e0ba2fff25 100644
--- a/mysql-test/t/rpl000001.test
+++ b/mysql-test/t/rpl000001.test
@@ -110,14 +110,14 @@ create table t1 (n int);
 insert into t1 values(3456);
 insert into mysql.user (Host, User, Password)
  VALUES ("10.10.10.%", "blafasel2", password("blafasel2"));
-select select_priv,user from mysql.user where user = 'blafasel2';
-update mysql.user set Select_priv = "Y" where User="blafasel2";
-select select_priv,user from mysql.user where user = 'blafasel2';
+select select_priv,user from mysql.user where user = _binary'blafasel2';
+update mysql.user set Select_priv = "Y" where User= _binary"blafasel2";
+select select_priv,user from mysql.user where user = _binary'blafasel2';
 save_master_pos;
 connection slave;
 sync_with_master;
 select n from t1;
-select select_priv,user from mysql.user where user = 'blafasel2';
+select select_priv,user from mysql.user where user = _binary'blafasel2';
 connection master1;
 drop table t1;
 save_master_pos;
diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test
index c13b57a3108..02bcdf1f5f1 100644
--- a/mysql-test/t/rpl_charset.test
+++ b/mysql-test/t/rpl_charset.test
@@ -26,7 +26,7 @@ show create database test2;
 show create database test3;
 
 connection master;
-set @@collation_server=armscii_bin;
+set @@collation_server=armscii8_bin;
 drop database test3;
 create database test3;
 --disable_query_log
diff --git a/mysql-test/t/rpl_do_grant.test b/mysql-test/t/rpl_do_grant.test
index 89ff1afb5c9..27a22874497 100644
--- a/mysql-test/t/rpl_do_grant.test
+++ b/mysql-test/t/rpl_do_grant.test
@@ -4,16 +4,16 @@ 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';
+delete from mysql.user where user=_binary'rpl_do_grant';
+delete from mysql.db where user=_binary'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';
+delete from mysql.user where user=_binary'rpl_ignore_grant';
+delete from mysql.db where user=_binary'rpl_ignore_grant';
 flush privileges;
 
 # test replication of GRANT
@@ -31,12 +31,12 @@ 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';
+select password<>_binary'' from mysql.user where user=_binary'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';
+delete from mysql.user where user=_binary'rpl_do_grant';
+delete from mysql.db where user=_binary'rpl_do_grant';
 flush privileges;
 save_master_pos;
 connection slave;
diff --git a/mysql-test/t/rpl_ignore_grant.test b/mysql-test/t/rpl_ignore_grant.test
index 2fd7f186b3e..9b012d08df3 100644
--- a/mysql-test/t/rpl_ignore_grant.test
+++ b/mysql-test/t/rpl_ignore_grant.test
@@ -6,16 +6,16 @@ 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';
+delete from mysql.user where user=_binary'rpl_ignore_grant';
+delete from mysql.db where user=_binary'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';
+delete from mysql.user where user=_binary'rpl_ignore_grant';
+delete from mysql.db where user=_binary'rpl_ignore_grant';
 flush privileges;
 
 # test non-replication of GRANT
@@ -29,8 +29,8 @@ 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';
+select count(*) from mysql.user where user=_binary'rpl_ignore_grant';
+select count(*) from mysql.db where user=_binary'rpl_ignore_grant';
 
 # test non-replication of SET PASSWORD
 # first force creation of the user on slave (because as the user does not exist
@@ -42,16 +42,16 @@ 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';
+select password<>_binary'' from mysql.user where user=_binary'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';
+delete from mysql.user where user=_binary'rpl_ignore_grant';
+delete from mysql.db where user=_binary'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';
+delete from mysql.user where user=_binary'rpl_ignore_grant';
+delete from mysql.db where user=_binary'rpl_ignore_grant';
 flush privileges;
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 58694356943..ac0c9a43010 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -299,6 +299,7 @@ drop table test_$1.t1;
 drop database test_$1;
 
 connection default;
+set names binary;
 delete from mysql.user 
 where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
 delete from mysql.db   
diff --git a/mysql-test/t/system_mysql_db.test b/mysql-test/t/system_mysql_db.test
index bd69297b739..a6d683489c3 100644
--- a/mysql-test/t/system_mysql_db.test
+++ b/mysql-test/t/system_mysql_db.test
@@ -1,8 +1,13 @@
-#

-# This test must examine integrity of system database "mysql"

-#

-

--- disable_query_log

-use mysql;

--- enable_query_log

--- source include/system_db_struct.inc

+#
+# This test must examine integrity of system database "mysql"
+#
+
+-- disable_query_log
+use mysql;
+-- enable_query_log
+-- source include/system_db_struct.inc
+-- disable_query_log
+use test;
+-- enable_query_log
+# keep results same with system_mysql_db_fix
+show tables;
diff --git a/mysql-test/t/system_mysql_db_fix.test b/mysql-test/t/system_mysql_db_fix.test
index a81772c74e4..6c44535e3b7 100644
--- a/mysql-test/t/system_mysql_db_fix.test
+++ b/mysql-test/t/system_mysql_db_fix.test
@@ -1,83 +1,76 @@
-#

+#
 # This is the test for mysql_fix_privilege_tables
-#

+#
 
--- disable_result_log

--- disable_query_log

-

-use test;

-

-# create system tables as in mysql-3.20

-

-CREATE TABLE db (

-  Host char(60) binary DEFAULT '' NOT NULL,

-  Db char(32) binary DEFAULT '' NOT NULL,

-  User char(16) binary DEFAULT '' NOT NULL,

-  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  PRIMARY KEY Host (Host,Db,User),

-  KEY User (User)

-)

-type=ISAM;

+-- disable_result_log
+-- disable_query_log
 
-INSERT INTO db VALUES ('%','test',   '','Y','Y','Y','Y','Y','Y');

-INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y');

+use test;
 
-CREATE TABLE host (

-  Host char(60) binary DEFAULT '' NOT NULL,

-  Db char(32) binary DEFAULT '' NOT NULL,

-  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  PRIMARY KEY Host (Host,Db)

-)

-type=ISAM;

+# create system tables as in mysql-3.20
 
-CREATE TABLE user (

-  Host char(60) binary DEFAULT '' NOT NULL,

-  User char(16) binary DEFAULT '' NOT NULL,

-  Password char(16),

-  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,

-  PRIMARY KEY Host (Host,User)

-)

-type=ISAM;

+CREATE TABLE db (
+  Host char(60) binary DEFAULT '' NOT NULL,
+  Db char(32) binary DEFAULT '' NOT NULL,
+  User char(16) binary DEFAULT '' NOT NULL,
+  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  PRIMARY KEY Host (Host,Db,User),
+  KEY User (User)
+)
+type=ISAM;
+
+INSERT INTO db VALUES ('%','test',   '','Y','Y','Y','Y','Y','Y');
+INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y');
+
+CREATE TABLE host (
+  Host char(60) binary DEFAULT '' NOT NULL,
+  Db char(32) binary DEFAULT '' NOT NULL,
+  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  PRIMARY KEY Host (Host,Db)
+)
+type=ISAM;
+
+CREATE TABLE user (
+  Host char(60) binary DEFAULT '' NOT NULL,
+  User char(16) binary DEFAULT '' NOT NULL,
+  Password char(16),
+  Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,
+  PRIMARY KEY Host (Host,User)
+)
+type=ISAM;
+
+INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y');
+INSERT INTO user VALUES ('localhost','',    '','N','N','N','N','N','N','N','N','N');
 
-INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y');

-INSERT INTO user VALUES ('localhost','',    '','N','N','N','N','N','N','N','N','N');

-

 -- exec $MYSQL_FIX_SYSTEM_TABLES --database=test
--- enable_query_log

--- enable_result_log

-

--- source include/system_db_struct.inc

-

--- disable_query_log

-

-DROP TABLE db;

-DROP TABLE host;

-DROP TABLE user;

-DROP TABLE func;

-DROP TABLE tables_priv;

-DROP TABLE columns_priv;

-DROP TABLE help_category;
-DROP TABLE help_keyword;
-DROP TABLE help_relation;
-DROP TABLE help_topic;
-

--- enable_query_log

+-- enable_query_log
+-- enable_result_log
+
+-- source include/system_db_struct.inc
+
+-- disable_query_log
+
+DROP TABLE db, host, user, func, tables_priv, columns_priv, help_category, help_keyword, help_relation, help_topic, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type;
+
+-- enable_query_log
 
+# check that we droped all system tables
+show tables;
diff --git a/mysql-test/t/system_mysql_db_refs.test b/mysql-test/t/system_mysql_db_refs.test
index 4c6557ba5c7..9e2c5a20b54 100644
--- a/mysql-test/t/system_mysql_db_refs.test
+++ b/mysql-test/t/system_mysql_db_refs.test
@@ -1,102 +1,101 @@
-#

-# This test must examine integrity of current system database

-#

-

-set @name="This is a very long string, that mustn't find room in a system field like Table_name. Thus it should be cut by the actual size of the field. So we can use this string to find out the actual length of the field and to use it in any compare queries";

-

-#

-# If this part is wrong, most likely you've done wrong modification of system database "mysql"

-#

-

-create table test_db select * from mysql.db;

-delete from test_db;

+#
+# This test must examine integrity of current system database
+#
+
+set @name="This is a very long string, that mustn't find room in a system field like Table_name. Thus it should be cut by the actual size of the field. So we can use this string to find out the actual length of the field and to use it in any compare queries";
+
+#
+# If this part is wrong, most likely you've done wrong modification of system database "mysql"
+#
+
+create table test_db select * from mysql.db;
+delete from test_db;
 --disable_warnings
-insert into test_db (Host,Db,User) values (@name,@name,@name);

+insert into test_db (Host,Db,User) values (@name,@name,@name);
 --enable_warnings
-

-create table test_host select * from mysql.host;

-delete from test_host;

+
+create table test_host select * from mysql.host;
+delete from test_host;
 --disable_warnings
-insert into test_host (Host,Db) values (@name,@name);

+insert into test_host (Host,Db) values (@name,@name);
 --enable_warnings
-

-create table test_user select * from mysql.user;

-delete from test_user;

+
+create table test_user select * from mysql.user;
+delete from test_user;
 --disable_warnings
-insert into test_user (Host,User) values (@name,@name);

+insert into test_user (Host,User) values (@name,@name);
 --enable_warnings
-

-create table test_func select * from mysql.func;

-delete from test_func;

+
+create table test_func select * from mysql.func;
+delete from test_func;
 --disable_warnings
-insert into test_func (name) values (@name);

+insert into test_func (name) values (@name);
 --enable_warnings
-

-create table test_tables_priv select * from mysql.tables_priv;

-delete from test_tables_priv;

+
+create table test_tables_priv select * from mysql.tables_priv;
+delete from test_tables_priv;
 --disable_warnings
-insert into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name);

+insert into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name);
 --enable_warnings
-

-create table test_columns_priv select * from mysql.columns_priv;

-delete from test_columns_priv;

+
+create table test_columns_priv select * from mysql.columns_priv;
+delete from test_columns_priv;
 --disable_warnings
-insert into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name);

+insert into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name);
 --enable_warnings
-

-# 'Host' field must be the same for all the tables:

-

+
+# 'Host' field must be the same for all the tables:
+
 select
- if(isnull(test_db.Host),'WRONG!!!','ok') as test_db_Host,

- if(isnull(test_host.Host),'WRONG!!!','ok') as test_host_Host,

- if(isnull(test_user.Host),'WRONG!!!','ok') as test_user_Host,

- if(isnull(test_tables_priv.Host),'WRONG!!!','ok') as test_tables_priv_Host,

- if(isnull(test_columns_priv.Host),'WRONG!!!','ok') as test_columns_priv_Host

-
-from      test_db

-left join test_host         on test_db.Host=test_host.Host

-left join test_user         on test_db.Host=test_user.Host

-left join test_tables_priv  on test_db.Host=test_tables_priv.Host

-left join test_columns_priv on test_db.Host=test_columns_priv.Host;

-

-# 'Db' field must be the same for all the tables:

-

+ if(isnull(test_db.Host),'WRONG!!!','ok') as test_db_Host,
+ if(isnull(test_host.Host),'WRONG!!!','ok') as test_host_Host,
+ if(isnull(test_user.Host),'WRONG!!!','ok') as test_user_Host,
+ if(isnull(test_tables_priv.Host),'WRONG!!!','ok') as test_tables_priv_Host,
+ if(isnull(test_columns_priv.Host),'WRONG!!!','ok') as test_columns_priv_Host
+
+from      test_db
+left join test_host         on test_db.Host=test_host.Host
+left join test_user         on test_db.Host=test_user.Host
+left join test_tables_priv  on test_db.Host=test_tables_priv.Host
+left join test_columns_priv on test_db.Host=test_columns_priv.Host;
+
+# 'Db' field must be the same for all the tables:
+
+select
+ if(isnull(test_db.Db),'WRONG!!!','ok') as test_db_Db,
+ if(isnull(test_host.Db),'WRONG!!!','ok') as test_host_Db,
+ if(isnull(test_tables_priv.Db),'WRONG!!!','ok') as test_tables_priv_Db,
+ if(isnull(test_columns_priv.Db),'WRONG!!!','ok') as est_columns_priv_Db
+
+from      test_db
+left join test_host         on test_db.Db=test_host.Db
+left join test_tables_priv  on test_db.Db=test_tables_priv.Db
+left join test_columns_priv on test_db.Db=test_columns_priv.Db;
+
+# 'User' field must be the same for all the tables:
+
+select
+ if(isnull(test_db.User),'WRONG!!!','ok') as test_db_User,
+ if(isnull(test_user.User),'WRONG!!!','ok') as test_user_User,
+ if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+ if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+
+from      test_db
+left join test_user         on test_db.User=test_user.User
+left join test_tables_priv  on test_db.User=test_tables_priv.User
+left join test_columns_priv on test_db.User=test_columns_priv.User;
+
+# 'Table_name' field must be the same for all the tables:
+
 select
- if(isnull(test_db.Db),'WRONG!!!','ok') as test_db_Db,

- if(isnull(test_host.Db),'WRONG!!!','ok') as test_host_Db,

- if(isnull(test_tables_priv.Db),'WRONG!!!','ok') as test_tables_priv_Db,

- if(isnull(test_columns_priv.Db),'WRONG!!!','ok') as est_columns_priv_Db

-
-from      test_db

-left join test_host         on test_db.Db=test_host.Db

-left join test_tables_priv  on test_db.Db=test_tables_priv.Db

-left join test_columns_priv on test_db.Db=test_columns_priv.Db;

-

-# 'User' field must be the same for all the tables:

-

-select

- if(isnull(test_db.User),'WRONG!!!','ok') as test_db_User,

- if(isnull(test_user.User),'WRONG!!!','ok') as test_user_User,

- if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,

- if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User

-
-from      test_db

-left join test_user         on test_db.User=test_user.User

-left join test_tables_priv  on test_db.User=test_tables_priv.User

-left join test_columns_priv on test_db.User=test_columns_priv.User;

-

-# 'Table_name' field must be the same for all the tables:

-

-select

- if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,

- if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User

-from      test_tables_priv

-left join test_columns_priv on test_tables_priv.Table_name=test_columns_priv.Table_name;

-

-drop table test_columns_priv;

-drop table test_tables_priv;

-drop table test_func;

-drop table test_host;

-drop table test_user;

-drop table test_db;

+ if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+ if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+from      test_tables_priv
+left join test_columns_priv on test_tables_priv.Table_name=test_columns_priv.Table_name;
 
+drop table test_columns_priv;
+drop table test_tables_priv;
+drop table test_func;
+drop table test_host;
+drop table test_user;
+drop table test_db;