bzr commit into mysql-5.1-bugteam branch (aelkin:2739) Bug#41003

[Andrei Elkin]

#At file:///home/andrei/MySQL/BZR/FIXES/5.1-bt-bug41003-log_bin_trust/ based on revid:gshchepa@stripped

 2739 Andrei Elkin	2008-12-12
      Bug #41003 log_bin_trust_function_creators_func test explicitly warns on a bug in it
      
      The test 
      1. did not verify that CREATE FUNCTION shall fails in a case of active binlog
         and @@log_bin_trust_function_creators is zero if there is no DETERMINISTIC qualifier
         and super user privilege;
      2. contained an explit warning on that CREATE FUNCTION actually succeeded whereas
         it was supposed to fail;
      3. did not demand the bin-log be set ON even though it has contained the opt file
         explictily setting the name for the binlog file.
            
      Fixed 1-3 with modifying the test accordingly.
removed:
  mysql-test/t/log_bin_trust_function_creators_func-master.opt
modified:
  mysql-test/r/log_bin_trust_function_creators_func.result
  mysql-test/t/log_bin_trust_function_creators_func.test

per-file messages:
  mysql-test/r/log_bin_trust_function_creators_func.result
    Bug #41003 changed results.
  mysql-test/t/log_bin_trust_function_creators_func-master.opt
    removed unnecessary file, the specificly requested binlog file name was not used in
    the test.
  mysql-test/t/log_bin_trust_function_creators_func.test
    corrected the test that previously: 1.  did not verify that CREATE FUNCTION shall fail
    in some cases; 2. contained an explit warning on that CREATE FUNCTION actually
=== modified file 'mysql-test/r/log_bin_trust_function_creators_func.result'
--- a/mysql-test/r/log_bin_trust_function_creators_func.result	2008-04-10 13:14:28 +0000
+++ b/mysql-test/r/log_bin_trust_function_creators_func.result	2008-12-12 19:15:56 +0000
@@ -10,10 +10,7 @@ create table t2 (a INT);
 SELECT @@log_bin_trust_function_creators;
 @@log_bin_trust_function_creators
 0
-SELECT @@sql_log_bin;
-@@sql_log_bin
-1
-## Creating new function f1 ##
+##  Creating new function f1 fails because no DETERMINISTIC ###
 CREATE FUNCTION f1(a INT) RETURNS INT
 BEGIN
 IF (a < 3) THEN 
@@ -21,8 +18,24 @@ INSERT INTO t2 VALUES (a);
 END IF;
 RETURN 1;
 END|
-'Bug: Create Function should give error here because non-super user';
-'is creating function here';
+ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+## Creating new function f1 fails because non-super user ##
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+IF (a < 3) THEN
+INSERT INTO t2 VALUES (a);
+END IF;
+RETURN 1;
+END|
+ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+## Creating new function f1 succeeds ##
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+IF (a < 3) THEN
+INSERT INTO t2 VALUES (a);
+END IF;
+RETURN 1;
+END|
 ## Creating new table t1 ##
 CREATE TABLE t1 (a INT);
 ## Inserting some records in t1 ##

=== removed file 'mysql-test/t/log_bin_trust_function_creators_func-master.opt'
--- a/mysql-test/t/log_bin_trust_function_creators_func-master.opt	2008-04-10 13:14:28 +0000
+++ b/mysql-test/t/log_bin_trust_function_creators_func-master.opt	1970-01-01 00:00:00 +0000
@@ -1 +0,0 @@
---log-bin=test.log

=== modified file 'mysql-test/t/log_bin_trust_function_creators_func.test'
--- a/mysql-test/t/log_bin_trust_function_creators_func.test	2008-04-10 13:14:28 +0000
+++ b/mysql-test/t/log_bin_trust_function_creators_func.test	2008-12-12 19:15:56 +0000
@@ -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);