Changes to WL#4681 to merge to 5.1-main tree.

8 files changed, 305 insertions, 88 deletions

diff --git a/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func-master.opt b/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func-master.opt
deleted file mode 100644
index 2dd562ea1aa..00000000000
--- a/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---log-bin=test.log
diff --git a/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func.test b/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func.test
index db91128b225..38207babc03 100644
--- a/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func.test
+++ b/mysql-test/suite/sys_vars/t/log_bin_trust_function_creators_func.test
@@ -20,6 +20,8 @@
 #                                                                             #
 ###############################################################################
 
+source include/have_log_bin.inc;
+
 --disable_warnings
 drop table if exists t1;
 --enable_warnings
@@ -45,10 +47,10 @@ connect (test_con1,localhost,tt,,);
 connection test_con1;
 
 SELECT @@log_bin_trust_function_creators;
-SELECT @@sql_log_bin;
 
---echo ## Creating new function f1 ##
+--echo ##  Creating new function f1 fails because no DETERMINISTIC ###
 delimiter |;
+--error ER_BINLOG_UNSAFE_ROUTINE
 CREATE FUNCTION f1(a INT) RETURNS INT
 BEGIN
    IF (a < 3) THEN 
@@ -58,8 +60,30 @@ BEGIN
 END|
 delimiter ;|
 
---echo 'Bug: Create Function should give error here because non-super user';
---echo 'is creating function here';
+--echo ## Creating new function f1 fails because non-super user ##
+delimiter |;
+--error ER_BINLOG_CREATE_ROUTINE_NEED_SUPER
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+   IF (a < 3) THEN
+    INSERT INTO t2 VALUES (a);
+  END IF;
+  RETURN 1;
+END|
+delimiter ;|
+
+connection default;
+
+--echo ## Creating new function f1 succeeds ##
+delimiter |;
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+   IF (a < 3) THEN
+    INSERT INTO t2 VALUES (a);
+  END IF;
+  RETURN 1;
+END|
+delimiter ;|
 
 --echo ## Creating new table t1 ##
 CREATE TABLE t1 (a INT);
diff --git a/mysql-test/suite/sys_vars/t/myisam_data_pointer_size_func.test b/mysql-test/suite/sys_vars/t/myisam_data_pointer_size_func.test
index 36ad31acf6c..985546cbde4 100644
--- a/mysql-test/suite/sys_vars/t/myisam_data_pointer_size_func.test
+++ b/mysql-test/suite/sys_vars/t/myisam_data_pointer_size_func.test
@@ -105,6 +105,7 @@ SELECT count(*) from t1;
 DROP PROCEDURE  sp_addRec;
 DROP TABLE t1;
 
+SET @@global.myisam_data_pointer_size = default;
 ################################################################
 # End of functionality Testing for myisam_data_pointer_size    #
 ################################################################
diff --git a/mysql-test/suite/sys_vars/t/rpl_init_slave_func-slave.opt b/mysql-test/suite/sys_vars/t/rpl_init_slave_func-slave.opt
deleted file mode 100644
index 337e8a60d97..00000000000
--- a/mysql-test/suite/sys_vars/t/rpl_init_slave_func-slave.opt
+++ /dev/null
@@ -1 +0,0 @@
---init-slave="set global max_connections=500"
diff --git a/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test b/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test
index b3d647f4394..f17cc1289b1 100644
--- a/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test
+++ b/mysql-test/suite/sys_vars/t/rpl_init_slave_func.test
@@ -10,86 +10,92 @@
 #                                                                             #
 # Creation Date: 2008-03-08                                                   #
 # Author:  Rizwan                                                             #
+# Modified: HHunger 2008-09-29 Fixed the bug by inserting the usual wait and  #
+#                              SQL-Satements to control master and slave,     #
+#                              deleted the sleep and made improvements like:  #
+#                              - Replaced the opt file by dynamic variables,  #
+#                              - Made the tests independant of the initial    #
+#                                values of the global variables,              #
+#                              - Reduced the test to the needed test case to  #
+#                                save run time,                               #
+#                              - Beautification.                              #
 #                                                                             #
 # Description: Test Cases of Dynamic System Variable init_slave               #
 #              that checks the behavior of this variable                      #
 #                                                                             #
-# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
-#  server-system-variables.html                                               #
+# Reference:                                                                  #
+# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html         #
 #                                                                             #
 ###############################################################################
 
 source include/master-slave.inc;
-
---echo '#--------------------FN_DYNVARS_037_01-------------------------#'
-##################################################################
-# Check if setting init_slave is changed in every new connection # 
-##################################################################
-
-SET @@global.init_slave = "SET @a = 10";
-
---echo 'connect (con1,localhost,root,,)'
-connect (con1,localhost,root,,);
---echo 'connection con1'
-connection con1;
-SELECT @@global.init_slave;
-disconnect con1;
---echo 'connection master'
-connection master;
-
---echo '#--------------------FN_DYNVARS_037_02-------------------------#'
-####################################################
-# Begin the functionality Testing of init_slave    #
-####################################################
-
-#====================================================
---echo 'check if value in slave opt file is executed'
-#====================================================
-save_master_pos;
---echo 'connection slave'
+--echo connection slave
 connection slave;
-sleep 1;
-show variables like 'init_slave';
-show variables like 'max_connections';
-sync_with_master;
-reset master;
+#
+# save the current values
 
-#===============================================================
---echo 'check if value in slave opt file doesnt apply to master'
-#===============================================================
+SET @start_max_connections= @@global.max_connections;
+SET @start_init_slave= @@global.init_slave;
 
---echo 'connection master'
-connection master;
-show variables like 'init_slave';
-show variables like 'max_connections';
-save_master_pos;
---echo 'connection slave'
-connection slave;
-sync_with_master;
-
-#=======================================================
---echo 'try creating a temporary variable in init_slave'
-#=======================================================
-
-SET @@global.init_slave = "SET @a=5";
+# setting of a global value with an effect on the next start of the slave server
+# check that @@global.init_slave could be set
+let $my_init_slave=
+   'SET @@global.max_connections = @@global.max_connections + 1';
+eval SET @@global.init_slave = $my_init_slave;
 
-stop slave;
---wait_for_slave_to_stop
-reset slave;
-# Clean up old test tables
+# show the data type of the variable
 --disable_warnings
-drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+DROP TABLE IF EXISTS t1;
+CREATE TEMPORARY TABLE t1 AS SELECT @@global.init_slave AS my_column;
 --enable_warnings
-start slave;
-
+DESCRIBE t1;
+DROP TABLE t1;
+#
+# check that the new setting of @@global.init_slave becomes immediately visible
+eval SELECT @@global.init_slave = $my_init_slave;
+--echo Expect 1
+# wait for the slave threads have set the global variable.
+let $wait_timeout= 90;
+let $wait_condition= SELECT @@global.max_connections = @start_max_connections;
+--source include/wait_condition_sp.inc
+# check that the action in init_slave does not happen immediately
+SELECT @@global.max_connections= @start_max_connections;
+--echo Expect 1
+#
+# reset of the server
+STOP SLAVE;
+--wait_for_slave_to_stop
+RESET MASTER;
+RESET SLAVE;
+START SLAVE;
+source include/wait_for_slave_to_start.inc;
+#
+# wait for the slave threads have set the global variable.
+let $wait_timeout= 90;
+let $wait_condition= SELECT @@global.max_connections = @start_max_connections + 1;
+--source include/wait_condition_sp.inc
+# check that the action in init_slave was executed and had the intended effect
+SELECT @@global.max_connections = @start_max_connections + 1;
+--echo Expect 1
+#
+# Setting a variable(which is local to a session) and must not be visible
+SET @@global.init_slave = "SET @a=5";
+#
+STOP SLAVE;
+--wait_for_slave_to_stop
+RESET MASTER;
+RESET SLAVE;
+START SLAVE;
+source include/wait_for_slave_to_start.inc;
+#
 SHOW VARIABLES LIKE 'init_slave';
+# expect NULL
 SELECT @a;
-
---echo 'Bug#35365 SET statement in init_slave not execute if slave is restarted'
-
-# Restore value
-set global max_connections= default;
-
+--echo Expect NULL
+#
+# Clean up
+SET @@global.max_connections= @start_max_connections;
+SET @@global.init_slave= @start_init_slave;
 ##################################################
 # End of functionality Testing for init_slave    #
 ##################################################
