StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>here you have a post that saves me in the past about this error: <a href="http://forum.9kgames.com/default.aspx?g=posts&m=17" rel="nofollow noreferrer">http://forum.9kgames.com/default.aspx?g=posts&m=17</a></p> <p>in my case I only had to specify the <code>DETERMINISTIC</code> in the create function:</p> <pre><code>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 </code></pre> <p>hope it helps</p> <p><strong>UPDATE: link is not working anymore so here is a copy from archive.org: <a href="https://web.archive.org/web/20120310020353/http://forum.9kgames.com/default.aspx?g=posts&m=17" rel="nofollow noreferrer">https://web.archive.org/web/20120310020353/http://forum.9kgames.com/default.aspx?g=posts&m=17</a></strong></p> <p>This warning is rising when </p> <p>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:</p> <p>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 <code>CREATE FUNCTION</code> statement to be accepted, at least one of <code>DETERMINISTIC</code>, <code>NO SQL</code>, or <code>READS SQL DATA</code> must be specified explicitly. Otherwise an error occurs: Code:</p> <pre><code>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). </code></pre> <p>This function is deterministic (and does not modify data), so it is safe: Code:</p> <pre><code>CREATE FUNCTION f1(i INT) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN RETURN i; END; </code></pre> <p>This function uses UUID() which is not deterministic, so the function also is not deterministic and is not safe: Code:</p> <pre><code>CREATE FUNCTION f2() RETURNS CHAR(36) CHARACTER SET utf8 BEGIN RETURN UUID(); END; </code></pre> <p>This function modifies data, so it may not be safe: Code:</p> <pre><code>CREATE FUNCTION f3(p_id INT) RETURNS INT BEGIN UPDATE t SET modtime = NOW() WHERE id = p_id; RETURN ROW_COUNT(); END; </code></pre> <p>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 <code>DETERMINISTIC</code>, 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. </p> <p>2) Although you’ve finished the step 1, in most cases, you may still need SUPER privilege to set the global variable <code>log_bin_trust_function_creators</code> 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 <code>log_bin_trust_function_creators</code> system variable to 1. By default, this variable has a value of 0, but you can change it like this: </p> <pre><code>mysql> SET GLOBAL log_bin_trust_function_creators = 1; </code></pre> <p>You can also set this variable by using the <code>log_bin_trust_function_creators</code> option when starting the server. If binary logging is not enabled, <code>log_bin_trust_function_creators</code> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload