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

[Andrei Elkin]

#At file:///home/andrei/MySQL/BZR/FIXES/5.1-rpl-bug41003-40129-log_bin_trust_no-threads/

 2704 Andrei Elkin	2008-11-27
      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 succeeded
    but must have failed; 3. did not demand the bin-log be set ON.
=== modified file 'mysql-test/r/log_bin_trust_function_creators_func.result'
--- a/mysql-test/r/log_bin_trust_function_creators_func.result	2008-09-10 10:50:39 +0000
+++ b/mysql-test/r/log_bin_trust_function_creators_func.result	2008-11-27 10:57:38 +0000
@@ -11,10 +11,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
@@ -22,8 +19,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-09-10 10:50:39 +0000
+++ b/mysql-test/t/log_bin_trust_function_creators_func.test	2008-11-27 10:57:38 +0000
@@ -20,6 +20,8 @@
 #                                                                             #
 ###############################################################################
 
+source include/have_log_bin.inc;
+
 --disable_warnings
 drop table if exists t1;
 --enable_warnings
@@ -46,10 +48,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
@@ -59,8 +61,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);