Bug #62304 Unable to call non-deterministic stored db functions with "CONTAINS_SQL"
Submitted: 30 Aug 2011 17:04 Modified: 30 Jan 2012 18:42
Reporter: Alice Wang Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:mysql 5.1.58 OS:Linux
Assigned to: CPU Architecture:Any
Tags: Unable to call non-deterministic stored db functions with "CONTAINS_SQL"

[30 Aug 2011 17:04] Alice Wang
One of our production mysql database server (Server version: 5.1.58-1~dotdeb.0-log)  is unable to call non-deterministic stored db functions with "CONTAINS_SQL". the ERROR message is "ERROR 1418 (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)". However, executing the same function in other mysql database servers (mysql 5.1.58,mysql 5.1.57) have no error at all.

we need to identify the mysql configuration problem that's causing different behavior in one database server than other mysql database server. 

How to repeat:
create a function as the following
select 'fn_1';
57	drop function if exists fn_1;
58	delimiter |
59	create function fn_1() returns datetime
60	  not deterministic
61	  contains sql
62	  begin
63	    DECLARE end_date DATETIME;
64	    SET end_date = DATE_FORMAT(CURDATE(),'%Y-%m-01 03:59:59');
65	    RETURN end_date;
66	  end
67	|
68	delimiter ;
Then run "select fn_1();" , got ERROR 1418.

Suggested fix:
update mysql.proc set sql_data_access="READS_SQL_DATA" where db='test_db' and name='fn_1';
Then run "select fn_1();" without error.
[30 Dec 2011 18:42] Valeriy Kravchuk
Please, send my.cnf from system affected by this problem and from any of systems where the same MySQL server version runs, but not affected by this problem. I think this has something to do with binary logging NOT enabled on systems without problem.
[31 Jan 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".