I want to create a stored function in MySQL. I've been granted ALL PRIVILEGES, what I think contains also the required SUPER privilege. And binary logging is enabled.
While creating a function I get the 1419 error:
Error Code: 1419. 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)
I read through the MySQL manuals and it looks like this binary logging issue should only apply to NOT DETERMINISTIC functions, which change data. I've created a simple example function which points out my question more clearly:
DROP FUNCTION IF EXISTS getIdTest;
DELIMITER $$
CREATE FUNCTION getIdTest( pv_order_nr VARCHAR( 45 ) )
RETURNS INT UNSIGNED
COMMENT 'Gets an order number and returns an ID'
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE lv_id INT UNSIGNED;
-- DOES THIS COMMAND MAKE THE FUNCTION NOT-DETERMINISTIC?
CREATE TEMPORARY TABLE tmp_log(
order_nr VARCHAR(45)
, message VARCHAR(255)
, created_at DATETIME
);
-- AND/OR DOES THIS COMMAND MAKE THE FUNCTION NON-DETERMINISTIC?
INSERT INTO tmp_log
SET order_nr = pv_order_nr
, message = CONCAT( 'Id read for order ', pv_order_nr, '.')
, created_at = NOW();
SELECT so.id_sales_order
INTO lv_id
FROM sales_order AS so
WHERE so.order_nr = pv_order_nr
LIMIT 1;
RETURN lv_id;
END
$$
DELIMITER ;
As you see my function is declared as DETERMINISTIC.
My question is, does the second statement in the function routine body (CREATE TEMPORARY TABLE) make the function NOT DETERMINISTIC?
If I omit this statement, does the third statement (INSERT INTO --a temporary table--) make the function NOT DETERMINISTIC as well?
Thanks for reading this :)
Felix
