| 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 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".
Description: 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'; 56 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.