The following error is returned when trying to use a MySQL function..
#1418 - 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
DROP FUNCTION IF EXISTS `stock_in_stock_ids` $$
CREATE DEFINER=`dynaccount`@`localhost` FUNCTION `stock_in_stock_ids`(_running_total_limit INT, _product_id INT, _group_id INT) RETURNS TEXT
DECLARE done INT DEFAULT FALSE;
DECLARE _running_count INT default 0;
DECLARE _id INT;
DECLARE _count INT;
DECLARE _ids TEXT DEFAULT NULL;
DECLARE _cur CURSOR FOR SELECT id, count FROM stock WHERE group_id=_group_id && type=2 && product_id=_product_id ORDER BY time DESC, id DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
FETCH _cur INTO _id, _count;
IF done THEN
SET _ids = '0';
SET _running_count = _running_count + _count;
SET _ids = CONCAT_WS(',', _ids, _id);
IF _running_count >= _running_total_limit THEN
END LOOP read_loop;
here you have a post that saves me in the past about this error: http://forum.9kgames.com/default.aspx?g=posts&m=17
in my case I only had to specify the
DETERMINISTIC in the create function:
CREATE DEFINER=`dynaccount`@`localhost` FUNCTION `stock_in_stock_ids`(_running_total_limit INT, _product_id INT, _group_id INT) RETURNS TEXT DETERMINISTIC READS SQL DATA
hope it helps
UPDATE: link is not working anymore so here is a copy from archive.org: https://web.archive.org/web/20120310020353/http://forum.9kgames.com/default.aspx?g=posts&m=17
This warning is rising when
a). You want to create a stored function and b). As the default MySQL server supports replication, i.e, BINARY LOGGING is turned ON. To resolve this issue, there’re some tips here:
1). For stored function itself. When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a
CREATE FUNCTION statement to be accepted, at least one of
NO SQL, or
READS SQL DATA must be specified explicitly. Otherwise an error occurs:
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).
This function is deterministic (and does not modify data), so it is safe: Code:
CREATE FUNCTION f1(i INT) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN RETURN i; END;
This function uses UUID() which is not deterministic, so the function also is not deterministic and is not safe: Code:
CREATE FUNCTION f2() RETURNS CHAR(36) CHARACTER SET utf8 BEGIN RETURN UUID(); END;
This function modifies data, so it may not be safe: Code:
CREATE FUNCTION f3(p_id INT) RETURNS INT BEGIN UPDATE t SET modtime = NOW() WHERE id = p_id; RETURN ROW_COUNT(); END;
MySQL does not check that a function declared DETERMINISTIC is free of statements that produce nondeterministic results.
Although it is possible to create a deterministic stored function without specifying
DETERMINISTIC, you cannot as of MySQL 5.1.15 execute this function using statement-based binary logging. To execute such a function, you must use row-based or mixed binary logging. Alternatively, if you explicitly specify DETERMINISTIC in the function definition, you can use any kind of logging, including statement-based logging.
2) Although you’ve finished the step 1, in most cases, you may still need SUPER privilege to set the global variable
log_bin_trust_function_creators as true and then run to create stored function.
To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global
log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
log_bin_trust_function_creators option when starting the server.
If binary logging is not enabled,
log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.
Note: If your applications run on a multi-tenant hosting server and the hosting company wouldn’t like to do this, you may need to modify your stored procedures instead of using stored functions.