diff --git a/mysql-test/suite/sys_vars/t/tmp_table_size_basic.test b/mysql-test/suite/sys_vars/t/tmp_table_size_basic.test
new file mode 100644
index 00000000000..c2ff51d50ca
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/tmp_table_size_basic.test
@@ -0,0 +1,206 @@
+########################### tmp_table_size_basic.test ##########################
+#                                                                              #
+# Variable Name: tmp_table_size                                                #
+# Scope: GLOBAL | SESSION                                                      #
+# Access Type: Dynamic                                                         #
+# Data Type: numeric                                                           #
+# Default Value: system dependend                                              #
+# Range: 1024-system dependend                                                 #
+#                                                                              #
+#                                                                              #
+# Creation Date: 2008-02-13                                                    #
+# Author:  Salman                                                              #
+#                                                                              #
+# Description: Test Cases of Dynamic System Variable tmp_table_size            #
+#              that checks the behavior of this variable in the following ways #
+#              * Default Value                                                 #
+#              * Valid & Invalid values                                        #
+#              * Scope & Access method                                         #
+#              * Data Integrity                                                #
+# Modified: 2008-12-04 HHunger                                                 #
+#              removed the differences between 64 and 32 bit platforms         #
+#                                                                              #
+# Reference:                                                                   #
+#    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html       #
+#                                                                              #
+################################################################################
+
+--source include/load_sysvars.inc
+
+##############################################################
+#           START OF tmp_table_size TESTS                    #
+##############################################################
+
+#############################################################
+#                 Save initial value                        #
+#############################################################
+
+SET @start_global_value = @@global.tmp_table_size;
+SET @start_session_value = @@session.tmp_table_size;
+
+--echo '#--------------------FN_DYNVARS_005_01-------------------------#'
+##############################################################
+#     Display the DEFAULT value of tmp_table_size            #
+##############################################################
+
+SET @@global.tmp_table_size = 100;
+SET @@global.tmp_table_size = DEFAULT;
+
+SET @@session.tmp_table_size = 200;
+SET @@session.tmp_table_size = DEFAULT;
+
+--echo '#--------------------FN_DYNVARS_005_02-------------------------#'
+########################################################################
+#     Check the DEFAULT value of tmp_table_size                        #
+########################################################################
+# The DEFAULT value is system dependend.
+# Therefore we have only a plausibility check here
+SELECT @@global.tmp_table_size >= 16777216;
+
+SELECT @@session.tmp_table_size >= 16777216;
+
+--echo '#--------------------FN_DYNVARS_005_03-------------------------#'
+########################################################################
+# Change the value of tmp_table_size to a valid value for GLOBAL Scope #
+########################################################################
+
+SET @@global.tmp_table_size = 1024;
+SELECT @@global.tmp_table_size;
+SET @@global.tmp_table_size = 60020;
+SELECT @@global.tmp_table_size;
+SET @@global.tmp_table_size = 4294967295;
+SELECT @@global.tmp_table_size;
+
+
+--echo '#--------------------FN_DYNVARS_005_04-------------------------#'
+#########################################################################
+# Change the value of tmp_table_size to a valid value for SESSION Scope #
+#########################################################################
+
+SET @@session.tmp_table_size = 1024;
+SELECT @@session.tmp_table_size;
+
+SET @@session.tmp_table_size = 4294967295;
+SELECT @@session.tmp_table_size;
+SET @@session.tmp_table_size = 65535;
+SELECT @@session.tmp_table_size;
+
+
+--echo '#------------------FN_DYNVARS_005_05-----------------------#'
+##########################################################
+# Change the value of tmp_table_size to an invalid value #
+##########################################################
+
+SET @@global.tmp_table_size = 0;
+SELECT @@global.tmp_table_size;
+
+SET @@global.tmp_table_size = -1024;
+SELECT @@global.tmp_table_size;
+
+SET @@global.tmp_table_size = 1000;
+SELECT @@global.tmp_table_size;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.tmp_table_size = ON;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.tmp_table_size = OFF;
+
+SET @@global.tmp_table_size = True;
+SELECT @@global.tmp_table_size;
+
+SET @@global.tmp_table_size = False;
+SELECT @@global.tmp_table_size;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.tmp_table_size = 65530.34;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.tmp_table_size ="Test";
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@session.tmp_table_size = ON;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@session.tmp_table_size = OFF;
+
+SET @@session.tmp_table_size = True;
+SELECT @@session.tmp_table_size;
+
+SET @@session.tmp_table_size = False;
+SELECT @@session.tmp_table_size;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@session.tmp_table_size = "Test";
+
+SET @@session.tmp_table_size = 12345678901;
+
+# With a 64 bit mysqld:12345678901,with a 32 bit mysqld: 4294967295
+SELECT @@session.tmp_table_size IN (12345678901,4294967295);
+
+--echo '#------------------FN_DYNVARS_005_06-----------------------#'
+####################################################################
+#   Check if the value in GLOBAL Table matches value in variable   #
+####################################################################
+
+SELECT @@global.tmp_table_size = VARIABLE_VALUE
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
+WHERE VARIABLE_NAME='tmp_table_size';
+
+--echo '#------------------FN_DYNVARS_005_07-----------------------#'
+####################################################################
+#  Check if the value in SESSION Table matches value in variable   #
+####################################################################
+
+SELECT @@session.tmp_table_size = VARIABLE_VALUE
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES
+WHERE VARIABLE_NAME='tmp_table_size';
+
+--echo '#---------------------FN_DYNVARS_001_09----------------------#'
+########################################################################
+#  Check if global and session variables are independent of each other #
+########################################################################
+
+SET @@global.tmp_table_size = 1024;
+SET @@tmp_table_size = 4294967295;
+SELECT @@tmp_table_size = @@global.tmp_table_size;
+
+--echo '#---------------------FN_DYNVARS_001_10----------------------#'
+##################################################################
+#    Check if accessing variable with SESSION,LOCAL and without  #
+#    SCOPE points to same session variable                       #
+##################################################################
+
+SET @@tmp_table_size = 100;
+SELECT @@tmp_table_size = @@local.tmp_table_size;
+SELECT @@local.tmp_table_size = @@session.tmp_table_size;
+
+
+--echo '#---------------------FN_DYNVARS_001_11----------------------#'
+#########################################################################
+#   Check if tmp_table_size can be accessed with and without @@ sign    #
+#########################################################################
+
+SET tmp_table_size = 1027;
+SELECT @@tmp_table_size;
+
+--Error ER_UNKNOWN_TABLE
+SELECT local.tmp_table_size;
+
+--Error ER_UNKNOWN_TABLE
+SELECT global.tmp_table_size;
+
+--Error ER_BAD_FIELD_ERROR
+SELECT tmp_table_size = @@session.tmp_table_size;
+
+####################################
+#     Restore initial value        #
+####################################
+
+SET @@global.tmp_table_size = @start_global_value;
+SET @@session.tmp_table_size = @start_session_value;
+
+###################################################
+#                 END OF tmp_table_size TESTS     #
+###################################################
+
diff --git a/mysql-test/suite/sys_vars/t/tmp_table_size_basic_32.test b/mysql-test/suite/sys_vars/t/tmp_table_size_basic_32.test
deleted file mode 100644
index c772b5c896c..00000000000
--- a/mysql-test/suite/sys_vars/t/tmp_table_size_basic_32.test
+++ /dev/null
@@ -1,9 +0,0 @@
-################################################################################
-# Created by Horst Hunger      2008-05-07                                      #
-#                                                                              #
-# Wrapper for 32 bit machines                                                  #
-################################################################################
-
---source include/have_32bit.inc
---source suite/sys_vars/inc/tmp_table_size_basic.inc
-
diff --git a/mysql-test/suite/sys_vars/t/tmp_table_size_basic_64.test b/mysql-test/suite/sys_vars/t/tmp_table_size_basic_64.test
deleted file mode 100644
index 46b3f65be9a..00000000000
--- a/mysql-test/suite/sys_vars/t/tmp_table_size_basic_64.test
+++ /dev/null
@@ -1,9 +0,0 @@
-################################################################################
-# Created by Horst Hunger      2008-05-07                                      #
-#                                                                              #
-# Wrapper for 64 bit machines                                                  #
-################################################################################
-
---source include/have_64bit.inc
---source suite/sys_vars/inc/tmp_table_size_basic.inc
-